| -- Test: TEST045 (CompGeneral) |
| -- Functionality: WITH clause and common subexpressions |
| -- Tables created: schema T045_CSES |
| -- Expected files: EXPECTED045 |
| -- |
| -- @@@ START COPYRIGHT @@@ |
| -- |
| -- 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. |
| -- |
| -- @@@ END COPYRIGHT @@@ |
| |
| obey TEST045(clnup); |
| log LOG045 clear; |
| obey TEST045(setup); |
| obey TEST045(ddl); |
| obey TEST045(common_subexpr); |
| obey TEST045(with_queries); |
| obey TEST045(clnup); |
| log; |
| exit; |
| |
| ?section clnup |
| |
| drop schema T045_CSES cascade; |
| cqd cse_cleanup_hive_tables 'on'; |
| cleanup obsolete volatile tables; |
| |
| ?section setup |
| |
| -- general setup |
| cqd hist_missing_stats_warning_level '0'; |
| cqd hive_max_string_length_in_bytes '32'; |
| cqd mode_special_4 'on'; |
| |
| create schema T045_CSES; |
| set schema T045_CSES; |
| |
| prepare show_cses from |
| select case when operator in ('BLOCKED_UNION', 'HIVE_INSERT') |
| then operator |
| else 'SCAN TEMP' end as operator, |
| count(*) as how_many |
| from table(explain(null, 'S')) |
| where operator = 'BLOCKED_UNION' |
| or tname like '%CSE_TEMP_%' |
| group by 1 |
| order by 1; |
| |
| ?section enable_cses; |
| cqd cse_for_with 'on'; |
| cqd cse_use_temp 'on'; |
| cqd cse_hive_temp_table 'on'; |
| cqd cse_debug_warnings 'on'; |
| |
| ?section disable_cses; |
| cqd cse_for_with 'off'; |
| cqd cse_use_temp 'off'; |
| cqd cse_hive_temp_table reset; |
| cqd cse_debug_warnings 'off'; |
| |
| -------------------------------------------------------------------- |
| ?section ddl |
| -------------------------------------------------------------------- |
| |
| create table sub_limit_01 ( a int, b char(30), c decimal(8,2) ) ; |
| |
| insert into sub_limit_01 values |
| (1, 'Limit_str_01', 11.23),(2, 'Limit_str_02', 12.23),(3, 'Limit_str_03', 13.23), |
| (4, 'Limit_str_04', 14.23),(5, 'Limit_str_05', 15.23),(6, 'Limit_str_06', 16.23), |
| (7, 'Limit_str_07', 17.23),(8, 'Limit_str_08', 18.23),(9, 'Limit_str_09', 19.23), |
| (10, 'Limit_str_10', 20.23),(11, 'Limit_str_11', 21.23),(12, 'Limit_str_12', 22.23), |
| (13, 'Limit_str_13', 23.23),(14, 'Limit_str_14', 24.23),(15, 'Limit_str_15', 25.23), |
| (16, 'Limit_str_16', 26.23),(17, 'Limit_str_17', 27.23),(18, 'Limit_str_18', 28.23), |
| (19, 'Limit_str_19', 29.23),(20, 'Limit_str_20', 30.23),(21, 'Limit_str_21', 31.23); |
| |
| |
| |
| create table store_sales |
| ( |
| ss_sold_date_sk int, |
| ss_sold_time_sk int, |
| ss_item_sk int not null, |
| ss_customer_sk int, |
| ss_cdemo_sk int, |
| ss_hdemo_sk int, |
| ss_addr_sk int, |
| ss_store_sk int, |
| ss_promo_sk int, |
| ss_ticket_number int not null, |
| ss_quantity int, |
| ss_wholesale_cost float, |
| ss_list_price float, |
| ss_sales_price float, |
| ss_ext_discount_amt float, |
| ss_ext_sales_price float, |
| ss_ext_wholesale_cost float, |
| ss_ext_list_price float, |
| ss_ext_tax float, |
| ss_coupon_amt float, |
| ss_net_paid float, |
| ss_net_paid_inc_tax float, |
| ss_net_profit float, |
| primary key(ss_item_sk,ss_ticket_number) |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table store_returns |
| ( |
| sr_returned_date_sk int, |
| sr_return_time_sk int, |
| sr_item_sk int not null, |
| sr_customer_sk int, |
| sr_cdemo_sk int, |
| sr_hdemo_sk int, |
| sr_addr_sk int, |
| sr_store_sk int, |
| sr_reason_sk int, |
| sr_ticket_number int not null, |
| sr_return_quantity int, |
| sr_return_amt float, |
| sr_return_tax float, |
| sr_return_amt_inc_tax float, |
| sr_fee float, |
| sr_return_ship_cost float, |
| sr_refunded_cash float, |
| sr_reversed_charge float, |
| sr_store_credit float, |
| sr_net_loss float, |
| primary key(sr_item_sk,sr_ticket_number) |
| |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table catalog_sales |
| ( |
| cs_sold_date_sk int, |
| cs_sold_time_sk int, |
| cs_ship_date_sk int, |
| cs_bill_customer_sk int, |
| cs_bill_cdemo_sk int, |
| cs_bill_hdemo_sk int, |
| cs_bill_addr_sk int, |
| cs_ship_customer_sk int, |
| cs_ship_cdemo_sk int, |
| cs_ship_hdemo_sk int, |
| cs_ship_addr_sk int, |
| cs_call_center_sk int, |
| cs_catalog_page_sk int, |
| cs_ship_mode_sk int, |
| cs_warehouse_sk int, |
| cs_item_sk int not null, |
| cs_promo_sk int, |
| cs_order_number int not null, |
| cs_quantity int, |
| cs_wholesale_cost float, |
| cs_list_price float, |
| cs_sales_price float, |
| cs_ext_discount_amt float, |
| cs_ext_sales_price float, |
| cs_ext_wholesale_cost float, |
| cs_ext_list_price float, |
| cs_ext_tax float, |
| cs_coupon_amt float, |
| cs_ext_ship_cost float, |
| cs_net_paid float, |
| cs_net_paid_inc_tax float, |
| cs_net_paid_inc_ship float, |
| cs_net_paid_inc_ship_tax float, |
| cs_net_profit float, |
| primary key(cs_item_sk,cs_order_number) |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table catalog_returns |
| ( |
| cr_returned_date_sk int, |
| cr_returned_time_sk int, |
| cr_item_sk int not null, |
| cr_refunded_customer_sk int, |
| cr_refunded_cdemo_sk int, |
| cr_refunded_hdemo_sk int, |
| cr_refunded_addr_sk int, |
| cr_returning_customer_sk int, |
| cr_returning_cdemo_sk int, |
| cr_returning_hdemo_sk int, |
| cr_returning_addr_sk int, |
| cr_call_center_sk int, |
| cr_catalog_page_sk int, |
| cr_ship_mode_sk int, |
| cr_warehouse_sk int, |
| cr_reason_sk int, |
| cr_order_number int not null, |
| cr_return_quantity int, |
| cr_return_amount float, |
| cr_return_tax float, |
| cr_return_amt_inc_tax float, |
| cr_fee float, |
| cr_return_ship_cost float, |
| cr_refunded_cash float, |
| cr_reversed_charge float, |
| cr_store_credit float, |
| cr_net_loss float, |
| |
| primary key(cr_item_sk,cr_order_number) |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table web_sales |
| ( |
| ws_sold_date_sk int, |
| ws_sold_time_sk int, |
| ws_ship_date_sk int, |
| ws_item_sk int not null, |
| ws_bill_customer_sk int, |
| ws_bill_cdemo_sk int, |
| ws_bill_hdemo_sk int, |
| ws_bill_addr_sk int, |
| ws_ship_customer_sk int, |
| ws_ship_cdemo_sk int, |
| ws_ship_hdemo_sk int, |
| ws_ship_addr_sk int, |
| ws_web_page_sk int, |
| ws_web_site_sk int, |
| ws_ship_mode_sk int, |
| ws_warehouse_sk int, |
| ws_promo_sk int, |
| ws_order_number int not null, |
| ws_quantity int, |
| ws_wholesale_cost float, |
| ws_list_price float, |
| ws_sales_price float, |
| ws_ext_discount_amt float, |
| ws_ext_sales_price float, |
| ws_ext_wholesale_cost float, |
| ws_ext_list_price float, |
| ws_ext_tax float, |
| ws_coupon_amt float, |
| ws_ext_ship_cost float, |
| ws_net_paid float, |
| ws_net_paid_inc_tax float, |
| ws_net_paid_inc_ship float, |
| ws_net_paid_inc_ship_tax float, |
| ws_net_profit float, |
| |
| primary key (ws_item_sk,ws_order_number) |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table web_returns |
| ( |
| wr_returned_date_sk int, |
| wr_returned_time_sk int, |
| wr_item_sk int not null, |
| wr_refunded_customer_sk int, |
| wr_refunded_cdemo_sk int, |
| wr_refunded_hdemo_sk int, |
| wr_refunded_addr_sk int, |
| wr_returning_customer_sk int, |
| wr_returning_cdemo_sk int, |
| wr_returning_hdemo_sk int, |
| wr_returning_addr_sk int, |
| wr_web_page_sk int, |
| wr_reason_sk int, |
| wr_order_number int not null, |
| wr_return_quantity int, |
| wr_return_amt float, |
| wr_return_tax float, |
| wr_return_amt_inc_tax float, |
| wr_fee float, |
| wr_return_ship_cost float, |
| wr_refunded_cash float, |
| wr_reversed_charge float, |
| wr_account_credit float, |
| wr_net_loss float, |
| primary key ( wr_order_number, wr_item_sk ) |
| |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table store |
| ( |
| s_store_sk int not null, |
| s_store_id char(16), |
| s_rec_start_date date, |
| s_rec_end_date date, |
| s_closed_date_sk int, |
| s_store_name varchar(50), |
| s_number_employees int, |
| s_floor_space int, |
| s_hours char(20), |
| S_manager varchar(40), |
| S_market_id int, |
| S_geography_class varchar(100), |
| S_market_desc varchar(100), |
| s_market_manager varchar(40), |
| s_division_id int, |
| s_division_name varchar(50), |
| s_company_id int, |
| s_company_name varchar(50), |
| s_street_number varchar(10), |
| s_street_name varchar(60), |
| s_street_type char(15), |
| s_suite_number char(10), --fix bug |
| s_city varchar(60), |
| s_county varchar(30), |
| s_state char(2), |
| s_zip char(10), |
| s_country varchar(20), |
| s_gmt_offset float, |
| s_tax_percentage float, |
| |
| primary key(s_store_sk) |
| |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table Customer |
| ( |
| c_customer_sk int not null, |
| c_customer_id char(16) CHARACTER SET UTF8 not null, |
| c_current_cdemo_sk int, |
| c_current_hdemo_sk int, |
| c_current_addr_sk int, |
| c_first_shipto_date_sk int, |
| c_first_sales_date_sk int, |
| c_salutation char(10) CHARACTER SET UTF8, |
| c_first_name char(20) CHARACTER SET UTF8, |
| c_last_name char(30) CHARACTER SET UTF8, |
| c_preferred_cust_flag char(1), |
| c_birth_day integer, |
| c_birth_month integer, |
| c_birth_year integer, |
| c_birth_country varchar(20) CHARACTER SET UTF8, |
| c_login char(13) CHARACTER SET UTF8, |
| c_email_address char(50) CHARACTER SET UTF8, |
| c_last_review_date_sk int, |
| primary key (c_customer_sk) |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table Customer_address |
| ( |
| ca_address_sk int not null, |
| ca_address_id char(16) not null, |
| ca_street_number char(10), |
| ca_street_name varchar(60), |
| ca_street_type char(15), |
| ca_suite_number char(10), |
| ca_city varchar(60), |
| ca_county varchar(30), |
| ca_state char(2), |
| ca_zip char(10), |
| ca_country varchar(20), |
| ca_gmt_offset decimal(5,2), |
| ca_location_type char(20), |
| primary key (ca_address_sk) |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table Date_dim |
| ( |
| d_date_sk int not null, |
| d_date_id char(16) not null, |
| d_date date, |
| d_month_seq integer, |
| d_week_seq integer, |
| d_quarter_seq integer, |
| d_year integer, |
| d_dow integer, |
| d_moy integer, |
| d_dom integer, |
| d_qoy integer, |
| d_fy_year integer, |
| d_fy_quarter_seq integer, |
| d_fy_week_seq integer, |
| d_day_name char(9), |
| d_quarter_name char(6), |
| d_holiday char(1), |
| d_weekend char(1), |
| d_following_holiday char(1), |
| d_first_dom integer, |
| d_last_dom integer, |
| d_same_day_ly integer, |
| d_same_day_lq integer, |
| d_current_day char(1), |
| d_current_week char(1), |
| d_current_month char(1), |
| d_current_quarter char(1), |
| d_current_year char(1), |
| primary key (d_date_sk) |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| create table item |
| ( |
| i_item_sk int not null, |
| i_item_id char(16) not null, |
| i_rec_start_date date, |
| i_rec_end_date date, |
| i_item_desc varchar(200), |
| i_current_price decimal(7,2), |
| i_wholesale_cost decimal(7,2), |
| i_brand_id integer, |
| i_brand char(50), |
| i_class_id integer, |
| i_class char(50), |
| i_category_id integer, |
| i_category char(50), |
| i_manufact_id integer, |
| i_manufact char(50), |
| i_size char(20), |
| i_formulation char(20), |
| i_color char(20), |
| i_units char(10), |
| i_container char(10), |
| i_manager_id integer, |
| i_product_name char(50), |
| primary key(i_item_sk) |
| ) |
| --SALT USING $PARTITION_NUM PARTITIONS |
| -- HBASE_OPTIONS |
| -- ( |
| -- DATA_BLOCK_ENCODING = 'FAST_DIFF', |
| -- COMPRESSION = 'SNAPPY' |
| -- ) |
| ; |
| |
| insert into date_dim values ( |
| 1, '1', date '2000-01-01', 1, 1, 1, 2000, 1, 1, 1, 1, 2000, 1, 1, 'aday', 'aq', ' ', ' ', ' ', 1, 31, 0, 0, ' ', ' ', ' ', ' ', ' ' |
| ); |
| |
| insert into store_sales values ( |
| 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1.0, 1.0, 1.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 1.0, 1.0, 0.0 |
| ); |
| |
| update statistics for table date_dim on every column; |
| update statistics for table date_dim on (d_qoy, d_year); |
| update statistics for table store_sales on every column; |
| |
| |
| |
| -------------------------------------------------------------------- |
| ?section common_subexpr |
| -------------------------------------------------------------------- |
| |
| -------------------------------------------------------------------- |
| -- test sub-expressions with limit clause |
| -------------------------------------------------------------------- |
| |
| create table sub_limit_02 as select * from sub_limit_01 limit 1; |
| create table sub_limit_03 (a int , b char(30), c decimal(8,2)) as select * from sub_limit_01 limit 2; |
| |
| insert into sub_limit_02 select * from sub_limit_01 order by a,c limit 2; |
| insert no check into sub_limit_02 select * from sub_limit_01 order by a,c limit 2; |
| insert with no rollback into table sub_limit_02 select * from sub_limit_01 order by a,c limit 2; |
| insert overwrite table sub_limit_02 select * from sub_limit_01 order by a,c limit 2; |
| insert with no rollback into sub_limit_02 (*) select * from sub_limit_01 order by a,c limit 2; |
| insert NOMVLOG into sub_limit_02 (a, b) select a, b from sub_limit_01 order by a,b limit 2; |
| |
| load into sub_limit_03 select * from sub_limit_01 limit 16; |
| |
| select count(*) from sub_limit_02; |
| |
| select count(*) from sub_limit_03; |
| |
| |
| |
| -------------------------------------------------------------------- |
| ?section with_queries |
| -------------------------------------------------------------------- |
| |
| obey TEST045(enable_cses); |
| |
| -------------------------------------------------------------------- |
| -- test some queries on populated Hive tables that we already have |
| -------------------------------------------------------------------- |
| |
| set schema hive.hive; |
| |
| prepare s from |
| with cse1 as (select count(*) from date_dim where d_dow = ?) |
| select * from cse1 |
| union all |
| select * from cse1; |
| execute show_cses; |
| execute s using 1; |
| -- execute a second time |
| execute s using 2; |
| |
| -- prepare a second time, so far not using query cache |
| prepare s from |
| with cse1 as (select count(*) from date_dim where d_dow = ?) |
| select * from cse1 |
| union all |
| select * from cse1; |
| execute show_cses; |
| execute s using 3; |
| -- execute a second time |
| execute s using 4; |
| |
| -- test subquery unnesting |
| cqd subquery_unnesting 'debug'; |
| |
| prepare s from |
| with cse1 as (select * from date_dim) |
| select count(*) |
| from cse1 x |
| where d_date_sk > (select avg(d_date_sk) from cse1 y where x.d_moy > y.d_moy); |
| -- currently not unnested |
| execute show_cses; |
| |
| -------------------------------------------------------------------- |
| -- test some TPC-DS queries |
| -------------------------------------------------------------------- |
| |
| set schema trafodion.t045_cses; |
| |
| --QID: 1 |
| |
| prepare s from |
| with customer_total_return as |
| (select sr_customer_sk as ctr_customer_sk |
| ,sr_store_sk as ctr_store_sk |
| ,sum(SR_REVERSED_CHARGE) as ctr_total_return |
| from store_returns |
| ,date_dim |
| where sr_returned_date_sk = d_date_sk |
| and d_year =2000 |
| group by sr_customer_sk |
| ,sr_store_sk |
| ) |
| |
| select c_customer_id |
| from customer_total_return ctr1 |
| ,store |
| ,customer |
| where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 |
| from customer_total_return ctr2 |
| where ctr1.ctr_store_sk = ctr2.ctr_store_sk) |
| and s_store_sk = ctr1.ctr_store_sk |
| and s_state = 'SD' |
| and ctr1.ctr_customer_sk = c_customer_sk |
| order by c_customer_id |
| limit 100; |
| execute show_cses; |
| execute s; |
| |
| |
| --QID: 2 |
| |
| prepare s from |
| with wscs as |
| (select sold_date_sk |
| ,sales_price |
| from (select ws_sold_date_sk sold_date_sk |
| ,ws_ext_sales_price sales_price |
| from web_sales |
| union all |
| select cs_sold_date_sk sold_date_sk |
| ,cs_ext_sales_price sales_price |
| from catalog_sales) t), |
| wswscs as |
| (select d_week_seq, |
| sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales, |
| sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales, |
| sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales, |
| sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales, |
| sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales, |
| sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales, |
| sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales |
| from wscs |
| ,date_dim |
| where d_date_sk = sold_date_sk |
| group by d_week_seq) |
| select d_week_seq1 |
| ,round(sun_sales1/sun_sales2,2) |
| ,round(mon_sales1/mon_sales2,2) |
| ,round(tue_sales1/tue_sales2,2) |
| ,round(wed_sales1/wed_sales2,2) |
| ,round(thu_sales1/thu_sales2,2) |
| ,round(fri_sales1/fri_sales2,2) |
| ,round(sat_sales1/sat_sales2,2) |
| from |
| (select wswscs.d_week_seq d_week_seq1 |
| ,sun_sales sun_sales1 |
| ,mon_sales mon_sales1 |
| ,tue_sales tue_sales1 |
| ,wed_sales wed_sales1 |
| ,thu_sales thu_sales1 |
| ,fri_sales fri_sales1 |
| ,sat_sales sat_sales1 |
| from wswscs,date_dim |
| where date_dim.d_week_seq = wswscs.d_week_seq and |
| d_year = 1999) y, |
| (select wswscs.d_week_seq d_week_seq2 |
| ,sun_sales sun_sales2 |
| ,mon_sales mon_sales2 |
| ,tue_sales tue_sales2 |
| ,wed_sales wed_sales2 |
| ,thu_sales thu_sales2 |
| ,fri_sales fri_sales2 |
| ,sat_sales sat_sales2 |
| from wswscs |
| ,date_dim |
| where date_dim.d_week_seq = wswscs.d_week_seq and |
| d_year = 1999+1) z |
| where d_week_seq1=d_week_seq2-53 |
| order by d_week_seq1; |
| -- use temp for wscs only, not wswscs, due to MapValueIds |
| execute show_cses; |
| execute s; |
| |
| |
| --QID: 4 |
| |
| prepare s from |
| with year_total as ( |
| select c_customer_id customer_id |
| ,c_first_name customer_first_name |
| ,c_last_name customer_last_name |
| ,c_preferred_cust_flag customer_preferred_cust_flag |
| ,c_birth_country customer_birth_country |
| ,c_login customer_login |
| ,c_email_address customer_email_address |
| ,d_year dyear |
| ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total |
| ,'s' sale_type |
| from customer |
| ,store_sales |
| ,date_dim |
| where c_customer_sk = ss_customer_sk |
| and ss_sold_date_sk = d_date_sk |
| group by c_customer_id |
| ,c_first_name |
| ,c_last_name |
| ,c_preferred_cust_flag |
| ,c_birth_country |
| ,c_login |
| ,c_email_address |
| ,d_year |
| union all |
| select c_customer_id customer_id |
| ,c_first_name customer_first_name |
| ,c_last_name customer_last_name |
| ,c_preferred_cust_flag customer_preferred_cust_flag |
| ,c_birth_country customer_birth_country |
| ,c_login customer_login |
| ,c_email_address customer_email_address |
| ,d_year dyear |
| ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total |
| ,'c' sale_type |
| from customer |
| ,catalog_sales |
| ,date_dim |
| where c_customer_sk = cs_bill_customer_sk |
| and cs_sold_date_sk = d_date_sk |
| group by c_customer_id |
| ,c_first_name |
| ,c_last_name |
| ,c_preferred_cust_flag |
| ,c_birth_country |
| ,c_login |
| ,c_email_address |
| ,d_year |
| union all |
| select c_customer_id customer_id |
| ,c_first_name customer_first_name |
| ,c_last_name customer_last_name |
| ,c_preferred_cust_flag customer_preferred_cust_flag |
| ,c_birth_country customer_birth_country |
| ,c_login customer_login |
| ,c_email_address customer_email_address |
| ,d_year dyear |
| ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total |
| ,'w' sale_type |
| from customer |
| ,web_sales |
| ,date_dim |
| where c_customer_sk = ws_bill_customer_sk |
| and ws_sold_date_sk = d_date_sk |
| group by c_customer_id |
| ,c_first_name |
| ,c_last_name |
| ,c_preferred_cust_flag |
| ,c_birth_country |
| ,c_login |
| ,c_email_address |
| ,d_year |
| ) |
| select |
| t_s_secyear.customer_id |
| ,t_s_secyear.customer_first_name |
| ,t_s_secyear.customer_last_name |
| ,t_s_secyear.customer_birth_country |
| from year_total t_s_firstyear |
| ,year_total t_s_secyear |
| ,year_total t_c_firstyear |
| ,year_total t_c_secyear |
| ,year_total t_w_firstyear |
| ,year_total t_w_secyear |
| where t_s_secyear.customer_id = t_s_firstyear.customer_id |
| and t_s_firstyear.customer_id = t_c_secyear.customer_id |
| and t_s_firstyear.customer_id = t_c_firstyear.customer_id |
| and t_s_firstyear.customer_id = t_w_firstyear.customer_id |
| and t_s_firstyear.customer_id = t_w_secyear.customer_id |
| and t_s_firstyear.sale_type = 's' |
| and t_c_firstyear.sale_type = 'c' |
| and t_w_firstyear.sale_type = 'w' |
| and t_s_secyear.sale_type = 's' |
| and t_c_secyear.sale_type = 'c' |
| and t_w_secyear.sale_type = 'w' |
| and t_s_firstyear.dyear = 1998 |
| and t_s_secyear.dyear = 1998+1 |
| and t_c_firstyear.dyear = 1998 |
| and t_c_secyear.dyear = 1998+1 |
| and t_w_firstyear.dyear = 1998 |
| and t_w_secyear.dyear = 1998+1 |
| and t_s_firstyear.year_total > 0 |
| and t_c_firstyear.year_total > 0 |
| and t_w_firstyear.year_total > 0 |
| and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end |
| > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end |
| and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end |
| > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end |
| order by t_s_secyear.customer_id |
| ,t_s_secyear.customer_first_name |
| ,t_s_secyear.customer_last_name |
| ,t_s_secyear.customer_birth_country |
| limit 100; |
| execute show_cses; |
| execute s; |
| |
| |
| --QID: 11 |
| |
| prepare s from |
| with year_total as ( |
| select c_customer_id customer_id |
| ,c_first_name customer_first_name |
| ,c_last_name customer_last_name |
| ,c_preferred_cust_flag customer_preferred_cust_flag |
| ,c_birth_country customer_birth_country |
| ,c_login customer_login |
| ,c_email_address customer_email_address |
| ,d_year dyear |
| ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total |
| ,'s' sale_type |
| from customer |
| ,store_sales |
| ,date_dim |
| where c_customer_sk = ss_customer_sk |
| and ss_sold_date_sk = d_date_sk |
| group by c_customer_id |
| ,c_first_name |
| ,c_last_name |
| ,c_preferred_cust_flag |
| ,c_birth_country |
| ,c_login |
| ,c_email_address |
| ,d_year |
| union all |
| select c_customer_id customer_id |
| ,c_first_name customer_first_name |
| ,c_last_name customer_last_name |
| ,c_preferred_cust_flag customer_preferred_cust_flag |
| ,c_birth_country customer_birth_country |
| ,c_login customer_login |
| ,c_email_address customer_email_address |
| ,d_year dyear |
| ,sum(ws_ext_list_price-ws_ext_discount_amt) year_total |
| ,'w' sale_type |
| from customer |
| ,web_sales |
| ,date_dim |
| where c_customer_sk = ws_bill_customer_sk |
| and ws_sold_date_sk = d_date_sk |
| group by c_customer_id |
| ,c_first_name |
| ,c_last_name |
| ,c_preferred_cust_flag |
| ,c_birth_country |
| ,c_login |
| ,c_email_address |
| ,d_year |
| ) |
| select |
| t_s_secyear.customer_id |
| ,t_s_secyear.customer_first_name |
| ,t_s_secyear.customer_last_name |
| ,t_s_secyear.customer_preferred_cust_flag |
| from year_total t_s_firstyear |
| ,year_total t_s_secyear |
| ,year_total t_w_firstyear |
| ,year_total t_w_secyear |
| where t_s_secyear.customer_id = t_s_firstyear.customer_id |
| and t_s_firstyear.customer_id = t_w_secyear.customer_id |
| and t_s_firstyear.customer_id = t_w_firstyear.customer_id |
| and t_s_firstyear.sale_type = 's' |
| and t_w_firstyear.sale_type = 'w' |
| and t_s_secyear.sale_type = 's' |
| and t_w_secyear.sale_type = 'w' |
| and t_s_firstyear.dyear = 2001 |
| and t_s_secyear.dyear = 2001+1 |
| and t_w_firstyear.dyear = 2001 |
| and t_w_secyear.dyear = 2001+1 |
| and t_s_firstyear.year_total > 0 |
| and t_w_firstyear.year_total > 0 |
| and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else 0.0 end |
| > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end |
| order by t_s_secyear.customer_id |
| ,t_s_secyear.customer_first_name |
| ,t_s_secyear.customer_last_name |
| ,t_s_secyear.customer_preferred_cust_flag |
| limit 100; |
| execute show_cses; |
| execute s; |
| |
| --QID: 14a |
| |
| prepare s from |
| -- ************** |
| -- VARIANT/ALTERNATIVE Version from TPC-DS (Rollout) is used |
| -- ************** |
| |
| with cross_items as |
| (select i_item_sk ss_item_sk |
| from item, |
| (select iss.i_brand_id brand_id |
| ,iss.i_class_id class_id |
| ,iss.i_category_id category_id |
| from store_sales |
| ,item iss |
| ,date_dim d1 |
| where ss_item_sk = iss.i_item_sk |
| and ss_sold_date_sk = d1.d_date_sk |
| and d1.d_year between 1999 AND 1999 + 2 |
| intersect |
| select ics.i_brand_id |
| ,ics.i_class_id |
| ,ics.i_category_id |
| from catalog_sales |
| ,item ics |
| ,date_dim d2 |
| where cs_item_sk = ics.i_item_sk |
| and cs_sold_date_sk = d2.d_date_sk |
| and d2.d_year between 1999 AND 1999 + 2 |
| intersect |
| select iws.i_brand_id |
| ,iws.i_class_id |
| ,iws.i_category_id |
| from web_sales |
| ,item iws |
| ,date_dim d3 |
| where ws_item_sk = iws.i_item_sk |
| and ws_sold_date_sk = d3.d_date_sk |
| and d3.d_year between 1999 AND 1999 + 2) x |
| where i_brand_id = brand_id |
| and i_class_id = class_id |
| and i_category_id = category_id |
| ) |
| , |
| avg_sales as |
| (select avg(quantity*list_price) average_sales |
| from (select ss_quantity quantity |
| ,ss_list_price list_price |
| from store_sales |
| ,date_dim |
| where ss_sold_date_sk = d_date_sk |
| and d_year between 1999 and 2001 |
| union all |
| select cs_quantity quantity |
| ,cs_list_price list_price |
| from catalog_sales |
| ,date_dim |
| where cs_sold_date_sk = d_date_sk |
| and d_year between 1999 AND 1999 + 2 |
| union all |
| select ws_quantity quantity |
| ,ws_list_price list_price |
| from web_sales |
| ,date_dim |
| where ws_sold_date_sk = d_date_sk |
| and d_year between 1999 AND 1999 + 2) x) |
| , |
| results AS |
| (select channel, i_brand_id, i_class_id, i_category_id, sum(sales) sum_sales, sum(number_sales) number_sales |
| from ( |
| select 'store' channel, i_brand_id,i_class_id |
| ,i_category_id,sum(ss_quantity*ss_list_price) sales |
| , count(*) number_sales |
| from store_sales |
| ,item |
| ,date_dim |
| where ss_item_sk in (select ss_item_sk from cross_items) |
| and ss_item_sk = i_item_sk |
| and ss_sold_date_sk = d_date_sk |
| and d_year = 1999 + 2 |
| and d_moy = 11 |
| group by i_brand_id,i_class_id,i_category_id |
| having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales) |
| union all |
| select 'catalog' channel, i_brand_id,i_class_id,i_category_id, sum(cs_quantity*cs_list_price) sales, count(*) number_sales |
| from catalog_sales |
| ,item |
| ,date_dim |
| where cs_item_sk in (select ss_item_sk from cross_items) |
| and cs_item_sk = i_item_sk |
| and cs_sold_date_sk = d_date_sk |
| and d_year = 1999 + 2 |
| and d_moy = 11 |
| group by i_brand_id,i_class_id,i_category_id |
| having sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales) |
| union all |
| select 'web' channel, i_brand_id,i_class_id,i_category_id, sum(ws_quantity*ws_list_price) sales , count(*) number_sales |
| from web_sales |
| ,item |
| ,date_dim |
| where ws_item_sk in (select ss_item_sk from cross_items) |
| and ws_item_sk = i_item_sk |
| and ws_sold_date_sk = d_date_sk |
| and d_year = 1999 + 2 |
| and d_moy = 11 |
| group by i_brand_id,i_class_id,i_category_id |
| having sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales) |
| ) y |
| group by channel, i_brand_id,i_class_id,i_category_id) |
| |
| select channel, i_brand_id, i_class_id, i_category_id, sum_sales, number_sales |
| from ( |
| select channel, i_brand_id, i_class_id, i_category_id, sum_sales, number_sales from results |
| union |
| select channel, i_brand_id, i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results |
| group by channel, i_brand_id, i_class_id |
| union |
| select channel, i_brand_id, null as i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results |
| group by channel, i_brand_id |
| union |
| select channel, null as i_brand_id, null as i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results |
| group by channel |
| union |
| select null as channel, null as i_brand_id, null as i_class_id, null as i_category_id, sum(sum_sales), sum(number_sales) from results) z |
| order by channel, i_brand_id, i_class_id, i_category_id |
| limit 100 ; |
| execute show_cses; |
| execute s; |
| |
| --QID: 23a |
| |
| prepare s from |
| with frequent_ss_items as |
| (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt |
| from store_sales |
| ,date_dim |
| ,item |
| where ss_sold_date_sk = d_date_sk |
| and ss_item_sk = i_item_sk |
| and d_year in (2000,2000+1,2000+2,2000+3) |
| group by substr(i_item_desc,1,30),i_item_sk,d_date |
| having count(*) >4), |
| max_store_sales as |
| (select max(csales) tpcds_cmax |
| from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales |
| from store_sales |
| ,customer |
| ,date_dim |
| where ss_customer_sk = c_customer_sk |
| and ss_sold_date_sk = d_date_sk |
| and d_year in (2000,2000+1,2000+2,2000+3) |
| group by c_customer_sk)), |
| best_ss_customer as |
| (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales |
| from store_sales |
| ,customer |
| where ss_customer_sk = c_customer_sk |
| group by c_customer_sk |
| having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select |
| * |
| from |
| max_store_sales)) |
| select sum(sales) |
| from (select cs_quantity*cs_list_price sales |
| from catalog_sales |
| ,date_dim |
| where d_year = 2000 |
| and d_moy = 6 |
| and cs_sold_date_sk = d_date_sk |
| and cs_item_sk in (select item_sk from frequent_ss_items) |
| and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) |
| union all |
| select ws_quantity*ws_list_price sales |
| from web_sales |
| ,date_dim |
| where d_year = 2000 |
| and d_moy = 6 |
| and ws_sold_date_sk = d_date_sk |
| and ws_item_sk in (select item_sk from frequent_ss_items) |
| and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) |
| limit 100; |
| execute show_cses; |
| execute s; |
| |
| --QID: 23b |
| |
| prepare s from |
| with frequent_ss_items as |
| (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt |
| from store_sales |
| ,date_dim |
| ,item |
| where ss_sold_date_sk = d_date_sk |
| and ss_item_sk = i_item_sk |
| and d_year in (2000,2000+1,2000+2,2000+3) |
| group by substr(i_item_desc,1,30),i_item_sk,d_date |
| having count(*) >4), |
| max_store_sales as |
| (select max(csales) tpcds_cmax |
| from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales |
| from store_sales |
| ,customer |
| ,date_dim |
| where ss_customer_sk = c_customer_sk |
| and ss_sold_date_sk = d_date_sk |
| and d_year in (2000,2000+1,2000+2,2000+3) |
| group by c_customer_sk)), |
| best_ss_customer as |
| (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales |
| from store_sales |
| ,customer |
| where ss_customer_sk = c_customer_sk |
| group by c_customer_sk |
| having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select |
| * |
| from max_store_sales)) |
| select c_last_name,c_first_name,sales |
| from (select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales |
| from catalog_sales |
| ,customer |
| ,date_dim |
| where d_year = 2000 |
| and d_moy = 6 |
| and cs_sold_date_sk = d_date_sk |
| and cs_item_sk in (select item_sk from frequent_ss_items) |
| and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) |
| and cs_bill_customer_sk = c_customer_sk |
| group by c_last_name,c_first_name |
| union all |
| select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales |
| from web_sales |
| ,customer |
| ,date_dim |
| where d_year = 2000 |
| and d_moy = 6 |
| and ws_sold_date_sk = d_date_sk |
| and ws_item_sk in (select item_sk from frequent_ss_items) |
| and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer) |
| and ws_bill_customer_sk = c_customer_sk |
| group by c_last_name,c_first_name) |
| order by c_last_name,c_first_name,sales |
| limit 100; |
| execute show_cses; |
| execute s; |
| |
| --QID: 24a |
| |
| prepare s from |
| with ssales as |
| (select c_last_name |
| ,c_first_name |
| ,s_store_name |
| ,ca_state |
| ,s_state |
| ,i_color |
| ,i_current_price |
| ,i_manager_id |
| ,i_units |
| ,i_size |
| ,sum(ss_net_paid) netpaid |
| from store_sales |
| ,store_returns |
| ,store |
| ,item |
| ,customer |
| ,customer_address |
| where ss_ticket_number = sr_ticket_number |
| and ss_item_sk = sr_item_sk |
| and ss_customer_sk = c_customer_sk |
| and ss_item_sk = i_item_sk |
| and ss_store_sk = s_store_sk |
| and c_birth_country = upper(ca_country) |
| and s_zip = ca_zip |
| and s_market_id = 10 |
| group by c_last_name |
| ,c_first_name |
| ,s_store_name |
| ,ca_state |
| ,s_state |
| ,i_color |
| ,i_current_price |
| ,i_manager_id |
| ,i_units |
| ,i_size) |
| select c_last_name |
| ,c_first_name |
| ,s_store_name |
| ,sum(netpaid) paid |
| from ssales |
| where i_color = 'smoke' |
| group by c_last_name |
| ,c_first_name |
| ,s_store_name |
| having sum(netpaid) > (select 0.05*avg(netpaid) |
| from ssales) |
| ; |
| execute show_cses; |
| execute s; |
| |
| --QID: 24b |
| |
| prepare s from |
| with ssales as |
| (select c_last_name |
| ,c_first_name |
| ,s_store_name |
| ,ca_state |
| ,s_state |
| ,i_color |
| ,i_current_price |
| ,i_manager_id |
| ,i_units |
| ,i_size |
| ,sum(ss_net_paid) netpaid |
| from store_sales |
| ,store_returns |
| ,store |
| ,item |
| ,customer |
| ,customer_address |
| where ss_ticket_number = sr_ticket_number |
| and ss_item_sk = sr_item_sk |
| and ss_customer_sk = c_customer_sk |
| and ss_item_sk = i_item_sk |
| and ss_store_sk = s_store_sk |
| and c_birth_country = upper(ca_country) |
| and s_zip = ca_zip |
| and s_market_id=10 |
| group by c_last_name |
| ,c_first_name |
| ,s_store_name |
| ,ca_state |
| ,s_state |
| ,i_color |
| ,i_current_price |
| ,i_manager_id |
| ,i_units |
| ,i_size) |
| select c_last_name |
| ,c_first_name |
| ,s_store_name |
| ,sum(netpaid) paid |
| from ssales |
| where i_color = 'pink' |
| group by c_last_name |
| ,c_first_name |
| ,s_store_name |
| having sum(netpaid) > (select 0.05*avg(netpaid) |
| from ssales) |
| ; |
| execute show_cses; |
| execute s; |
| |
| --QID: 30 |
| |
| prepare s from |
| with customer_total_return as |
| (select wr_returning_customer_sk as ctr_customer_sk |
| ,ca_state as ctr_state, |
| sum(wr_return_amt) as ctr_total_return |
| from web_returns |
| ,date_dim |
| ,customer_address |
| where wr_returned_date_sk = d_date_sk |
| and d_year =1999 |
| and wr_returning_addr_sk = ca_address_sk |
| group by wr_returning_customer_sk |
| ,ca_state) |
| select c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag |
| ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address |
| ,c_last_review_date_sk,ctr_total_return |
| from customer_total_return ctr1 |
| ,customer_address |
| ,customer |
| where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 |
| from customer_total_return ctr2 |
| where ctr1.ctr_state = ctr2.ctr_state) |
| and ca_address_sk = c_current_addr_sk |
| and ca_state = 'OK' |
| and ctr1.ctr_customer_sk = c_customer_sk |
| order by c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag |
| ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address |
| ,c_last_review_date_sk,ctr_total_return |
| limit 100; |
| execute show_cses; |
| execute s; |
| |
| --QID: 31 |
| |
| prepare s from |
| with ss as |
| (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales |
| from store_sales,date_dim,customer_address |
| where ss_sold_date_sk = d_date_sk |
| and ss_addr_sk=ca_address_sk |
| group by ca_county,d_qoy, d_year), |
| ws as |
| (select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales |
| from web_sales,date_dim,customer_address |
| where ws_sold_date_sk = d_date_sk |
| and ws_bill_addr_sk=ca_address_sk |
| group by ca_county,d_qoy, d_year) |
| select |
| ss1.ca_county |
| ,ss1.d_year |
| ,ws2.web_sales/ws1.web_sales web_q1_q2_increase |
| ,ss2.store_sales/ss1.store_sales store_q1_q2_increase |
| ,ws3.web_sales/ws2.web_sales web_q2_q3_increase |
| ,ss3.store_sales/ss2.store_sales store_q2_q3_increase |
| from |
| ss ss1 |
| ,ss ss2 |
| ,ss ss3 |
| ,ws ws1 |
| ,ws ws2 |
| ,ws ws3 |
| where |
| ss1.d_qoy = 1 |
| and ss1.d_year = 2002 |
| and ss1.ca_county = ss2.ca_county |
| and ss2.d_qoy = 2 |
| and ss2.d_year = 2002 |
| and ss2.ca_county = ss3.ca_county |
| and ss3.d_qoy = 3 |
| and ss3.d_year = 2002 |
| and ss1.ca_county = ws1.ca_county |
| and ws1.d_qoy = 1 |
| and ws1.d_year = 2002 |
| and ws1.ca_county = ws2.ca_county |
| and ws2.d_qoy = 2 |
| and ws2.d_year = 2002 |
| and ws1.ca_county = ws3.ca_county |
| and ws3.d_qoy = 3 |
| and ws3.d_year =2002 |
| and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end |
| > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end |
| and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end |
| > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end |
| order by store_q1_q2_increase; |
| -- Different constants used in different references of WITH clause - not yet supported |
| execute show_cses; |
| execute s; |
