WINDOW FUNCTION SUM

description

Calculate the sum of the data in the window

  1. SUM([DISTINCT | ALL] expression) [OVER (analytic_clause)]

example

Group by property, and calculate the sum of the x columns of the current row and the previous row within the group.

  1. select x, property,   
  2. sum(x) over    
  3. (   
  4. partition by property   
  5. order by x   
  6. rows between 1 preceding and 1 following    
  7. ) as 'moving total'    
  8. from int_t where property in ('odd','even');
  10. | x  | property | moving total |
  11. |----|----------|--------------|
  12. | 2  | even     | 6            |
  13. | 4  | even     | 12           |
  14. | 6  | even     | 18           |
  15. | 8  | even     | 24           |
  16. | 10 | even     | 18           |
  17. | 1  | odd      | 4            |
  18. | 3  | odd      | 9            |
  19. | 5  | odd      | 15           |
  20. | 7  | odd      | 21           |
  21. | 9  | odd      | 16           |

keywords

  1. WINDOW,FUNCTION,SUM