OVER

OVER (
     PARTITION BY column_1, column_2, ...    - определяются партиции внутри окна (аналог GROUP BY) 
     ORDER BY column_3, ...    - указывается сортировка записей в партициях
     ROWS/RANGE BETWEEN ...    - задаются границы окна
)

пример:

SELECT SUM(column) OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...) AS **sum**
FROM table

Можно вынести создание окна:

SELECT SUM(column) OVER w AS sum
FROM table
WHERE ...
GROUP BY ...
HAVING ...
WINDOW w AS (
    PARTITION BY ... 
    ORDER BY ...
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )
ORDER BY ...
LIMIT ...

Инструкции:

• PARTITION BY — определяет столбец, по которому данные будут делиться на партиции. Например, user_id для группировки по пользователям
• ORDER BY — определяет столбец, по которому значения внутри окна будут сортироваться при обработке. Например, date для сортировки по хронологии
• ROWS/RANGE — могут дополнительно ограничивать диапазон работы функций внутри партиции

Инструкция ORDER BY определяет столбец, по которому значения внутри окна будут сортироваться при обработке. Например, сортировка по date внутри окна задаётся так:

SELECT user_id, date, price, 
       SUM(price) OVER (PARTITION BY user_id ORDER BY date) AS sum
FROM table

В этом случае для каждой записи в таблице будет вычислена сумма текущей и всех предыдущих покупок пользователя.
Почему же считается сумма именно текущей и всех предыдущих, а не вообще всех покупок пользователя?
Дело в том, что при использовании оконных функций в паре с агрегирующими для каждой строки определяется так называемая рамка окна — набор строк в её партиции. Если в OVER указать ORDER BY, то по умолчанию рамка будет состоять из всех строк от начала партиции до текущей строки (также в рамку будут включены строки, равные текущей строке по значению, указанному в ORDER BY).

• ROWS BETWEEN:

UNBOUNDED PRECEDING
<значение> PRECEDING
CURRENT ROW
<значение> FOLLOWING
UNBOUNDED FOLLOWING

• UNBOUNDED PRECEDING — указывает, что рамка начинается с первой строки партиции.
• UNBOUNDED FOLLOWING — указывает, что рамка заканчивается на последней строке партиции.
• <значение> PRECEDING и <значение> FOLLOWING — указывают, что рамка начинается или заканчивается со сдвигом на заданное число строк относительно текущей строки.
• CURRENT ROW — указывает, что рамка начинается или заканчивается на текущей строке.

SELECT SUM(column_3) OVER (PARTITION BY column_1 
                           ORDER BY column_2 
                           ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING) AS sum
FROM table

По умолчанию рамка определяется так:

RANGE UNBOUNDED PRECEDING

Это равносильно расширенному определению рамки:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Но если не указан ORDER BY, то рамка по умолчанию будет состоять из всех строк партиции (а если не указан и PARTITION, то рамкой будет все )

Варианты значение PRECEDING и значение FOLLOWING допускаются только в режиме ROWS.

Если в инструкции ORDER BY находится столбец date с типом данных DATE, то рамку окна можно задать следующим образом:

RANGE BETWEEN '3 days' PRECEDING AND '3 days' FOLLOWING

При указании рамки через RANGE обязательным условием является указание только одного столбца в инструкции ORDER BY.
Также важно знать, что оконные функции разрешается использовать в запросе только в SELECT и ORDER BY. Во всех остальных операторах, включая WHERE, HAVING и GROUP BY, они запрещены, так как логически выполняются после обычных агрегирующих функций.
Если необходимо отфильтровать или сгруппировать строки после вычисления оконных функций, можно использовать вложенный запрос:

SELECT user_id, date, price, sum
FROM (
    SELECT user_id, date, price, SUM(price) OVER (PARTITION BY user_id ORDER BY date) AS sum
    FROM table
) t
WHERE sum > 1000

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

1. Агрегирующие функции SUM, AVG, MAX, MIN, COUNT
   Внутри окна к таким функциям может применяться ORDER BY. Так, сортировка позволит получить вместо общей суммы нарастающую, а вместо абсолютного максимума — максимум среди значений вплоть до текущего.
2. Ранжирующие функции:
   • ROW_NUMBER — простая нумерация (1, 2, 3, 4, 5).
   • RANK — нумерация с учётом повторяющихся значений с пропуском рангов (1, 2, 2, 4, 5).
   • DENSE_RANK — нумерация с учётом повторяющихся значений без пропуска рангов (1, 2, 2, 3, 4).
     Разумеется, для функций ранжирования всегда нужно указывать ORDER BY, иначе они будут работать некорректно.
3. Функции смещения LAG and LEAD:
   • LAG, LEAD — значение предыдущей или следующей строки.
   • FIRST_VALUE, LAST_VALUE — первое или последнее значение в окне.
     Для функций смещения определение правил сортировки тоже необходимо.

в PostgreSQL оконные функции в качестве результата возвращают значение типа DECIMAL несмотря на то, что исходное значение находится в формате INTEGER.