http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_invalid/query30_invalid_SQL.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_invalid/query30_invalid_SQL.sql b/kylin-it/src/test/resources/query/sql_invalid/query30_invalid_SQL.sql new file mode 100644 index 0000000..229d198 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_invalid/query30_invalid_SQL.sql @@ -0,0 +1,54 @@ +-- +-- 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 + ,test_cal_dt.retail_year + ,test_cal_dt.rtl_month_of_rtl_year_id + ,test_cal_dt.retail_week + ,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 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 test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + inner JOIN test_seller_type_dim + ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + where test_cal_dt.retail_year='2013' + and retail_week in(1,2,3,4,5,6,7,7,7) + and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses') + and test_sites.site_name='Ebay' + and test_cal_dt.retail_year not in ('2014') + group by test_cal_dt.week_beg_dt + ,test_cal_dt.retail_year + ,test_cal_dt.rtl_month_of_rtl_year_id + ,test_cal_dt.retail_week + ,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_invalid/query31_invalid_SQL.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_invalid/query31_invalid_SQL.sql b/kylin-it/src/test/resources/query/sql_invalid/query31_invalid_SQL.sql new file mode 100644 index 0000000..8fac5a6 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_invalid/query31_invalid_SQL.sql @@ -0,0 +1,55 @@ +-- +-- 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 + ,test_cal_dt.retail_year + ,test_cal_dt.rtl_month_of_rtl_year_id + ,test_cal_dt.retail_week + ,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_amt) as gmv + , count(*) as trans_cnt + FROM test_kylin_fact + inner JOIN 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 test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + inner JOIN test_seller_type_dim + ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + where test_cal_dt.retail_year='2013' + and retail_week in(1,2,3,4,5,6,7,7,7) + and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses') + and test_sites.site_name='Ebay' + and test_cal_dt.retail_year not in ('2014') + group by test_cal_dt.week_beg_dt + ,test_cal_dt.retail_year + ,test_cal_dt.rtl_month_of_rtl_year_id + ,test_cal_dt.retail_week + ,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_invalid/query32_invalid_SQL.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_invalid/query32_invalid_SQL.sql b/kylin-it/src/test/resources/query/sql_invalid/query32_invalid_SQL.sql new file mode 100644 index 0000000..40b82d8 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_invalid/query32_invalid_SQL.sql @@ -0,0 +1,56 @@ +-- +-- 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 + ,test_cal_dt.retail_year + ,test_cal_dt.rtl_month_of_rtl_year_id + ,test_cal_dt.retail_week + ,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 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 test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + inner JOIN test_seller_type_dim + ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + where test_cal_dt.retail_year='2013' + and retail_week in(1,2,3,4,5,6,7,7,7) + and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses') + and test_sites.site_name='Ebay' + and test_cal_dt.retail_year not in ('2014') + and test_kylin_fact.price<100 + group by test_cal_dt.week_beg_dt + ,test_cal_dt.retail_year + ,test_cal_dt.rtl_month_of_rtl_year_id + ,test_cal_dt.retail_week + ,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_invalid/query33_invalid_SQL.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_invalid/query33_invalid_SQL.sql b/kylin-it/src/test/resources/query/sql_invalid/query33_invalid_SQL.sql new file mode 100644 index 0000000..d18dbcc --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_invalid/query33_invalid_SQL.sql @@ -0,0 +1,56 @@ +-- +-- 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 + ,test_cal_dt.retail_year + ,test_cal_dt.rtl_month_of_rtl_year_id + ,test_cal_dt.retail_week + ,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 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 test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + inner JOIN test_seller_type_dim + ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + where test_cal_dt.retail_year='2013' + and retail_week in(1,2,3,4,5,6,7,7,7) + and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses') + and test_sites.site_name='Ebay' + and test_cal_dt.retail_year not in ('2014') + and test_kylin_fact.trans_id=1000000001 + group by test_cal_dt.week_beg_dt + ,test_cal_dt.retail_year + ,test_cal_dt.rtl_month_of_rtl_year_id + ,test_cal_dt.retail_week + ,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_invalid/query34_invalid_SQL.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_invalid/query34_invalid_SQL.sql b/kylin-it/src/test/resources/query/sql_invalid/query34_invalid_SQL.sql new file mode 100644 index 0000000..4308735 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_invalid/query34_invalid_SQL.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 COUNT(DISTINCT "TableauSQL"."TRANS_CNT") AS "ctd_TRANS_CNT_qk", "TableauSQL"."LSTG_FORMAT_NAME" AS "none_LSTG_FORMAT_NAME_nk" + FROM ( select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(seller_id) as TRANS_CNT + from test_kylin_fact + group by test_kylin_fact.lstg_format_name + ) "TableauSQL" + GROUP BY "TableauSQL"."LSTG_FORMAT_NAME" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_invalid/query_count_distinct_on_dimension.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_invalid/query_count_distinct_on_dimension.sql.disabled b/kylin-it/src/test/resources/query/sql_invalid/query_count_distinct_on_dimension.sql.disabled new file mode 100644 index 0000000..63d83b5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_invalid/query_count_distinct_on_dimension.sql.disabled @@ -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. +-- + +##dd +select count(distinct cal_dt) from test_kylin_fact http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_lookup/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_lookup/query01.sql b/kylin-it/src/test/resources/query/sql_lookup/query01.sql new file mode 100644 index 0000000..eb08c01 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_lookup/query01.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 CAL_DT, WEEK_BEG_DT from edw.test_cal_dt + + + + + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_lookup/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_lookup/query02.sql b/kylin-it/src/test/resources/query/sql_lookup/query02.sql new file mode 100644 index 0000000..b667519 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_lookup/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 CATEG_LVL3_NAME, CATEG_LVL2_NAME, SITE_ID, META_CATEG_NAME, LEAF_CATEG_ID from test_category_groupings http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_lookup/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_lookup/query03.sql b/kylin-it/src/test/resources/query/sql_lookup/query03.sql new file mode 100644 index 0000000..f663627 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_lookup/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 SELLER_TYPE_DESC, SELLER_TYPE_CD from edw.test_seller_type_dim http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_lookup/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_lookup/query04.sql b/kylin-it/src/test/resources/query/sql_lookup/query04.sql new file mode 100644 index 0000000..69a77d7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_lookup/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 SITE_NAME, SITE_ID from edw.test_sites http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_optimize/enable-limit01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_optimize/enable-limit01.sql b/kylin-it/src/test/resources/query/sql_optimize/enable-limit01.sql new file mode 100644 index 0000000..4a62d92 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_optimize/enable-limit01.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 * from test_kylin_fact limit 10 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_orderby/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_orderby/query01.sql b/kylin-it/src/test/resources/query/sql_orderby/query01.sql new file mode 100644 index 0000000..a1d5065 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_orderby/query01.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, sum(price) as c1, count(1) as c2 + 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='ABIN' + and test_cal_dt.week_beg_dt >= DATE '2013-06-09' + group by test_cal_dt.week_beg_dt + order by test_cal_dt.week_beg_dt + + -- optiq 0.8 reports varchar instead of date on week_beg_dt and fail test case http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_orderby/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_orderby/query02.sql b/kylin-it/src/test/resources/query/sql_orderby/query02.sql new file mode 100644 index 0000000..cdcef50 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_orderby/query02.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 lstg_format_name='FP-GTC' + group by lstg_format_name + order by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_orderby/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_orderby/query03.sql b/kylin-it/src/test/resources/query/sql_orderby/query03.sql new file mode 100644 index 0000000..69854e2 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_orderby/query03.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 + group by lstg_format_name + order by sum(price) http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query01.sql b/kylin-it/src/test/resources/query/sql_streaming/query01.sql new file mode 100644 index 0000000..8a5f302 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query01.sql @@ -0,0 +1 @@ +select count(*) AS c from streaming_table \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query02.sql b/kylin-it/src/test/resources/query/sql_streaming/query02.sql new file mode 100644 index 0000000..0fc0f88 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query02.sql @@ -0,0 +1 @@ +select count(*) AS c ,cast(sum(item_count) as BIGINT) as i from streaming_table group by site,day_start \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query03.sql b/kylin-it/src/test/resources/query/sql_streaming/query03.sql new file mode 100644 index 0000000..33ab8cb --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query03.sql @@ -0,0 +1 @@ +select count(*) as c ,hour_start AS h from streaming_table group by hour_start \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query04.sql b/kylin-it/src/test/resources/query/sql_streaming/query04.sql new file mode 100644 index 0000000..5117c37 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query04.sql @@ -0,0 +1 @@ +select count(*) as c,sum(gmv) as g,cast(sum(item_count) as BIGINT) AS i from streaming_table group by minute_start \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query05.sql b/kylin-it/src/test/resources/query/sql_streaming/query05.sql new file mode 100644 index 0000000..566046a --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query05.sql @@ -0,0 +1 @@ +select count(*) as c from streaming_table where day_start >= DATE'2015-01-02' \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query06.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query06.sql b/kylin-it/src/test/resources/query/sql_streaming/query06.sql new file mode 100644 index 0000000..ceabc91 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query06.sql @@ -0,0 +1 @@ +select count(*) as c from streaming_table where minute_start >= {TS '2015-01-02 20:00:00'} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query07.sql b/kylin-it/src/test/resources/query/sql_streaming/query07.sql new file mode 100644 index 0000000..b09759d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query07.sql @@ -0,0 +1 @@ +select count(*) as c from streaming_table where minute_start < {TS '2015-01-02 21:00:00'} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query08.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query08.sql b/kylin-it/src/test/resources/query/sql_streaming/query08.sql new file mode 100644 index 0000000..120ef50 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query08.sql @@ -0,0 +1 @@ +select count(*) as c from streaming_table where minute_start < {TS '2015-01-02 21:00:00'} and minute_start > {TS '2015-01-02 20:00:00'} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query09.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query09.sql b/kylin-it/src/test/resources/query/sql_streaming/query09.sql new file mode 100644 index 0000000..0d29d92 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query09.sql @@ -0,0 +1 @@ +select count(*) as c from streaming_table where minute_start >= {TS '2015-01-02 20:30:00'} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_streaming/query10.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_streaming/query10.sql b/kylin-it/src/test/resources/query/sql_streaming/query10.sql new file mode 100644 index 0000000..c92add2 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_streaming/query10.sql @@ -0,0 +1 @@ +select count(*) as c from streaming_table where minute_start >= {TS '2015-01-01 20:30:00'} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_subquery/query00.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query00.sql b/kylin-it/src/test/resources/query/sql_subquery/query00.sql new file mode 100644 index 0000000..ca1fe9b --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query00.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, sum(test_kylin_fact.price) as sum_price, 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_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_subquery/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query01.sql b/kylin-it/src/test/resources/query/sql_subquery/query01.sql new file mode 100644 index 0000000..a2a9bd6 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query01.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 sum(sum_price) AS "COL" + FROM ( + select test_cal_dt.week_beg_dt, sum(test_kylin_fact.price) as sum_price, 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_cal_dt.week_beg_dt + ) "TableauSQL" + HAVING COUNT(1)>0 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_subquery/query02.sql.disable ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query02.sql.disable b/kylin-it/src/test/resources/query/sql_subquery/query02.sql.disable new file mode 100644 index 0000000..968dbae --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query02.sql.disable @@ -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 sum(1) AS "COL" + FROM ( + select test_cal_dt.week_beg_dt, sum(test_kylin_fact.price) as sum_price + from test_kylin_fact + inner join test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + group by test_cal_dt.week_beg_dt + ) "TableauSQL" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_subquery/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query03.sql b/kylin-it/src/test/resources/query/sql_subquery/query03.sql new file mode 100644 index 0000000..07f4f08 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query03.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 fact.cal_dt, sum(fact.price) as sum_price, count(1) as cnt_1 +from test_kylin_fact fact + left JOIN edw.test_cal_dt as test_cal_dt + ON fact.cal_dt = test_cal_dt.cal_dt + left JOIN test_category_groupings + ON fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND fact.lstg_site_id = test_category_groupings.site_id + left JOIN edw.test_sites as test_sites + ON fact.lstg_site_id = test_sites.site_id +inner join +( + select test_kylin_fact.cal_dt, sum(test_kylin_fact.price) 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 group by test_kylin_fact.cal_dt order by 2 desc limit 7 +) cal_2 on fact.cal_dt = cal_2.cal_dt +group by fact.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_subquery/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query04.sql b/kylin-it/src/test/resources/query/sql_subquery/query04.sql new file mode 100644 index 0000000..837cb0d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query04.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 fact.cal_dt, sum(fact.price) as sum_price, count(1) as cnt_1 +from test_kylin_fact fact + left JOIN edw.test_cal_dt as test_cal_dt + ON fact.cal_dt = test_cal_dt.cal_dt + left JOIN test_category_groupings + ON fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND fact.lstg_site_id = test_category_groupings.site_id + left JOIN edw.test_sites as test_sites + ON fact.lstg_site_id = test_sites.site_id +inner join +( + select test_kylin_fact.cal_dt, max(test_kylin_fact.cal_dt) as mmm 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 group by test_kylin_fact.cal_dt order by 2 desc limit 7 +) cal_2 on fact.cal_dt = cal_2.mmm +group by fact.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_subquery/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query05.sql b/kylin-it/src/test/resources/query/sql_subquery/query05.sql new file mode 100644 index 0000000..c3bc2bb --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query05.sql @@ -0,0 +1,37 @@ +-- +-- 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 "sum_PRICE_ok" + 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 ( + SELECT COUNT(1) AS "XTableau_join_flag", + SUM("TEST_KYLIN_FACT"."PRICE") AS "X__alias__A", + "TEST_KYLIN_FACT"."CAL_DT" AS "none_CAL_DT_ok" 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 10 ) "t0" ON ("TEST_KYLIN_FACT"."CAL_DT" = "t0"."none_CAL_DT_ok") + GROUP BY "TEST_KYLIN_FACT"."CAL_DT" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_subquery/query06.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query06.sql b/kylin-it/src/test/resources/query/sql_subquery/query06.sql new file mode 100644 index 0000000..7a8b26a --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query06.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", SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok" FROM "TEST_KYLIN_FACT" "TEST_KYLIN_FACT" + INNER JOIN ( + SELECT COUNT(1) AS "XTableau_join_flag", SUM("TEST_KYLIN_FACT"."PRICE") AS "X__alias__A", "TEST_KYLIN_FACT"."CAL_DT" AS "none_CAL_DT_ok" FROM "TEST_KYLIN_FACT" "TEST_KYLIN_FACT" + GROUP BY "TEST_KYLIN_FACT"."CAL_DT" ORDER BY 2 DESC LIMIT 7 ) + + "t0" ON ("TEST_KYLIN_FACT"."CAL_DT" = "t0"."none_CAL_DT_ok") GROUP BY "TEST_KYLIN_FACT"."CAL_DT" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_subquery/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query07.sql b/kylin-it/src/test/resources/query/sql_subquery/query07.sql new file mode 100644 index 0000000..0553855 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query07.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 cal_dt, sum(price) as sum_price +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/1428bbc4/kylin-it/src/test/resources/query/sql_subquery/query08.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query08.sql b/kylin-it/src/test/resources/query/sql_subquery/query08.sql new file mode 100644 index 0000000..48da204 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query08.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 t1.week_beg_dt, t1.sum_price, t2.cnt +FROM ( + select test_cal_dt.week_beg_dt, sum(price) as sum_price + 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 +) t1 +inner join ( + select test_cal_dt.week_beg_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 + group by test_cal_dt.week_beg_dt +) t2 +on t1.week_beg_dt=t2.week_beg_dt \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query00.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query00.sql b/kylin-it/src/test/resources/query/sql_tableau/query00.sql new file mode 100644 index 0000000..1092b97 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query00.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_cal_dt.week_beg_dt, sum(test_kylin_fact.price) + 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 + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query01.sql b/kylin-it/src/test/resources/query/sql_tableau/query01.sql new file mode 100644 index 0000000..cd0ebdf --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query01.sql @@ -0,0 +1,33 @@ +-- +-- 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(1) AS "COL", + 2 AS "COL2" + FROM ( + 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 + 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 + ) "TableauSQL" + GROUP BY 2 + HAVING COUNT(1)>0 + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query02.sql b/kylin-it/src/test/resources/query/sql_tableau/query02.sql new file mode 100644 index 0000000..75c384d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query02.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 * + FROM ( + 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 + 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 + ) "TableauSQL" + LIMIT 1 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query03.sql b/kylin-it/src/test/resources/query/sql_tableau/query03.sql new file mode 100644 index 0000000..26171ce --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query03.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(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 + 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_tableau/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query04.sql b/kylin-it/src/test/resources/query/sql_tableau/query04.sql new file mode 100644 index 0000000..7c67108 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query04.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 "TableauSQL"."META_CATEG_NAME" AS "none_META_CATEG_NAME_nk" + FROM ( + 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 + 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 + ) "TableauSQL" + GROUP BY "TableauSQL"."META_CATEG_NAME" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query05.sql b/kylin-it/src/test/resources/query/sql_tableau/query05.sql new file mode 100644 index 0000000..b499554 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query05.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 SUM("TableauSQL"."GMV") AS "sum_GMV_ok", + SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_ok" + FROM ( + 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 + 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 + ) "TableauSQL" + HAVING (COUNT(1) > 0) http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query06.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query06.sql b/kylin-it/src/test/resources/query/sql_tableau/query06.sql new file mode 100644 index 0000000..b499554 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query06.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 SUM("TableauSQL"."GMV") AS "sum_GMV_ok", + SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_ok" + FROM ( + 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 + 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 + ) "TableauSQL" + HAVING (COUNT(1) > 0) http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query07.sql b/kylin-it/src/test/resources/query/sql_tableau/query07.sql new file mode 100644 index 0000000..5c137e3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query07.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("TableauSQL"."GMV") AS "sum_GMV_ok", SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_ok" + FROM ( + 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 + 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 + ) "TableauSQL" HAVING (COUNT(1) > 0) http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query08.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query08.sql.disabled b/kylin-it/src/test/resources/query/sql_tableau/query08.sql.disabled new file mode 100644 index 0000000..c2fc675 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query08.sql.disabled @@ -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. +-- + +-- LEAF_CATEG_ID is a join field on fact table, but no on cuboid. We really support this? + +SELECT "TEST_KYLIN_FACT"."LEAF_CATEG_ID" AS "NONE_LEAF_CATEG_ID_OK" +FROM "olap"."TEST_KYLIN_FACT" "TEST_KYLIN_FACT" +GROUP BY "TEST_KYLIN_FACT"."LEAF_CATEG_ID" + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query09.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query09.sql.disabled b/kylin-it/src/test/resources/query/sql_tableau/query09.sql.disabled new file mode 100644 index 0000000..6b6ad41 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query09.sql.disabled @@ -0,0 +1,57 @@ +-- +-- 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"."ADULT_CATEG_YN" AS "ADULT_CATEG_YN", + "TEST_CATEGORY_GROUPINGS"."BSNS_VRTCL_NAME" AS "BSNS_VRTCL_NAME", + "TEST_CATEGORY_GROUPINGS"."CATEG_BUSN_MGR" AS "CATEG_BUSN_MGR", + "TEST_CATEGORY_GROUPINGS"."CATEG_BUSN_UNIT" AS "CATEG_BUSN_UNIT", + "TEST_CATEGORY_GROUPINGS"."CATEG_FLAGS" AS "CATEG_FLAGS", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL2_ID" AS "CATEG_LVLC_ID", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL2_NAME" AS "CATEG_LVLC_NAME", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL3_ID" AS "CATEG_LVLD_ID", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL3_NAME" AS "CATEG_LVLD_NAME", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL4_ID" AS "CATEG_LVLE_ID", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL4_NAME" AS "CATEG_LVLE_NAME", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL5_ID" AS "CATEG_LVLF_ID", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL5_NAME" AS "CATEG_LVLF_NAME", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL6_ID" AS "CATEG_LVLG_ID", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL6_NAME" AS "CATEG_LVLG_NAME", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL7_ID" AS "CATEG_LVLH_ID", + "TEST_CATEGORY_GROUPINGS"."CATEG_LVL7_NAME" AS "CATEG_LVLH_NAME", + "TEST_CATEGORY_GROUPINGS"."CRE_DATE" AS "CRE_DATE", + "TEST_CATEGORY_GROUPINGS"."CRE_USER" AS "CRE_USER", + "TEST_CATEGORY_GROUPINGS"."DOMAIN_ID" AS "DOMAIN_ID", + "TEST_CATEGORY_GROUPINGS"."GCS_ID" AS "GCS_ID", + "TEST_CATEGORY_GROUPINGS"."LEAF_CATEG_ID" AS "LEAF_CATEG_ID", + "TEST_CATEGORY_GROUPINGS"."LEAF_CATEG_NAME" AS "LEAF_CATEG_NAME", + "TEST_CATEGORY_GROUPINGS"."META_CATEG_ID" AS "META_CATEG_ID", + "TEST_CATEGORY_GROUPINGS"."META_CATEG_NAME" AS "META_CATEG_NAME", + "TEST_CATEGORY_GROUPINGS"."MOVE_TO" AS "MOVE_TO", + 1 AS "Number_of_Records", + "TEST_CATEGORY_GROUPINGS"."REGN_CATEG" AS "REGN_CATEG", + "TEST_CATEGORY_GROUPINGS"."SAP_CATEGORY_ID" AS "SAP_CATEGORY_ID", + "TEST_CATEGORY_GROUPINGS"."SITE_ID" AS "SITE_ID", + "TEST_CATEGORY_GROUPINGS"."SRC_ID" AS "SRC_ID", + "TEST_CATEGORY_GROUPINGS"."UPD_DATE" AS "UPD_DATE", + "TEST_CATEGORY_GROUPINGS"."UPD_USER" AS "UPD_USER", + "TEST_CATEGORY_GROUPINGS"."USER_DEFINED_FIELD1" AS "USER_DEFINED_FIELDB", + "TEST_CATEGORY_GROUPINGS"."USER_DEFINED_FIELD3" AS "USER_DEFINED_FIELDD", + "TEST_CATEGORY_GROUPINGS"."USER_DEFINED_FIELD5" AS "USER_DEFINED_FIELDF", + "TEST_CATEGORY_GROUPINGS"."VCS_ID" AS "VCS_ID" +FROM "olap"."TEST_CATEGORY_GROUPINGS" "TEST_CATEGORY_GROUPINGS" +LIMIT 10000 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query10.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query10.sql b/kylin-it/src/test/resources/query/sql_tableau/query10.sql new file mode 100644 index 0000000..cf43fa7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query10.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_CAL_DT"."WEEK_BEG_DT" AS "none_WEEK_BEG_DT_nk", + SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok" + 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" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query11.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query11.sql b/kylin-it/src/test/resources/query/sql_tableau/query11.sql new file mode 100644 index 0000000..7d908fd --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query11.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 COUNT(1) AS cnt_ITEM_COUNT_ok, + TEST_CAL_DT.WEEK_BEG_DT AS none_WEEK_BEG_DT_nk + 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" + http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query12.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query12.sql b/kylin-it/src/test/resources/query/sql_tableau/query12.sql new file mode 100644 index 0000000..cf43fa7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query12.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_CAL_DT"."WEEK_BEG_DT" AS "none_WEEK_BEG_DT_nk", + SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok" + 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" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query13.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query13.sql b/kylin-it/src/test/resources/query/sql_tableau/query13.sql new file mode 100644 index 0000000..e1b00d9 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query13.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 EXTRACT(YEAR FROM TEST_CAL_DT.WEEK_BEG_DT) AS yr_WEEK_BEG_DT_ok + 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 EXTRACT(YEAR FROM TEST_CAL_DT.WEEK_BEG_DT) http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query14.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query14.sql b/kylin-it/src/test/resources/query/sql_tableau/query14.sql new file mode 100644 index 0000000..50d8952 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query14.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 QUARTER("TEST_CAL_DT"."WEEK_BEG_DT") AS "qr_WEEK_BEG_DT_ok", EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT") AS "yr_WEEK_BEG_DT_ok" 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 QUARTER("TEST_CAL_DT"."WEEK_BEG_DT"), EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT") http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query15.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query15.sql b/kylin-it/src/test/resources/query/sql_tableau/query15.sql new file mode 100644 index 0000000..8f30460 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query15.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 QUARTER(TEST_CAL_DT.WEEK_BEG_DT) AS qr_WEEK_BEG_DT_ok + 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 QUARTER(TEST_CAL_DT.WEEK_BEG_DT) http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query16.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query16.sql b/kylin-it/src/test/resources/query/sql_tableau/query16.sql new file mode 100644 index 0000000..aaa4ab2 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query16.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 EXTRACT(YEAR FROM TEST_CAL_DT.WEEK_BEG_DT) AS yr_WEEK_BEG_DT_ok, QUARTER(TEST_CAL_DT.WEEK_BEG_DT) AS qr_WEEK_BEG_DT_ok + 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 EXTRACT(YEAR FROM TEST_CAL_DT.WEEK_BEG_DT), QUARTER(TEST_CAL_DT.WEEK_BEG_DT) http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query17.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query17.sql b/kylin-it/src/test/resources/query/sql_tableau/query17.sql new file mode 100644 index 0000000..11f6df1 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query17.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 EXTRACT(MONTH FROM "TEST_CAL_DT"."WEEK_BEG_DT") AS "mn_WEEK_BEG_DT_ok", (( EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT") * 100) + EXTRACT(MONTH FROM "TEST_CAL_DT"."WEEK_BEG_DT")) AS "my_WEEK_BEG_DT_ok", QUARTER("TEST_CAL_DT"."WEEK_BEG_DT") AS "qr_WEEK_BEG_DT_ok", EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT") AS "yr_WEEK_BEG_DT_ok" 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 EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT"), QUARTER("TEST_CAL_DT"."WEEK_BEG_DT"), (( EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT") * 100) + EXTRACT(MONTH FROM "TEST_CAL_DT"."WEEK_BEG_DT")), EXTRACT(MONTH FROM "TEST_CAL_DT"."WEEK_BEG_DT") http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query18.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query18.sql b/kylin-it/src/test/resources/query/sql_tableau/query18.sql new file mode 100644 index 0000000..188ceb7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query18.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(price) as GMV, count(seller_id) 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_tableau/query19.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query19.sql b/kylin-it/src/test/resources/query/sql_tableau/query19.sql new file mode 100644 index 0000000..5f6e086 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query19.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 "TableauSQL"."LSTG_FORMAT_NAME" AS "none_LSTG_FORMAT_NAME_nk", SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_qk" + FROM ( select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(seller_id) as TRANS_CNT + from test_kylin_fact + group by test_kylin_fact.lstg_format_name ) "TableauSQL" + GROUP BY "TableauSQL"."LSTG_FORMAT_NAME" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query20.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query20.sql b/kylin-it/src/test/resources/query/sql_tableau/query20.sql new file mode 100644 index 0000000..c776128 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query20.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 "TableauSQL"."LSTG_FORMAT_NAME" AS "none_LSTG_FORMAT_NAME_nk", SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_qk" + FROM ( select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(seller_id) as TRANS_CNT + from test_kylin_fact where test_kylin_fact.lstg_format_name > 'ab' + group by test_kylin_fact.lstg_format_name having count(seller_id) > 2 ) "TableauSQL" + GROUP BY "TableauSQL"."LSTG_FORMAT_NAME"
