| ==== |
| ---- QUERY: TPCDS-Q89 |
| select * |
| from( |
| select i_category, i_class, i_brand, |
| s_store_name, s_company_name, |
| d_moy, |
| sum(ss_sales_price) sum_sales, |
| avg(sum(ss_sales_price)) over |
| (partition by i_category, i_brand, s_store_name, s_company_name) |
| 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_year in (1999) and |
| ((i_category in ('Books','Electronics','Sports') and |
| i_class in ('computers','stereo','football') |
| ) |
| or (i_category in ('Men','Jewelry','Women') and |
| i_class in ('shirts','birdal','dresses') |
| )) |
| group by i_category, i_class, i_brand, |
| s_store_name, s_company_name, 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 sum_sales - avg_monthly_sales, s_store_name |
| limit 100 |
| ---- RESULTS |
| 'Men','shirts','importoimporto #1','ese','Unknown',3,2070.65,5492.817500 |
| 'Men','shirts','importoimporto #1','ought','Unknown',6,2152.15,5045.758333 |
| 'Men','shirts','importoimporto #1','able','Unknown',2,2333.43,5194.818333 |
| 'Men','shirts','importoimporto #1','ese','Unknown',6,2691.33,5492.817500 |
| 'Men','shirts','importoimporto #1','ought','Unknown',2,2253.70,5045.758333 |
| 'Women','dresses','amalgamalg #1','able','Unknown',6,1761.81,4507.240000 |
| 'Men','shirts','importoimporto #1','ation','Unknown',3,2672.68,5410.916667 |
| 'Men','shirts','importoimporto #1','ation','Unknown',5,2677.81,5410.916667 |
| 'Men','shirts','importoimporto #1','eing','Unknown',5,2356.88,5074.076667 |
| 'Men','shirts','importoimporto #1','ese','Unknown',4,2784.11,5492.817500 |
| 'Women','dresses','amalgamalg #1','ought','Unknown',7,1926.67,4619.707500 |
| 'Women','dresses','amalgamalg #1','ought','Unknown',1,2065.94,4619.707500 |
| 'Men','shirts','importoimporto #1','ation','Unknown',6,2881.34,5410.916667 |
| 'Men','shirts','importoimporto #1','able','Unknown',3,2674.91,5194.818333 |
| 'Women','dresses','amalgamalg #1','ation','Unknown',2,2192.31,4690.890000 |
| 'Men','shirts','importoimporto #1','bar','Unknown',5,2475.90,4973.509167 |
| 'Women','dresses','amalgamalg #1','ese','Unknown',2,2146.52,4642.990833 |
| 'Men','shirts','importoimporto #1','eing','Unknown',2,2588.02,5074.076667 |
| 'Women','dresses','amalgamalg #1','bar','Unknown',7,2428.75,4914.118333 |
| 'Women','dresses','amalgamalg #1','bar','Unknown',3,2451.51,4914.118333 |
| 'Men','shirts','importoimporto #1','ation','Unknown',7,2951.67,5410.916667 |
| 'Men','shirts','importoimporto #1','able','Unknown',1,2765.75,5194.818333 |
| 'Women','dresses','amalgamalg #1','able','Unknown',2,2082.54,4507.240000 |
| 'Men','shirts','importoimporto #1','ation','Unknown',4,2988.08,5410.916667 |
| 'Men','shirts','importoimporto #1','ought','Unknown',7,2667.92,5045.758333 |
| 'Women','dresses','amalgamalg #1','bar','Unknown',4,2549.27,4914.118333 |
| 'Women','dresses','amalgamalg #1','ese','Unknown',7,2307.00,4642.990833 |
| 'Women','dresses','amalgamalg #1','eing','Unknown',4,2204.15,4528.822500 |
| 'Men','shirts','importoimporto #1','able','Unknown',7,2880.34,5194.818333 |
| 'Men','shirts','importoimporto #1','ese','Unknown',5,3210.74,5492.817500 |
| 'Women','dresses','amalgamalg #1','ation','Unknown',4,2418.39,4690.890000 |
| 'Men','shirts','importoimporto #1','eing','Unknown',4,2833.40,5074.076667 |
| 'Women','dresses','amalgamalg #1','bar','Unknown',2,2692.79,4914.118333 |
| 'Men','shirts','importoimporto #1','eing','Unknown',6,2854.62,5074.076667 |
| 'Women','dresses','amalgamalg #1','eing','Unknown',6,2314.71,4528.822500 |
| 'Men','shirts','importoimporto #1','ought','Unknown',5,2834.94,5045.758333 |
| 'Women','dresses','amalgamalg #1','ation','Unknown',5,2480.25,4690.890000 |
| 'Women','dresses','amalgamalg #1','able','Unknown',7,2302.21,4507.240000 |
| 'Women','dresses','amalgamalg #1','ation','Unknown',6,2501.15,4690.890000 |
| 'Men','shirts','importoimporto #1','ese','Unknown',2,3307.78,5492.817500 |
| 'Men','shirts','importoimporto #1','able','Unknown',5,3016.43,5194.818333 |
| 'Men','shirts','importoimporto #1','able','Unknown',4,3040.23,5194.818333 |
| 'Women','dresses','amalgamalg #1','eing','Unknown',5,2420.31,4528.822500 |
| 'Women','dresses','amalgamalg #1','ese','Unknown',5,2535.80,4642.990833 |
| 'Women','dresses','amalgamalg #1','ought','Unknown',2,2521.70,4619.707500 |
| 'Men','shirts','importoimporto #1','bar','Unknown',3,2877.96,4973.509167 |
| 'Women','dresses','amalgamalg #1','ese','Unknown',3,2577.15,4642.990833 |
| 'Women','dresses','amalgamalg #1','ation','Unknown',7,2637.27,4690.890000 |
| 'Men','shirts','importoimporto #1','ation','Unknown',1,3380.16,5410.916667 |
| 'Women','dresses','amalgamalg #1','eing','Unknown',7,2524.32,4528.822500 |
| 'Men','shirts','importoimporto #1','bar','Unknown',4,2986.70,4973.509167 |
| 'Women','dresses','amalgamalg #1','ought','Unknown',4,2648.80,4619.707500 |
| 'Men','shirts','importoimporto #1','bar','Unknown',7,3026.41,4973.509167 |
| 'Women','dresses','amalgamalg #1','ation','Unknown',3,2751.11,4690.890000 |
| 'Men','shirts','importoimporto #1','bar','Unknown',2,3073.45,4973.509167 |
| 'Men','shirts','importoimporto #1','eing','Unknown',3,3179.96,5074.076667 |
| 'Women','dresses','amalgamalg #1','ation','Unknown',1,2797.07,4690.890000 |
| 'Women','dresses','amalgamalg #1','able','Unknown',1,2621.05,4507.240000 |
| 'Women','dresses','amalgamalg #1','ese','Unknown',4,2808.36,4642.990833 |
| 'Women','dresses','amalgamalg #2','bar','Unknown',6,680.47,2506.963333 |
| 'Men','shirts','importoimporto #1','ation','Unknown',2,3591.65,5410.916667 |
| 'Women','dresses','amalgamalg #1','able','Unknown',3,2706.15,4507.240000 |
| 'Women','dresses','amalgamalg #2','eing','Unknown',2,773.08,2569.346667 |
| 'Men','shirts','importoimporto #1','bar','Unknown',6,3199.08,4973.509167 |
| 'Men','shirts','importoimporto #1','ese','Unknown',7,3754.33,5492.817500 |
| 'Women','dresses','amalgamalg #1','bar','Unknown',5,3190.77,4914.118333 |
| 'Women','dresses','amalgamalg #1','eing','Unknown',2,2841.88,4528.822500 |
| 'Men','shirts','importoimporto #1','able','Unknown',6,3538.25,5194.818333 |
| 'Women','dresses','amalgamalg #1','ese','Unknown',6,3006.94,4642.990833 |
| 'Women','dresses','amalgamalg #1','bar','Unknown',1,3284.93,4914.118333 |
| 'Women','dresses','amalgamalg #2','bar','Unknown',5,878.42,2506.963333 |
| 'Women','dresses','amalgamalg #1','able','Unknown',5,2891.95,4507.240000 |
| 'Men','shirts','importoimporto #1','eing','Unknown',7,3465.77,5074.076667 |
| 'Women','dresses','amalgamalg #2','eing','Unknown',3,964.29,2569.346667 |
| 'Women','dresses','amalgamalg #1','bar','Unknown',6,3324.49,4914.118333 |
| 'Men','shirts','importoimporto #1','ought','Unknown',3,3467.09,5045.758333 |
| 'Women','dresses','amalgamalg #1','ought','Unknown',5,3049.25,4619.707500 |
| 'Women','dresses','amalgamalg #1','eing','Unknown',3,2978.07,4528.822500 |
| 'Men','shirts','importoimporto #1','ought','Unknown',4,3544.97,5045.758333 |
| 'Men','shirts','importoimporto #1','bar','Unknown',1,3490.62,4973.509167 |
| 'Men','shirts','importoimporto #2','able','Unknown',2,628.63,2095.470833 |
| 'Men','shirts','importoimporto #1','eing','Unknown',1,3628.21,5074.076667 |
| 'Women','dresses','amalgamalg #1','able','Unknown',4,3082.17,4507.240000 |
| 'Men','shirts','importoimporto #2','eing','Unknown',5,765.86,2137.656667 |
| 'Women','dresses','amalgamalg #2','ation','Unknown',4,1064.70,2431.226667 |
| 'Women','dresses','amalgamalg #2','ought','Unknown',3,991.89,2355.441667 |
| 'Women','dresses','amalgamalg #2','eing','Unknown',5,1211.13,2569.346667 |
| 'Women','dresses','amalgamalg #2','ation','Unknown',7,1078.41,2431.226667 |
| 'Men','shirts','importoimporto #2','ation','Unknown',4,807.67,2141.060000 |
| 'Women','dresses','amalgamalg #2','ation','Unknown',2,1102.89,2431.226667 |
| 'Women','dresses','amalgamalg #2','eing','Unknown',6,1247.86,2569.346667 |
| 'Women','dresses','amalgamalg #2','able','Unknown',4,1020.39,2337.278333 |
| 'Men','shirts','importoimporto #2','able','Unknown',7,780.91,2095.470833 |
| 'Women','dresses','amalgamalg #2','ese','Unknown',2,904.34,2200.820833 |
| 'Women','dresses','amalgamalg #2','ought','Unknown',7,1061.87,2355.441667 |
| 'Women','dresses','amalgamalg #2','able','Unknown',3,1054.94,2337.278333 |
| 'Men','shirts','importoimporto #2','ought','Unknown',6,633.96,1908.056667 |
| 'Men','shirts','importoimporto #2','ese','Unknown',6,853.37,2113.573333 |
| 'Women','dresses','amalgamalg #2','able','Unknown',5,1084.94,2337.278333 |
| 'Women','dresses','amalgamalg #2','eing','Unknown',4,1317.08,2569.346667 |
| ---- TYPES |
| STRING, STRING, STRING, STRING, STRING, INT, DECIMAL, DECIMAL |
| ==== |
