You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by we...@apache.org on 2017/05/31 00:11:53 UTC
[15/17] hive git commit: HIVE-16764: Support numeric as same as
decimal (Pengcheng Xiong, reviewed by Ashutosh Chauhan)
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query48.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query48.q b/ql/src/test/queries/clientpositive/perf/query48.q
index a02f630..cfff1d7 100644
--- a/ql/src/test/queries/clientpositive/perf/query48.q
+++ b/ql/src/test/queries/clientpositive/perf/query48.q
@@ -1,2 +1,69 @@
set hive.mapred.mode=nonstrict;
-explain select sum (ss_quantity) from store_sales, store, customer_demographics, customer_address, date_dim where store.s_store_sk = store_sales.ss_store_sk and store_sales.ss_sold_date_sk = date_dim.d_date_sk and d_year = 1998 and ( ( customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk and cd_marital_status = 'M' and cd_education_status = '4 yr Degree' and ss_sales_price between 100.00 and 150.00 ) or ( customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk and cd_marital_status = 'M' and cd_education_status = '4 yr Degree' and ss_sales_price between 50.00 and 100.00 ) or ( customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk and cd_marital_status = 'M' and cd_education_status = '4 yr Degree' and ss_sales_price between 150.00 and 200.00 ) ) and ( ( store_sales.ss_addr_sk = customer_address.ca_address_sk and ca_country = 'United States' and ca_state in ('KY', 'GA', 'NM') and ss_net_profit between 0 and 2000 ) or (store_sales.ss_addr_sk = customer_address.ca_address_
sk and ca_country = 'United States' and ca_state in ('MT', 'OR', 'IN') and ss_net_profit between 150 and 3000 ) or (store_sales.ss_addr_sk = customer_address.ca_address_sk and ca_country = 'United States' and ca_state in ('WI', 'MO', 'WV') and ss_net_profit between 50 and 25000 ) ) ;
+-- start query 1 in stream 0 using template query48.tpl and seed 622697896
+explain
+select sum (ss_quantity)
+ from store_sales, store, customer_demographics, customer_address, date_dim
+ where s_store_sk = ss_store_sk
+ and ss_sold_date_sk = d_date_sk and d_year = 1998
+ and
+ (
+ (
+ cd_demo_sk = ss_cdemo_sk
+ and
+ cd_marital_status = 'M'
+ and
+ cd_education_status = '4 yr Degree'
+ and
+ ss_sales_price between 100.00 and 150.00
+ )
+ or
+ (
+ cd_demo_sk = ss_cdemo_sk
+ and
+ cd_marital_status = 'M'
+ and
+ cd_education_status = '4 yr Degree'
+ and
+ ss_sales_price between 50.00 and 100.00
+ )
+ or
+ (
+ cd_demo_sk = ss_cdemo_sk
+ and
+ cd_marital_status = 'M'
+ and
+ cd_education_status = '4 yr Degree'
+ and
+ ss_sales_price between 150.00 and 200.00
+ )
+ )
+ and
+ (
+ (
+ ss_addr_sk = ca_address_sk
+ and
+ ca_country = 'United States'
+ and
+ ca_state in ('KY', 'GA', 'NM')
+ and ss_net_profit between 0 and 2000
+ )
+ or
+ (ss_addr_sk = ca_address_sk
+ and
+ ca_country = 'United States'
+ and
+ ca_state in ('MT', 'OR', 'IN')
+ and ss_net_profit between 150 and 3000
+ )
+ or
+ (ss_addr_sk = ca_address_sk
+ and
+ ca_country = 'United States'
+ and
+ ca_state in ('WI', 'MO', 'WV')
+ and ss_net_profit between 50 and 25000
+ )
+ )
+;
+
+-- end query 1 in stream 0 using template query48.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query49.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query49.q b/ql/src/test/queries/clientpositive/perf/query49.q
new file mode 100644
index 0000000..6c62e1f
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query49.q
@@ -0,0 +1,129 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query49.tpl and seed 1819994127
+explain
+select
+ 'web' as channel
+ ,web.item
+ ,web.return_ratio
+ ,web.return_rank
+ ,web.currency_rank
+ from (
+ select
+ item
+ ,return_ratio
+ ,currency_ratio
+ ,rank() over (order by return_ratio) as return_rank
+ ,rank() over (order by currency_ratio) as currency_rank
+ from
+ ( select ws.ws_item_sk as item
+ ,(cast(sum(coalesce(wr.wr_return_quantity,0)) as dec(15,4))/
+ cast(sum(coalesce(ws.ws_quantity,0)) as dec(15,4) )) as return_ratio
+ ,(cast(sum(coalesce(wr.wr_return_amt,0)) as dec(15,4))/
+ cast(sum(coalesce(ws.ws_net_paid,0)) as dec(15,4) )) as currency_ratio
+ from
+ web_sales ws left outer join web_returns wr
+ on (ws.ws_order_number = wr.wr_order_number and
+ ws.ws_item_sk = wr.wr_item_sk)
+ ,date_dim
+ where
+ wr.wr_return_amt > 10000
+ and ws.ws_net_profit > 1
+ and ws.ws_net_paid > 0
+ and ws.ws_quantity > 0
+ and ws_sold_date_sk = d_date_sk
+ and d_year = 2000
+ and d_moy = 12
+ group by ws.ws_item_sk
+ ) in_web
+ ) web
+ where
+ (
+ web.return_rank <= 10
+ or
+ web.currency_rank <= 10
+ )
+ union
+ select
+ 'catalog' as channel
+ ,catalog.item
+ ,catalog.return_ratio
+ ,catalog.return_rank
+ ,catalog.currency_rank
+ from (
+ select
+ item
+ ,return_ratio
+ ,currency_ratio
+ ,rank() over (order by return_ratio) as return_rank
+ ,rank() over (order by currency_ratio) as currency_rank
+ from
+ ( select
+ cs.cs_item_sk as item
+ ,(cast(sum(coalesce(cr.cr_return_quantity,0)) as dec(15,4))/
+ cast(sum(coalesce(cs.cs_quantity,0)) as dec(15,4) )) as return_ratio
+ ,(cast(sum(coalesce(cr.cr_return_amount,0)) as dec(15,4))/
+ cast(sum(coalesce(cs.cs_net_paid,0)) as dec(15,4) )) as currency_ratio
+ from
+ catalog_sales cs left outer join catalog_returns cr
+ on (cs.cs_order_number = cr.cr_order_number and
+ cs.cs_item_sk = cr.cr_item_sk)
+ ,date_dim
+ where
+ cr.cr_return_amount > 10000
+ and cs.cs_net_profit > 1
+ and cs.cs_net_paid > 0
+ and cs.cs_quantity > 0
+ and cs_sold_date_sk = d_date_sk
+ and d_year = 2000
+ and d_moy = 12
+ group by cs.cs_item_sk
+ ) in_cat
+ ) catalog
+ where
+ (
+ catalog.return_rank <= 10
+ or
+ catalog.currency_rank <=10
+ )
+ union
+ select
+ 'store' as channel
+ ,store.item
+ ,store.return_ratio
+ ,store.return_rank
+ ,store.currency_rank
+ from (
+ select
+ item
+ ,return_ratio
+ ,currency_ratio
+ ,rank() over (order by return_ratio) as return_rank
+ ,rank() over (order by currency_ratio) as currency_rank
+ from
+ ( select sts.ss_item_sk as item
+ ,(cast(sum(coalesce(sr.sr_return_quantity,0)) as dec(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) as dec(15,4) )) as return_ratio
+ ,(cast(sum(coalesce(sr.sr_return_amt,0)) as dec(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) as dec(15,4) )) as currency_ratio
+ from
+ store_sales sts left outer join store_returns sr
+ on (sts.ss_ticket_number = sr.sr_ticket_number and sts.ss_item_sk = sr.sr_item_sk)
+ ,date_dim
+ where
+ sr.sr_return_amt > 10000
+ and sts.ss_net_profit > 1
+ and sts.ss_net_paid > 0
+ and sts.ss_quantity > 0
+ and ss_sold_date_sk = d_date_sk
+ and d_year = 2000
+ and d_moy = 12
+ group by sts.ss_item_sk
+ ) in_store
+ ) store
+ where (
+ store.return_rank <= 10
+ or
+ store.currency_rank <= 10
+ )
+ order by 1,4,5
+ limit 100;
+
+-- end query 1 in stream 0 using template query49.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query5.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query5.q b/ql/src/test/queries/clientpositive/perf/query5.q
index a877c4c..bf61fb2 100644
--- a/ql/src/test/queries/clientpositive/perf/query5.q
+++ b/ql/src/test/queries/clientpositive/perf/query5.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query5.tpl and seed 1819994127
explain
with ssr as
(select s_store_id,
@@ -125,3 +127,4 @@ with ssr as
,id
limit 100;
+-- end query 1 in stream 0 using template query5.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query50.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query50.q b/ql/src/test/queries/clientpositive/perf/query50.q
index e4bdf0a..0e2caf6 100644
--- a/ql/src/test/queries/clientpositive/perf/query50.q
+++ b/ql/src/test/queries/clientpositive/perf/query50.q
@@ -1,4 +1,6 @@
-explain
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query50.tpl and seed 1819994127
+explain
select
s_store_name
,s_company_id
@@ -10,14 +12,14 @@ select
,s_county
,s_state
,s_zip
- ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end) as 30days
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk <= 30 ) then 1 else 0 end) as `30 days`
,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 30) and
- (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end ) as 3160days
+ (sr_returned_date_sk - ss_sold_date_sk <= 60) then 1 else 0 end ) as `31-60 days`
,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 60) and
- (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end) as 6190days
+ (sr_returned_date_sk - ss_sold_date_sk <= 90) then 1 else 0 end) as `61-90 days`
,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 90) and
- (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end) as 91120days
- ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else 0 end) as 120days
+ (sr_returned_date_sk - ss_sold_date_sk <= 120) then 1 else 0 end) as `91-120 days`
+ ,sum(case when (sr_returned_date_sk - ss_sold_date_sk > 120) then 1 else 0 end) as `>120 days`
from
store_sales
,store_returns
@@ -27,12 +29,12 @@ from
where
d2.d_year = 2000
and d2.d_moy = 9
-and store_sales.ss_ticket_number = store_returns.sr_ticket_number
-and store_sales.ss_item_sk = store_returns.sr_item_sk
-and store_sales.ss_sold_date_sk = d1.d_date_sk
+and ss_ticket_number = sr_ticket_number
+and ss_item_sk = sr_item_sk
+and ss_sold_date_sk = d1.d_date_sk
and sr_returned_date_sk = d2.d_date_sk
-and store_sales.ss_customer_sk = store_returns.sr_customer_sk
-and store_sales.ss_store_sk = store.s_store_sk
+and ss_customer_sk = sr_customer_sk
+and ss_store_sk = s_store_sk
group by
s_store_name
,s_company_id
@@ -56,3 +58,4 @@ order by s_store_name
,s_zip
limit 100;
+-- end query 1 in stream 0 using template query50.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query51.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query51.q b/ql/src/test/queries/clientpositive/perf/query51.q
index b7688cf..9f90525 100644
--- a/ql/src/test/queries/clientpositive/perf/query51.q
+++ b/ql/src/test/queries/clientpositive/perf/query51.q
@@ -1,24 +1,26 @@
set hive.mapred.mode=nonstrict;
-explain WITH web_v1 as (
+-- start query 1 in stream 0 using template query51.tpl and seed 1819994127
+explain
+WITH web_v1 as (
select
- ws_item_sk item_sk, d_date, sum(ws_sales_price),
+ ws_item_sk item_sk, d_date,
sum(sum(ws_sales_price))
over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
from web_sales
,date_dim
where ws_sold_date_sk=d_date_sk
- and d_month_seq between 1193 and 1193+11
+ and d_month_seq between 1212 and 1212+11
and ws_item_sk is not NULL
group by ws_item_sk, d_date),
store_v1 as (
select
- ss_item_sk item_sk, d_date, sum(ss_sales_price),
+ ss_item_sk item_sk, d_date,
sum(sum(ss_sales_price))
over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
from store_sales
,date_dim
where ss_sold_date_sk=d_date_sk
- and d_month_seq between 1193 and 1193+11
+ and d_month_seq between 1212 and 1212+11
and ss_item_sk is not NULL
group by ss_item_sk, d_date)
select *
@@ -41,3 +43,5 @@ where web_cumulative > store_cumulative
order by item_sk
,d_date
limit 100;
+
+-- end query 1 in stream 0 using template query51.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query52.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query52.q b/ql/src/test/queries/clientpositive/perf/query52.q
index 24d0ac3..1fee846 100644
--- a/ql/src/test/queries/clientpositive/perf/query52.q
+++ b/ql/src/test/queries/clientpositive/perf/query52.q
@@ -1 +1,24 @@
-explain select dt.d_year ,item.i_brand_id brand_id ,item.i_brand brand ,sum(ss_ext_sales_price) ext_price 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_manager_id = 1 and dt.d_moy=12 and dt.d_year=1998 group by dt.d_year ,item.i_brand ,item.i_brand_id order by dt.d_year ,ext_price desc ,brand_id limit 100 ;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query52.tpl and seed 1819994127
+explain
+select dt.d_year
+ ,item.i_brand_id brand_id
+ ,item.i_brand brand
+ ,sum(ss_ext_sales_price) ext_price
+ 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_manager_id = 1
+ and dt.d_moy=12
+ and dt.d_year=1998
+ group by dt.d_year
+ ,item.i_brand
+ ,item.i_brand_id
+ order by dt.d_year
+ ,ext_price desc
+ ,brand_id
+limit 100 ;
+
+-- end query 1 in stream 0 using template query52.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query53.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query53.q b/ql/src/test/queries/clientpositive/perf/query53.q
new file mode 100644
index 0000000..0b81574
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query53.q
@@ -0,0 +1,30 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query53.tpl and seed 1819994127
+explain
+select * from
+(select i_manufact_id,
+sum(ss_sales_price) sum_sales,
+avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales
+from item, store_sales, date_dim, store
+where ss_item_sk = i_item_sk and
+ss_sold_date_sk = d_date_sk and
+ss_store_sk = s_store_sk and
+d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11) and
+((i_category in ('Books','Children','Electronics') and
+i_class in ('personal','portable','reference','self-help') and
+i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
+ 'exportiunivamalg #9','scholaramalgamalg #9'))
+or(i_category in ('Women','Music','Men') and
+i_class in ('accessories','classical','fragrances','pants') and
+i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
+ 'importoamalg #1')))
+group by i_manufact_id, d_qoy ) tmp1
+where case when avg_quarterly_sales > 0
+ then abs (sum_sales - avg_quarterly_sales)/ avg_quarterly_sales
+ else null end > 0.1
+order by avg_quarterly_sales,
+ sum_sales,
+ i_manufact_id
+limit 100;
+
+-- end query 1 in stream 0 using template query53.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query54.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query54.q b/ql/src/test/queries/clientpositive/perf/query54.q
index b1a3e83..424f385 100644
--- a/ql/src/test/queries/clientpositive/perf/query54.q
+++ b/ql/src/test/queries/clientpositive/perf/query54.q
@@ -1 +1,58 @@
-explain with my_customers as ( select c_customer_sk , c_current_addr_sk from ( select cs_sold_date_sk sold_date_sk, cs_bill_customer_sk customer_sk, cs_item_sk item_sk from catalog_sales union all select ws_sold_date_sk sold_date_sk, ws_bill_customer_sk customer_sk, ws_item_sk item_sk from web_sales ) cs_or_ws_sales, item, date_dim, customer where sold_date_sk = d_date_sk and item_sk = i_item_sk and i_category = 'Jewelry' and i_class = 'football' and c_customer_sk = cs_or_ws_sales.customer_sk and d_moy = 3 and d_year = 2000 group by c_customer_sk , c_current_addr_sk ) , my_revenue as ( select c_customer_sk, sum(ss_ext_sales_price) as revenue from my_customers, store_sales, customer_address, store, date_dim where c_current_addr_sk = ca_address_sk and ca_county = s_county and ca_state = s_state and ss_sold_date_sk = d_date_sk and c_customer_sk = ss_customer_sk and d_month_seq between (1203) and (1205) group by c_customer_sk ) , segments as (select cast((revenue/50) as int) as segment
from my_revenue ) select segment, count(*) as num_customers, segment*50 as segment_base from segments group by segment order by segment, num_customers limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query54.tpl and seed 1930872976
+explain
+with my_customers as (
+ select distinct c_customer_sk
+ , c_current_addr_sk
+ from
+ ( select cs_sold_date_sk sold_date_sk,
+ cs_bill_customer_sk customer_sk,
+ cs_item_sk item_sk
+ from catalog_sales
+ union all
+ select ws_sold_date_sk sold_date_sk,
+ ws_bill_customer_sk customer_sk,
+ ws_item_sk item_sk
+ from web_sales
+ ) cs_or_ws_sales,
+ item,
+ date_dim,
+ customer
+ where sold_date_sk = d_date_sk
+ and item_sk = i_item_sk
+ and i_category = 'Jewelry'
+ and i_class = 'consignment'
+ and c_customer_sk = cs_or_ws_sales.customer_sk
+ and d_moy = 3
+ and d_year = 1999
+ )
+ , my_revenue as (
+ select c_customer_sk,
+ sum(ss_ext_sales_price) as revenue
+ from my_customers,
+ store_sales,
+ customer_address,
+ store,
+ date_dim
+ where c_current_addr_sk = ca_address_sk
+ and ca_county = s_county
+ and ca_state = s_state
+ and ss_sold_date_sk = d_date_sk
+ and c_customer_sk = ss_customer_sk
+ and d_month_seq between (select distinct d_month_seq+1
+ from date_dim where d_year = 1999 and d_moy = 3)
+ and (select distinct d_month_seq+3
+ from date_dim where d_year = 1999 and d_moy = 3)
+ group by c_customer_sk
+ )
+ , segments as
+ (select cast((revenue/50) as int) as segment
+ from my_revenue
+ )
+ select segment, count(*) as num_customers, segment*50 as segment_base
+ from segments
+ group by segment
+ order by segment, num_customers
+ limit 100;
+
+-- end query 1 in stream 0 using template query54.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query55.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query55.q b/ql/src/test/queries/clientpositive/perf/query55.q
index 644ba1a..f953f11 100644
--- a/ql/src/test/queries/clientpositive/perf/query55.q
+++ b/ql/src/test/queries/clientpositive/perf/query55.q
@@ -1 +1,16 @@
-explain select i_brand_id brand_id, i_brand brand, sum(ss_ext_sales_price) ext_price from date_dim, store_sales, item where date_dim.d_date_sk = store_sales.ss_sold_date_sk and store_sales.ss_item_sk = item.i_item_sk and i_manager_id=36 and d_moy=12 and d_year=2001 group by i_brand, i_brand_id order by ext_price desc, i_brand_id limit 100 ;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query55.tpl and seed 2031708268
+explain
+select i_brand_id brand_id, i_brand brand,
+ sum(ss_ext_sales_price) ext_price
+ from date_dim, store_sales, item
+ where d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id=36
+ and d_moy=12
+ and d_year=2001
+ group by i_brand, i_brand_id
+ order by ext_price desc, i_brand_id
+limit 100 ;
+
+-- end query 1 in stream 0 using template query55.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query56.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query56.q b/ql/src/test/queries/clientpositive/perf/query56.q
index 63e53ea..f3c8323 100644
--- a/ql/src/test/queries/clientpositive/perf/query56.q
+++ b/ql/src/test/queries/clientpositive/perf/query56.q
@@ -1,4 +1,7 @@
-explain with ss as (
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query56.tpl and seed 1951559352
+explain
+with ss as (
select i_item_id,sum(ss_ext_sales_price) total_sales
from
store_sales,
@@ -63,3 +66,5 @@ where i_color in ('orchid','chiffon','lace'))
group by i_item_id
order by total_sales
limit 100;
+
+-- end query 1 in stream 0 using template query56.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query58.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query58.q b/ql/src/test/queries/clientpositive/perf/query58.q
index d392ded..8d918ef 100644
--- a/ql/src/test/queries/clientpositive/perf/query58.q
+++ b/ql/src/test/queries/clientpositive/perf/query58.q
@@ -1,4 +1,49 @@
-explain select ss_items.item_id
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query58.tpl and seed 1819994127
+explain
+with ss_items as
+ (select i_item_id item_id
+ ,sum(ss_ext_sales_price) ss_item_rev
+ from store_sales
+ ,item
+ ,date_dim
+ where ss_item_sk = i_item_sk
+ and d_date in (select d_date
+ from date_dim
+ where d_week_seq = (select d_week_seq
+ from date_dim
+ where d_date = '1998-02-19'))
+ and ss_sold_date_sk = d_date_sk
+ group by i_item_id),
+ cs_items as
+ (select i_item_id item_id
+ ,sum(cs_ext_sales_price) cs_item_rev
+ from catalog_sales
+ ,item
+ ,date_dim
+ where cs_item_sk = i_item_sk
+ and d_date in (select d_date
+ from date_dim
+ where d_week_seq = (select d_week_seq
+ from date_dim
+ where d_date = '1998-02-19'))
+ and cs_sold_date_sk = d_date_sk
+ group by i_item_id),
+ ws_items as
+ (select i_item_id item_id
+ ,sum(ws_ext_sales_price) ws_item_rev
+ from web_sales
+ ,item
+ ,date_dim
+ where ws_item_sk = i_item_sk
+ and d_date in (select d_date
+ from date_dim
+ where d_week_seq =(select d_week_seq
+ from date_dim
+ where d_date = '1998-02-19'))
+ and ws_sold_date_sk = d_date_sk
+ group by i_item_id)
+ select ss_items.item_id
,ss_item_rev
,ss_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ss_dev
,cs_item_rev
@@ -6,42 +51,17 @@ explain select ss_items.item_id
,ws_item_rev
,ws_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ws_dev
,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average
-FROM
-( select i_item_id item_id ,sum(ss_ext_sales_price) as ss_item_rev
- from store_sales
- JOIN item ON store_sales.ss_item_sk = item.i_item_sk
- JOIN date_dim ON store_sales.ss_sold_date_sk = date_dim.d_date_sk
- JOIN (select d1.d_date
- from date_dim d1 JOIN date_dim d2 ON d1.d_week_seq = d2.d_week_seq
- where d2.d_date = '1998-08-04') sub ON date_dim.d_date = sub.d_date
- group by i_item_id ) ss_items
-JOIN
-( select i_item_id item_id ,sum(cs_ext_sales_price) as cs_item_rev
- from catalog_sales
- JOIN item ON catalog_sales.cs_item_sk = item.i_item_sk
- JOIN date_dim ON catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
- JOIN (select d1.d_date
- from date_dim d1 JOIN date_dim d2 ON d1.d_week_seq = d2.d_week_seq
- where d2.d_date = '1998-08-04') sub ON date_dim.d_date = sub.d_date
- group by i_item_id ) cs_items
-ON ss_items.item_id=cs_items.item_id
-JOIN
-( select i_item_id item_id ,sum(ws_ext_sales_price) as ws_item_rev
- from web_sales
- JOIN item ON web_sales.ws_item_sk = item.i_item_sk
- JOIN date_dim ON web_sales.ws_sold_date_sk = date_dim.d_date_sk
- JOIN (select d1.d_date
- from date_dim d1 JOIN date_dim d2 ON d1.d_week_seq = d2.d_week_seq
- where d2.d_date = '1998-08-04') sub ON date_dim.d_date = sub.d_date
- group by i_item_id ) ws_items
-ON ss_items.item_id=ws_items.item_id
- where
- ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
+ from ss_items,cs_items,ws_items
+ where ss_items.item_id=cs_items.item_id
+ and ss_items.item_id=ws_items.item_id
+ and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
- order by item_id ,ss_item_rev
+ order by item_id
+ ,ss_item_rev
limit 100;
+-- end query 1 in stream 0 using template query58.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query59.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query59.q b/ql/src/test/queries/clientpositive/perf/query59.q
new file mode 100644
index 0000000..0999653
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query59.q
@@ -0,0 +1,46 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query59.tpl and seed 1819994127
+explain
+with wss as
+ (select d_week_seq,
+ ss_store_sk,
+ sum(case when (d_day_name='Sunday') then ss_sales_price else null end) sun_sales,
+ sum(case when (d_day_name='Monday') then ss_sales_price else null end) mon_sales,
+ sum(case when (d_day_name='Tuesday') then ss_sales_price else null end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then ss_sales_price else null end) wed_sales,
+ sum(case when (d_day_name='Thursday') then ss_sales_price else null end) thu_sales,
+ sum(case when (d_day_name='Friday') then ss_sales_price else null end) fri_sales,
+ sum(case when (d_day_name='Saturday') then ss_sales_price else null end) sat_sales
+ from store_sales,date_dim
+ where d_date_sk = ss_sold_date_sk
+ group by d_week_seq,ss_store_sk
+ )
+ select s_store_name1,s_store_id1,d_week_seq1
+ ,sun_sales1/sun_sales2,mon_sales1/mon_sales2
+ ,tue_sales1/tue_sales1,wed_sales1/wed_sales2,thu_sales1/thu_sales2
+ ,fri_sales1/fri_sales2,sat_sales1/sat_sales2
+ from
+ (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1
+ ,s_store_id s_store_id1,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 wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1185 and 1185 + 11) y,
+ (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2
+ ,s_store_id s_store_id2,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 wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1185+ 12 and 1185 + 23) x
+ where s_store_id1=s_store_id2
+ and d_week_seq1=d_week_seq2-52
+ order by s_store_name1,s_store_id1,d_week_seq1
+limit 100;
+
+-- end query 1 in stream 0 using template query59.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query6.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query6.q b/ql/src/test/queries/clientpositive/perf/query6.q
index 8e4a7d3..d45045d 100644
--- a/ql/src/test/queries/clientpositive/perf/query6.q
+++ b/ql/src/test/queries/clientpositive/perf/query6.q
@@ -1,6 +1,7 @@
set hive.mapred.mode=nonstrict;
-
-explain select a.ca_state state, count(*) cnt
+-- start query 1 in stream 0 using template query6.tpl and seed 1819994127
+explain
+select a.ca_state state, count(*) cnt
from customer_address a
,customer c
,store_sales s
@@ -23,3 +24,5 @@ explain select a.ca_state state, count(*) cnt
having count(*) >= 10
order by cnt
limit 100;
+
+-- end query 1 in stream 0 using template query6.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query60.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query60.q b/ql/src/test/queries/clientpositive/perf/query60.q
index efa86d1..a5ab248 100644
--- a/ql/src/test/queries/clientpositive/perf/query60.q
+++ b/ql/src/test/queries/clientpositive/perf/query60.q
@@ -1,5 +1,7 @@
+set hive.mapred.mode=nonstrict;
-- start query 1 in stream 0 using template query60.tpl and seed 1930872976
-explain with ss as (
+explain
+with ss as (
select
i_item_id,sum(ss_ext_sales_price) total_sales
from
@@ -75,3 +77,4 @@ where i_category in ('Children'))
,total_sales
limit 100;
+-- end query 1 in stream 0 using template query60.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query61.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query61.q b/ql/src/test/queries/clientpositive/perf/query61.q
new file mode 100644
index 0000000..edaf6f6
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query61.q
@@ -0,0 +1,46 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query61.tpl and seed 1930872976
+explain
+select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
+from
+ (select sum(ss_ext_sales_price) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) promotional_sales,
+ (select sum(ss_ext_sales_price) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Electronics'
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) all_sales
+order by promotions, total
+limit 100;
+
+-- end query 1 in stream 0 using template query61.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query63.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query63.q b/ql/src/test/queries/clientpositive/perf/query63.q
new file mode 100644
index 0000000..49e513c
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query63.q
@@ -0,0 +1,31 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query63.tpl and seed 1819994127
+explain
+select *
+from (select i_manager_id
+ ,sum(ss_sales_price) sum_sales
+ ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
+ from item
+ ,store_sales
+ ,date_dim
+ ,store
+ where ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)
+ and (( i_category in ('Books','Children','Electronics')
+ and i_class in ('personal','portable','refernece','self-help')
+ and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
+ 'exportiunivamalg #9','scholaramalgamalg #9'))
+ or( i_category in ('Women','Music','Men')
+ and i_class in ('accessories','classical','fragrances','pants')
+ and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
+ 'importoamalg #1')))
+group by i_manager_id, d_moy) tmp1
+where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
+order by i_manager_id
+ ,avg_monthly_sales
+ ,sum_sales
+limit 100;
+
+-- end query 1 in stream 0 using template query63.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query64.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query64.q b/ql/src/test/queries/clientpositive/perf/query64.q
index 13ebf4a..b069c2a 100644
--- a/ql/src/test/queries/clientpositive/perf/query64.q
+++ b/ql/src/test/queries/clientpositive/perf/query64.q
@@ -1,2 +1,121 @@
set hive.mapred.mode=nonstrict;
-explain select cs1.product_name ,cs1.store_name ,cs1.store_zip ,cs1.b_street_number ,cs1.b_streen_name ,cs1.b_city ,cs1.b_zip ,cs1.c_street_number ,cs1.c_street_name ,cs1.c_city ,cs1.c_zip ,cs1.syear ,cs1.cnt ,cs1.s1 ,cs1.s2 ,cs1.s3 ,cs2.s1 ,cs2.s2 ,cs2.s3 ,cs2.syear ,cs2.cnt from (select i_product_name as product_name ,i_item_sk as item_sk ,s_store_name as store_name ,s_zip as store_zip ,ad1.ca_street_number as b_street_number ,ad1.ca_street_name as b_streen_name ,ad1.ca_city as b_city ,ad1.ca_zip as b_zip ,ad2.ca_street_number as c_street_number ,ad2.ca_street_name as c_street_name ,ad2.ca_city as c_city ,ad2.ca_zip as c_zip ,d1.d_year as syear ,d2.d_year as fsyear ,d3.d_year as s2year ,count(*) as cnt ,sum(ss_wholesale_cost) as s1 ,sum(ss_list_price) as s2 ,sum(ss_coupon_amt) as s3 FROM store_sales JOIN store_returns ON store_sales.ss_item_sk = store_returns.sr_item_sk and store_sales.ss_ticket_number = store_returns.sr_ticket_number JOIN customer ON store_sales.ss_customer_sk =
customer.c_customer_sk JOIN date_dim d1 ON store_sales.ss_sold_date_sk = d1.d_date_sk JOIN date_dim d2 ON customer.c_first_sales_date_sk = d2.d_date_sk JOIN date_dim d3 ON customer.c_first_shipto_date_sk = d3.d_date_sk JOIN store ON store_sales.ss_store_sk = store.s_store_sk JOIN customer_demographics cd1 ON store_sales.ss_cdemo_sk= cd1.cd_demo_sk JOIN customer_demographics cd2 ON customer.c_current_cdemo_sk = cd2.cd_demo_sk JOIN promotion ON store_sales.ss_promo_sk = promotion.p_promo_sk JOIN household_demographics hd1 ON store_sales.ss_hdemo_sk = hd1.hd_demo_sk JOIN household_demographics hd2 ON customer.c_current_hdemo_sk = hd2.hd_demo_sk JOIN customer_address ad1 ON store_sales.ss_addr_sk = ad1.ca_address_sk JOIN customer_address ad2 ON customer.c_current_addr_sk = ad2.ca_address_sk JOIN income_band ib1 ON hd1.hd_income_band_sk = ib1.ib_income_band_sk JOIN income_band ib2 ON hd2.hd_income_band_sk = ib2.ib_income_band_sk JOIN item ON store_sales.ss_item_sk = item.i_item_sk JOIN (
select cs_item_sk ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund from catalog_sales JOIN catalog_returns ON catalog_sales.cs_item_sk = catalog_returns.cr_item_sk and catalog_sales.cs_order_number = catalog_returns.cr_order_number group by cs_item_sk having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)) cs_ui ON store_sales.ss_item_sk = cs_ui.cs_item_sk WHERE cd1.cd_marital_status <> cd2.cd_marital_status and i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and i_current_price between 35 and 35 + 10 and i_current_price between 35 + 1 and 35 + 15 group by i_product_name ,i_item_sk ,s_store_name ,s_zip ,ad1.ca_street_number ,ad1.ca_street_name ,ad1.ca_city ,ad1.ca_zip ,ad2.ca_street_number ,ad2.ca_street_name ,ad2.ca_city ,ad2.ca_zip ,d1.d_year ,d2.d_year ,d3.d_year ) cs1 JOIN (select i_product_name as product_name ,i_item_sk as item_sk ,s_store_name as store_name ,s_zip as store
_zip ,ad1.ca_street_number as b_street_number ,ad1.ca_street_name as b_streen_name ,ad1.ca_city as b_city ,ad1.ca_zip as b_zip ,ad2.ca_street_number as c_street_number ,ad2.ca_street_name as c_street_name ,ad2.ca_city as c_city ,ad2.ca_zip as c_zip ,d1.d_year as syear ,d2.d_year as fsyear ,d3.d_year as s2year ,count(*) as cnt ,sum(ss_wholesale_cost) as s1 ,sum(ss_list_price) as s2 ,sum(ss_coupon_amt) as s3 FROM store_sales JOIN store_returns ON store_sales.ss_item_sk = store_returns.sr_item_sk and store_sales.ss_ticket_number = store_returns.sr_ticket_number JOIN customer ON store_sales.ss_customer_sk = customer.c_customer_sk JOIN date_dim d1 ON store_sales.ss_sold_date_sk = d1.d_date_sk JOIN date_dim d2 ON customer.c_first_sales_date_sk = d2.d_date_sk JOIN date_dim d3 ON customer.c_first_shipto_date_sk = d3.d_date_sk JOIN store ON store_sales.ss_store_sk = store.s_store_sk JOIN customer_demographics cd1 ON store_sales.ss_cdemo_sk= cd1.cd_demo_sk JOIN customer_demographics cd2 ON cu
stomer.c_current_cdemo_sk = cd2.cd_demo_sk JOIN promotion ON store_sales.ss_promo_sk = promotion.p_promo_sk JOIN household_demographics hd1 ON store_sales.ss_hdemo_sk = hd1.hd_demo_sk JOIN household_demographics hd2 ON customer.c_current_hdemo_sk = hd2.hd_demo_sk JOIN customer_address ad1 ON store_sales.ss_addr_sk = ad1.ca_address_sk JOIN customer_address ad2 ON customer.c_current_addr_sk = ad2.ca_address_sk JOIN income_band ib1 ON hd1.hd_income_band_sk = ib1.ib_income_band_sk JOIN income_band ib2 ON hd2.hd_income_band_sk = ib2.ib_income_band_sk JOIN item ON store_sales.ss_item_sk = item.i_item_sk JOIN (select cs_item_sk ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund from catalog_sales JOIN catalog_returns ON catalog_sales.cs_item_sk = catalog_returns.cr_item_sk and catalog_sales.cs_order_number = catalog_returns.cr_order_number group by cs_item_sk having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_cre
dit)) cs_ui ON store_sales.ss_item_sk = cs_ui.cs_item_sk WHERE cd1.cd_marital_status <> cd2.cd_marital_status and i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and i_current_price between 35 and 35 + 10 and i_current_price between 35 + 1 and 35 + 15 group by i_product_name ,i_item_sk ,s_store_name ,s_zip ,ad1.ca_street_number ,ad1.ca_street_name ,ad1.ca_city ,ad1.ca_zip ,ad2.ca_street_number ,ad2.ca_street_name ,ad2.ca_city ,ad2.ca_zip ,d1.d_year ,d2.d_year ,d3.d_year ) cs2 ON cs1.item_sk=cs2.item_sk where cs1.syear = 2000 and cs2.syear = 2000 + 1 and cs2.cnt <= cs1.cnt and cs1.store_name = cs2.store_name and cs1.store_zip = cs2.store_zip order by cs1.product_name ,cs1.store_name ,cs2.cnt;
+-- start query 1 in stream 0 using template query64.tpl and seed 1220860970
+explain
+with cs_ui as
+ (select cs_item_sk
+ ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
+ from catalog_sales
+ ,catalog_returns
+ where cs_item_sk = cr_item_sk
+ and cs_order_number = cr_order_number
+ group by cs_item_sk
+ having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
+cross_sales as
+ (select i_product_name product_name
+ ,i_item_sk item_sk
+ ,s_store_name store_name
+ ,s_zip store_zip
+ ,ad1.ca_street_number b_street_number
+ ,ad1.ca_street_name b_streen_name
+ ,ad1.ca_city b_city
+ ,ad1.ca_zip b_zip
+ ,ad2.ca_street_number c_street_number
+ ,ad2.ca_street_name c_street_name
+ ,ad2.ca_city c_city
+ ,ad2.ca_zip c_zip
+ ,d1.d_year as syear
+ ,d2.d_year as fsyear
+ ,d3.d_year s2year
+ ,count(*) cnt
+ ,sum(ss_wholesale_cost) s1
+ ,sum(ss_list_price) s2
+ ,sum(ss_coupon_amt) s3
+ FROM store_sales
+ ,store_returns
+ ,cs_ui
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,customer
+ ,customer_demographics cd1
+ ,customer_demographics cd2
+ ,promotion
+ ,household_demographics hd1
+ ,household_demographics hd2
+ ,customer_address ad1
+ ,customer_address ad2
+ ,income_band ib1
+ ,income_band ib2
+ ,item
+ WHERE ss_store_sk = s_store_sk AND
+ ss_sold_date_sk = d1.d_date_sk AND
+ ss_customer_sk = c_customer_sk AND
+ ss_cdemo_sk= cd1.cd_demo_sk AND
+ ss_hdemo_sk = hd1.hd_demo_sk AND
+ ss_addr_sk = ad1.ca_address_sk and
+ ss_item_sk = i_item_sk and
+ ss_item_sk = sr_item_sk and
+ ss_ticket_number = sr_ticket_number and
+ ss_item_sk = cs_ui.cs_item_sk and
+ c_current_cdemo_sk = cd2.cd_demo_sk AND
+ c_current_hdemo_sk = hd2.hd_demo_sk AND
+ c_current_addr_sk = ad2.ca_address_sk and
+ c_first_sales_date_sk = d2.d_date_sk and
+ c_first_shipto_date_sk = d3.d_date_sk and
+ ss_promo_sk = p_promo_sk and
+ hd1.hd_income_band_sk = ib1.ib_income_band_sk and
+ hd2.hd_income_band_sk = ib2.ib_income_band_sk and
+ cd1.cd_marital_status <> cd2.cd_marital_status and
+ i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and
+ i_current_price between 35 and 35 + 10 and
+ i_current_price between 35 + 1 and 35 + 15
+group by i_product_name
+ ,i_item_sk
+ ,s_store_name
+ ,s_zip
+ ,ad1.ca_street_number
+ ,ad1.ca_street_name
+ ,ad1.ca_city
+ ,ad1.ca_zip
+ ,ad2.ca_street_number
+ ,ad2.ca_street_name
+ ,ad2.ca_city
+ ,ad2.ca_zip
+ ,d1.d_year
+ ,d2.d_year
+ ,d3.d_year
+)
+select cs1.product_name
+ ,cs1.store_name
+ ,cs1.store_zip
+ ,cs1.b_street_number
+ ,cs1.b_streen_name
+ ,cs1.b_city
+ ,cs1.b_zip
+ ,cs1.c_street_number
+ ,cs1.c_street_name
+ ,cs1.c_city
+ ,cs1.c_zip
+ ,cs1.syear
+ ,cs1.cnt
+ ,cs1.s1
+ ,cs1.s2
+ ,cs1.s3
+ ,cs2.s1
+ ,cs2.s2
+ ,cs2.s3
+ ,cs2.syear
+ ,cs2.cnt
+from cross_sales cs1,cross_sales cs2
+where cs1.item_sk=cs2.item_sk and
+ cs1.syear = 2000 and
+ cs2.syear = 2000 + 1 and
+ cs2.cnt <= cs1.cnt and
+ cs1.store_name = cs2.store_name and
+ cs1.store_zip = cs2.store_zip
+order by cs1.product_name
+ ,cs1.store_name
+ ,cs2.cnt;
+
+-- end query 1 in stream 0 using template query64.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query65.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query65.q b/ql/src/test/queries/clientpositive/perf/query65.q
index bd4475d..d5b53a2 100644
--- a/ql/src/test/queries/clientpositive/perf/query65.q
+++ b/ql/src/test/queries/clientpositive/perf/query65.q
@@ -1,38 +1,31 @@
set hive.mapred.mode=nonstrict;
-explain select
- s_store_name,
- i_item_desc,
- sc.revenue,
- i_current_price,
- i_wholesale_cost,
- i_brand
-from
- store,
- item,
- (select
- ss_store_sk, avg(revenue) as ave
- from
- (select
- ss_store_sk, ss_item_sk, sum(ss_sales_price) as revenue
- from
- store_sales, date_dim
- where
- ss_sold_date_sk = d_date_sk
- and d_month_seq between 1212 and 1212 + 11
- group by ss_store_sk , ss_item_sk) sa
- group by ss_store_sk) sb,
- (select
- ss_store_sk, ss_item_sk, sum(ss_sales_price) as revenue
- from
- store_sales, date_dim
- where
- ss_sold_date_sk = d_date_sk
- and d_month_seq between 1212 and 1212 + 11
- group by ss_store_sk , ss_item_sk) sc
-where
- sb.ss_store_sk = sc.ss_store_sk
- and sc.revenue <= 0.1 * sb.ave
- and s_store_sk = sc.ss_store_sk
- and i_item_sk = sc.ss_item_sk
-order by s_store_name , i_item_desc
+-- start query 1 in stream 0 using template query65.tpl and seed 1819994127
+explain
+select
+ s_store_name,
+ i_item_desc,
+ sc.revenue,
+ i_current_price,
+ i_wholesale_cost,
+ i_brand
+ from store, item,
+ (select ss_store_sk, avg(revenue) as ave
+ from
+ (select ss_store_sk, ss_item_sk,
+ sum(ss_sales_price) as revenue
+ from store_sales, date_dim
+ where ss_sold_date_sk = d_date_sk and d_month_seq between 1212 and 1212+11
+ group by ss_store_sk, ss_item_sk) sa
+ group by ss_store_sk) sb,
+ (select ss_store_sk, ss_item_sk, sum(ss_sales_price) as revenue
+ from store_sales, date_dim
+ where ss_sold_date_sk = d_date_sk and d_month_seq between 1212 and 1212+11
+ group by ss_store_sk, ss_item_sk) sc
+ where sb.ss_store_sk = sc.ss_store_sk and
+ sc.revenue <= 0.1 * sb.ave and
+ s_store_sk = sc.ss_store_sk and
+ i_item_sk = sc.ss_item_sk
+ order by s_store_name, i_item_desc
limit 100;
+
+-- end query 1 in stream 0 using template query65.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query66.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query66.q b/ql/src/test/queries/clientpositive/perf/query66.q
index 9224680..280bac8 100644
--- a/ql/src/test/queries/clientpositive/perf/query66.q
+++ b/ql/src/test/queries/clientpositive/perf/query66.q
@@ -1,11 +1,13 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query66.tpl and seed 2042478054
explain
select
w_warehouse_name
- ,w_warehouse_sq_ft
- ,w_city
- ,w_county
- ,w_state
- ,w_country
+ ,w_warehouse_sq_ft
+ ,w_city
+ ,w_county
+ ,w_state
+ ,w_country
,ship_carriers
,year
,sum(jan_sales) as jan_sales
@@ -45,74 +47,74 @@ select
,sum(nov_net) as nov_net
,sum(dec_net) as dec_net
from (
- select
- w_warehouse_name
- ,w_warehouse_sq_ft
- ,w_city
- ,w_county
- ,w_state
- ,w_country
- ,concat('DIAMOND', ',', 'AIRBORNE') as ship_carriers
- ,d_year as year
+ (select
+ w_warehouse_name
+ ,w_warehouse_sq_ft
+ ,w_city
+ ,w_county
+ ,w_state
+ ,w_country
+ ,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers
+ ,d_year as year
,sum(case when d_moy = 1
- then ws_sales_price* ws_quantity else 0 end) as jan_sales
- ,sum(case when d_moy = 2
- then ws_sales_price* ws_quantity else 0 end) as feb_sales
- ,sum(case when d_moy = 3
- then ws_sales_price* ws_quantity else 0 end) as mar_sales
- ,sum(case when d_moy = 4
- then ws_sales_price* ws_quantity else 0 end) as apr_sales
- ,sum(case when d_moy = 5
- then ws_sales_price* ws_quantity else 0 end) as may_sales
- ,sum(case when d_moy = 6
- then ws_sales_price* ws_quantity else 0 end) as jun_sales
- ,sum(case when d_moy = 7
- then ws_sales_price* ws_quantity else 0 end) as jul_sales
- ,sum(case when d_moy = 8
- then ws_sales_price* ws_quantity else 0 end) as aug_sales
- ,sum(case when d_moy = 9
- then ws_sales_price* ws_quantity else 0 end) as sep_sales
- ,sum(case when d_moy = 10
- then ws_sales_price* ws_quantity else 0 end) as oct_sales
- ,sum(case when d_moy = 11
- then ws_sales_price* ws_quantity else 0 end) as nov_sales
- ,sum(case when d_moy = 12
- then ws_sales_price* ws_quantity else 0 end) as dec_sales
- ,sum(case when d_moy = 1
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as jan_net
- ,sum(case when d_moy = 2
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as feb_net
- ,sum(case when d_moy = 3
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as mar_net
- ,sum(case when d_moy = 4
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as apr_net
- ,sum(case when d_moy = 5
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as may_net
- ,sum(case when d_moy = 6
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as jun_net
- ,sum(case when d_moy = 7
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as jul_net
- ,sum(case when d_moy = 8
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as aug_net
- ,sum(case when d_moy = 9
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as sep_net
- ,sum(case when d_moy = 10
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as oct_net
- ,sum(case when d_moy = 11
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as nov_net
- ,sum(case when d_moy = 12
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as dec_net
+ then ws_sales_price* ws_quantity else 0 end) as jan_sales
+ ,sum(case when d_moy = 2
+ then ws_sales_price* ws_quantity else 0 end) as feb_sales
+ ,sum(case when d_moy = 3
+ then ws_sales_price* ws_quantity else 0 end) as mar_sales
+ ,sum(case when d_moy = 4
+ then ws_sales_price* ws_quantity else 0 end) as apr_sales
+ ,sum(case when d_moy = 5
+ then ws_sales_price* ws_quantity else 0 end) as may_sales
+ ,sum(case when d_moy = 6
+ then ws_sales_price* ws_quantity else 0 end) as jun_sales
+ ,sum(case when d_moy = 7
+ then ws_sales_price* ws_quantity else 0 end) as jul_sales
+ ,sum(case when d_moy = 8
+ then ws_sales_price* ws_quantity else 0 end) as aug_sales
+ ,sum(case when d_moy = 9
+ then ws_sales_price* ws_quantity else 0 end) as sep_sales
+ ,sum(case when d_moy = 10
+ then ws_sales_price* ws_quantity else 0 end) as oct_sales
+ ,sum(case when d_moy = 11
+ then ws_sales_price* ws_quantity else 0 end) as nov_sales
+ ,sum(case when d_moy = 12
+ then ws_sales_price* ws_quantity else 0 end) as dec_sales
+ ,sum(case when d_moy = 1
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as jan_net
+ ,sum(case when d_moy = 2
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as feb_net
+ ,sum(case when d_moy = 3
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as mar_net
+ ,sum(case when d_moy = 4
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as apr_net
+ ,sum(case when d_moy = 5
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as may_net
+ ,sum(case when d_moy = 6
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as jun_net
+ ,sum(case when d_moy = 7
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as jul_net
+ ,sum(case when d_moy = 8
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as aug_net
+ ,sum(case when d_moy = 9
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as sep_net
+ ,sum(case when d_moy = 10
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as oct_net
+ ,sum(case when d_moy = 11
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as nov_net
+ ,sum(case when d_moy = 12
+ then ws_net_paid_inc_tax * ws_quantity else 0 end) as dec_net
from
web_sales
,warehouse
,date_dim
,time_dim
- ,ship_mode
+ ,ship_mode
where
- web_sales.ws_warehouse_sk = warehouse.w_warehouse_sk
- and web_sales.ws_sold_date_sk = date_dim.d_date_sk
- and web_sales.ws_sold_time_sk = time_dim.t_time_sk
- and web_sales.ws_ship_mode_sk = ship_mode.sm_ship_mode_sk
+ ws_warehouse_sk = w_warehouse_sk
+ and ws_sold_date_sk = d_date_sk
+ and ws_sold_time_sk = t_time_sk
+ and ws_ship_mode_sk = sm_ship_mode_sk
and d_year = 2002
and t_time between 49530 and 49530+28800
and sm_carrier in ('DIAMOND','AIRBORNE')
@@ -124,75 +126,76 @@ select
,w_state
,w_country
,d_year
+ )
union all
- select
- w_warehouse_name
- ,w_warehouse_sq_ft
- ,w_city
- ,w_county
- ,w_state
- ,w_country
- ,concat('DIAMOND', ',', 'AIRBORNE') as ship_carriers
+ (select
+ w_warehouse_name
+ ,w_warehouse_sq_ft
+ ,w_city
+ ,w_county
+ ,w_state
+ ,w_country
+ ,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers
,d_year as year
- ,sum(case when d_moy = 1
- then cs_ext_sales_price* cs_quantity else 0 end) as jan_sales
- ,sum(case when d_moy = 2
- then cs_ext_sales_price* cs_quantity else 0 end) as feb_sales
- ,sum(case when d_moy = 3
- then cs_ext_sales_price* cs_quantity else 0 end) as mar_sales
- ,sum(case when d_moy = 4
- then cs_ext_sales_price* cs_quantity else 0 end) as apr_sales
- ,sum(case when d_moy = 5
- then cs_ext_sales_price* cs_quantity else 0 end) as may_sales
- ,sum(case when d_moy = 6
- then cs_ext_sales_price* cs_quantity else 0 end) as jun_sales
- ,sum(case when d_moy = 7
- then cs_ext_sales_price* cs_quantity else 0 end) as jul_sales
- ,sum(case when d_moy = 8
- then cs_ext_sales_price* cs_quantity else 0 end) as aug_sales
- ,sum(case when d_moy = 9
- then cs_ext_sales_price* cs_quantity else 0 end) as sep_sales
- ,sum(case when d_moy = 10
- then cs_ext_sales_price* cs_quantity else 0 end) as oct_sales
- ,sum(case when d_moy = 11
- then cs_ext_sales_price* cs_quantity else 0 end) as nov_sales
- ,sum(case when d_moy = 12
- then cs_ext_sales_price* cs_quantity else 0 end) as dec_sales
- ,sum(case when d_moy = 1
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jan_net
- ,sum(case when d_moy = 2
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as feb_net
- ,sum(case when d_moy = 3
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as mar_net
- ,sum(case when d_moy = 4
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as apr_net
- ,sum(case when d_moy = 5
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as may_net
- ,sum(case when d_moy = 6
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jun_net
- ,sum(case when d_moy = 7
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jul_net
- ,sum(case when d_moy = 8
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as aug_net
- ,sum(case when d_moy = 9
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as sep_net
- ,sum(case when d_moy = 10
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as oct_net
- ,sum(case when d_moy = 11
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as nov_net
- ,sum(case when d_moy = 12
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as dec_net
+ ,sum(case when d_moy = 1
+ then cs_ext_sales_price* cs_quantity else 0 end) as jan_sales
+ ,sum(case when d_moy = 2
+ then cs_ext_sales_price* cs_quantity else 0 end) as feb_sales
+ ,sum(case when d_moy = 3
+ then cs_ext_sales_price* cs_quantity else 0 end) as mar_sales
+ ,sum(case when d_moy = 4
+ then cs_ext_sales_price* cs_quantity else 0 end) as apr_sales
+ ,sum(case when d_moy = 5
+ then cs_ext_sales_price* cs_quantity else 0 end) as may_sales
+ ,sum(case when d_moy = 6
+ then cs_ext_sales_price* cs_quantity else 0 end) as jun_sales
+ ,sum(case when d_moy = 7
+ then cs_ext_sales_price* cs_quantity else 0 end) as jul_sales
+ ,sum(case when d_moy = 8
+ then cs_ext_sales_price* cs_quantity else 0 end) as aug_sales
+ ,sum(case when d_moy = 9
+ then cs_ext_sales_price* cs_quantity else 0 end) as sep_sales
+ ,sum(case when d_moy = 10
+ then cs_ext_sales_price* cs_quantity else 0 end) as oct_sales
+ ,sum(case when d_moy = 11
+ then cs_ext_sales_price* cs_quantity else 0 end) as nov_sales
+ ,sum(case when d_moy = 12
+ then cs_ext_sales_price* cs_quantity else 0 end) as dec_sales
+ ,sum(case when d_moy = 1
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jan_net
+ ,sum(case when d_moy = 2
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as feb_net
+ ,sum(case when d_moy = 3
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as mar_net
+ ,sum(case when d_moy = 4
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as apr_net
+ ,sum(case when d_moy = 5
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as may_net
+ ,sum(case when d_moy = 6
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jun_net
+ ,sum(case when d_moy = 7
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jul_net
+ ,sum(case when d_moy = 8
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as aug_net
+ ,sum(case when d_moy = 9
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as sep_net
+ ,sum(case when d_moy = 10
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as oct_net
+ ,sum(case when d_moy = 11
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as nov_net
+ ,sum(case when d_moy = 12
+ then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as dec_net
from
catalog_sales
,warehouse
,date_dim
,time_dim
- ,ship_mode
+ ,ship_mode
where
- catalog_sales.cs_warehouse_sk = warehouse.w_warehouse_sk
- and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
- and catalog_sales.cs_sold_time_sk = time_dim.t_time_sk
- and catalog_sales.cs_ship_mode_sk = ship_mode.sm_ship_mode_sk
+ cs_warehouse_sk = w_warehouse_sk
+ and cs_sold_date_sk = d_date_sk
+ and cs_sold_time_sk = t_time_sk
+ and cs_ship_mode_sk = sm_ship_mode_sk
and d_year = 2002
and t_time between 49530 AND 49530+28800
and sm_carrier in ('DIAMOND','AIRBORNE')
@@ -204,6 +207,7 @@ select
,w_state
,w_country
,d_year
+ )
) x
group by
w_warehouse_name
@@ -217,3 +221,4 @@ select
order by w_warehouse_name
limit 100;
+-- end query 1 in stream 0 using template query66.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query67.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query67.q b/ql/src/test/queries/clientpositive/perf/query67.q
index ca2fc61..c3ecf2a 100644
--- a/ql/src/test/queries/clientpositive/perf/query67.q
+++ b/ql/src/test/queries/clientpositive/perf/query67.q
@@ -1,5 +1,5 @@
set hive.mapred.mode=nonstrict;
-
+-- start query 1 in stream 0 using template query67.tpl and seed 1819994127
explain
select *
from (select i_category
@@ -43,3 +43,4 @@ order by i_category
,rk
limit 100;
+-- end query 1 in stream 0 using template query67.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query68.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query68.q b/ql/src/test/queries/clientpositive/perf/query68.q
index 34f87c2..964dc8a 100644
--- a/ql/src/test/queries/clientpositive/perf/query68.q
+++ b/ql/src/test/queries/clientpositive/perf/query68.q
@@ -1 +1,44 @@
-explain select c_last_name ,c_first_name ,ca_city ,bought_city ,ss_ticket_number ,extended_price ,extended_tax ,list_price from (select ss_ticket_number ,ss_customer_sk ,ca_city bought_city ,sum(ss_ext_sales_price) extended_price ,sum(ss_ext_list_price) list_price ,sum(ss_ext_tax) extended_tax from store_sales ,date_dim ,store ,household_demographics ,customer_address where store_sales.ss_sold_date_sk = date_dim.d_date_sk and store_sales.ss_store_sk = store.s_store_sk and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk and store_sales.ss_addr_sk = customer_address.ca_address_sk and date_dim.d_dom between 1 and 2 and (household_demographics.hd_dep_count = 4 or household_demographics.hd_vehicle_count= 2) and date_dim.d_year in (1998,1998+1,1998+2) and store.s_city in ('Rosedale','Bethlehem') group by ss_ticket_number ,ss_customer_sk ,ss_addr_sk,ca_city) dn ,customer ,customer_address current_addr where dn.ss_customer_sk = customer.c_customer_sk and customer.c_current_addr_
sk = current_addr.ca_address_sk and current_addr.ca_city <> bought_city order by c_last_name ,ss_ticket_number limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query68.tpl and seed 803547492
+explain
+select c_last_name
+ ,c_first_name
+ ,ca_city
+ ,bought_city
+ ,ss_ticket_number
+ ,extended_price
+ ,extended_tax
+ ,list_price
+ from (select ss_ticket_number
+ ,ss_customer_sk
+ ,ca_city bought_city
+ ,sum(ss_ext_sales_price) extended_price
+ ,sum(ss_ext_list_price) list_price
+ ,sum(ss_ext_tax) extended_tax
+ from store_sales
+ ,date_dim
+ ,store
+ ,household_demographics
+ ,customer_address
+ where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and store_sales.ss_addr_sk = customer_address.ca_address_sk
+ and date_dim.d_dom between 1 and 2
+ and (household_demographics.hd_dep_count = 2 or
+ household_demographics.hd_vehicle_count= 1)
+ and date_dim.d_year in (1998,1998+1,1998+2)
+ and store.s_city in ('Cedar Grove','Wildwood')
+ group by ss_ticket_number
+ ,ss_customer_sk
+ ,ss_addr_sk,ca_city) dn
+ ,customer
+ ,customer_address current_addr
+ where ss_customer_sk = c_customer_sk
+ and customer.c_current_addr_sk = current_addr.ca_address_sk
+ and current_addr.ca_city <> bought_city
+ order by c_last_name
+ ,ss_ticket_number
+ limit 100;
+
+-- end query 1 in stream 0 using template query68.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query69.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query69.q b/ql/src/test/queries/clientpositive/perf/query69.q
index d9528ae..ce2d19c 100644
--- a/ql/src/test/queries/clientpositive/perf/query69.q
+++ b/ql/src/test/queries/clientpositive/perf/query69.q
@@ -1,6 +1,7 @@
set hive.mapred.mode=nonstrict;
-
-explain select
+-- start query 1 in stream 0 using template query69.tpl and seed 797269820
+explain
+select
cd_gender,
cd_marital_status,
cd_education_status,
@@ -44,3 +45,5 @@ explain select
cd_purchase_estimate,
cd_credit_rating
limit 100;
+
+-- end query 1 in stream 0 using template query69.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query7.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query7.q b/ql/src/test/queries/clientpositive/perf/query7.q
index 39c4383..7bc1a00 100644
--- a/ql/src/test/queries/clientpositive/perf/query7.q
+++ b/ql/src/test/queries/clientpositive/perf/query7.q
@@ -1 +1,23 @@
-explain select i_item_id, avg(ss_quantity) agg1, avg(ss_list_price) agg2, avg(ss_coupon_amt) agg3, avg(ss_sales_price) agg4 from store_sales, customer_demographics, date_dim, item, promotion where store_sales.ss_sold_date_sk = date_dim.d_date_sk and store_sales.ss_item_sk = item.i_item_sk and store_sales.ss_cdemo_sk = customer_demographics.cd_demo_sk and store_sales.ss_promo_sk = promotion.p_promo_sk and cd_gender = 'F' and cd_marital_status = 'W' and cd_education_status = 'Primary' and (p_channel_email = 'N' or p_channel_event = 'N') and d_year = 1998 group by i_item_id order by i_item_id limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query7.tpl and seed 1930872976
+explain
+select i_item_id,
+ avg(ss_quantity) agg1,
+ avg(ss_list_price) agg2,
+ avg(ss_coupon_amt) agg3,
+ avg(ss_sales_price) agg4
+ from store_sales, customer_demographics, date_dim, item, promotion
+ where ss_sold_date_sk = d_date_sk and
+ ss_item_sk = i_item_sk and
+ ss_cdemo_sk = cd_demo_sk and
+ ss_promo_sk = p_promo_sk and
+ cd_gender = 'F' and
+ cd_marital_status = 'W' and
+ cd_education_status = 'Primary' and
+ (p_channel_email = 'N' or p_channel_event = 'N') and
+ d_year = 1998
+ group by i_item_id
+ order by i_item_id
+ limit 100;
+
+-- end query 1 in stream 0 using template query7.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query70.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query70.q b/ql/src/test/queries/clientpositive/perf/query70.q
index 80f8c23..7974976 100644
--- a/ql/src/test/queries/clientpositive/perf/query70.q
+++ b/ql/src/test/queries/clientpositive/perf/query70.q
@@ -1,5 +1,5 @@
set hive.mapred.mode=nonstrict;
-
+-- start query 1 in stream 0 using template query70.tpl and seed 1819994127
explain
select
sum(ss_net_profit) as total_sum
@@ -7,25 +7,25 @@ select
,s_county
,grouping(s_state)+grouping(s_county) as lochierarchy
,rank() over (
- partition by grouping(s_state)+grouping(s_county),
- case when grouping(s_county) = 0 then s_state end
- order by sum(ss_net_profit) desc) as rank_within_parent
+ partition by grouping(s_state)+grouping(s_county),
+ case when grouping(s_county) = 0 then s_state end
+ order by sum(ss_net_profit) desc) as rank_within_parent
from
store_sales
,date_dim d1
- ,store s
+ ,store
where
d1.d_month_seq between 1212 and 1212+11
and d1.d_date_sk = ss_sold_date_sk
and s_store_sk = ss_store_sk
- and s.s_state in
+ and s_state in
( select s_state
from (select s_state as s_state,
- rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking
+ rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking
from store_sales, store, date_dim
where d_month_seq between 1212 and 1212+11
- and d_date_sk = ss_sold_date_sk
- and s_store_sk = ss_store_sk
+ and d_date_sk = ss_sold_date_sk
+ and s_store_sk = ss_store_sk
group by s_state
) tmp1
where ranking <= 5
@@ -37,3 +37,4 @@ select
,rank_within_parent
limit 100;
+-- end query 1 in stream 0 using template query70.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query71.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query71.q b/ql/src/test/queries/clientpositive/perf/query71.q
index d0601d2..ea6548e 100644
--- a/ql/src/test/queries/clientpositive/perf/query71.q
+++ b/ql/src/test/queries/clientpositive/perf/query71.q
@@ -1,2 +1,42 @@
set hive.mapred.mode=nonstrict;
-explain select i_brand_id brand_id, i_brand brand,t_hour,t_minute, sum(ext_price) ext_price from item JOIN (select ws_ext_sales_price as ext_price, ws_sold_date_sk as sold_date_sk, ws_item_sk as sold_item_sk, ws_sold_time_sk as time_sk from web_sales,date_dim where date_dim.d_date_sk = web_sales.ws_sold_date_sk and d_moy=12 and d_year=2001 union all select cs_ext_sales_price as ext_price, cs_sold_date_sk as sold_date_sk, cs_item_sk as sold_item_sk, cs_sold_time_sk as time_sk from catalog_sales,date_dim where date_dim.d_date_sk = catalog_sales.cs_sold_date_sk and d_moy=12 and d_year=2001 union all select ss_ext_sales_price as ext_price, ss_sold_date_sk as sold_date_sk, ss_item_sk as sold_item_sk, ss_sold_time_sk as time_sk from store_sales,date_dim where date_dim.d_date_sk = store_sales.ss_sold_date_sk and d_moy=12 and d_year=2001 ) tmp ON tmp.sold_item_sk = item.i_item_sk JOIN time_dim ON tmp.time_sk = time_dim.t_time_sk where i_manager_id=1 and (t_meal_time = 'breakfast' or t_meal_
time = 'dinner') group by i_brand, i_brand_id,t_hour,t_minute order by ext_price desc, i_brand_id ;
+-- start query 1 in stream 0 using template query71.tpl and seed 2031708268
+explain
+select i_brand_id brand_id, i_brand brand,t_hour,t_minute,
+ sum(ext_price) ext_price
+ from item, (select ws_ext_sales_price as ext_price,
+ ws_sold_date_sk as sold_date_sk,
+ ws_item_sk as sold_item_sk,
+ ws_sold_time_sk as time_sk
+ from web_sales,date_dim
+ where d_date_sk = ws_sold_date_sk
+ and d_moy=12
+ and d_year=2001
+ union all
+ select cs_ext_sales_price as ext_price,
+ cs_sold_date_sk as sold_date_sk,
+ cs_item_sk as sold_item_sk,
+ cs_sold_time_sk as time_sk
+ from catalog_sales,date_dim
+ where d_date_sk = cs_sold_date_sk
+ and d_moy=12
+ and d_year=2001
+ union all
+ select ss_ext_sales_price as ext_price,
+ ss_sold_date_sk as sold_date_sk,
+ ss_item_sk as sold_item_sk,
+ ss_sold_time_sk as time_sk
+ from store_sales,date_dim
+ where d_date_sk = ss_sold_date_sk
+ and d_moy=12
+ and d_year=2001
+ ) as tmp,time_dim
+ where
+ sold_item_sk = i_item_sk
+ and i_manager_id=1
+ and time_sk = t_time_sk
+ and (t_meal_time = 'breakfast' or t_meal_time = 'dinner')
+ group by i_brand, i_brand_id,t_hour,t_minute
+ order by ext_price desc, i_brand_id
+ ;
+
+-- end query 1 in stream 0 using template query71.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query72.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query72.q b/ql/src/test/queries/clientpositive/perf/query72.q
index 6c076d6..20fbcb1 100644
--- a/ql/src/test/queries/clientpositive/perf/query72.q
+++ b/ql/src/test/queries/clientpositive/perf/query72.q
@@ -1,3 +1,5 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query72.tpl and seed 2031708268
explain
select i_item_desc
,w_warehouse_name
@@ -28,3 +30,4 @@ 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;
+-- end query 1 in stream 0 using template query72.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query73.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query73.q b/ql/src/test/queries/clientpositive/perf/query73.q
index 95c4695..42ccaa1 100644
--- a/ql/src/test/queries/clientpositive/perf/query73.q
+++ b/ql/src/test/queries/clientpositive/perf/query73.q
@@ -1,2 +1,30 @@
set hive.mapred.mode=nonstrict;
-explain select c_last_name ,c_first_name ,c_salutation ,c_preferred_cust_flag ,ss_ticket_number ,cnt from (select ss_ticket_number ,ss_customer_sk ,count(*) cnt from store_sales,date_dim,store,household_demographics where store_sales.ss_sold_date_sk = date_dim.d_date_sk and store_sales.ss_store_sk = store.s_store_sk and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk and date_dim.d_dom between 1 and 2 and (household_demographics.hd_buy_potential = '1001-5000' or household_demographics.hd_buy_potential = '5001-10000') and household_demographics.hd_vehicle_count > 0 and case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count else null end > 1 and date_dim.d_year in (1998,1998+1,1998+2) and store.s_county in ('Kittitas County','Adams County','Richland County','Furnas County') group by ss_ticket_number,ss_customer_sk) dj,customer where dj.ss_customer_sk = customer.c_customer_sk and cnt between 1
and 5 order by cnt desc;
+-- start query 1 in stream 0 using template query73.tpl and seed 1971067816
+explain
+select c_last_name
+ ,c_first_name
+ ,c_salutation
+ ,c_preferred_cust_flag
+ ,ss_ticket_number
+ ,cnt from
+ (select ss_ticket_number
+ ,ss_customer_sk
+ ,count(*) cnt
+ from store_sales,date_dim,store,household_demographics
+ where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and date_dim.d_dom between 1 and 2
+ and (household_demographics.hd_buy_potential = '>10000' or
+ household_demographics.hd_buy_potential = 'unknown')
+ and household_demographics.hd_vehicle_count > 0
+ and case when household_demographics.hd_vehicle_count > 0 then
+ household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count else null end > 1
+ and date_dim.d_year in (2000,2000+1,2000+2)
+ and store.s_county in ('Mobile County','Maverick County','Huron County','Kittitas County')
+ group by ss_ticket_number,ss_customer_sk) dj,customer
+ where ss_customer_sk = c_customer_sk
+ and cnt between 1 and 5
+ order by cnt desc;
+
+-- end query 1 in stream 0 using template query73.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query75.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query75.q b/ql/src/test/queries/clientpositive/perf/query75.q
index a815c9e..ac1fc38 100644
--- a/ql/src/test/queries/clientpositive/perf/query75.q
+++ b/ql/src/test/queries/clientpositive/perf/query75.q
@@ -1 +1,72 @@
-explain WITH all_sales AS ( SELECT d_year ,i_brand_id ,i_class_id ,i_category_id ,i_manufact_id ,SUM(sales_cnt) AS sales_cnt ,SUM(sales_amt) AS sales_amt FROM (SELECT d_year ,i_brand_id ,i_class_id ,i_category_id ,i_manufact_id ,cs_quantity - COALESCE(cr_return_quantity,0) AS sales_cnt ,cs_ext_sales_price - COALESCE(cr_return_amount,0.0) AS sales_amt FROM catalog_sales JOIN item ON i_item_sk=cs_item_sk JOIN date_dim ON d_date_sk=cs_sold_date_sk LEFT JOIN catalog_returns ON (cs_order_number=cr_order_number AND cs_item_sk=cr_item_sk) WHERE i_category='Sports' UNION ALL SELECT d_year ,i_brand_id ,i_class_id ,i_category_id ,i_manufact_id ,ss_quantity - COALESCE(sr_return_quantity,0) AS sales_cnt ,ss_ext_sales_price - COALESCE(sr_return_amt,0.0) AS sales_amt FROM store_sales JOIN item ON i_item_sk=ss_item_sk JOIN date_dim ON d_date_sk=ss_sold_date_sk LEFT JOIN store_returns ON (ss_ticket_number=sr_ticket_number AND ss_item_sk=sr_item_sk) WHERE i_category='Sports' UNION ALL SELECT d_year
,i_brand_id ,i_class_id ,i_category_id ,i_manufact_id ,ws_quantity - COALESCE(wr_return_quantity,0) AS sales_cnt ,ws_ext_sales_price - COALESCE(wr_return_amt,0.0) AS sales_amt FROM web_sales JOIN item ON i_item_sk=ws_item_sk JOIN date_dim ON d_date_sk=ws_sold_date_sk LEFT JOIN web_returns ON (ws_order_number=wr_order_number AND ws_item_sk=wr_item_sk) WHERE i_category='Sports') sales_detail GROUP BY d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id) SELECT prev_yr.d_year AS prev_year ,curr_yr.d_year AS year ,curr_yr.i_brand_id ,curr_yr.i_class_id ,curr_yr.i_category_id ,curr_yr.i_manufact_id ,prev_yr.sales_cnt AS prev_yr_cnt ,curr_yr.sales_cnt AS curr_yr_cnt ,curr_yr.sales_cnt-prev_yr.sales_cnt AS sales_cnt_diff ,curr_yr.sales_amt-prev_yr.sales_amt AS sales_amt_diff FROM all_sales curr_yr, all_sales prev_yr WHERE curr_yr.i_brand_id=prev_yr.i_brand_id AND curr_yr.i_class_id=prev_yr.i_class_id AND curr_yr.i_category_id=prev_yr.i_category_id AND curr_yr.i_manufact_id=prev_yr.
i_manufact_id AND curr_yr.d_year=2002 AND prev_yr.d_year=2002-1 AND CAST(curr_yr.sales_cnt AS DECIMAL(17,2))/CAST(prev_yr.sales_cnt AS DECIMAL(17,2))<0.9 ORDER BY sales_cnt_diff limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query75.tpl and seed 1819994127
+explain
+WITH all_sales AS (
+ SELECT d_year
+ ,i_brand_id
+ ,i_class_id
+ ,i_category_id
+ ,i_manufact_id
+ ,SUM(sales_cnt) AS sales_cnt
+ ,SUM(sales_amt) AS sales_amt
+ FROM (SELECT d_year
+ ,i_brand_id
+ ,i_class_id
+ ,i_category_id
+ ,i_manufact_id
+ ,cs_quantity - COALESCE(cr_return_quantity,0) AS sales_cnt
+ ,cs_ext_sales_price - COALESCE(cr_return_amount,0.0) AS sales_amt
+ FROM catalog_sales JOIN item ON i_item_sk=cs_item_sk
+ JOIN date_dim ON d_date_sk=cs_sold_date_sk
+ LEFT JOIN catalog_returns ON (cs_order_number=cr_order_number
+ AND cs_item_sk=cr_item_sk)
+ WHERE i_category='Sports'
+ UNION
+ SELECT d_year
+ ,i_brand_id
+ ,i_class_id
+ ,i_category_id
+ ,i_manufact_id
+ ,ss_quantity - COALESCE(sr_return_quantity,0) AS sales_cnt
+ ,ss_ext_sales_price - COALESCE(sr_return_amt,0.0) AS sales_amt
+ FROM store_sales JOIN item ON i_item_sk=ss_item_sk
+ JOIN date_dim ON d_date_sk=ss_sold_date_sk
+ LEFT JOIN store_returns ON (ss_ticket_number=sr_ticket_number
+ AND ss_item_sk=sr_item_sk)
+ WHERE i_category='Sports'
+ UNION
+ SELECT d_year
+ ,i_brand_id
+ ,i_class_id
+ ,i_category_id
+ ,i_manufact_id
+ ,ws_quantity - COALESCE(wr_return_quantity,0) AS sales_cnt
+ ,ws_ext_sales_price - COALESCE(wr_return_amt,0.0) AS sales_amt
+ FROM web_sales JOIN item ON i_item_sk=ws_item_sk
+ JOIN date_dim ON d_date_sk=ws_sold_date_sk
+ LEFT JOIN web_returns ON (ws_order_number=wr_order_number
+ AND ws_item_sk=wr_item_sk)
+ WHERE i_category='Sports') sales_detail
+ GROUP BY d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id)
+ SELECT prev_yr.d_year AS prev_year
+ ,curr_yr.d_year AS year
+ ,curr_yr.i_brand_id
+ ,curr_yr.i_class_id
+ ,curr_yr.i_category_id
+ ,curr_yr.i_manufact_id
+ ,prev_yr.sales_cnt AS prev_yr_cnt
+ ,curr_yr.sales_cnt AS curr_yr_cnt
+ ,curr_yr.sales_cnt-prev_yr.sales_cnt AS sales_cnt_diff
+ ,curr_yr.sales_amt-prev_yr.sales_amt AS sales_amt_diff
+ FROM all_sales curr_yr, all_sales prev_yr
+ WHERE curr_yr.i_brand_id=prev_yr.i_brand_id
+ AND curr_yr.i_class_id=prev_yr.i_class_id
+ AND curr_yr.i_category_id=prev_yr.i_category_id
+ AND curr_yr.i_manufact_id=prev_yr.i_manufact_id
+ AND curr_yr.d_year=2002
+ AND prev_yr.d_year=2002-1
+ AND CAST(curr_yr.sales_cnt AS DECIMAL(17,2))/CAST(prev_yr.sales_cnt AS DECIMAL(17,2))<0.9
+ ORDER BY sales_cnt_diff
+ limit 100;
+
+-- end query 1 in stream 0 using template query75.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query76.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query76.q b/ql/src/test/queries/clientpositive/perf/query76.q
index 1894cf4..ca943ce 100644
--- a/ql/src/test/queries/clientpositive/perf/query76.q
+++ b/ql/src/test/queries/clientpositive/perf/query76.q
@@ -1 +1,26 @@
-explain select channel, col_name, d_year, d_qoy, i_category, COUNT(*) sales_cnt, SUM(ext_sales_price) sales_amt FROM ( SELECT 'store' as channel, 'ss_addr_sk' col_name, d_year, d_qoy, i_category, ss_ext_sales_price ext_sales_price FROM store_sales, item, date_dim WHERE ss_addr_sk IS NULL AND store_sales.ss_sold_date_sk=date_dim.d_date_sk AND store_sales.ss_item_sk=item.i_item_sk UNION ALL SELECT 'web' as channel, 'ws_web_page_sk' col_name, d_year, d_qoy, i_category, ws_ext_sales_price ext_sales_price FROM web_sales, item, date_dim WHERE ws_web_page_sk IS NULL AND web_sales.ws_sold_date_sk=date_dim.d_date_sk AND web_sales.ws_item_sk=item.i_item_sk UNION ALL SELECT 'catalog' as channel, 'cs_warehouse_sk' col_name, d_year, d_qoy, i_category, cs_ext_sales_price ext_sales_price FROM catalog_sales, item, date_dim WHERE cs_warehouse_sk IS NULL AND catalog_sales.cs_sold_date_sk=date_dim.d_date_sk AND catalog_sales.cs_item_sk=item.i_item_sk) foo GROUP BY channel, col_name, d_year, d_qoy, i_c
ategory ORDER BY channel, col_name, d_year, d_qoy, i_category limit 100;
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query76.tpl and seed 2031708268
+explain
+select channel, col_name, d_year, d_qoy, i_category, COUNT(*) sales_cnt, SUM(ext_sales_price) sales_amt FROM (
+ SELECT 'store' as channel, 'ss_addr_sk' col_name, d_year, d_qoy, i_category, ss_ext_sales_price ext_sales_price
+ FROM store_sales, item, date_dim
+ WHERE ss_addr_sk IS NULL
+ AND ss_sold_date_sk=d_date_sk
+ AND ss_item_sk=i_item_sk
+ UNION ALL
+ SELECT 'web' as channel, 'ws_web_page_sk' col_name, d_year, d_qoy, i_category, ws_ext_sales_price ext_sales_price
+ FROM web_sales, item, date_dim
+ WHERE ws_web_page_sk IS NULL
+ AND ws_sold_date_sk=d_date_sk
+ AND ws_item_sk=i_item_sk
+ UNION ALL
+ SELECT 'catalog' as channel, 'cs_warehouse_sk' col_name, d_year, d_qoy, i_category, cs_ext_sales_price ext_sales_price
+ FROM catalog_sales, item, date_dim
+ WHERE cs_warehouse_sk IS NULL
+ AND cs_sold_date_sk=d_date_sk
+ AND cs_item_sk=i_item_sk) foo
+GROUP BY channel, col_name, d_year, d_qoy, i_category
+ORDER BY channel, col_name, d_year, d_qoy, i_category
+limit 100;
+
+-- end query 1 in stream 0 using template query76.tpl
http://git-wip-us.apache.org/repos/asf/hive/blob/86b18772/ql/src/test/queries/clientpositive/perf/query77.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query77.q b/ql/src/test/queries/clientpositive/perf/query77.q
new file mode 100644
index 0000000..2857813
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query77.q
@@ -0,0 +1,109 @@
+set hive.mapred.mode=nonstrict;
+-- start query 1 in stream 0 using template query77.tpl and seed 1819994127
+explain
+with ss as
+ (select s_store_sk,
+ sum(ss_ext_sales_price) as sales,
+ sum(ss_net_profit) as profit
+ from store_sales,
+ date_dim,
+ store
+ where ss_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and ss_store_sk = s_store_sk
+ group by s_store_sk)
+ ,
+ sr as
+ (select s_store_sk,
+ sum(sr_return_amt) as returns,
+ sum(sr_net_loss) as profit_loss
+ from store_returns,
+ date_dim,
+ store
+ where sr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and sr_store_sk = s_store_sk
+ group by s_store_sk),
+ cs as
+ (select cs_call_center_sk,
+ sum(cs_ext_sales_price) as sales,
+ sum(cs_net_profit) as profit
+ from catalog_sales,
+ date_dim
+ where cs_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ group by cs_call_center_sk
+ ),
+ cr as
+ (select
+ sum(cr_return_amount) as returns,
+ sum(cr_net_loss) as profit_loss
+ from catalog_returns,
+ date_dim
+ where cr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ ),
+ ws as
+ ( select wp_web_page_sk,
+ sum(ws_ext_sales_price) as sales,
+ sum(ws_net_profit) as profit
+ from web_sales,
+ date_dim,
+ web_page
+ where ws_sold_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and ws_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk),
+ wr as
+ (select wp_web_page_sk,
+ sum(wr_return_amt) as returns,
+ sum(wr_net_loss) as profit_loss
+ from web_returns,
+ date_dim,
+ web_page
+ where wr_returned_date_sk = d_date_sk
+ and d_date between cast('1998-08-04' as date)
+ and (cast('1998-08-04' as date) + 30 days)
+ and wr_web_page_sk = wp_web_page_sk
+ group by wp_web_page_sk)
+ select channel
+ , id
+ , sum(sales) as sales
+ , sum(returns) as returns
+ , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+ , ss.s_store_sk as id
+ , sales
+ , coalesce(returns, 0) as returns
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ss left join sr
+ on ss.s_store_sk = sr.s_store_sk
+ union all
+ select 'catalog channel' as channel
+ , cs_call_center_sk as id
+ , sales
+ , returns
+ , (profit - profit_loss) as profit
+ from cs
+ , cr
+ union all
+ select 'web channel' as channel
+ , ws.wp_web_page_sk as id
+ , sales
+ , coalesce(returns, 0) returns
+ , (profit - coalesce(profit_loss,0)) as profit
+ from ws left join wr
+ on ws.wp_web_page_sk = wr.wp_web_page_sk
+ ) x
+ group by rollup (channel, id)
+ order by channel
+ ,id
+ limit 100;
+
+-- end query 1 in stream 0 using template query77.tpl