Window Function Example

window_function.sql

with t as (
    select x, y+x as y
    from generate_series(1, 3) as t1(x) full outer join generate_series(11, 13) as t2(y) on 1=1
)
select x, y,
     array_agg(x) over () as frame,
     sum(x) over () as sum,
     sum(x) over (partition by x order by y) as sum_rolling,
     count(1) over (partition by x) as cnt_part,
     count(1) over (partition by x order by y) as cnt_rolling,
     count(1) over (partition by x rows unbounded preceding) as cnt_part,
     lag(y, 1) over (partition by x) as y_lag,
     x::float/sum(x) over () as part
from t
;

x |y  |frame             |sum |sum_rolling |cnt_part |cnt_rolling |cnt_part |y_lag |part                |
--|---|------------------|----|------------|---------|------------|---------|------|--------------------|
1 |12 |1 1 1 2 2 2 3 3 3 |18  |1           |3        |1           |1        |      |0.05555555555555555 |
1 |13 |1 1 1 2 2 2 3 3 3 |18  |2           |3        |2           |2        |12    |0.05555555555555555 |
1 |14 |1 1 1 2 2 2 3 3 3 |18  |3           |3        |3           |3        |13    |0.05555555555555555 |
2 |13 |1 1 1 2 2 2 3 3 3 |18  |2           |3        |1           |1        |      |0.1111111111111111  |
2 |14 |1 1 1 2 2 2 3 3 3 |18  |4           |3        |2           |2        |13    |0.1111111111111111  |
2 |15 |1 1 1 2 2 2 3 3 3 |18  |6           |3        |3           |3        |14    |0.1111111111111111  |
3 |14 |1 1 1 2 2 2 3 3 3 |18  |3           |3        |1           |1        |      |0.16666666666666666 |
3 |15 |1 1 1 2 2 2 3 3 3 |18  |6           |3        |2           |2        |14    |0.16666666666666666 |
3 |16 |1 1 1 2 2 2 3 3 3 |18  |9           |3        |3           |3        |15    |0.16666666666666666 |