resources/tpcds-modifiedQueries/q27.sql

0001 -- start query 27 in stream 0 using template query27.tpl
0002  with results as
0003  (select i_item_id,
0004         s_state,
0005         ss_quantity agg1,
0006         ss_list_price agg2,
0007         ss_coupon_amt agg3,
0008         ss_sales_price agg4
0009         --0 as g_state,
0010         --avg(ss_quantity) agg1,
0011         --avg(ss_list_price) agg2,
0012         --avg(ss_coupon_amt) agg3,
0013         --avg(ss_sales_price) agg4
0014  from store_sales, customer_demographics, date_dim, store, item
0015  where ss_sold_date_sk = d_date_sk and
0016        ss_sold_date_sk between 2451545 and 2451910 and
0017        ss_item_sk = i_item_sk and
0018        ss_store_sk = s_store_sk and
0019        ss_cdemo_sk = cd_demo_sk and
0020        cd_gender = 'F' and
0021        cd_marital_status = 'D' and
0022        cd_education_status = 'Primary' and
0023        d_year = 2000 and
0024        s_state in ('TN','AL', 'SD', 'SD', 'SD', 'SD')
0025  --group by i_item_id, s_state
0026  )
0027
0028  select i_item_id,
0029   s_state, g_state, agg1, agg2, agg3, agg4
0030    from (
0031         select i_item_id, s_state, 0 as g_state, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3, avg(agg4) agg4 from results
0032         group by i_item_id, s_state
0033          union all
0034         select i_item_id, NULL AS s_state, 1 AS g_state, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3,
0035          avg(agg4) agg4 from results
0036         group by i_item_id
0037          union all
0038         select NULL AS i_item_id, NULL as s_state, 1 as g_state, avg(agg1) agg1, avg(agg2) agg2, avg(agg3) agg3,
0039          avg(agg4) agg4 from results
0040         ) foo
0041   order by i_item_id, s_state
0042  limit 100
0043 -- end query 27 in stream 0 using template query27.tpl