| ==== |
| ---- QUERY |
| # Analyic function with no partition |
| select i_item_sk, i_current_price, |
| SUM (i_current_price) |
| OVER (ORDER BY i_item_sk) running_total |
| from item |
| order by |
| i_brand, |
| i_item_sk |
| limit 10 |
| ---- RESULTS |
| 2,1.12,28.14 |
| 24,2.61,213.93 |
| 36,91.42,501.66 |
| 54,37.84,730.17 |
| 110,1.31,1167.33 |
| 168,6.73,1719.08 |
| 190,0.56,1859.34 |
| 200,3.46,1911.81 |
| 372,8.87,3602.84 |
| 404,2.52,3873.45 |
| ---- TYPES |
| BIGINT, DECIMAL, DECIMAL |
| ==== |
| ---- QUERY |
| # Same as above, but with partition |
| select i_item_sk, i_brand, i_current_price, |
| SUM (i_current_price) |
| OVER (partition by i_brand ORDER BY i_item_sk) running_total |
| from item |
| order by |
| i_brand, |
| i_item_sk |
| limit 10 |
| ---- RESULTS |
| 2,'amalgamalg #1',1.12,1.12 |
| 24,'amalgamalg #1',2.61,3.73 |
| 36,'amalgamalg #1',91.42,95.15 |
| 54,'amalgamalg #1',37.84,132.99 |
| 110,'amalgamalg #1',1.31,134.30 |
| 168,'amalgamalg #1',6.73,141.03 |
| 190,'amalgamalg #1',0.56,141.59 |
| 200,'amalgamalg #1',3.46,145.05 |
| 372,'amalgamalg #1',8.87,153.92 |
| 404,'amalgamalg #1',2.52,156.44 |
| ---- TYPES |
| BIGINT, STRING, DECIMAL, DECIMAL |
| ==== |
| ---- QUERY |
| # Same as above, but the order by column in the analytic function has repeating values |
| select i_item_sk, i_brand, i_current_price, |
| SUM (i_current_price) |
| OVER (partition by i_brand ORDER BY i_current_price) running_total |
| from item |
| order by |
| i_brand, |
| i_item_sk |
| limit 10 |
| ---- RESULTS |
| 2,'amalgamalg #1',1.12,17.76 |
| 24,'amalgamalg #1',2.61,111.23 |
| 36,'amalgamalg #1',91.42,1967.39 |
| 54,'amalgamalg #1',37.84,1091.13 |
| 110,'amalgamalg #1',1.31,29.10 |
| 168,'amalgamalg #1',6.73,497.90 |
| 190,'amalgamalg #1',0.56,5.73 |
| 200,'amalgamalg #1',3.46,150.20 |
| 372,'amalgamalg #1',8.87,726.45 |
| 404,'amalgamalg #1',2.52,103.45 |
| ---- TYPES |
| BIGINT, STRING, DECIMAL, DECIMAL |
| ==== |
| ---- QUERY |
| # Nested analyic functions |
| select i_item_sk, i_brand, running_total, |
| SUM (running_total) |
| OVER (partition by i_manufact_id ORDER BY running_total) running_total2 |
| from ( |
| select i_item_sk, i_brand, i_current_price, i_manufact_id, |
| SUM (i_current_price) |
| OVER (partition by i_brand ORDER BY i_current_price) running_total |
| from item |
| order by |
| i_brand, |
| i_item_sk |
| ) sub |
| order by |
| i_item_sk, |
| i_brand desc |
| limit 10 |
| ---- RESULTS |
| 1,'exportischolar #2',988.61,5055.15 |
| 2,'amalgamalg #1',17.76,92.01 |
| 3,'brandbrand #4',37.69,243.96 |
| 4,'importoexporti #1',26.69,82.85 |
| 5,'importoimporto #2',243.76,1132.66 |
| 6,'exportiimporto #1',10.73,38.84 |
| 7,'amalgexporti #2',850.23,6619.62 |
| 8,'exportiexporti #1',100.41,514.95 |
| 9,'edu packamalg #2',292.44,1274.12 |
| 10,'namelessunivamalg #11',16.46,51.79 |
| ---- TYPES |
| BIGINT, STRING, DECIMAL, DECIMAL |
| ==== |
| ---- QUERY |
| # 2 analyic functions on different partition and order by columns |
| select i_item_sk, i_brand, i_current_price, i_manufact_id, |
| SUM (i_current_price) |
| OVER (partition by i_brand ORDER BY i_current_price) running_total, |
| MAX (i_current_price) |
| OVER (partition by i_manufact_id ORDER BY i_item_sk) running_max |
| from item |
| order by |
| i_brand, |
| i_item_sk |
| limit 10 |
| ---- RESULTS |
| 2,'amalgamalg #1',1.12,294,17.76,1.12 |
| 24,'amalgamalg #1',2.61,260,111.23,3.73 |
| 36,'amalgamalg #1',91.42,553,1967.39,91.42 |
| 54,'amalgamalg #1',37.84,865,1091.13,37.84 |
| 110,'amalgamalg #1',1.31,253,29.10,1.31 |
| 168,'amalgamalg #1',6.73,310,497.90,8.19 |
| 190,'amalgamalg #1',0.56,46,5.73,3.84 |
| 200,'amalgamalg #1',3.46,40,150.20,3.46 |
| 372,'amalgamalg #1',8.87,652,726.45,8.87 |
| 404,'amalgamalg #1',2.52,260,103.45,3.73 |
| ---- TYPES |
| BIGINT, STRING, DECIMAL, INT, DECIMAL, DECIMAL |
| ==== |
| ---- QUERY |
| # Union all in the FROM subquery |
| select i_item_sk, i_brand, i_current_price, |
| SUM (i_current_price) |
| OVER (partition by i_brand ORDER BY i_item_sk) running_total |
| from ( |
| select * from item |
| union all |
| select * from item |
| ) sub |
| order by |
| i_brand, |
| i_item_sk |
| limit 10 |
| ---- RESULTS |
| 2,'amalgamalg #1',1.12,2.24 |
| 2,'amalgamalg #1',1.12,2.24 |
| 24,'amalgamalg #1',2.61,7.46 |
| 24,'amalgamalg #1',2.61,7.46 |
| 36,'amalgamalg #1',91.42,190.30 |
| 36,'amalgamalg #1',91.42,190.30 |
| 54,'amalgamalg #1',37.84,265.98 |
| 54,'amalgamalg #1',37.84,265.98 |
| 110,'amalgamalg #1',1.31,268.60 |
| 110,'amalgamalg #1',1.31,268.60 |
| ---- TYPES |
| BIGINT, STRING, DECIMAL, DECIMAL |
| ==== |
| ---- QUERY |
| # Aggregate the column generated by the analytic function |
| select max(running_total) |
| from ( |
| select i_item_sk, i_current_price, |
| SUM (i_current_price) |
| OVER (partition by i_brand ORDER BY i_current_price) running_total |
| from item |
| order by |
| i_brand, |
| i_item_sk |
| ) sub |
| ---- RESULTS |
| 2815.26 |
| ---- TYPES |
| DECIMAL |
| ==== |