Changeset: fc16f3074955 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/fc16f3074955 Branch: Jul2021 Log Message:
merged diffs (truncated from 12423 to 300 lines): diff --git a/sql/benchmarks/tpcds/Tests/one.test.in b/sql/benchmarks/tpcds/Tests/one.test.in --- a/sql/benchmarks/tpcds/Tests/one.test.in +++ b/sql/benchmarks/tpcds/Tests/one.test.in @@ -1366,957 +1366,721 @@ statement ok rollback query T rowsort -WITH customer_total_return AS - (SELECT sr_customer_sk AS ctr_customer_sk, - sr_store_sk AS ctr_store_sk, - sum(sr_return_amt) 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 = 'TN' - AND ctr1.ctr_customer_sk = c_customer_sk -ORDER BY c_customer_id -LIMIT 100 +-- query 1 +with customer_total_return as +(select sr_customer_sk as ctr_customer_sk +,sr_store_sk as ctr_store_sk +,sum(SR_RETURN_AMT) 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 = 'TN' +and ctr1.ctr_customer_sk = c_customer_sk +order by c_customer_id +limit 100 ---- 100 values hashing to 9ace740d2b6b717b43d7cdee1ef41373 query IRRRRRRR rowsort -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) sq1), - 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 = 2001) 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 = 2001+1) z -WHERE d_week_seq1 = d_week_seq2-53 -ORDER BY d_week_seq1 +-- query 2 +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) sq1), + 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 = 2001) 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 = 2001+1) z + where d_week_seq1=d_week_seq2-53 + order by d_week_seq1 ---- 20104 values hashing to d389792e8b24a6c5158cc3543fc1a10d query IITR rowsort -SELECT dt.d_year, - item.i_brand_id brand_id, - item.i_brand brand, - sum(ss_ext_sales_price) sum_agg -FROM date_dim dt, - store_sales, - item -WHERE dt.d_date_sk = store_sales.ss_sold_date_sk - AND store_sales.ss_item_sk = item.i_item_sk - AND item.i_manufact_id = 128 - AND dt.d_moy=11 -GROUP BY dt.d_year, - item.i_brand, - item.i_brand_id -ORDER BY dt.d_year, - sum_agg DESC, - brand_id -LIMIT 100 +-- query 3 +select dt.d_year + ,item.i_brand_id brand_id + ,item.i_brand brand + ,sum(ss_ext_sales_price) sum_agg + from date_dim dt + ,store_sales + ,item + where dt.d_date_sk = store_sales.ss_sold_date_sk + and store_sales.ss_item_sk = item.i_item_sk + and item.i_manufact_id = 128 + and dt.d_moy=11 + group by dt.d_year + ,item.i_brand + ,item.i_brand_id + order by dt.d_year + ,sum_agg desc + ,brand_id + limit 100 ---- 356 values hashing to f622c5c5ffff908edcd28dd716f580df query TTTT rowsort -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 _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
