Оконные функции

В качестве оконных функции можно использовать и агрегатные функции

window (
    [partition by ...]
    [order by ...]
    [
        {range | rows}
        {frame_start | between frame_start and frame_end }
    ]
)
/*
    frame_start, frame_end
    - unbounded preceding
    - value preceding
    - current row
    - value following
    - unbounded following
*/
select
    x
    , sum(x) over()
    , count(x) over()
from
    generate_series(1, 10) as f(x);
select
    x
    , sum(x) over w
    , count(x) over w
from
    generate_series(1, 10) as f(x)
window
    w as ();
-- по умолчанию
-- window w as (range between unbounded preceding and current row);
x  | sum | count
---+-----+------
1  | 55  | 10
...
10 | 55  | 10
select
    x
    , sum(x) over w
    , count(x) over w
from
    generate_series(1, 10) as f(x)
window
    w as (rows between unbounded preceding and current row);
x  | sum | count
---+-----+------
1  | 1   | 1
2  | 3   | 2
3  | 6   | 3
...
10 | 55  | 10
select
    x
    , sum(x) over w
    , count(x) over w
from
    generate_series(1, 10) as f(x)
window
    w as (rows between current row and unbounded following);
x  | sum | count
---+-----+------
1  | 55  | 10
2  | 54  | 9
3  | 52  | 8
...
10 | 10  | 1
select
    x
    , sum(x) over w
    , count(x) over w
from
    generate_series(1, 10) as f(x)
window
    w as (order by x);
x  | sum | count
---+-----+------
1  | 1   | 1
2  | 3   | 2
3  | 6   | 3
...
10 | 55  | 10

cume_dist()

dense_rank()

first_value()

Функция возвращает первое значение окна

select
    x
    , first_value() over w fv
from
    generate_series(1, 10) as f(x)
window
    w as (order by x);
x  | fv
---+---
1  | 1
2  | 1
...
10 | 1

lag()

Функция позволяет заглянуть назад

select
    x
    -- 1 - на 1 шаг назад
    , lag(x, 1) over w lg1
    -- 2 - на 2 шага назад
    , lag(x, 2) over w lg2
from
    generate_series(1, 10) as f(x)
window
    w as (order by x);
x  | lg1  | lg2
---+------+-----
1  | null | null
2  | 1    | null
3  | 2    | 1
...
10 | 9    | 8

last_value()

Функция возвращает последнее значение окна

select
    x
    , last_value() over w lv
from
    generate_series(1, 10) as f(x)
window
    w as (order by x);
x  | lv
---+---
1  | 1
2  | 2
...
10 | 10

lead()

Функция позволяет заглянуть вперед

select
    x
    -- 1 - на 1 шаг вперед
    , lead(x, 1) over w ld1
    -- 2 - на 2 шага вперед
    , lead(x, 2) over w ld2
from
    generate_series(1, 10) as f(x)
window
    w as (order by x);
x  | ld1  | ld2
---+------+-----
1  | 2    | 3
2  | 3    | 4
...
8  | 9    | 10
9  | 10   | null
10 | null | null

nth_value()

Возвращает значение по индексу

select
    x
    , nth_value(x, 3) over w nv3
    , nth_value(x, 4) over w nv4
from
    generate_series(1, 10) as f(x)
window
    w as (order by x);
x  | nv3  | nv4
---+------+-----
1  | null | null
2  | null | null
3  | 3    | null
4  | 3    | 4
...
10 | 3    | 4

ntile()

percent_rank()

rank()

row_number()

Функция нумерации

select
    x
    , row_number() over () rn
from
    generate_series(1, 10);
x  | rn
---+---
1  | 1
...
10 | 10