http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query46.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query46.sql b/kylin-it/src/test/resources/query/sql/query46.sql new file mode 100644 index 0000000..3bfe9d9 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query46.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 count(*) as CNT from test_category_groupings
http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query47.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query47.sql b/kylin-it/src/test/resources/query/sql/query47.sql new file mode 100644 index 0000000..cbd2c6d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query47.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 count(*) as CNT from edw.test_seller_type_dim http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query48.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query48.sql b/kylin-it/src/test/resources/query/sql/query48.sql new file mode 100644 index 0000000..54ddb31 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query48.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 count(*) as CNT from edw.test_sites http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query49.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query49.sql b/kylin-it/src/test/resources/query/sql/query49.sql new file mode 100644 index 0000000..a28beb6 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query49.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(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.lstg_site_id = test_category_groupings.site_id AND test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_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/59943fee/kylin-it/src/test/resources/query/sql/query50.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query50.sql b/kylin-it/src/test/resources/query/sql/query50.sql new file mode 100644 index 0000000..286e2df --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query50.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 , 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 1 < 3 + group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query51.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query51.sql b/kylin-it/src/test/resources/query/sql/query51.sql new file mode 100644 index 0000000..82cf289 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query51.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 DATE '2013-03-24' <= test_cal_dt.week_beg_dt + group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query52.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query52.sql b/kylin-it/src/test/resources/query/sql/query52.sql new file mode 100644 index 0000000..192f6b9 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query52.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 test_kylin_fact.seller_id in ( 10000002, 10000003, 10000004,10000005,10000006,10000008,10000009,10000001,10000010,10000011) + group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query53.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query53.sql b/kylin-it/src/test/resources/query/sql/query53.sql new file mode 100644 index 0000000..b835ec0 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query53.sql @@ -0,0 +1,24 @@ +-- +-- 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.cal_dt,test_kylin_fact.seller_id, sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT +from test_kylin_fact +where DATE '2012-09-01' <= test_kylin_fact.cal_dt and test_kylin_fact.seller_id = 10000002 + group by test_kylin_fact.cal_dt, +test_kylin_fact.seller_id http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query54.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query54.sql b/kylin-it/src/test/resources/query/sql/query54.sql new file mode 100644 index 0000000..b9532fc --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query54.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 1 <> 1 + group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query55.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query55.sql b/kylin-it/src/test/resources/query/sql/query55.sql new file mode 100644 index 0000000..346a7d7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query55.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 count(*) as c from edw.test_cal_dt as test_cal_dt where extract(YEAR from test_cal_dt.cal_dt) = 2012 http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query56.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query56.sql b/kylin-it/src/test/resources/query/sql/query56.sql new file mode 100644 index 0000000..08e8ec3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query56.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 + 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 + extract(MONTH from test_cal_dt.week_beg_dt) = 12 + group by test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query57.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query57.sql b/kylin-it/src/test/resources/query/sql/query57.sql new file mode 100644 index 0000000..386d248 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query57.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 + 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 extract(MONTH from test_cal_dt.week_beg_dt) = 12 + group by test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query58.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query58.sql b/kylin-it/src/test/resources/query/sql/query58.sql new file mode 100644 index 0000000..0ddeff9 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query58.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 + where + (test_kylin_fact.lstg_format_name='FP-GTC') and extract(MONTH from test_cal_dt.week_beg_dt) = 12 + group by test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query59.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query59.sql b/kylin-it/src/test/resources/query/sql/query59.sql new file mode 100644 index 0000000..0731854 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query59.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 GVM,lstg_format_name from test_kylin_fact group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query60.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query60.sql b/kylin-it/src/test/resources/query/sql/query60.sql new file mode 100644 index 0000000..9881637 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query60.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.cal_dt, sum(test_kylin_fact.price) as sum_price, count(1) as cnt_1, 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 +group by test_kylin_fact.cal_dt +order by 2 desc +limit 3 http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query61.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query61.sql b/kylin-it/src/test/resources/query/sql/query61.sql new file mode 100644 index 0000000..183fd00 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query61.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 count(1) as cnt_1 +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_kylin_fact.cal_dt +order by 1 desc +limit 4 http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query62.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query62.sql b/kylin-it/src/test/resources/query/sql/query62.sql new file mode 100644 index 0000000..7f195e1 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query62.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 , min(cal_dt) as min_cal_dt + , count(*) as TRANS_CNT from test_kylin_fact + 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/59943fee/kylin-it/src/test/resources/query/sql/query63.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query63.sql b/kylin-it/src/test/resources/query/sql/query63.sql new file mode 100644 index 0000000..687cee7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query63.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 min(cal_dt) as min_cal_dt + from test_kylin_fact http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query64.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query64.sql b/kylin-it/src/test/resources/query/sql/query64.sql new file mode 100644 index 0000000..a7f877f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query64.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 , min(cal_dt) as min_cal_dt + , 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/59943fee/kylin-it/src/test/resources/query/sql/query65.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query65.sql b/kylin-it/src/test/resources/query/sql/query65.sql new file mode 100644 index 0000000..e20393b --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query65.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 min(cal_dt) as min_cal_dt, max(cal_dt) as max_cal_dt from test_kylin_fact http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query66.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query66.sql b/kylin-it/src/test/resources/query/sql/query66.sql new file mode 100644 index 0000000..d9f14ee --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query66.sql @@ -0,0 +1,24 @@ +-- +-- 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.cal_dt, max(test_kylin_fact.cal_dt) as mmm 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_kylin_fact.cal_dt order by 2 desc limit 7 http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query67.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query67.sql b/kylin-it/src/test/resources/query/sql/query67.sql new file mode 100644 index 0000000..e145d72 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query67.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 (CASE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" WHEN 'Auction' THEN '111' ELSE '222' END) AS "LSTG_FORMAT_NAME__group_", + SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok" +FROM "TEST_KYLIN_FACT" "TEST_KYLIN_FACT" +group by (CASE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" WHEN 'Auction' THEN '111' ELSE '222' END) ORDER BY 1 ASC http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query68.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query68.sql b/kylin-it/src/test/resources/query/sql/query68.sql new file mode 100644 index 0000000..30f9980 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query68.sql @@ -0,0 +1,26 @@ +-- +-- 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 as bb , sum(price) as aa from test_kylin_fact fact +inner join ( + + +select count(1) as cnt, min(cal_dt) as "mmm", cal_dt as dt from test_kylin_fact group by cal_dt order by 2 desc limit 10 + + +) t0 on (fact.cal_dt = t0.dt) group by cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query69.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query69.sql b/kylin-it/src/test/resources/query/sql/query69.sql new file mode 100644 index 0000000..40377e3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query69.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 lstg_format_name, + sum(price) as GMV, + count(1) as TRANS_CNT + from test_kylin_fact + where (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) = 'Auction' + group by lstg_format_name + order by sum(price) http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query70.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query70.sql b/kylin-it/src/test/resources/query/sql/query70.sql new file mode 100644 index 0000000..a0aa8b3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query70.sql @@ -0,0 +1,24 @@ +-- +-- 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, + count(1) as TRANS_CNT , sum(test_kylin_fact.item_count) as total_items + from test_kylin_fact + where "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" = 'Auction' and (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) = 'Auction' + group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query71.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query71.sql b/kylin-it/src/test/resources/query/sql/query71.sql new file mode 100644 index 0000000..b3abe3d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query71.sql @@ -0,0 +1,24 @@ +-- +-- 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, + count(1) as TRANS_CNT + from test_kylin_fact + where "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" in ('Auction', 'ABIN') and (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) = 'Auction' + group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query72.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query72.sql b/kylin-it/src/test/resources/query/sql/query72.sql new file mode 100644 index 0000000..7eda4ee --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query72.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 test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_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.lstg_format_name='FP-GTC' or extract(MONTH from test_cal_dt.week_beg_dt) = 12 + group by test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query73.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query73.sql b/kylin-it/src/test/resources/query/sql/query73.sql new file mode 100644 index 0000000..04c6155 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query73.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 min("t"."s") as min_pr from (select max(price) as "s" from test_kylin_fact group by lstg_format_name) "t" having (count(1) > 0) http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query74.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query74.sql b/kylin-it/src/test/resources/query/sql/query74.sql new file mode 100644 index 0000000..0949336 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query74.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 min("t"."s") as min_x, max("t"."s") as max_x from (select max(price) as "s" from test_kylin_fact group by lstg_format_name) "t" having ( count(1) > 0) http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query75.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query75.sql b/kylin-it/src/test/resources/query/sql/query75.sql new file mode 100644 index 0000000..ebc8608 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query75.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 (NOT ((CASE WHEN (lstg_format_name IS NULL) THEN 1 WHEN NOT (lstg_format_name IS NULL) THEN 0 ELSE NULL END) <> 0)) + group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query76.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query76.sql b/kylin-it/src/test/resources/query/sql/query76.sql new file mode 100644 index 0000000..b6991f7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query76.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 + group by lstg_format_name, SLR_SEGMENT_CD + having SLR_SEGMENT_CD > 0 http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query77.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query77.sql b/kylin-it/src/test/resources/query/sql/query77.sql new file mode 100644 index 0000000..18e5240 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query77.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 and count(*)>72 http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query78.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query78.sql b/kylin-it/src/test/resources/query/sql/query78.sql new file mode 100644 index 0000000..da0718a --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query78.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 sum(PRICE) as GMV, LSTG_FORMAT_NAME as FORMAT_NAME +from test_kylin_fact +where (LSTG_FORMAT_NAME in ('ABIN')) or (LSTG_FORMAT_NAME>='FP-GTC' and LSTG_FORMAT_NAME<='Others') +group by LSTG_FORMAT_NAME http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query79.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query79.sql b/kylin-it/src/test/resources/query/sql/query79.sql new file mode 100644 index 0000000..76b7928 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query79.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 max(cal_dt) as cnt from test_kylin_fact http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query80.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query80.sql b/kylin-it/src/test/resources/query/sql/query80.sql new file mode 100644 index 0000000..0e2dbb4 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query80.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 + min(test_cal_dt.cal_dt) as mmin + ,max(test_cal_dt.cal_dt) as mmax, + 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 http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query81.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query81.sql b/kylin-it/src/test/resources/query/sql/query81.sql new file mode 100644 index 0000000..93868e7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query81.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 test_cal_dt.week_beg_dt, sum(price) as GMV + 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 (lstg_format_name='FP-GTC' or 'a' = 'b') + group by test_cal_dt.week_beg_dt \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query82.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query82.sql b/kylin-it/src/test/resources/query/sql/query82.sql new file mode 100644 index 0000000..57e9de0 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query82.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_kylin_fact.cal_dt, seller_id + 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_kylin_fact.cal_dt, test_kylin_fact.seller_id order by sum(test_kylin_fact.price) desc limit 20 http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query83.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query83.sql b/kylin-it/src/test/resources/query/sql/query83.sql new file mode 100644 index 0000000..514beb4 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query83.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 + seller_id + 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 + where test_kylin_fact.cal_dt < DATE '2013-02-01' + group by + test_kylin_fact.seller_id order by sum(test_kylin_fact.price) desc limit 20 http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query85.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query85.sql b/kylin-it/src/test/resources/query/sql/query85.sql new file mode 100644 index 0000000..1a51a02 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query85.sql @@ -0,0 +1,26 @@ +-- +-- 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(*) 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 +where test_kylin_fact.cal_dt < DATE '2012-05-01' or test_kylin_fact.cal_dt > DATE '2013-05-01' + http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query86.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query86.sql b/kylin-it/src/test/resources/query/sql/query86.sql new file mode 100644 index 0000000..f6feaaf --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query86.sql @@ -0,0 +1,24 @@ +-- +-- 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.cal_dt, count(*) as mmm 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 lstg_format_name = 'Others' group by test_kylin_fact.cal_dt order by test_kylin_fact.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query87.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query87.sql b/kylin-it/src/test/resources/query/sql/query87.sql new file mode 100644 index 0000000..8e29323 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query87.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. +-- + +-- equals to non-exist value 'GGGG' +select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(*) as TRANS_CNT from test_kylin_fact +where test_kylin_fact.lstg_format_name = 'GGGG' + group by test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query88.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query88.sql b/kylin-it/src/test/resources/query/sql/query88.sql new file mode 100644 index 0000000..7415d3e --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query88.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. +-- + +-- greater than non-exist value 'GGGG' +select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(*) as TRANS_CNT from test_kylin_fact +where test_kylin_fact.lstg_format_name > 'GGGG' + group by test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query89.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query89.sql b/kylin-it/src/test/resources/query/sql/query89.sql new file mode 100644 index 0000000..38e8a75 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query89.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. +-- + +-- greater than non-exist and super big value 'ZZZZ' +select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(*) as TRANS_CNT from test_kylin_fact +where test_kylin_fact.lstg_format_name > 'ZZZZ' + group by test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query90.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query90.sql.disabled b/kylin-it/src/test/resources/query/sql/query90.sql.disabled new file mode 100644 index 0000000..e551a45 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query90.sql.disabled @@ -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 meta_categ_name, count(1) as cnt, sum(price) as GMV + + from test_kylin_fact + left JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + left 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 + left JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + where meta_categ_name not in ('', 'a') + group by meta_categ_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query91.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query91.sql.disabled b/kylin-it/src/test/resources/query/sql/query91.sql.disabled new file mode 100644 index 0000000..cc6dca5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query91.sql.disabled @@ -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 meta_categ_name, count(1) as cnt, sum(price) as GMV + + from test_kylin_fact + left JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + left 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 + left JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + where meta_categ_name is not null + group by meta_categ_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query92.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query92.sql.disabled b/kylin-it/src/test/resources/query/sql/query92.sql.disabled new file mode 100644 index 0000000..3c5b473 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query92.sql.disabled @@ -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 meta_categ_name, count(1) as cnt, sum(price) as GMV + + from test_kylin_fact + left JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + left 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 + left JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + where meta_categ_name not in ('Unknown','ToyHobbies') + group by meta_categ_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query93.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query93.sql.disabled b/kylin-it/src/test/resources/query/sql/query93.sql.disabled new file mode 100644 index 0000000..578b93f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query93.sql.disabled @@ -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 meta_categ_name, count(1) as cnt, sum(price) as GMV + + from test_kylin_fact + left JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + left 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 + left JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + where meta_categ_name not in ('Unknown', 'ToyHobbies', '', 'a', 'BookMagazines') + group by meta_categ_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query94.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query94.sql.disabled b/kylin-it/src/test/resources/query/sql/query94.sql.disabled new file mode 100644 index 0000000..3c5b473 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query94.sql.disabled @@ -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 meta_categ_name, count(1) as cnt, sum(price) as GMV + + from test_kylin_fact + left JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + left 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 + left JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + where meta_categ_name not in ('Unknown','ToyHobbies') + group by meta_categ_name http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query95.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query95.sql b/kylin-it/src/test/resources/query/sql/query95.sql new file mode 100644 index 0000000..4e80d59 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query95.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 upper(lstg_format_name) as lstg_format_name, count(*) as cnt from test_kylin_fact +where lower(lstg_format_name)='abin' and substring(lstg_format_name,1,3) in ('ABI') and upper(lstg_format_name) > 'AAAA' and +lower(lstg_format_name) like '%b%' and char_length(lstg_format_name) < 10 and char_length(lstg_format_name) > 3 and lstg_format_name||'a'='ABINa' +group by lstg_format_name \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql/query96.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query96.sql b/kylin-it/src/test/resources/query/sql/query96.sql new file mode 100644 index 0000000..c9c0c6f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query96.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 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='XXXUnknown' + group by test_cal_dt.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql_all_types/query00.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_all_types/query00.sql b/kylin-it/src/test/resources/query/sql_all_types/query00.sql new file mode 100644 index 0000000..4680ae0 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_all_types/query00.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 ML_tinyint, sum(price) as GMV from test_kylin_fact group by ML_tinyint order by ML_tinyint http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql_all_types/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_all_types/query01.sql b/kylin-it/src/test/resources/query/sql_all_types/query01.sql new file mode 100644 index 0000000..eb76210 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_all_types/query01.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 ML_smallint, sum(price) as GMV from test_kylin_fact group by ML_smallint order by ML_smallint http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql_all_types/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_all_types/query02.sql b/kylin-it/src/test/resources/query/sql_all_types/query02.sql new file mode 100644 index 0000000..40dcacf --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_all_types/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 ML_int, sum(price) as GMV from test_kylin_fact group by ML_int order by ML_int http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql_all_types/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_all_types/query03.sql b/kylin-it/src/test/resources/query/sql_all_types/query03.sql new file mode 100644 index 0000000..325d250 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_all_types/query03.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 ML_bigint, sum(price) as GMV from test_kylin_fact group by ML_bigint order by ML_bigint http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql_all_types/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_all_types/query04.sql b/kylin-it/src/test/resources/query/sql_all_types/query04.sql new file mode 100644 index 0000000..ea09698 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_all_types/query04.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 ML_float, sum(price) as GMV from test_kylin_fact group by ML_float order by ML_float http://git-wip-us.apache.org/repos/asf/kylin/blob/59943fee/kylin-it/src/test/resources/query/sql_all_types/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_all_types/query05.sql b/kylin-it/src/test/resources/query/sql_all_types/query05.sql new file mode 100644 index 0000000..84bca1b --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_all_types/query05.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 ML_double, sum(price) as GMV from test_kylin_fact group by ML_double order by ML_double
