This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new b084f3b6716 branch-4.0: [chore](tools) split TPC-DS' multi sql in one
file #58691 (#59913)
b084f3b6716 is described below
commit b084f3b671605859f2a8f485ffe5020087d66abb
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Thu Jan 15 18:05:33 2026 +0800
branch-4.0: [chore](tools) split TPC-DS' multi sql in one file #58691
(#59913)
Cherry-picked from #58691
Co-authored-by: zclllyybb <[email protected]>
---
tools/tpcds-tools/bin/run-tpcds-queries.sh | 51 +++++++---
tools/tpcds-tools/queries/sf1/query14.sql | 106 ---------------------
.../{sf1000/query14.sql => sf1/query14_1.sql} | 101 --------------------
tools/tpcds-tools/queries/sf1/query23.sql | 55 -----------
.../{sf1000/query23.sql => sf1/query23_1.sql} | 49 ----------
tools/tpcds-tools/queries/sf1/query24.sql | 52 ----------
tools/tpcds-tools/queries/sf1/query24_1.sql | 52 ++++++++++
tools/tpcds-tools/queries/sf1/query39.sql | 26 -----
.../queries/sf1/{query39.sql => query39_1.sql} | 25 -----
tools/tpcds-tools/queries/sf100/query14.sql | 106 ---------------------
.../queries/sf100/{query14.sql => query14_1.sql} | 101 --------------------
tools/tpcds-tools/queries/sf100/query23.sql | 55 -----------
.../queries/sf100/{query23.sql => query23_1.sql} | 49 ----------
tools/tpcds-tools/queries/sf100/query24.sql | 52 ----------
tools/tpcds-tools/queries/sf100/query24_1.sql | 52 ++++++++++
tools/tpcds-tools/queries/sf100/query39.sql | 26 -----
.../queries/sf100/{query39.sql => query39_1.sql} | 24 -----
tools/tpcds-tools/queries/sf1000/query14.sql | 106 ---------------------
.../queries/sf1000/{query14.sql => query14_1.sql} | 101 --------------------
tools/tpcds-tools/queries/sf1000/query23.sql | 55 -----------
.../queries/sf1000/{query23.sql => query23_1.sql} | 49 ----------
tools/tpcds-tools/queries/sf1000/query24.sql | 52 ----------
tools/tpcds-tools/queries/sf1000/query24_1.sql | 52 ++++++++++
tools/tpcds-tools/queries/sf1000/query39.sql | 25 -----
.../queries/sf1000/{query39.sql => query39_1.sql} | 24 -----
tools/tpcds-tools/queries/sf10000/query14.sql | 106 ---------------------
.../queries/sf10000/{query14.sql => query14_1.sql} | 101 --------------------
tools/tpcds-tools/queries/sf10000/query23.sql | 55 -----------
.../queries/sf10000/{query23.sql => query23_1.sql} | 49 ----------
tools/tpcds-tools/queries/sf10000/query24.sql | 52 ----------
.../queries/sf10000/{query24.sql => query24_1.sql} | 52 ----------
tools/tpcds-tools/queries/sf10000/query39.sql | 26 -----
.../queries/sf10000/{query39.sql => query39_1.sql} | 25 -----
33 files changed, 192 insertions(+), 1720 deletions(-)
diff --git a/tools/tpcds-tools/bin/run-tpcds-queries.sh
b/tools/tpcds-tools/bin/run-tpcds-queries.sh
index 1af50eea3e9..011ae6a58f7 100755
--- a/tools/tpcds-tools/bin/run-tpcds-queries.sh
+++ b/tools/tpcds-tools/bin/run-tpcds-queries.sh
@@ -137,17 +137,27 @@ best_hot_run_sum=0
# run part of queries, set their index to query_array
# query_array=(59 17 29 25 47 40 54)
query_array=$(seq 1 99)
-# shellcheck disable=SC2068
-for i in ${query_array[@]}; do
- cold=0
- hot1=0
- hot2=0
- echo -ne "query${i}\t" | tee -a result.csv
+
+# Function to run a single query file
+run_query() {
+ local query_file=$1
+ local query_name=$2
+
+ if [[ ! -f "${query_file}" ]]; then
+ return
+ fi
+
+ local cold=0
+ local hot1=0
+ local hot2=0
+
+ echo -ne "${query_name}\t" | tee -a result.csv
start=$(date +%s%3N)
if ! output=$(mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}"
-D"${DB}" --comments \
- <"${TPCDS_QUERIES_DIR}/query${i}.sql" 2>&1); then
- printf "Error: Failed to execute query q%s (cold run). Output:\n%s\n"
"${i}" "${output}" >&2
- continue
+ <"${query_file}" 2>&1); then
+ printf "Error: Failed to execute query %s (cold run). Output:\n%s\n"
"${query_name}" "${output}" >&2
+ echo "" | tee -a result.csv
+ return
fi
end=$(date +%s%3N)
cold=$((end - start))
@@ -155,9 +165,10 @@ for i in ${query_array[@]}; do
start=$(date +%s%3N)
if ! output=$(mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}"
-D"${DB}" --comments \
- <"${TPCDS_QUERIES_DIR}/query${i}.sql" 2>&1); then
- printf "Error: Failed to execute query q%s (hot run 1). Output:\n%s\n"
"${i}" "${output}" >&2
- continue
+ <"${query_file}" 2>&1); then
+ printf "Error: Failed to execute query %s (hot run 1). Output:\n%s\n"
"${query_name}" "${output}" >&2
+ echo "" | tee -a result.csv
+ return
fi
end=$(date +%s%3N)
hot1=$((end - start))
@@ -165,9 +176,10 @@ for i in ${query_array[@]}; do
start=$(date +%s%3N)
if ! output=$(mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}"
-D"${DB}" --comments \
- <"${TPCDS_QUERIES_DIR}/query${i}.sql" 2>&1); then
- printf "Error: Failed to execute query q%s (hot run 2). Output:\n%s\n"
"${i}" "${output}" >&2
- continue
+ <"${query_file}" 2>&1); then
+ printf "Error: Failed to execute query %s (hot run 2). Output:\n%s\n"
"${query_name}" "${output}" >&2
+ echo "" | tee -a result.csv
+ return
fi
end=$(date +%s%3N)
hot2=$((end - start))
@@ -183,6 +195,15 @@ for i in ${query_array[@]}; do
echo -ne "${hot2}" | tee -a result.csv
echo "" | tee -a result.csv
fi
+}
+
+# shellcheck disable=SC2068
+for i in ${query_array[@]}; do
+ # Run main query file
+ run_query "${TPCDS_QUERIES_DIR}/query${i}.sql" "query${i}"
+
+ # Run variant query file if exists
+ run_query "${TPCDS_QUERIES_DIR}/query${i}_1.sql" "query${i}_1"
done
echo "Total cold run time: ${cold_run_sum} ms"
diff --git a/tools/tpcds-tools/queries/sf1/query14.sql
b/tools/tpcds-tools/queries/sf1/query14.sql
index e4cc31f9c86..3489bafc6f1 100644
--- a/tools/tpcds-tools/queries/sf1/query14.sql
+++ b/tools/tpcds-tools/queries/sf1/query14.sql
@@ -99,109 +99,3 @@ with cross_items as
group by rollup (channel, i_brand_id,i_class_id,i_category_id)
order by channel,i_brand_id,i_class_id,i_category_id
limit 100;
-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 1999 + 2
- 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)
- select this_year.channel ty_channel
- ,this_year.i_brand_id ty_brand
- ,this_year.i_class_id ty_class
- ,this_year.i_category_id ty_category
- ,this_year.sales ty_sales
- ,this_year.number_sales ty_number_sales
- ,last_year.channel ly_channel
- ,last_year.i_brand_id ly_brand
- ,last_year.i_class_id ly_class
- ,last_year.i_category_id ly_category
- ,last_year.sales ly_sales
- ,last_year.number_sales ly_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_week_seq = (select d_week_seq
- from date_dim
- where d_year = 1999 + 1
- and d_moy = 12
- and d_dom = 3)
- group by i_brand_id,i_class_id,i_category_id
- having sum(ss_quantity*ss_list_price) > (select average_sales from
avg_sales)) this_year,
- (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_week_seq = (select d_week_seq
- from date_dim
- where d_year = 1999
- and d_moy = 12
- and d_dom = 3)
- group by i_brand_id,i_class_id,i_category_id
- having sum(ss_quantity*ss_list_price) > (select average_sales from
avg_sales)) last_year
- where this_year.i_brand_id= last_year.i_brand_id
- and this_year.i_class_id = last_year.i_class_id
- and this_year.i_category_id = last_year.i_category_id
- order by this_year.channel, this_year.i_brand_id, this_year.i_class_id,
this_year.i_category_id
- limit 100;
diff --git a/tools/tpcds-tools/queries/sf1000/query14.sql
b/tools/tpcds-tools/queries/sf1/query14_1.sql
similarity index 52%
copy from tools/tpcds-tools/queries/sf1000/query14.sql
copy to tools/tpcds-tools/queries/sf1/query14_1.sql
index 0ec2b10f184..eef68e7df84 100644
--- a/tools/tpcds-tools/queries/sf1000/query14.sql
+++ b/tools/tpcds-tools/queries/sf1/query14_1.sql
@@ -1,104 +1,3 @@
-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)
- t 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 1999 + 2
- 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)
- select channel, i_brand_id,i_class_id,i_category_id,sum(sales),
sum(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 rollup (channel, i_brand_id,i_class_id,i_category_id)
- order by channel,i_brand_id,i_class_id,i_category_id
- limit 100;
with cross_items as
(select i_item_sk ss_item_sk
from item,
diff --git a/tools/tpcds-tools/queries/sf1/query23.sql
b/tools/tpcds-tools/queries/sf1/query23.sql
index aff598b44dd..e1a063c5e0b 100644
--- a/tools/tpcds-tools/queries/sf1/query23.sql
+++ b/tools/tpcds-tools/queries/sf1/query23.sql
@@ -47,58 +47,3 @@ from
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)) t2
limit 100;
-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) t),
- 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 = 7
- 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 = 7
- 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) t2
- order by c_last_name,c_first_name,sales
- limit 100;
diff --git a/tools/tpcds-tools/queries/sf1000/query23.sql
b/tools/tpcds-tools/queries/sf1/query23_1.sql
similarity index 54%
copy from tools/tpcds-tools/queries/sf1000/query23.sql
copy to tools/tpcds-tools/queries/sf1/query23_1.sql
index b9896edaf59..604269cb76d 100644
--- a/tools/tpcds-tools/queries/sf1000/query23.sql
+++ b/tools/tpcds-tools/queries/sf1/query23_1.sql
@@ -1,52 +1,3 @@
-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) t),
-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 = 7
- 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 = 7
- 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)) t2
- limit 100;
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
diff --git a/tools/tpcds-tools/queries/sf1/query24.sql
b/tools/tpcds-tools/queries/sf1/query24.sql
index f29cf674646..acb5f65c29d 100644
--- a/tools/tpcds-tools/queries/sf1/query24.sql
+++ b/tools/tpcds-tools/queries/sf1/query24.sql
@@ -50,55 +50,3 @@ order by c_last_name
,c_first_name
,s_store_name
;
-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_current_addr_sk = ca_address_sk
- and c_birth_country <> upper(ca_country)
- and s_zip = ca_zip
- and s_market_id = 5
-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 = 'seashell'
-group by c_last_name
- ,c_first_name
- ,s_store_name
-having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
-order by c_last_name
- ,c_first_name
- ,s_store_name
-;
diff --git a/tools/tpcds-tools/queries/sf1/query24_1.sql
b/tools/tpcds-tools/queries/sf1/query24_1.sql
new file mode 100644
index 00000000000..e61c5735eda
--- /dev/null
+++ b/tools/tpcds-tools/queries/sf1/query24_1.sql
@@ -0,0 +1,52 @@
+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_current_addr_sk = ca_address_sk
+ and c_birth_country <> upper(ca_country)
+ and s_zip = ca_zip
+ and s_market_id = 5
+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 = 'seashell'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
+order by c_last_name
+ ,c_first_name
+ ,s_store_name
+;
diff --git a/tools/tpcds-tools/queries/sf1/query39.sql
b/tools/tpcds-tools/queries/sf1/query39.sql
index 9ffe92d6792..1b7e629b3dc 100644
--- a/tools/tpcds-tools/queries/sf1/query39.sql
+++ b/tools/tpcds-tools/queries/sf1/query39.sql
@@ -23,29 +23,3 @@ where inv1.i_item_sk = inv2.i_item_sk
order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
,inv2.d_moy,inv2.mean, inv2.cov
;
-with inv as
-(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stdev,mean, case mean when 0 then null else stdev/mean end cov
- from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand)
mean
- from inventory
- ,item
- ,warehouse
- ,date_dim
- where inv_item_sk = i_item_sk
- and inv_warehouse_sk = w_warehouse_sk
- and inv_date_sk = d_date_sk
- and d_year =2000
- group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
- where case mean when 0 then 0 else stdev/mean end > 1)
-select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
- ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
-from inv inv1,inv inv2
-where inv1.i_item_sk = inv2.i_item_sk
- and inv1.w_warehouse_sk = inv2.w_warehouse_sk
- and inv1.d_moy=1
- and inv2.d_moy=1+1
- and inv1.cov > 1.5
-order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
- ,inv2.d_moy,inv2.mean, inv2.cov
-;
diff --git a/tools/tpcds-tools/queries/sf1/query39.sql
b/tools/tpcds-tools/queries/sf1/query39_1.sql
similarity index 50%
copy from tools/tpcds-tools/queries/sf1/query39.sql
copy to tools/tpcds-tools/queries/sf1/query39_1.sql
index 9ffe92d6792..787857b4b41 100644
--- a/tools/tpcds-tools/queries/sf1/query39.sql
+++ b/tools/tpcds-tools/queries/sf1/query39_1.sql
@@ -16,31 +16,6 @@ with inv as
select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
from inv inv1,inv inv2
-where inv1.i_item_sk = inv2.i_item_sk
- and inv1.w_warehouse_sk = inv2.w_warehouse_sk
- and inv1.d_moy=1
- and inv2.d_moy=1+1
-order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
- ,inv2.d_moy,inv2.mean, inv2.cov
-;
-with inv as
-(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stdev,mean, case mean when 0 then null else stdev/mean end cov
- from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand)
mean
- from inventory
- ,item
- ,warehouse
- ,date_dim
- where inv_item_sk = i_item_sk
- and inv_warehouse_sk = w_warehouse_sk
- and inv_date_sk = d_date_sk
- and d_year =2000
- group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
- where case mean when 0 then 0 else stdev/mean end > 1)
-select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
- ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
-from inv inv1,inv inv2
where inv1.i_item_sk = inv2.i_item_sk
and inv1.w_warehouse_sk = inv2.w_warehouse_sk
and inv1.d_moy=1
diff --git a/tools/tpcds-tools/queries/sf100/query14.sql
b/tools/tpcds-tools/queries/sf100/query14.sql
index 774c54cc183..ec782942830 100644
--- a/tools/tpcds-tools/queries/sf100/query14.sql
+++ b/tools/tpcds-tools/queries/sf100/query14.sql
@@ -99,109 +99,3 @@ with cross_items as
group by rollup (channel, i_brand_id,i_class_id,i_category_id)
order by channel,i_brand_id,i_class_id,i_category_id
limit 100;
-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 2000 AND 2000 + 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 2000 AND 2000 + 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 2000 AND 2000 + 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 2000 and 2000 + 2
- 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 2000 and 2000 + 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 2000 and 2000 + 2) x)
- select this_year.channel ty_channel
- ,this_year.i_brand_id ty_brand
- ,this_year.i_class_id ty_class
- ,this_year.i_category_id ty_category
- ,this_year.sales ty_sales
- ,this_year.number_sales ty_number_sales
- ,last_year.channel ly_channel
- ,last_year.i_brand_id ly_brand
- ,last_year.i_class_id ly_class
- ,last_year.i_category_id ly_category
- ,last_year.sales ly_sales
- ,last_year.number_sales ly_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_week_seq = (select d_week_seq
- from date_dim
- where d_year = 2000 + 1
- and d_moy = 12
- and d_dom = 10)
- group by i_brand_id,i_class_id,i_category_id
- having sum(ss_quantity*ss_list_price) > (select average_sales from
avg_sales)) this_year,
- (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_week_seq = (select d_week_seq
- from date_dim
- where d_year = 2000
- and d_moy = 12
- and d_dom = 10)
- group by i_brand_id,i_class_id,i_category_id
- having sum(ss_quantity*ss_list_price) > (select average_sales from
avg_sales)) last_year
- where this_year.i_brand_id= last_year.i_brand_id
- and this_year.i_class_id = last_year.i_class_id
- and this_year.i_category_id = last_year.i_category_id
- order by this_year.channel, this_year.i_brand_id, this_year.i_class_id,
this_year.i_category_id
- limit 100;
diff --git a/tools/tpcds-tools/queries/sf100/query14.sql
b/tools/tpcds-tools/queries/sf100/query14_1.sql
similarity index 52%
copy from tools/tpcds-tools/queries/sf100/query14.sql
copy to tools/tpcds-tools/queries/sf100/query14_1.sql
index 774c54cc183..fa43740a138 100644
--- a/tools/tpcds-tools/queries/sf100/query14.sql
+++ b/tools/tpcds-tools/queries/sf100/query14_1.sql
@@ -1,104 +1,3 @@
-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 2000 AND 2000 + 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 2000 AND 2000 + 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 2000 AND 2000 + 2)
- t 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 2000 and 2000 + 2
- 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 2000 and 2000 + 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 2000 and 2000 + 2) x)
- select channel, i_brand_id,i_class_id,i_category_id,sum(sales),
sum(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 = 2000+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 = 2000+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 = 2000+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 rollup (channel, i_brand_id,i_class_id,i_category_id)
- order by channel,i_brand_id,i_class_id,i_category_id
- limit 100;
with cross_items as
(select i_item_sk ss_item_sk
from item,
diff --git a/tools/tpcds-tools/queries/sf100/query23.sql
b/tools/tpcds-tools/queries/sf100/query23.sql
index c5d30837a28..17650e54b07 100644
--- a/tools/tpcds-tools/queries/sf100/query23.sql
+++ b/tools/tpcds-tools/queries/sf100/query23.sql
@@ -47,58 +47,3 @@ from
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)) t2
limit 100;
-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) t),
- 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 = 5
- 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 = 5
- 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) t2
- order by c_last_name,c_first_name,sales
- limit 100;
diff --git a/tools/tpcds-tools/queries/sf100/query23.sql
b/tools/tpcds-tools/queries/sf100/query23_1.sql
similarity index 54%
copy from tools/tpcds-tools/queries/sf100/query23.sql
copy to tools/tpcds-tools/queries/sf100/query23_1.sql
index c5d30837a28..0a403f300d8 100644
--- a/tools/tpcds-tools/queries/sf100/query23.sql
+++ b/tools/tpcds-tools/queries/sf100/query23_1.sql
@@ -1,52 +1,3 @@
-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) t),
- 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 = 5
- 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 = 5
- 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)) t2
- limit 100;
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
diff --git a/tools/tpcds-tools/queries/sf100/query24.sql
b/tools/tpcds-tools/queries/sf100/query24.sql
index 27511a97a05..25a24819341 100644
--- a/tools/tpcds-tools/queries/sf100/query24.sql
+++ b/tools/tpcds-tools/queries/sf100/query24.sql
@@ -50,55 +50,3 @@ order by c_last_name
,c_first_name
,s_store_name
;
-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_profit) 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_current_addr_sk = ca_address_sk
- and c_birth_country <> upper(ca_country)
- and s_zip = ca_zip
- and s_market_id = 8
-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 = 'blue'
-group by c_last_name
- ,c_first_name
- ,s_store_name
-having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
-order by c_last_name
- ,c_first_name
- ,s_store_name
-;
diff --git a/tools/tpcds-tools/queries/sf100/query24_1.sql
b/tools/tpcds-tools/queries/sf100/query24_1.sql
new file mode 100644
index 00000000000..e702e7d38bf
--- /dev/null
+++ b/tools/tpcds-tools/queries/sf100/query24_1.sql
@@ -0,0 +1,52 @@
+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_profit) 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_current_addr_sk = ca_address_sk
+ and c_birth_country <> upper(ca_country)
+ and s_zip = ca_zip
+ and s_market_id = 8
+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 = 'blue'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
+order by c_last_name
+ ,c_first_name
+ ,s_store_name
+;
diff --git a/tools/tpcds-tools/queries/sf100/query39.sql
b/tools/tpcds-tools/queries/sf100/query39.sql
index 0fc09ed47ec..eb26bafca5f 100644
--- a/tools/tpcds-tools/queries/sf100/query39.sql
+++ b/tools/tpcds-tools/queries/sf100/query39.sql
@@ -22,29 +22,3 @@ where inv1.i_item_sk = inv2.i_item_sk
and inv2.d_moy=1+1
order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
,inv2.d_moy,inv2.mean, inv2.cov;
-with inv as
-(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stdev,mean, case mean when 0 then null else stdev/mean end cov
- from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand)
mean
- from inventory
- ,item
- ,warehouse
- ,date_dim
- where inv_item_sk = i_item_sk
- and inv_warehouse_sk = w_warehouse_sk
- and inv_date_sk = d_date_sk
- and d_year =1998
- group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
- where case mean when 0 then 0 else stdev/mean end > 1)
-select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
- ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
-from inv inv1,inv inv2
-where inv1.i_item_sk = inv2.i_item_sk
- and inv1.w_warehouse_sk = inv2.w_warehouse_sk
- and inv1.d_moy=1
- and inv2.d_moy=1+1
- and inv1.cov > 1.5
-order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
- ,inv2.d_moy,inv2.mean, inv2.cov
-;
diff --git a/tools/tpcds-tools/queries/sf100/query39.sql
b/tools/tpcds-tools/queries/sf100/query39_1.sql
similarity index 50%
copy from tools/tpcds-tools/queries/sf100/query39.sql
copy to tools/tpcds-tools/queries/sf100/query39_1.sql
index 0fc09ed47ec..b77e1c8f6d4 100644
--- a/tools/tpcds-tools/queries/sf100/query39.sql
+++ b/tools/tpcds-tools/queries/sf100/query39_1.sql
@@ -16,30 +16,6 @@ with inv as
select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
from inv inv1,inv inv2
-where inv1.i_item_sk = inv2.i_item_sk
- and inv1.w_warehouse_sk = inv2.w_warehouse_sk
- and inv1.d_moy=1
- and inv2.d_moy=1+1
-order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
- ,inv2.d_moy,inv2.mean, inv2.cov;
-with inv as
-(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stdev,mean, case mean when 0 then null else stdev/mean end cov
- from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand)
mean
- from inventory
- ,item
- ,warehouse
- ,date_dim
- where inv_item_sk = i_item_sk
- and inv_warehouse_sk = w_warehouse_sk
- and inv_date_sk = d_date_sk
- and d_year =1998
- group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
- where case mean when 0 then 0 else stdev/mean end > 1)
-select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
- ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
-from inv inv1,inv inv2
where inv1.i_item_sk = inv2.i_item_sk
and inv1.w_warehouse_sk = inv2.w_warehouse_sk
and inv1.d_moy=1
diff --git a/tools/tpcds-tools/queries/sf1000/query14.sql
b/tools/tpcds-tools/queries/sf1000/query14.sql
index 0ec2b10f184..10cbd6a59a6 100644
--- a/tools/tpcds-tools/queries/sf1000/query14.sql
+++ b/tools/tpcds-tools/queries/sf1000/query14.sql
@@ -99,109 +99,3 @@ avg_sales as
group by rollup (channel, i_brand_id,i_class_id,i_category_id)
order by channel,i_brand_id,i_class_id,i_category_id
limit 100;
-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 1999 + 2
- 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)
- select this_year.channel ty_channel
- ,this_year.i_brand_id ty_brand
- ,this_year.i_class_id ty_class
- ,this_year.i_category_id ty_category
- ,this_year.sales ty_sales
- ,this_year.number_sales ty_number_sales
- ,last_year.channel ly_channel
- ,last_year.i_brand_id ly_brand
- ,last_year.i_class_id ly_class
- ,last_year.i_category_id ly_category
- ,last_year.sales ly_sales
- ,last_year.number_sales ly_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_week_seq = (select d_week_seq
- from date_dim
- where d_year = 1999 + 1
- and d_moy = 12
- and d_dom = 3)
- group by i_brand_id,i_class_id,i_category_id
- having sum(ss_quantity*ss_list_price) > (select average_sales from
avg_sales)) this_year,
- (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_week_seq = (select d_week_seq
- from date_dim
- where d_year = 1999
- and d_moy = 12
- and d_dom = 3)
- group by i_brand_id,i_class_id,i_category_id
- having sum(ss_quantity*ss_list_price) > (select average_sales from
avg_sales)) last_year
- where this_year.i_brand_id= last_year.i_brand_id
- and this_year.i_class_id = last_year.i_class_id
- and this_year.i_category_id = last_year.i_category_id
- order by this_year.channel, this_year.i_brand_id, this_year.i_class_id,
this_year.i_category_id
- limit 100;
diff --git a/tools/tpcds-tools/queries/sf1000/query14.sql
b/tools/tpcds-tools/queries/sf1000/query14_1.sql
similarity index 52%
copy from tools/tpcds-tools/queries/sf1000/query14.sql
copy to tools/tpcds-tools/queries/sf1000/query14_1.sql
index 0ec2b10f184..eef68e7df84 100644
--- a/tools/tpcds-tools/queries/sf1000/query14.sql
+++ b/tools/tpcds-tools/queries/sf1000/query14_1.sql
@@ -1,104 +1,3 @@
-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)
- t 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 1999 + 2
- 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)
- select channel, i_brand_id,i_class_id,i_category_id,sum(sales),
sum(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 rollup (channel, i_brand_id,i_class_id,i_category_id)
- order by channel,i_brand_id,i_class_id,i_category_id
- limit 100;
with cross_items as
(select i_item_sk ss_item_sk
from item,
diff --git a/tools/tpcds-tools/queries/sf1000/query23.sql
b/tools/tpcds-tools/queries/sf1000/query23.sql
index b9896edaf59..3ea7ffa1b3b 100644
--- a/tools/tpcds-tools/queries/sf1000/query23.sql
+++ b/tools/tpcds-tools/queries/sf1000/query23.sql
@@ -47,58 +47,3 @@ from
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)) t2
limit 100;
-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) t),
- 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 = 7
- 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 = 7
- 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) t2
- order by c_last_name,c_first_name,sales
- limit 100;
diff --git a/tools/tpcds-tools/queries/sf1000/query23.sql
b/tools/tpcds-tools/queries/sf1000/query23_1.sql
similarity index 54%
copy from tools/tpcds-tools/queries/sf1000/query23.sql
copy to tools/tpcds-tools/queries/sf1000/query23_1.sql
index b9896edaf59..604269cb76d 100644
--- a/tools/tpcds-tools/queries/sf1000/query23.sql
+++ b/tools/tpcds-tools/queries/sf1000/query23_1.sql
@@ -1,52 +1,3 @@
-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) t),
-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 = 7
- 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 = 7
- 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)) t2
- limit 100;
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
diff --git a/tools/tpcds-tools/queries/sf1000/query24.sql
b/tools/tpcds-tools/queries/sf1000/query24.sql
index f29cf674646..acb5f65c29d 100644
--- a/tools/tpcds-tools/queries/sf1000/query24.sql
+++ b/tools/tpcds-tools/queries/sf1000/query24.sql
@@ -50,55 +50,3 @@ order by c_last_name
,c_first_name
,s_store_name
;
-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_current_addr_sk = ca_address_sk
- and c_birth_country <> upper(ca_country)
- and s_zip = ca_zip
- and s_market_id = 5
-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 = 'seashell'
-group by c_last_name
- ,c_first_name
- ,s_store_name
-having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
-order by c_last_name
- ,c_first_name
- ,s_store_name
-;
diff --git a/tools/tpcds-tools/queries/sf1000/query24_1.sql
b/tools/tpcds-tools/queries/sf1000/query24_1.sql
new file mode 100644
index 00000000000..e61c5735eda
--- /dev/null
+++ b/tools/tpcds-tools/queries/sf1000/query24_1.sql
@@ -0,0 +1,52 @@
+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_current_addr_sk = ca_address_sk
+ and c_birth_country <> upper(ca_country)
+ and s_zip = ca_zip
+ and s_market_id = 5
+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 = 'seashell'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
+order by c_last_name
+ ,c_first_name
+ ,s_store_name
+;
diff --git a/tools/tpcds-tools/queries/sf1000/query39.sql
b/tools/tpcds-tools/queries/sf1000/query39.sql
index 48e4e3a12a5..9e53c80d79b 100644
--- a/tools/tpcds-tools/queries/sf1000/query39.sql
+++ b/tools/tpcds-tools/queries/sf1000/query39.sql
@@ -22,28 +22,3 @@ where inv1.i_item_sk = inv2.i_item_sk
and inv2.d_moy=1+1
order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
,inv2.d_moy,inv2.mean, inv2.cov;
-with inv as
-(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stdev,mean, case mean when 0 then null else stdev/mean end cov
- from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand)
mean
- from inventory
- ,item
- ,warehouse
- ,date_dim
- where inv_item_sk = i_item_sk
- and inv_warehouse_sk = w_warehouse_sk
- and inv_date_sk = d_date_sk
- and d_year =2000
- group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
- where case mean when 0 then 0 else stdev/mean end > 1)
-select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
- ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
-from inv inv1,inv inv2
-where inv1.i_item_sk = inv2.i_item_sk
- and inv1.w_warehouse_sk = inv2.w_warehouse_sk
- and inv1.d_moy=1
- and inv2.d_moy=1+1
- and inv1.cov > 1.5
-order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
- ,inv2.d_moy,inv2.mean, inv2.cov;
diff --git a/tools/tpcds-tools/queries/sf1000/query39.sql
b/tools/tpcds-tools/queries/sf1000/query39_1.sql
similarity index 50%
copy from tools/tpcds-tools/queries/sf1000/query39.sql
copy to tools/tpcds-tools/queries/sf1000/query39_1.sql
index 48e4e3a12a5..aecb5f9ec22 100644
--- a/tools/tpcds-tools/queries/sf1000/query39.sql
+++ b/tools/tpcds-tools/queries/sf1000/query39_1.sql
@@ -16,30 +16,6 @@ with inv as
select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
from inv inv1,inv inv2
-where inv1.i_item_sk = inv2.i_item_sk
- and inv1.w_warehouse_sk = inv2.w_warehouse_sk
- and inv1.d_moy=1
- and inv2.d_moy=1+1
-order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
- ,inv2.d_moy,inv2.mean, inv2.cov;
-with inv as
-(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stdev,mean, case mean when 0 then null else stdev/mean end cov
- from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand)
mean
- from inventory
- ,item
- ,warehouse
- ,date_dim
- where inv_item_sk = i_item_sk
- and inv_warehouse_sk = w_warehouse_sk
- and inv_date_sk = d_date_sk
- and d_year =2000
- group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
- where case mean when 0 then 0 else stdev/mean end > 1)
-select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
- ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
-from inv inv1,inv inv2
where inv1.i_item_sk = inv2.i_item_sk
and inv1.w_warehouse_sk = inv2.w_warehouse_sk
and inv1.d_moy=1
diff --git a/tools/tpcds-tools/queries/sf10000/query14.sql
b/tools/tpcds-tools/queries/sf10000/query14.sql
index 4e6947c30ae..ec782942830 100644
--- a/tools/tpcds-tools/queries/sf10000/query14.sql
+++ b/tools/tpcds-tools/queries/sf10000/query14.sql
@@ -99,109 +99,3 @@ with cross_items as
group by rollup (channel, i_brand_id,i_class_id,i_category_id)
order by channel,i_brand_id,i_class_id,i_category_id
limit 100;
-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 2000 AND 2000 + 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 2000 AND 2000 + 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 2000 AND 2000 + 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 2000 and 2000 + 2
- 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 2000 and 2000 + 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 2000 and 2000 + 2) x)
- select this_year.channel ty_channel
- ,this_year.i_brand_id ty_brand
- ,this_year.i_class_id ty_class
- ,this_year.i_category_id ty_category
- ,this_year.sales ty_sales
- ,this_year.number_sales ty_number_sales
- ,last_year.channel ly_channel
- ,last_year.i_brand_id ly_brand
- ,last_year.i_class_id ly_class
- ,last_year.i_category_id ly_category
- ,last_year.sales ly_sales
- ,last_year.number_sales ly_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_week_seq = (select d_week_seq
- from date_dim
- where d_year = 2000 + 1
- and d_moy = 12
- and d_dom = 15)
- group by i_brand_id,i_class_id,i_category_id
- having sum(ss_quantity*ss_list_price) > (select average_sales from
avg_sales)) this_year,
- (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_week_seq = (select d_week_seq
- from date_dim
- where d_year = 2000
- and d_moy = 12
- and d_dom = 15)
- group by i_brand_id,i_class_id,i_category_id
- having sum(ss_quantity*ss_list_price) > (select average_sales from
avg_sales)) last_year
- where this_year.i_brand_id= last_year.i_brand_id
- and this_year.i_class_id = last_year.i_class_id
- and this_year.i_category_id = last_year.i_category_id
- order by this_year.channel, this_year.i_brand_id, this_year.i_class_id,
this_year.i_category_id
- limit 100;
diff --git a/tools/tpcds-tools/queries/sf10000/query14.sql
b/tools/tpcds-tools/queries/sf10000/query14_1.sql
similarity index 52%
copy from tools/tpcds-tools/queries/sf10000/query14.sql
copy to tools/tpcds-tools/queries/sf10000/query14_1.sql
index 4e6947c30ae..8fbf8972a55 100644
--- a/tools/tpcds-tools/queries/sf10000/query14.sql
+++ b/tools/tpcds-tools/queries/sf10000/query14_1.sql
@@ -1,104 +1,3 @@
-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 2000 AND 2000 + 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 2000 AND 2000 + 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 2000 AND 2000 + 2)
- t 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 2000 and 2000 + 2
- 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 2000 and 2000 + 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 2000 and 2000 + 2) x)
- select channel, i_brand_id,i_class_id,i_category_id,sum(sales),
sum(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 = 2000+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 = 2000+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 = 2000+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 rollup (channel, i_brand_id,i_class_id,i_category_id)
- order by channel,i_brand_id,i_class_id,i_category_id
- limit 100;
with cross_items as
(select i_item_sk ss_item_sk
from item,
diff --git a/tools/tpcds-tools/queries/sf10000/query23.sql
b/tools/tpcds-tools/queries/sf10000/query23.sql
index ff73709f4eb..582c976b370 100644
--- a/tools/tpcds-tools/queries/sf10000/query23.sql
+++ b/tools/tpcds-tools/queries/sf10000/query23.sql
@@ -47,58 +47,3 @@ from
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)) t2
limit 100;
-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) t),
- 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 = 3
- 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 = 3
- 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) t2
- order by c_last_name,c_first_name,sales
- limit 100;
diff --git a/tools/tpcds-tools/queries/sf10000/query23.sql
b/tools/tpcds-tools/queries/sf10000/query23_1.sql
similarity index 54%
copy from tools/tpcds-tools/queries/sf10000/query23.sql
copy to tools/tpcds-tools/queries/sf10000/query23_1.sql
index ff73709f4eb..b466936cf54 100644
--- a/tools/tpcds-tools/queries/sf10000/query23.sql
+++ b/tools/tpcds-tools/queries/sf10000/query23_1.sql
@@ -1,52 +1,3 @@
-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
- ,date_dim
- ,customer
- 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) t),
- 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 = 3
- 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 = 3
- 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)) t2
- limit 100;
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
diff --git a/tools/tpcds-tools/queries/sf10000/query24.sql
b/tools/tpcds-tools/queries/sf10000/query24.sql
index 98102afa717..e2d858a3d1c 100644
--- a/tools/tpcds-tools/queries/sf10000/query24.sql
+++ b/tools/tpcds-tools/queries/sf10000/query24.sql
@@ -50,55 +50,3 @@ order by c_last_name
,c_first_name
,s_store_name
;
-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_sales_price) 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_current_addr_sk = ca_address_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 = 'chiffon'
-group by c_last_name
- ,c_first_name
- ,s_store_name
-having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
-order by c_last_name
- ,c_first_name
- ,s_store_name
-;
diff --git a/tools/tpcds-tools/queries/sf10000/query24.sql
b/tools/tpcds-tools/queries/sf10000/query24_1.sql
similarity index 50%
copy from tools/tpcds-tools/queries/sf10000/query24.sql
copy to tools/tpcds-tools/queries/sf10000/query24_1.sql
index 98102afa717..4293650db02 100644
--- a/tools/tpcds-tools/queries/sf10000/query24.sql
+++ b/tools/tpcds-tools/queries/sf10000/query24_1.sql
@@ -1,56 +1,4 @@
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_sales_price) 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_current_addr_sk = ca_address_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 = 'snow'
-group by c_last_name
- ,c_first_name
- ,s_store_name
-having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
-order by c_last_name
- ,c_first_name
- ,s_store_name
-;
-with ssales as
(select c_last_name
,c_first_name
,s_store_name
diff --git a/tools/tpcds-tools/queries/sf10000/query39.sql
b/tools/tpcds-tools/queries/sf10000/query39.sql
index 47faacbdd55..e58084c3eda 100644
--- a/tools/tpcds-tools/queries/sf10000/query39.sql
+++ b/tools/tpcds-tools/queries/sf10000/query39.sql
@@ -23,29 +23,3 @@ where inv1.i_item_sk = inv2.i_item_sk
order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
,inv2.d_moy,inv2.mean, inv2.cov
;
-with inv as
-(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stdev,mean, case mean when 0 then null else stdev/mean end cov
- from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand)
mean
- from inventory
- ,item
- ,warehouse
- ,date_dim
- where inv_item_sk = i_item_sk
- and inv_warehouse_sk = w_warehouse_sk
- and inv_date_sk = d_date_sk
- and d_year =2000
- group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
- where case mean when 0 then 0 else stdev/mean end > 1)
-select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
- ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
-from inv inv1,inv inv2
-where inv1.i_item_sk = inv2.i_item_sk
- and inv1.w_warehouse_sk = inv2.w_warehouse_sk
- and inv1.d_moy=2
- and inv2.d_moy=2+1
- and inv1.cov > 1.5
-order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
- ,inv2.d_moy,inv2.mean, inv2.cov
-;
diff --git a/tools/tpcds-tools/queries/sf10000/query39.sql
b/tools/tpcds-tools/queries/sf10000/query39_1.sql
similarity index 50%
copy from tools/tpcds-tools/queries/sf10000/query39.sql
copy to tools/tpcds-tools/queries/sf10000/query39_1.sql
index 47faacbdd55..517f861ff45 100644
--- a/tools/tpcds-tools/queries/sf10000/query39.sql
+++ b/tools/tpcds-tools/queries/sf10000/query39_1.sql
@@ -16,31 +16,6 @@ with inv as
select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
from inv inv1,inv inv2
-where inv1.i_item_sk = inv2.i_item_sk
- and inv1.w_warehouse_sk = inv2.w_warehouse_sk
- and inv1.d_moy=2
- and inv2.d_moy=2+1
-order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
- ,inv2.d_moy,inv2.mean, inv2.cov
-;
-with inv as
-(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stdev,mean, case mean when 0 then null else stdev/mean end cov
- from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
- ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand)
mean
- from inventory
- ,item
- ,warehouse
- ,date_dim
- where inv_item_sk = i_item_sk
- and inv_warehouse_sk = w_warehouse_sk
- and inv_date_sk = d_date_sk
- and d_year =2000
- group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
- where case mean when 0 then 0 else stdev/mean end > 1)
-select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
- ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
-from inv inv1,inv inv2
where inv1.i_item_sk = inv2.i_item_sk
and inv1.w_warehouse_sk = inv2.w_warehouse_sk
and inv1.d_moy=2
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
