http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_cache/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_cache/query07.sql b/kylin-it/src/test/resources/query/sql_cache/query07.sql new file mode 100644 index 0000000..b9e2821 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_cache/query07.sql @@ -0,0 +1,11 @@ +select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_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.cal_dt < DATE '2018-10-10' AND test_kylin_fact.cal_dt > DATE '2009-09-10' + group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_cache/query08.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_cache/query08.sql b/kylin-it/src/test/resources/query/sql_cache/query08.sql new file mode 100644 index 0000000..308a5f3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_cache/query08.sql @@ -0,0 +1,11 @@ +select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_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.cal_dt < DATE '2012-09-10' AND test_kylin_fact.cal_dt > DATE '2012-01-01' + group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_cache/query09.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_cache/query09.sql b/kylin-it/src/test/resources/query/sql_cache/query09.sql new file mode 100644 index 0000000..b9e2821 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_cache/query09.sql @@ -0,0 +1,11 @@ +select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_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.cal_dt < DATE '2018-10-10' AND test_kylin_fact.cal_dt > DATE '2009-09-10' + group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_casewhen/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query01.sql b/kylin-it/src/test/resources/query/sql_casewhen/query01.sql new file mode 100644 index 0000000..ce6e81a --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_casewhen/query01.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 lstg_format_name +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/7f08dab5/kylin-it/src/test/resources/query/sql_casewhen/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query02.sql b/kylin-it/src/test/resources/query/sql_casewhen/query02.sql new file mode 100644 index 0000000..697f423 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_casewhen/query02.sql @@ -0,0 +1,22 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" +FROM TEST_KYLIN_FACT +WHERE ((CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" = 'Auction') THEN 'Auction1' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) = 'Auction1') +GROUP BY "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_casewhen/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query03.sql b/kylin-it/src/test/resources/query/sql_casewhen/query03.sql new file mode 100644 index 0000000..85c662a --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_casewhen/query03.sql @@ -0,0 +1,25 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" +FROM TEST_KYLIN_FACT +WHERE (CASE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" + WHEN 'Auction' THEN '111' + WHEN 'FP-GTC' THEN '222' + ELSE '999' END) = '111' +GROUP BY "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_casewhen/query55.sql.disable ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query55.sql.disable b/kylin-it/src/test/resources/query/sql_casewhen/query55.sql.disable new file mode 100644 index 0000000..d29b60d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_casewhen/query55.sql.disable @@ -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 WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" 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 WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_casewhen/query56.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query56.sql b/kylin-it/src/test/resources/query/sql_casewhen/query56.sql new file mode 100644 index 0000000..7006fbf --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_casewhen/query56.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 WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" = 'Auction') THEN 'Auction2' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" 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 WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" = 'Auction') THEN 'Auction2' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query01.sql b/kylin-it/src/test/resources/query/sql_derived/query01.sql new file mode 100644 index 0000000..c4b0aae --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query01.sql @@ -0,0 +1,47 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_kylin_fact.seller_id + ,test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_sites.site_id + ,test_sites.cre_user + ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_kylin_fact.seller_id = 10000002 + group by + test_kylin_fact.seller_id + ,test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_sites.site_id + ,test_sites.cre_user http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query02.sql b/kylin-it/src/test/resources/query/sql_derived/query02.sql new file mode 100644 index 0000000..027cd9c --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query02.sql @@ -0,0 +1,47 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + test_kylin_fact.seller_id + ,test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_sites.site_id + ,test_sites.cre_user + ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT + FROM test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + where test_kylin_fact.seller_id = 10000002 and test_sites.site_id=0 + group by + test_kylin_fact.seller_id + ,test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.categ_lvl2_name + ,test_category_groupings.categ_lvl3_name + ,test_kylin_fact.lstg_format_name + ,test_sites.site_name + ,test_sites.site_id + ,test_sites.cre_user http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query03.sql b/kylin-it/src/test/resources/query/sql_derived/query03.sql new file mode 100644 index 0000000..35736b9 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query03.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 distinct LSTG_FORMAT_NAME from test_kylin_fact + + http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query04.sql b/kylin-it/src/test/resources/query/sql_derived/query04.sql new file mode 100644 index 0000000..dab5d0f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query04.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 site_id, site_name, cre_user from edw.test_sites + http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query05.sql b/kylin-it/src/test/resources/query/sql_derived/query05.sql new file mode 100644 index 0000000..61a9b2c --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query05.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_sites.site_name, 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 + GROUP BY + test_sites.site_name, test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query06.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query06.sql b/kylin-it/src/test/resources/query/sql_derived/query06.sql new file mode 100644 index 0000000..0ea32b2 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query06.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 distinct cal_dt from test_kylin_fact + http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query07.sql b/kylin-it/src/test/resources/query/sql_derived/query07.sql new file mode 100644 index 0000000..a27fbd1 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query07.sql @@ -0,0 +1,22 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select cal_dt, lstg_format_name, sum(price) as GMV + from test_kylin_fact + where cal_dt=date '2013-05-06' + group by cal_dt, lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query08.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query08.sql.disabled b/kylin-it/src/test/resources/query/sql_derived/query08.sql.disabled new file mode 100644 index 0000000..c4e7d0c --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query08.sql.disabled @@ -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 distinct leaf_categ_id, lstg_site_id from test_kylin_fact http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query09.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query09.sql b/kylin-it/src/test/resources/query/sql_derived/query09.sql new file mode 100644 index 0000000..c0aacef --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query09.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 distinct leaf_categ_id, site_id from test_category_groupings http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_derived/query10.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_derived/query10.sql b/kylin-it/src/test/resources/query/sql_derived/query10.sql new file mode 100644 index 0000000..dd29659 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_derived/query10.sql @@ -0,0 +1,44 @@ +-- +-- 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.upd_user + ,test_category_groupings.upd_date + ,test_kylin_fact.leaf_categ_id + ,test_category_groupings.leaf_categ_id + ,test_kylin_fact.lstg_site_id + ,test_category_groupings.site_id + ,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_category_groupings.upd_date='2012-09-11 20:26:04' + group by test_cal_dt.week_beg_dt + ,test_category_groupings.meta_categ_name + ,test_category_groupings.upd_user + ,test_category_groupings.upd_date + ,test_kylin_fact.leaf_categ_id + ,test_category_groupings.leaf_categ_id + ,test_kylin_fact.lstg_site_id + ,test_category_groupings.site_id http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_distinct/query00.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_distinct/query00.sql b/kylin-it/src/test/resources/query/sql_distinct/query00.sql new file mode 100644 index 0000000..03a2ada --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_distinct/query00.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, cal_dt, + sum(price) as GMV, + count(1) as TRANS_CNT, + count(distinct seller_id) as DIST_SELLER + from test_kylin_fact + group by lstg_format_name, cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_distinct/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_distinct/query01.sql b/kylin-it/src/test/resources/query/sql_distinct/query01.sql new file mode 100644 index 0000000..9f92497 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_distinct/query01.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, + count(distinct seller_id) as DIST_SELLER + from test_kylin_fact + where lstg_format_name='FP-GTC' + group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_distinct/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_distinct/query02.sql b/kylin-it/src/test/resources/query/sql_distinct/query02.sql new file mode 100644 index 0000000..a903bf3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_distinct/query02.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 lstg_format_name, + sum(price) as GMV, + count(1) as TRANS_CNT, + count(distinct seller_id) as DIST_SELLER + from test_kylin_fact + where lstg_format_name='FP-GTC' + group by lstg_format_name + having count(distinct seller_id) > 50 http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_distinct/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_distinct/query03.sql b/kylin-it/src/test/resources/query/sql_distinct/query03.sql new file mode 100644 index 0000000..61ab376 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_distinct/query03.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 test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV + , count(1) as TRANS_CNT, count(distinct seller_id) as DIST_SELLER + from test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + where test_kylin_fact.lstg_format_name='FP-GTC' + and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01' + group by test_cal_dt.week_beg_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_distinct/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_distinct/query04.sql b/kylin-it/src/test/resources/query/sql_distinct/query04.sql new file mode 100644 index 0000000..cb118fa --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_distinct/query04.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 GMV + , count(1) as TRANS_CNT, count(distinct seller_id) as DIST_SELLER + from test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + where test_kylin_fact.lstg_format_name='FP-GTC' + and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01' + group by test_cal_dt.week_beg_dt + having count(distinct seller_id) > 2 http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_distinct/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_distinct/query05.sql b/kylin-it/src/test/resources/query/sql_distinct/query05.sql new file mode 100644 index 0000000..cee8ef4 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_distinct/query05.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, + count(distinct seller_id) as DIST_SELLER + from test_kylin_fact + group by lstg_format_name + order by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_distinct/query06.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_distinct/query06.sql b/kylin-it/src/test/resources/query/sql_distinct/query06.sql new file mode 100644 index 0000000..4a46048 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_distinct/query06.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 lstg_format_name, + sum(price) as GMV, + count(1) as TRANS_CNT, + count(distinct seller_id) as DIST_SELLER + 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/7f08dab5/kylin-it/src/test/resources/query/sql_distinct/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_distinct/query07.sql b/kylin-it/src/test/resources/query/sql_distinct/query07.sql new file mode 100644 index 0000000..b76cdc8 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_distinct/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 lstg_format_name, + sum(price) as GMV, + count(1) as TRANS_CNT, + count(distinct seller_id) as DIST_SELLER + from test_kylin_fact + group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_dynamic/query01.dat ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_dynamic/query01.dat b/kylin-it/src/test/resources/query/sql_dynamic/query01.dat new file mode 100644 index 0000000..a72c741 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_dynamic/query01.dat @@ -0,0 +1,2 @@ +FP-GTC +Collectibles http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_dynamic/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_dynamic/query01.sql b/kylin-it/src/test/resources/query/sql_dynamic/query01.sql new file mode 100644 index 0000000..d777fdf --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_dynamic/query01.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, test_category_groupings.meta_categ_name, sum(test_kylin_fact.price) as gmv, count(*) as trans_cnt + from test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + where test_kylin_fact.lstg_format_name = ? + and test_category_groupings.meta_categ_name = ? + and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-10-01' + group by test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name, test_category_groupings.meta_categ_name http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_dynamic/query02.dat ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_dynamic/query02.dat b/kylin-it/src/test/resources/query/sql_dynamic/query02.dat new file mode 100644 index 0000000..a72c741 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_dynamic/query02.dat @@ -0,0 +1,2 @@ +FP-GTC +Collectibles http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_dynamic/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_dynamic/query02.sql b/kylin-it/src/test/resources/query/sql_dynamic/query02.sql new file mode 100644 index 0000000..eaeccdd --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_dynamic/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 sum(1) as "col" from ( + select test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name, test_category_groupings.meta_categ_name, sum(test_kylin_fact.price) as gmv, count(*) as trans_cnt + from test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + where test_kylin_fact.lstg_format_name = ? + and test_category_groupings.meta_categ_name = ? + and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-10-01' + group by test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name, test_category_groupings.meta_categ_name + ) "tableausql" having count(1)>0 http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_fast_common/query00.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_fast_common/query00.sql b/kylin-it/src/test/resources/query/sql_fast_common/query00.sql new file mode 100644 index 0000000..36249a8 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_fast_common/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 lstg_format_name, cal_dt, + sum(price) as GMV, + count(1) as TRANS_CNT + from test_kylin_fact + group by lstg_format_name, cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_hive/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_hive/query01.sql b/kylin-it/src/test/resources/query/sql_hive/query01.sql new file mode 100644 index 0000000..34b81e2 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_hive/query01.sql @@ -0,0 +1,20 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select cal_dt, sum(price)as GMV, count(1) as trans_cnt from test_kylin_fact + group by cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_hive/query10.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_hive/query10.sql b/kylin-it/src/test/resources/query/sql_hive/query10.sql new file mode 100644 index 0000000..ca77a41 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_hive/query10.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 test_cal_dt.QTR_BEG_DT,sum(test_kylin_fact.price) as gmv + , count(*) as trans_cnt + from test_kylin_fact + inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + where test_kylin_fact.lstg_format_name='FP-GTC' + and test_cal_dt.week_beg_dt between '2013-05-01' and '2013-08-01' + group by test_cal_dt.QTR_BEG_DT http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_ii/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_ii/query01.sql b/kylin-it/src/test/resources/query/sql_ii/query01.sql new file mode 100644 index 0000000..be187f5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_ii/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 count(*) as all_count from test_kylin_fact http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_ii/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_ii/query02.sql b/kylin-it/src/test/resources/query/sql_ii/query02.sql new file mode 100644 index 0000000..eb52d11 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_ii/query02.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select sum(price) as price_sum, min(price) as price_min, max(price) as price_max from test_kylin_fact http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_ii/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_ii/query03.sql b/kylin-it/src/test/resources/query/sql_ii/query03.sql new file mode 100644 index 0000000..d20efec --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_ii/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 sum(price) as price_sum, min(price) as price_min , max(price) as price_max , LSTG_FORMAT_NAME from test_kylin_fact group by LSTG_FORMAT_NAME http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_ii/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_ii/query04.sql b/kylin-it/src/test/resources/query/sql_ii/query04.sql new file mode 100644 index 0000000..4a75fba --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_ii/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 TRANS_ID,CAL_DT,LSTG_FORMAT_NAME,LEAF_CATEG_ID,LSTG_SITE_ID,SLR_SEGMENT_CD,PRICE,SELLER_ID from test_kylin_fact where LSTG_FORMAT_NAME = 'ABIN' http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_ii/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_ii/query05.sql b/kylin-it/src/test/resources/query/sql_ii/query05.sql new file mode 100644 index 0000000..4043a2c --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_ii/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 sum(price) as price_sum, min(price) as price_min , max(price) as price_max , SELLER_ID from test_kylin_fact where SELLER_ID > 10000149 group by SELLER_ID http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_ii/query06.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_ii/query06.sql.disabled b/kylin-it/src/test/resources/query/sql_ii/query06.sql.disabled new file mode 100644 index 0000000..f97f440 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_ii/query06.sql.disabled @@ -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 price from test_kylin_fact where price > 50 http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_ii/query07.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_ii/query07.sql.disabled b/kylin-it/src/test/resources/query/sql_ii/query07.sql.disabled new file mode 100644 index 0000000..0471a47 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_ii/query07.sql.disabled @@ -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 price_sum, min(price) as price_min , max(price) as price_max , LSTG_FORMAT_NAME from test_kylin_fact where price > 50 group by LSTG_FORMAT_NAME http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_ii/query08.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_ii/query08.sql b/kylin-it/src/test/resources/query/sql_ii/query08.sql new file mode 100644 index 0000000..51a676d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_ii/query08.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 TRANS_ID,CAL_DT,LSTG_FORMAT_NAME,LEAF_CATEG_ID,LSTG_SITE_ID,SLR_SEGMENT_CD,PRICE,SELLER_ID from test_kylin_fact where LSTG_FORMAT_NAME > 'ABIN' http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_ii/query09.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_ii/query09.sql b/kylin-it/src/test/resources/query/sql_ii/query09.sql new file mode 100644 index 0000000..1562288 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_ii/query09.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 price, SELLER_ID from test_kylin_fact where SELLER_ID > 10000149 http://git-wip-us.apache.org/repos/asf/kylin/blob/7f08dab5/kylin-it/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql b/kylin-it/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql new file mode 100644 index 0000000..7d2b1b5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_invalid/query29_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) 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 edw.test_sites as 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/7f08dab5/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/7f08dab5/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/7f08dab5/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/7f08dab5/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/7f08dab5/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/7f08dab5/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/7f08dab5/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/7f08dab5/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/7f08dab5/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/7f08dab5/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/7f08dab5/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/7f08dab5/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
