Что такое window functions (OVER (PARTITION BY ... ORDER BY ...))? Чем ROW_NUMBER, RANK, DENSE_RANK отличаются?

Window Functions в PostgreSQL

Что такое window function

Window function — это функция, которая выполняет вычисление над набором строк (окном), связанных с текущей строкой, не сворачивая их в одну. В отличие от GROUP BY, все исходные строки остаются в результате.

SELECT
    id,
    company_id,
    salary,
    AVG(salary) OVER (PARTITION BY company_id) AS avg_company_salary,
    salary - AVG(salary) OVER (PARTITION BY company_id) AS diff_from_avg
FROM jobs
ORDER BY company_id, salary DESC;

Синтаксис OVER

функция() OVER (
    [PARTITION BY col1, col2]  -- разбивка на группы
    [ORDER BY col3 [ASC|DESC]]  -- порядок внутри группы
    [frame_clause]              -- ROWS/RANGE BETWEEN ...
)

ROW_NUMBER

Присваивает уникальный последовательный номер каждой строке в разделе. При одинаковых значениях порядок произвольный (если ORDER BY не полностью детерминирован).

SELECT
    id,
    title,
    company_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY salary DESC) AS rn
FROM jobs;
-- Для зарплат: 5000, 5000, 3000 -> 1, 2, 3

Применение: получить первую N строк в каждой группе (TOP-N per group):

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY salary DESC) AS rn
    FROM jobs
) t
WHERE rn <= 3;

RANK

Присваивает ранг с пропусками. При одинаковых значениях одинаковый ранг, следующий ранг — с пропуском.

SELECT
    title,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rnk
FROM jobs;
-- Для зарплат: 5000, 5000, 3000 -> 1, 1, 3  (2 пропущен)

DENSE_RANK

Присваивает ранг без пропусков. При одинаковых значениях одинаковый ранг, следующий ранг — без пропуска.

SELECT
    title,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM jobs;
-- Для зарплат: 5000, 5000, 3000 -> 1, 1, 2  (без пропуска)

Другие полезные window functions

SELECT
    id,
    created_at,
    salary,
    LAG(salary) OVER (PARTITION BY company_id ORDER BY created_at) AS prev_salary,
    LEAD(salary) OVER (PARTITION BY company_id ORDER BY created_at) AS next_salary,
    FIRST_VALUE(salary) OVER (PARTITION BY company_id ORDER BY salary DESC) AS max_salary,
    NTILE(4) OVER (ORDER BY salary) AS quartile,
    PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM jobs;

Frame clause

-- Скользящая сумма за последние 7 дней
SELECT
    created_at::date AS day,
    COUNT(*) AS daily_jobs,
    SUM(COUNT(*)) OVER (
        ORDER BY created_at::date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d
FROM jobs
GROUP BY created_at::date
ORDER BY day;

Подводные камни

• Window functions выполняются после WHERE и GROUP BY, но до ORDER BY и LIMIT — нельзя фильтровать по результату window function в WHERE, нужен подзапрос или CTE.
• ROW_NUMBER с неполным ORDER BY (без уникальных колонок) даёт недетерминированный результат — строки с одинаковыми значениями могут получать разные номера при каждом выполнении.
• RANGE (по умолчанию) и ROWS ведут себя по-разному: RANGE смотрит на значения, ROWS — на физические строки. При ORDER BY без frame_clause используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — это учитывает все строки с таким же значением, что неожиданно для SUM.
• Большое количество PARTITION BY разделов с малым числом строк работает медленно — PostgreSQL создаёт отдельный sort для каждого окна.
• Несколько window functions с разными OVER могут быть объединены в один проход через WINDOW-clause:

SELECT salary,
    ROW_NUMBER() OVER w,
    RANK() OVER w
FROM jobs
WINDOW w AS (PARTITION BY company_id ORDER BY salary DESC);

• LAG/LEAD возвращают NULL для первой/последней строки раздела — используйте третий аргумент (default value) если NULL неприемлем.
• Window functions несовместимы с DISTINCT — придётся использовать подзапрос.

• Путать window function с GROUP BY.
• Не задавать tie-breaker в ORDER BY.
• Ожидать, что RANK и DENSE_RANK одинаковы.

• Просит top-1 order per user.
• Проверяет разницу рангов на одинаковых значениях.
• Уточняет стоимость сортировки в плане.