-- The example below shows the difference between -- SUM(sales) OVER (PARTITION BY year_id ORDER BY sales -- RANGE UNBOUNDED PRECEDING) -- SUM(sales) OVER (PARTITION BY year_id ORDER BY sales -- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) SELECT year_id, region, sales, SUM(sales) OVER (PARTITION BY year_id ORDER BY sales, region) cumm_yearly, SUM(sales) OVER (PARTITION BY year_id ORDER BY sales RANGE UNBOUNDED PRECEDING) preceding_sum, SUM(sales) OVER (PARTITION BY year_id ORDER BY sales RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) following_sum FROM (SELECT 2011 year_id, 'East' region, 7500 sales FROM DUAL UNION SELECT 2011 year_id, 'North' region, 6500 sales FROM DUAL UNION SELECT 2011 year_id, 'South' region, 8500 sales FROM DUAL UNION SELECT 2011 year_id, 'West' region, 9500 sales FROM DUAL UNION SELECT 2011 year_id, 'Central' region, 9500 sales FROM DUAL UNION SELECT 2012 year_id, 'North' region, 9250 sales FROM DUAL UNION SELECT 2012 year_id, 'South' region, 8550 sales FROM DUAL ) q_sales; -- Using RANGE UNBOUNDED PRECEDING, the cummulative sum increases in -- ascending order. Based on the windowing clause, if two consecutive -- values are the same (9500 for year 2011, West and Central), the -- preceding_sum is the same (41500). -- Using RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, the sum_following -- starts with total and deceases with corresponding row value. For same -- values in consecutive rows, the sum_following remains the same (19000).
23403