http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/h2/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/h2/query07.sql b/kylin-it/src/test/resources/query/h2/query07.sql new file mode 100644 index 0000000..9fd2b7f --- /dev/null +++ b/kylin-it/src/test/resources/query/h2/query07.sql @@ -0,0 +1,21 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select count(*) from ( select test_kylin_fact.lstg_format_name from test_kylin_fact + where test_kylin_fact.lstg_format_name='FP-GTC' + group by test_kylin_fact.lstg_format_name ) t
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/h2/query09.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/h2/query09.sql b/kylin-it/src/test/resources/query/h2/query09.sql new file mode 100644 index 0000000..e6e5576 --- /dev/null +++ b/kylin-it/src/test/resources/query/h2/query09.sql @@ -0,0 +1,23 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select count(*) from (select test_cal_dt.week_beg_dt + from test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + group by test_cal_dt.week_beg_dt) t http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/h2/query10.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/h2/query10.sql b/kylin-it/src/test/resources/query/h2/query10.sql new file mode 100644 index 0000000..9511363 --- /dev/null +++ b/kylin-it/src/test/resources/query/h2/query10.sql @@ -0,0 +1,25 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_cal_dt.week_beg_dt + from test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + where test_kylin_fact.lstg_format_name='FP-GTC' + and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01' + group by test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query00.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query00.sql b/kylin-it/src/test/resources/query/sql/query00.sql new file mode 100644 index 0000000..6e4d94b --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query00.sql @@ -0,0 +1,22 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select lstg_format_name, sum(price) as GMV + from test_kylin_fact + where lstg_format_name='FP-GTC' + group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query01.sql b/kylin-it/src/test/resources/query/sql/query01.sql new file mode 100644 index 0000000..9ed1db3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query01.sql @@ -0,0 +1,20 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select LSTG_FORMAT_NAME,slr_segment_cd ,sum(price) as GMV, count(1) as TRANS_CNT from test_kylin_fact + group by LSTG_FORMAT_NAME ,slr_segment_cd http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query02.sql b/kylin-it/src/test/resources/query/sql/query02.sql new file mode 100644 index 0000000..8d27c93 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query02.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select sum(price) as GMV, count(1) as TRANS_CNT from test_kylin_fact http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query03.sql b/kylin-it/src/test/resources/query/sql/query03.sql new file mode 100644 index 0000000..a605594 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query03.sql @@ -0,0 +1,20 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(*) as TRANS_CNT from test_kylin_fact + group by test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query04.sql b/kylin-it/src/test/resources/query/sql/query04.sql new file mode 100644 index 0000000..a3672f5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query04.sql @@ -0,0 +1,21 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT from test_kylin_fact + group by test_kylin_fact.lstg_format_name having sum(price)>5000 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query05.sql b/kylin-it/src/test/resources/query/sql/query05.sql new file mode 100644 index 0000000..512356a --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query05.sql @@ -0,0 +1,22 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT from test_kylin_fact + where test_kylin_fact.lstg_format_name is null + group by test_kylin_fact.lstg_format_name having sum(price)>5000 and count(*)>72 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query06.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query06.sql b/kylin-it/src/test/resources/query/sql/query06.sql new file mode 100644 index 0000000..de99c4f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query06.sql @@ -0,0 +1,23 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT from test_kylin_fact + where test_kylin_fact.lstg_format_name is not null + group by test_kylin_fact.lstg_format_name + having sum(price)>5000 or count(*)>20 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query07.sql b/kylin-it/src/test/resources/query/sql/query07.sql new file mode 100644 index 0000000..0c27d98 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query07.sql @@ -0,0 +1,22 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT from test_kylin_fact + where test_kylin_fact.lstg_format_name='FP-GTC' + group by test_kylin_fact.lstg_format_name having sum(price)>5000 or count(*)>20 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query08.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query08.sql b/kylin-it/src/test/resources/query/sql/query08.sql new file mode 100644 index 0000000..7a31541 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query08.sql @@ -0,0 +1,22 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT from test_kylin_fact + where not (test_kylin_fact.lstg_format_name='FP-GTC') + group by test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query09.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query09.sql b/kylin-it/src/test/resources/query/sql/query09.sql new file mode 100644 index 0000000..d13f8c8 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query09.sql @@ -0,0 +1,27 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_cal_dt.week_beg_dt, count(*) as TRANS_CNT + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + group by test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query10.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query10.sql b/kylin-it/src/test/resources/query/sql/query10.sql new file mode 100644 index 0000000..ad86086 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query10.sql @@ -0,0 +1,30 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT , sum(test_kylin_fact.item_count) as total_items + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_kylin_fact.lstg_format_name='FP-GTC' + and test_cal_dt.week_beg_dt between '2013-05-01' and DATE '2013-08-01' + group by test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query11.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query11.sql b/kylin-it/src/test/resources/query/sql/query11.sql new file mode 100644 index 0000000..e431a63 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query11.sql @@ -0,0 +1,30 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01' + group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt + having sum(price)>500 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query12.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query12.sql b/kylin-it/src/test/resources/query/sql/query12.sql new file mode 100644 index 0000000..012ecfc --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query12.sql @@ -0,0 +1,29 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_cal_dt.week_beg_dt >= DATE '2013-02-10' + group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query13.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query13.sql b/kylin-it/src/test/resources/query/sql/query13.sql new file mode 100644 index 0000000..7d1c481 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query13.sql @@ -0,0 +1,28 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_kylin_fact.lstg_format_name='FP-GTC' + and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01' http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query14.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query14.sql b/kylin-it/src/test/resources/query/sql/query14.sql new file mode 100644 index 0000000..9cca8f5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query14.sql @@ -0,0 +1,36 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,sum(test_kylin_fact.price) as GMV + , count(*) as trans_cnt + FROM test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query15.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query15.sql b/kylin-it/src/test/resources/query/sql/query15.sql new file mode 100644 index 0000000..b189de3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query15.sql @@ -0,0 +1,32 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,sum(price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query16.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query16.sql b/kylin-it/src/test/resources/query/sql/query16.sql new file mode 100644 index 0000000..2c64347 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query16.sql @@ -0,0 +1,35 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,sum(price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query17.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query17.sql b/kylin-it/src/test/resources/query/sql/query17.sql new file mode 100644 index 0000000..c6cb4e5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query17.sql @@ -0,0 +1,38 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,sum(price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_cal_dt.week_beg_dt between DATE '2013-09-01' and DATE '2013-10-01' + and test_category_groupings.meta_categ_name='Collectibles' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query18.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query18.sql b/kylin-it/src/test/resources/query/sql/query18.sql new file mode 100644 index 0000000..30939be --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query18.sql @@ -0,0 +1,34 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,sum(price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_cal_dt.week_beg_dt between DATE '2013-09-01' and DATE '2013-10-01' + and test_category_groupings.categ_lvl2_name='Comics' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query19.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query19.sql b/kylin-it/src/test/resources/query/sql/query19.sql new file mode 100644 index 0000000..c6cb4e5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query19.sql @@ -0,0 +1,38 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,sum(price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_cal_dt.week_beg_dt between DATE '2013-09-01' and DATE '2013-10-01' + and test_category_groupings.meta_categ_name='Collectibles' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query20.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query20.sql b/kylin-it/src/test/resources/query/sql/query20.sql new file mode 100644 index 0000000..5958037 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query20.sql @@ -0,0 +1,34 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,sum(price) as GMV, count(*) as TRANS_CNT , sum(test_kylin_fact.item_count) as total_items + FROM test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_cal_dt.week_beg_dt between DATE '2013-09-01' and DATE '2013-10-01' + and test_category_groupings.categ_lvl3_name='Other' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query21.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query21.sql b/kylin-it/src/test/resources/query/sql/query21.sql new file mode 100644 index 0000000..5a7c446 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query21.sql @@ -0,0 +1,40 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_cal_dt.week_beg_dt between DATE '2013-02-01' and DATE '2013-03-01' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query22.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query22.sql b/kylin-it/src/test/resources/query/sql/query22.sql new file mode 100644 index 0000000..8aa7f66 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query22.sql @@ -0,0 +1,42 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_cal_dt.week_beg_dt between DATE '2012-02-01' and DATE '2013-10-01' + and site_name='Canada' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query23.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query23.sql b/kylin-it/src/test/resources/query/sql/query23.sql new file mode 100644 index 0000000..6d4e5cb --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query23.sql @@ -0,0 +1,41 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_cal_dt.week_beg_dt between DATE '2013-02-01' and DATE '2013-10-01' + and site_name='Ebay' + and test_category_groupings.categ_lvl3_name='Other' + and test_kylin_fact.lstg_format_name='Auction' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query24.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query24.sql b/kylin-it/src/test/resources/query/sql/query24.sql new file mode 100644 index 0000000..22c376e --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query24.sql @@ -0,0 +1,45 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_seller_type_dim.seller_type_desc + ,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + inner JOIN edw.test_seller_type_dim as test_seller_type_dim + ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_seller_type_dim.seller_type_desc + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query25.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query25.sql b/kylin-it/src/test/resources/query/sql/query25.sql new file mode 100644 index 0000000..fe19065 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query25.sql @@ -0,0 +1,46 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_seller_type_dim.seller_type_desc + ,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + inner JOIN edw.test_seller_type_dim as test_seller_type_dim + ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + where test_cal_dt.week_beg_dt between DATE '2013-01-01' and DATE '2013-06-01' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_seller_type_dim.seller_type_desc + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query26.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query26.sql b/kylin-it/src/test/resources/query/sql/query26.sql new file mode 100644 index 0000000..c908c6c --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query26.sql @@ -0,0 +1,48 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_seller_type_dim.seller_type_desc + ,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + inner JOIN edw.test_seller_type_dim as test_seller_type_dim + ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + where test_cal_dt.week_beg_dt between DATE '2013-01-01' and DATE '2013-06-04' + and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.meta_categ_name='Clothing, Shoes & Accessories') and + test_category_groupings.categ_lvl3_name <>'Other' and test_sites.site_name='Ebay' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_seller_type_dim.seller_type_desc + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query27.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query27.sql b/kylin-it/src/test/resources/query/sql/query27.sql new file mode 100644 index 0000000..0c204ed --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query27.sql @@ -0,0 +1,47 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_seller_type_dim.seller_type_desc + ,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + inner JOIN edw.test_seller_type_dim as test_seller_type_dim + ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + where + (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses') + and test_sites.site_name='Ebay' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_seller_type_dim.seller_type_desc http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query28.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query28.sql b/kylin-it/src/test/resources/query/sql/query28.sql new file mode 100644 index 0000000..58a0b8e --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query28.sql @@ -0,0 +1,30 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + inner JOIN edw.test_seller_type_dim as test_seller_type_dim + ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query29.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query29.sql b/kylin-it/src/test/resources/query/sql/query29.sql new file mode 100644 index 0000000..b2e9c9d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query29.sql @@ -0,0 +1,32 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,sum(price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id + AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query30.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query30.sql b/kylin-it/src/test/resources/query/sql/query30.sql new file mode 100644 index 0000000..4d7011f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query30.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select sum(price) as GMV, count(1) as TRANS_CNT , sum(test_kylin_fact.item_count) as total_items from test_kylin_fact limit 50 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query31.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query31.sql b/kylin-it/src/test/resources/query/sql/query31.sql new file mode 100644 index 0000000..faa7863 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query31.sql @@ -0,0 +1,40 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_sites.site_name = 'è±å½' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query32.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query32.sql b/kylin-it/src/test/resources/query/sql/query32.sql new file mode 100644 index 0000000..c389cbe --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query32.sql @@ -0,0 +1,39 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query33.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query33.sql b/kylin-it/src/test/resources/query/sql/query33.sql new file mode 100644 index 0000000..ff56aee --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query33.sql @@ -0,0 +1,43 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_kylin_fact.seller_id + ,test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_kylin_fact.seller_id = 10000002 + group by + test_kylin_fact.seller_id + ,test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query34.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query34.sql b/kylin-it/src/test/resources/query/sql/query34.sql new file mode 100644 index 0000000..821df2c --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query34.sql @@ -0,0 +1,41 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_kylin_fact.seller_id = 10000002 + group by + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query35.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query35.sql b/kylin-it/src/test/resources/query/sql/query35.sql new file mode 100644 index 0000000..c744f73 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query35.sql @@ -0,0 +1,41 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_kylin_fact.seller_id = 10000002 or test_kylin_fact.lstg_format_name = 'FP-non GTC' + group by + test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query36.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query36.sql b/kylin-it/src/test/resources/query/sql/query36.sql new file mode 100644 index 0000000..74b8ede --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query36.sql @@ -0,0 +1,34 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_category_groupings.meta_categ_name + ,sum(test_kylin_fact.price) as GMV_SUM + ,max(test_kylin_fact.price) as GMV_MAX + ,min(test_kylin_fact.price) as GMV_MIN + ,count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_kylin_fact.seller_id = 10000002 or test_kylin_fact.lstg_format_name = 'FP-non GTC' + group by + test_category_groupings.meta_categ_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query37.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query37.sql b/kylin-it/src/test/resources/query/sql/query37.sql new file mode 100644 index 0000000..eebd515 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query37.sql @@ -0,0 +1,36 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_cal_dt.week_beg_dt, sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + where + (test_kylin_fact.lstg_format_name > '') + and ( + (test_kylin_fact.lstg_format_name='FP-GTC') + OR + (test_cal_dt.week_beg_dt between DATE '2013-05-20' and DATE '2013-05-21') + ) + and ( + (test_kylin_fact.lstg_format_name='ABIN') + OR + (test_cal_dt.week_beg_dt between DATE '2013-05-20' and DATE '2013-05-21') + ) + group by test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query38.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query38.sql b/kylin-it/src/test/resources/query/sql/query38.sql new file mode 100644 index 0000000..8a832f3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query38.sql @@ -0,0 +1,22 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select lstg_format_name, sum(price) as GMV + from test_kylin_fact + where lstg_format_name not in ('FP-GTC', 'ABIN') + group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query39.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query39.sql b/kylin-it/src/test/resources/query/sql/query39.sql new file mode 100644 index 0000000..75155ec --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query39.sql @@ -0,0 +1,20 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select sum(price) as GMV + from test_kylin_fact http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query40.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query40.sql b/kylin-it/src/test/resources/query/sql/query40.sql new file mode 100644 index 0000000..a6705b5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query40.sql @@ -0,0 +1,22 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select cal_dt, lstg_format_name, sum(price) as GMV , sum(test_kylin_fact.item_count) as total_items + from test_kylin_fact + where cal_dt between date '2013-05-06' and date '2013-07-31' + group by cal_dt, lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query41.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query41.sql b/kylin-it/src/test/resources/query/sql/query41.sql new file mode 100644 index 0000000..8b4d566 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query41.sql @@ -0,0 +1,30 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,sum(test_kylin_fact.price) as GMV + ,count(*) as trans_cnt + FROM test_kylin_fact + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id + AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + group by + test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query42.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query42.sql b/kylin-it/src/test/resources/query/sql/query42.sql new file mode 100644 index 0000000..3364a2a --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query42.sql @@ -0,0 +1,27 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_cal_dt.cal_dt, count(*) as TRANS_CNT + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + group by test_cal_dt.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query43.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query43.sql b/kylin-it/src/test/resources/query/sql/query43.sql new file mode 100644 index 0000000..1125699 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query43.sql @@ -0,0 +1,29 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_cal_dt.cal_dt, count(*) as CNT + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_kylin_fact.lstg_format_name='FP-GTC' + and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01' + group by test_cal_dt.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql/query44.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query44.sql b/kylin-it/src/test/resources/query/sql/query44.sql new file mode 100644 index 0000000..ae4f774 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query44.sql @@ -0,0 +1,31 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_category_groupings.meta_categ_name + ,sum(test_kylin_fact.price) as GMV + ,count(*) as trans_cnt + FROM test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + group by + test_category_groupings.meta_categ_name
