| ==== |
| ---- QUERY: TPCDS-Q76 |
| 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_store_sk' col_name, d_year, d_qoy, i_category, ss_ext_sales_price ext_sales_price |
| FROM store_sales, item, date_dim |
| WHERE ss_store_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_ship_customer_sk' col_name, d_year, d_qoy, i_category, ws_ext_sales_price ext_sales_price |
| FROM web_sales, item, date_dim |
| WHERE ws_ship_customer_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_ship_addr_sk' col_name, d_year, d_qoy, i_category, cs_ext_sales_price ext_sales_price |
| FROM catalog_sales, item, date_dim |
| WHERE cs_ship_addr_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; |
| ---- RESULTS |
| 'catalog','cs_ship_addr_sk',1998,1,'Books',14,24660.12 |
| 'catalog','cs_ship_addr_sk',1998,1,'Children',5,5064.75 |
| 'catalog','cs_ship_addr_sk',1998,1,'Electronics',13,31709.80 |
| 'catalog','cs_ship_addr_sk',1998,1,'Home',12,11651.18 |
| 'catalog','cs_ship_addr_sk',1998,1,'Jewelry',13,13102.23 |
| 'catalog','cs_ship_addr_sk',1998,1,'Men',11,17458.37 |
| 'catalog','cs_ship_addr_sk',1998,1,'Music',13,6741.65 |
| 'catalog','cs_ship_addr_sk',1998,1,'Shoes',9,24531.24 |
| 'catalog','cs_ship_addr_sk',1998,1,'Sports',9,19244.50 |
| 'catalog','cs_ship_addr_sk',1998,1,'Women',13,24864.50 |
| 'catalog','cs_ship_addr_sk',1998,2,'Books',14,16581.16 |
| 'catalog','cs_ship_addr_sk',1998,2,'Children',12,26195.71 |
| 'catalog','cs_ship_addr_sk',1998,2,'Electronics',9,7133.74 |
| 'catalog','cs_ship_addr_sk',1998,2,'Home',15,44228.54 |
| 'catalog','cs_ship_addr_sk',1998,2,'Jewelry',11,6804.54 |
| 'catalog','cs_ship_addr_sk',1998,2,'Men',11,11700.11 |
| 'catalog','cs_ship_addr_sk',1998,2,'Music',10,32062.82 |
| 'catalog','cs_ship_addr_sk',1998,2,'Shoes',7,4682.58 |
| 'catalog','cs_ship_addr_sk',1998,2,'Sports',7,5470.29 |
| 'catalog','cs_ship_addr_sk',1998,2,'Women',5,14928.09 |
| 'catalog','cs_ship_addr_sk',1998,2,'NULL',1,1535.61 |
| 'catalog','cs_ship_addr_sk',1998,3,'Books',20,22224.09 |
| 'catalog','cs_ship_addr_sk',1998,3,'Children',19,18667.83 |
| 'catalog','cs_ship_addr_sk',1998,3,'Electronics',15,21854.97 |
| 'catalog','cs_ship_addr_sk',1998,3,'Home',22,24690.28 |
| 'catalog','cs_ship_addr_sk',1998,3,'Jewelry',14,4586.41 |
| 'catalog','cs_ship_addr_sk',1998,3,'Men',20,30700.69 |
| 'catalog','cs_ship_addr_sk',1998,3,'Music',23,44816.68 |
| 'catalog','cs_ship_addr_sk',1998,3,'Shoes',20,33458.73 |
| 'catalog','cs_ship_addr_sk',1998,3,'Sports',25,35679.92 |
| 'catalog','cs_ship_addr_sk',1998,3,'Women',22,48029.28 |
| 'catalog','cs_ship_addr_sk',1998,4,'Books',28,46283.99 |
| 'catalog','cs_ship_addr_sk',1998,4,'Children',40,57305.90 |
| 'catalog','cs_ship_addr_sk',1998,4,'Electronics',29,42656.07 |
| 'catalog','cs_ship_addr_sk',1998,4,'Home',29,29708.36 |
| 'catalog','cs_ship_addr_sk',1998,4,'Jewelry',29,24689.26 |
| 'catalog','cs_ship_addr_sk',1998,4,'Men',36,64378.63 |
| 'catalog','cs_ship_addr_sk',1998,4,'Music',35,51308.03 |
| 'catalog','cs_ship_addr_sk',1998,4,'Shoes',27,10881.49 |
| 'catalog','cs_ship_addr_sk',1998,4,'Sports',33,33380.56 |
| 'catalog','cs_ship_addr_sk',1998,4,'Women',35,14347.96 |
| 'catalog','cs_ship_addr_sk',1998,4,'NULL',2,839.65 |
| 'catalog','cs_ship_addr_sk',1999,1,'Books',14,35278.57 |
| 'catalog','cs_ship_addr_sk',1999,1,'Children',13,17183.17 |
| 'catalog','cs_ship_addr_sk',1999,1,'Electronics',4,153.18 |
| 'catalog','cs_ship_addr_sk',1999,1,'Home',11,19895.88 |
| 'catalog','cs_ship_addr_sk',1999,1,'Jewelry',9,1341.90 |
| 'catalog','cs_ship_addr_sk',1999,1,'Men',9,6625.86 |
| 'catalog','cs_ship_addr_sk',1999,1,'Music',10,27861.23 |
| 'catalog','cs_ship_addr_sk',1999,1,'Shoes',15,5365.20 |
| 'catalog','cs_ship_addr_sk',1999,1,'Sports',9,10745.83 |
| 'catalog','cs_ship_addr_sk',1999,1,'Women',11,15388.74 |
| 'catalog','cs_ship_addr_sk',1999,2,'Books',6,3659.91 |
| 'catalog','cs_ship_addr_sk',1999,2,'Children',13,6066.50 |
| 'catalog','cs_ship_addr_sk',1999,2,'Electronics',10,14915.50 |
| 'catalog','cs_ship_addr_sk',1999,2,'Home',13,12035.96 |
| 'catalog','cs_ship_addr_sk',1999,2,'Jewelry',6,23975.00 |
| 'catalog','cs_ship_addr_sk',1999,2,'Men',13,16414.16 |
| 'catalog','cs_ship_addr_sk',1999,2,'Music',13,6061.53 |
| 'catalog','cs_ship_addr_sk',1999,2,'Shoes',10,12500.71 |
| 'catalog','cs_ship_addr_sk',1999,2,'Sports',11,2181.52 |
| 'catalog','cs_ship_addr_sk',1999,2,'Women',11,16390.31 |
| 'catalog','cs_ship_addr_sk',1999,3,'Books',18,29809.59 |
| 'catalog','cs_ship_addr_sk',1999,3,'Children',16,12816.74 |
| 'catalog','cs_ship_addr_sk',1999,3,'Electronics',17,36415.09 |
| 'catalog','cs_ship_addr_sk',1999,3,'Home',15,19664.04 |
| 'catalog','cs_ship_addr_sk',1999,3,'Jewelry',20,23257.68 |
| 'catalog','cs_ship_addr_sk',1999,3,'Men',19,20150.72 |
| 'catalog','cs_ship_addr_sk',1999,3,'Music',20,12062.24 |
| 'catalog','cs_ship_addr_sk',1999,3,'Shoes',13,14924.61 |
| 'catalog','cs_ship_addr_sk',1999,3,'Sports',24,56456.44 |
| 'catalog','cs_ship_addr_sk',1999,3,'Women',17,6958.54 |
| 'catalog','cs_ship_addr_sk',1999,4,'Books',27,50890.95 |
| 'catalog','cs_ship_addr_sk',1999,4,'Children',36,30608.13 |
| 'catalog','cs_ship_addr_sk',1999,4,'Electronics',28,59307.37 |
| 'catalog','cs_ship_addr_sk',1999,4,'Home',46,51713.78 |
| 'catalog','cs_ship_addr_sk',1999,4,'Jewelry',34,44238.07 |
| 'catalog','cs_ship_addr_sk',1999,4,'Men',19,17925.34 |
| 'catalog','cs_ship_addr_sk',1999,4,'Music',28,14816.79 |
| 'catalog','cs_ship_addr_sk',1999,4,'Shoes',35,48226.50 |
| 'catalog','cs_ship_addr_sk',1999,4,'Sports',32,35012.19 |
| 'catalog','cs_ship_addr_sk',1999,4,'Women',30,24033.32 |
| 'catalog','cs_ship_addr_sk',1999,4,'NULL',1,NULL |
| 'catalog','cs_ship_addr_sk',2000,1,'Books',15,50786.51 |
| 'catalog','cs_ship_addr_sk',2000,1,'Children',6,5623.72 |
| 'catalog','cs_ship_addr_sk',2000,1,'Electronics',13,35869.18 |
| 'catalog','cs_ship_addr_sk',2000,1,'Home',14,11212.87 |
| 'catalog','cs_ship_addr_sk',2000,1,'Jewelry',9,16751.45 |
| 'catalog','cs_ship_addr_sk',2000,1,'Men',14,34465.53 |
| 'catalog','cs_ship_addr_sk',2000,1,'Music',9,23103.03 |
| 'catalog','cs_ship_addr_sk',2000,1,'Shoes',9,2854.29 |
| 'catalog','cs_ship_addr_sk',2000,1,'Sports',5,159.04 |
| 'catalog','cs_ship_addr_sk',2000,1,'Women',8,8663.29 |
| 'catalog','cs_ship_addr_sk',2000,2,'Books',14,18943.05 |
| 'catalog','cs_ship_addr_sk',2000,2,'Children',8,7309.74 |
| 'catalog','cs_ship_addr_sk',2000,2,'Electronics',9,11856.27 |
| 'catalog','cs_ship_addr_sk',2000,2,'Home',8,10107.78 |
| 'catalog','cs_ship_addr_sk',2000,2,'Jewelry',9,19113.02 |
| 'catalog','cs_ship_addr_sk',2000,2,'Men',12,41513.90 |
| 'catalog','cs_ship_addr_sk',2000,2,'Music',10,7181.12 |
| ---- TYPES |
| STRING, STRING, INT, INT, STRING, BIGINT, DECIMAL |
| ==== |
