Changeset: 2495e68b872c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2495e68b872c
Added Files:
sql/benchmarks/tpcds/Tests/11.reqtests
sql/benchmarks/tpcds/Tests/11.sql
sql/benchmarks/tpcds/Tests/11.sql.src
sql/benchmarks/tpcds/Tests/38.reqtests
sql/benchmarks/tpcds/Tests/38.sql
sql/benchmarks/tpcds/Tests/38.sql.src
sql/benchmarks/tpcds/Tests/72.reqtests
sql/benchmarks/tpcds/Tests/72.sql
sql/benchmarks/tpcds/Tests/72.sql.src
sql/benchmarks/tpcds/Tests/83.reqtests
sql/benchmarks/tpcds/Tests/83.sql
sql/benchmarks/tpcds/Tests/83.sql.src
sql/benchmarks/tpcds/Tests/99.reqtests
sql/benchmarks/tpcds/Tests/99.sql
sql/benchmarks/tpcds/Tests/99.sql.src
Modified Files:
sql/benchmarks/tpcds/Tests/All
Branch: default
Log Message:
Added TPC-DS queries 11, 38, 72, 83 and 99.
The output for SF 0.01 is actually right. Now I will check the output for SF 1
to find a possible wrong output.
diffs (truncated from 381 to 300 lines):
diff --git a/sql/benchmarks/tpcds/Tests/11.reqtests
b/sql/benchmarks/tpcds/Tests/11.reqtests
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/11.reqtests
@@ -0,0 +1,5 @@
+create
+check0
+load
+alter
+check1
diff --git a/sql/benchmarks/tpcds/Tests/11.sql
b/sql/benchmarks/tpcds/Tests/11.sql
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/11.sql
@@ -0,0 +1,80 @@
+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;
diff --git a/sql/benchmarks/tpcds/Tests/11.sql.src
b/sql/benchmarks/tpcds/Tests/11.sql.src
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/11.sql.src
@@ -0,0 +1,1 @@
+$RELSRCDIR/../11.sql
diff --git a/sql/benchmarks/tpcds/Tests/38.reqtests
b/sql/benchmarks/tpcds/Tests/38.reqtests
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/38.reqtests
@@ -0,0 +1,5 @@
+create
+check0
+load
+alter
+check1
diff --git a/sql/benchmarks/tpcds/Tests/38.sql
b/sql/benchmarks/tpcds/Tests/38.sql
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/38.sql
@@ -0,0 +1,28 @@
+SELECT count(*)
+FROM
+ (SELECT DISTINCT c_last_name,
+ c_first_name,
+ d_date
+ FROM store_sales,
+ date_dim,
+ customer
+ WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ AND store_sales.ss_customer_sk = customer.c_customer_sk
+ AND d_month_seq BETWEEN 1200 AND 1200 + 11 INTERSECT
+ SELECT DISTINCT c_last_name,
+ c_first_name,
+ d_date
+ FROM catalog_sales,
+ date_dim,
+ customer WHERE catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
+ AND catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
+ AND d_month_seq BETWEEN 1200 AND 1200 + 11 INTERSECT
+ SELECT DISTINCT c_last_name,
+ c_first_name,
+ d_date
+ FROM web_sales,
+ date_dim,
+ customer WHERE web_sales.ws_sold_date_sk = date_dim.d_date_sk
+ AND web_sales.ws_bill_customer_sk = customer.c_customer_sk
+ AND d_month_seq BETWEEN 1200 AND 1200 + 11 ) hot_cust
+LIMIT 100;
diff --git a/sql/benchmarks/tpcds/Tests/38.sql.src
b/sql/benchmarks/tpcds/Tests/38.sql.src
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/38.sql.src
@@ -0,0 +1,1 @@
+$RELSRCDIR/../38.sql
diff --git a/sql/benchmarks/tpcds/Tests/72.reqtests
b/sql/benchmarks/tpcds/Tests/72.reqtests
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/72.reqtests
@@ -0,0 +1,5 @@
+create
+check0
+load
+alter
+check1
diff --git a/sql/benchmarks/tpcds/Tests/72.sql
b/sql/benchmarks/tpcds/Tests/72.sql
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/72.sql
@@ -0,0 +1,38 @@
+SELECT i_item_desc,
+ w_warehouse_name,
+ d1.d_week_seq,
+ sum(CASE
+ WHEN p_promo_sk IS NULL THEN 1
+ ELSE 0
+ END) no_promo,
+ sum(CASE
+ WHEN p_promo_sk IS NOT NULL THEN 1
+ ELSE 0
+ END) promo,
+ count(*) total_cnt
+FROM catalog_sales
+JOIN inventory ON (cs_item_sk = inv_item_sk)
+JOIN warehouse ON (w_warehouse_sk=inv_warehouse_sk)
+JOIN item ON (i_item_sk = cs_item_sk)
+JOIN customer_demographics ON (cs_bill_cdemo_sk = cd_demo_sk)
+JOIN household_demographics ON (cs_bill_hdemo_sk = hd_demo_sk)
+JOIN date_dim d1 ON (cs_sold_date_sk = d1.d_date_sk)
+JOIN date_dim d2 ON (inv_date_sk = d2.d_date_sk)
+JOIN date_dim d3 ON (cs_ship_date_sk = d3.d_date_sk)
+LEFT OUTER JOIN promotion ON (cs_promo_sk=p_promo_sk)
+LEFT OUTER JOIN catalog_returns ON (cr_item_sk = cs_item_sk
+ AND cr_order_number = cs_order_number)
+WHERE d1.d_week_seq = d2.d_week_seq
+ AND inv_quantity_on_hand < cs_quantity
+ AND d3.d_date > d1.d_date + 5
+ AND hd_buy_potential = '>10000'
+ AND d1.d_year = 1999
+ AND cd_marital_status = 'D'
+GROUP BY i_item_desc,
+ w_warehouse_name,
+ d1.d_week_seq
+ORDER BY total_cnt DESC,
+ i_item_desc,
+ w_warehouse_name,
+ d_week_seq
+LIMIT 100;
diff --git a/sql/benchmarks/tpcds/Tests/72.sql.src
b/sql/benchmarks/tpcds/Tests/72.sql.src
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/72.sql.src
@@ -0,0 +1,1 @@
+$RELSRCDIR/../72.sql
diff --git a/sql/benchmarks/tpcds/Tests/83.reqtests
b/sql/benchmarks/tpcds/Tests/83.reqtests
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/83.reqtests
@@ -0,0 +1,5 @@
+create
+check0
+load
+alter
+check1
diff --git a/sql/benchmarks/tpcds/Tests/83.sql
b/sql/benchmarks/tpcds/Tests/83.sql
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/83.sql
@@ -0,0 +1,70 @@
+WITH sr_items AS
+ (SELECT i_item_id item_id,
+ sum(sr_return_quantity) sr_item_qty
+ FROM store_returns,
+ item,
+ date_dim
+ WHERE sr_item_sk = i_item_sk
+ AND d_date IN
+ (SELECT d_date
+ FROM date_dim
+ WHERE d_week_seq IN
+ (SELECT d_week_seq
+ FROM date_dim
+ WHERE d_date IN ('2000-06-30',
+ '2000-09-27',
+ '2000-11-17')))
+ AND sr_returned_date_sk = d_date_sk
+ GROUP BY i_item_id),
+ cr_items AS
+ (SELECT i_item_id item_id,
+ sum(cr_return_quantity) cr_item_qty
+ FROM catalog_returns,
+ item,
+ date_dim
+ WHERE cr_item_sk = i_item_sk
+ AND d_date IN
+ (SELECT d_date
+ FROM date_dim
+ WHERE d_week_seq IN
+ (SELECT d_week_seq
+ FROM date_dim
+ WHERE d_date IN ('2000-06-30',
+ '2000-09-27',
+ '2000-11-17')))
+ AND cr_returned_date_sk = d_date_sk
+ GROUP BY i_item_id),
+ wr_items AS
+ (SELECT i_item_id item_id,
+ sum(wr_return_quantity) wr_item_qty
+ FROM web_returns,
+ item,
+ date_dim
+ WHERE wr_item_sk = i_item_sk
+ AND d_date IN
+ (SELECT d_date
+ FROM date_dim
+ WHERE d_week_seq IN
+ (SELECT d_week_seq
+ FROM date_dim
+ WHERE d_date IN ('2000-06-30',
+ '2000-09-27',
+ '2000-11-17')))
+ AND wr_returned_date_sk = d_date_sk
+ GROUP BY i_item_id)
+SELECT sr_items.item_id ,
+ sr_item_qty ,
+ (sr_item_qty*1.0000)/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0000 * 100
sr_dev ,
+ cr_item_qty ,
+ (cr_item_qty*1.0000)/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0000 * 100
cr_dev ,
+ wr_item_qty ,
+ (wr_item_qty*1.0000)/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0000 * 100
wr_dev ,
+ (sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average
+FROM sr_items ,
+ cr_items ,
+ wr_items
+WHERE sr_items.item_id=cr_items.item_id
+ AND sr_items.item_id=wr_items.item_id
+ORDER BY sr_items.item_id ,
+ sr_item_qty
+LIMIT 100;
diff --git a/sql/benchmarks/tpcds/Tests/83.sql.src
b/sql/benchmarks/tpcds/Tests/83.sql.src
new file mode 100644
--- /dev/null
+++ b/sql/benchmarks/tpcds/Tests/83.sql.src
@@ -0,0 +1,1 @@
+$RELSRCDIR/../83.sql
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
