Оконные функции: ROW_NUMBER, RANK, SUM OVER

Считай ранги, накопительные итоги и сравнения внутри группы, не схлопывая строки, благодаря OVER и PARTITION BY.

10 мин · 3 вопросов

GROUP BY схлопывает строки в группы; оконная функция считает агрегат или ранг, сохраняя все строки. Это инструмент для топ-N по группе, накопительных итогов и сравнений со средним по своей же команде. Доступна в SQLite начиная с версии 3.25 (2018). Загрузи набор данных.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (name, dept, salary)
VALUES
  ('alice', 'tech', 5200),
  ('bruno', 'tech', 4800),
  ('chloe', 'tech', 4800),
  ('david', 'tech', 4100),
  ('emma', 'sales', 3900),
  ('fanny', 'sales', 3600),
  ('gilles', 'sales', 3600),
  ('hugo', 'sales', 3200);

Три функции ранжирования, три поведения при ничьих: ROW_NUMBER нумерует без повторов (произвольный разрыв ничьей), RANK даёт равным одинаковый ранг, а затем пропускает (1, 2, 2, 4), DENSE_RANK не пропускает (1, 2, 2, 3). Клауза WINDOW избавляет от повторения определения окна (поддерживается в SQLite и PostgreSQL, но не в SQL Server).

SELECT name, salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);

PARTITION BY перезапускает вычисление для каждого подмножества: одно окно на отдел, и ничего не схлопывается. В связке с CTE это даёт тот самый паттерн для собеседований: N самых высокооплачиваемых в каждом отделе. CTE обязателен, потому что оконная функция вычисляется после WHERE: отфильтровать по rn напрямую нельзя.

WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;

Классические агрегаты становятся оконными с OVER: SUM(salary) OVER (ORDER BY ...) даёт накопительный итог строка за строкой. Задай рамку через ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, чтобы получить строго построчный накопительный итог.

SELECT name, dept, salary,
  SUM(salary) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW
  ) AS running_total
FROM employees;

Проверка знаний

Убедись, что запомнил ключевые моменты этого урока.

1. При зарплатах 5200, 4800, 4800, 4100 какой ранг RANK() присвоит значению 4100?
   • 3
   • 4
   • 2
2. Почему нужен CTE, чтобы отфильтровать по ROW_NUMBER()?
   • OVER вычисляется после WHERE, поэтому внутри него запрещён
   • CTE всегда эффективнее
   • ROW_NUMBER работает только внутри WITH
3. Что делает PARTITION BY dept в клаузе OVER?
   • Сортирует итоговый результат по отделу
   • Перезапускает вычисление окна для каждого отдела
   • Схлопывает строки в одну на отдел