| ==== |
| ---- QUERY: TPCDS-Q67A |
| with results as |
| ( select i_category ,i_class ,i_brand ,i_product_name ,d_year ,d_qoy ,d_moy ,s_store_id |
| ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales |
| from store_sales ,date_dim ,store ,item |
| where ss_sold_date_sk=d_date_sk |
| and ss_item_sk=i_item_sk |
| and ss_store_sk = s_store_sk |
| and d_month_seq between 1200 and 1200 + 11 |
| group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id) |
| , |
| results_rollup as |
| (select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id, sumsales |
| from results |
| union all |
| select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, null s_store_id, sum(sumsales) sumsales |
| from results |
| group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy |
| union all |
| select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales |
| from results |
| group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy |
| union all |
| select i_category, i_class, i_brand, i_product_name, d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales |
| from results |
| group by i_category, i_class, i_brand, i_product_name, d_year |
| union all |
| select i_category, i_class, i_brand, i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales |
| from results |
| group by i_category, i_class, i_brand, i_product_name |
| union all |
| select i_category, i_class, i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales |
| from results |
| group by i_category, i_class, i_brand |
| union all |
| select i_category, i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales |
| from results |
| group by i_category, i_class |
| union all |
| select i_category, null i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales |
| from results |
| group by i_category |
| union all |
| select null i_category, null i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales |
| from results) |
| select * |
| from (select i_category |
| ,i_class |
| ,i_brand |
| ,i_product_name |
| ,d_year |
| ,d_qoy |
| ,d_moy |
| ,s_store_id |
| ,sumsales |
| ,rank() over (partition by i_category order by sumsales desc) rk |
| from results_rollup) dw2 |
| where rk <= 100 |
| order by i_category |
| ,i_class |
| ,i_brand |
| ,i_product_name |
| ,d_year |
| ,d_qoy |
| ,d_moy |
| ,s_store_id |
| ,sumsales |
| ,rk |
| limit 100; |
| ---- RESULTS |
| 'Books','arts','amalgmaxi #12','NULL',NULL,NULL,NULL,'NULL',1577322.20,26 |
| 'Books','arts','amalgmaxi #2','NULL',NULL,NULL,NULL,'NULL',757558.15,93 |
| 'Books','arts','amalgmaxi #3','NULL',NULL,NULL,NULL,'NULL',889848.65,79 |
| 'Books','arts','amalgmaxi #9','NULL',NULL,NULL,NULL,'NULL',816219.08,89 |
| 'Books','arts','NULL','NULL',NULL,NULL,NULL,'NULL',5188162.15,17 |
| 'Books','business','importomaxi #12','NULL',NULL,NULL,NULL,'NULL',1288307.29,42 |
| 'Books','business','importomaxi #2','NULL',NULL,NULL,NULL,'NULL',985887.08,68 |
| 'Books','business','importomaxi #8','NULL',NULL,NULL,NULL,'NULL',1067725.65,61 |
| 'Books','business','importomaxi #9','NULL',NULL,NULL,NULL,'NULL',1269598.91,45 |
| 'Books','business','NULL','NULL',NULL,NULL,NULL,'NULL',5905977.34,10 |
| 'Books','computers','exportimaxi #12','NULL',NULL,NULL,NULL,'NULL',1456918.73,28 |
| 'Books','computers','exportimaxi #2','NULL',NULL,NULL,NULL,'NULL',1626664.04,24 |
| 'Books','computers','exportimaxi #3','NULL',NULL,NULL,NULL,'NULL',875667.64,82 |
| 'Books','computers','exportimaxi #6','NULL',NULL,NULL,NULL,'NULL',847659.03,86 |
| 'Books','computers','exportimaxi #8','NULL',NULL,NULL,NULL,'NULL',1189646.65,49 |
| 'Books','computers','exportimaxi #9','NULL',NULL,NULL,NULL,'NULL',1059845.98,63 |
| 'Books','computers','NULL','NULL',NULL,NULL,NULL,'NULL',7061536.03,7 |
| 'Books','cooking','amalgunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1097730.00,58 |
| 'Books','cooking','amalgunivamalg #3','NULL',NULL,NULL,NULL,'NULL',978431.47,69 |
| 'Books','cooking','amalgunivamalg #6','NULL',NULL,NULL,NULL,'NULL',858562.34,84 |
| 'Books','cooking','amalgunivamalg #8','NULL',NULL,NULL,NULL,'NULL',917692.00,74 |
| 'Books','cooking','amalgunivamalg #9','NULL',NULL,NULL,NULL,'NULL',881045.30,80 |
| 'Books','cooking','NULL','NULL',NULL,NULL,NULL,'NULL',5357549.49,16 |
| 'Books','entertainments','edu packmaxi #12','NULL',NULL,NULL,NULL,'NULL',962267.01,70 |
| 'Books','entertainments','edu packmaxi #2','NULL',NULL,NULL,NULL,'NULL',750249.81,94 |
| 'Books','entertainments','edu packmaxi #3','NULL',NULL,NULL,NULL,'NULL',1325140.63,39 |
| 'Books','entertainments','edu packmaxi #6','NULL',NULL,NULL,NULL,'NULL',874454.87,83 |
| 'Books','entertainments','edu packmaxi #8','NULL',NULL,NULL,NULL,'NULL',894416.62,78 |
| 'Books','entertainments','NULL','NULL',NULL,NULL,NULL,'NULL',5465761.48,14 |
| 'Books','fiction','scholarunivamalg #12','NULL',NULL,NULL,NULL,'NULL',957012.63,71 |
| 'Books','fiction','scholarunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1798976.95,20 |
| 'Books','fiction','scholarunivamalg #3','NULL',NULL,NULL,NULL,'NULL',1288272.89,43 |
| 'Books','fiction','scholarunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1031571.39,65 |
| 'Books','fiction','scholarunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1444468.85,30 |
| 'Books','fiction','scholarunivamalg #9','NULL',NULL,NULL,NULL,'NULL',1333004.22,38 |
| 'Books','fiction','NULL','NULL',NULL,NULL,NULL,'NULL',7867516.44,3 |
| 'Books','history','scholarmaxi #12','NULL',NULL,NULL,NULL,'NULL',1407800.96,34 |
| 'Books','history','scholarmaxi #2','NULL',NULL,NULL,NULL,'NULL',1601550.87,25 |
| 'Books','history','scholarmaxi #3','NULL',NULL,NULL,NULL,'NULL',1181491.20,50 |
| 'Books','history','scholarmaxi #6','NULL',NULL,NULL,NULL,'NULL',1175904.65,51 |
| 'Books','history','scholarmaxi #8','NULL',NULL,NULL,NULL,'NULL',1205167.13,48 |
| 'Books','history','scholarmaxi #9','NULL',NULL,NULL,NULL,'NULL',1873407.99,18 |
| 'Books','history','NULL','NULL',NULL,NULL,NULL,'NULL',8458815.11,2 |
| 'Books','home repair','importounivamalg #12','NULL',NULL,NULL,NULL,'NULL',929871.46,72 |
| 'Books','home repair','importounivamalg #2','NULL',NULL,NULL,NULL,'NULL',1672201.56,22 |
| 'Books','home repair','importounivamalg #3','NULL',NULL,NULL,NULL,'NULL',1321000.94,40 |
| 'Books','home repair','importounivamalg #6','NULL',NULL,NULL,NULL,'NULL',717528.04,96 |
| 'Books','home repair','importounivamalg #8','NULL',NULL,NULL,NULL,'NULL',1669033.95,23 |
| 'Books','home repair','importounivamalg #9','NULL',NULL,NULL,NULL,'NULL',1066519.11,62 |
| 'Books','home repair','NULL','NULL',NULL,NULL,NULL,'NULL',7388154.74,4 |
| 'Books','mystery','corpunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1146116.05,54 |
| 'Books','mystery','corpunivamalg #2','NULL',NULL,NULL,NULL,'NULL',747999.99,95 |
| 'Books','mystery','corpunivamalg #3','NULL',NULL,NULL,NULL,'NULL',1145257.96,55 |
| 'Books','mystery','corpunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1098189.71,57 |
| 'Books','mystery','corpunivamalg #8','NULL',NULL,NULL,NULL,'NULL',759890.03,92 |
| 'Books','mystery','NULL','NULL',NULL,NULL,NULL,'NULL',5559086.76,13 |
| 'Books','parenting','corpmaxi #12','NULL',NULL,NULL,NULL,'NULL',1022503.60,66 |
| 'Books','parenting','corpmaxi #2','NULL',NULL,NULL,NULL,'NULL',908607.31,76 |
| 'Books','parenting','corpmaxi #6','NULL',NULL,NULL,NULL,'NULL',1717275.15,21 |
| 'Books','parenting','corpmaxi #9','NULL',NULL,NULL,NULL,'NULL',895829.36,77 |
| 'Books','parenting','NULL','NULL',NULL,NULL,NULL,'NULL',5905648.45,11 |
| 'Books','reference','brandmaxi #12','NULL',NULL,NULL,NULL,'NULL',1418372.80,33 |
| 'Books','reference','brandmaxi #2','NULL',NULL,NULL,NULL,'NULL',1154379.77,53 |
| 'Books','reference','brandmaxi #3','NULL',NULL,NULL,NULL,'NULL',1168210.15,52 |
| 'Books','reference','brandmaxi #6','NULL',NULL,NULL,NULL,'NULL',1349780.84,37 |
| 'Books','reference','brandmaxi #8','NULL',NULL,NULL,NULL,'NULL',1429502.89,31 |
| 'Books','reference','brandmaxi #9','NULL',NULL,NULL,NULL,'NULL',714094.43,97 |
| 'Books','reference','NULL','NULL',NULL,NULL,NULL,'NULL',7245435.64,6 |
| 'Books','romance','namelessmaxi #12','NULL',NULL,NULL,NULL,'NULL',1356568.45,36 |
| 'Books','romance','namelessmaxi #2','NULL',NULL,NULL,NULL,'NULL',1236630.16,47 |
| 'Books','romance','namelessmaxi #3','NULL',NULL,NULL,NULL,'NULL',1866764.20,19 |
| 'Books','romance','namelessmaxi #6','NULL',NULL,NULL,NULL,'NULL',1086707.92,59 |
| 'Books','romance','namelessmaxi #8','NULL',NULL,NULL,NULL,'NULL',825762.40,88 |
| 'Books','romance','namelessmaxi #9','NULL',NULL,NULL,NULL,'NULL',876869.83,81 |
| 'Books','romance','NULL','NULL',NULL,NULL,NULL,'NULL',7252274.41,5 |
| 'Books','science','maximaxi #2','NULL',NULL,NULL,NULL,'NULL',702171.30,98 |
| 'Books','science','maximaxi #3','NULL',NULL,NULL,NULL,'NULL',777021.93,90 |
| 'Books','science','maximaxi #6','NULL',NULL,NULL,NULL,'NULL',1446589.43,29 |
| 'Books','science','maximaxi #8','NULL',NULL,NULL,NULL,'NULL',1004042.04,67 |
| 'Books','science','maximaxi #9','NULL',NULL,NULL,NULL,'NULL',849422.20,85 |
| 'Books','science','NULL','NULL',NULL,NULL,NULL,'NULL',5359276.19,15 |
| 'Books','self-help','exportiunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1283148.53,44 |
| 'Books','self-help','exportiunivamalg #3','NULL',NULL,NULL,NULL,'NULL',770181.59,91 |
| 'Books','self-help','exportiunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1391623.86,35 |
| 'Books','self-help','exportiunivamalg #8','NULL',NULL,NULL,NULL,'NULL',695644.62,100 |
| 'Books','self-help','exportiunivamalg #9','NULL',NULL,NULL,NULL,'NULL',1427999.91,32 |
| 'Books','self-help','NULL','NULL',NULL,NULL,NULL,'NULL',6261649.72,8 |
| 'Books','sports','edu packunivamalg #12','NULL',NULL,NULL,NULL,'NULL',928301.94,73 |
| 'Books','sports','edu packunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1503681.79,27 |
| 'Books','sports','edu packunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1263974.84,46 |
| 'Books','sports','edu packunivamalg #9','NULL',NULL,NULL,NULL,'NULL',840684.29,87 |
| 'Books','sports','NULL','NULL',NULL,NULL,NULL,'NULL',5610649.06,12 |
| 'Books','travel','univunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1135250.45,56 |
| 'Books','travel','univunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1053533.86,64 |
| 'Books','travel','univunivamalg #3','NULL',NULL,NULL,NULL,'NULL',1085484.15,60 |
| 'Books','travel','univunivamalg #6','NULL',NULL,NULL,NULL,'NULL',696575.97,99 |
| 'Books','travel','univunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1310411.80,41 |
| 'Books','travel','univunivamalg #9','NULL',NULL,NULL,NULL,'NULL',917487.24,75 |
| 'Books','travel','NULL','NULL',NULL,NULL,NULL,'NULL',6200882.35,9 |
| 'Books','NULL','NULL','NULL',NULL,NULL,NULL,'NULL',102246971.86,1 |
| ---- TYPES |
| STRING, STRING, STRING, STRING, INT, INT, INT, STRING, DECIMAL, BIGINT |
| ==== |