WINDOW FUNCTION COUNT

description

Count the number of occurrences of data in the window

1. COUNT(expression) [OVER (analytic_clause)]

example

Count the number of occurrences of x from the current row to the first row.

1. select x, property,
2. count(x) over
3. (
4. partition by property
5. order by x
6. rows between unbounded preceding and current row
7. ) as 'cumulative total'
8. from int_t where property in ('odd','even');

10. | x  | property | cumulative count |
11. |----|----------|------------------|
12. | 2  | even     | 1                |
13. | 4  | even     | 2                |
14. | 6  | even     | 3                |
15. | 8  | even     | 4                |
16. | 10 | even     | 5                |
17. | 1  | odd      | 1                |
18. | 3  | odd      | 2                |
19. | 5  | odd      | 3                |
20. | 7  | odd      | 4                |
21. | 9  | odd      | 5                |

keywords

1. WINDOW,FUNCTION,COUNT