Віконні функції SQL: повний посібник для аналітиків даних

Що таке віконні функції і чому вони важливі

Віконні функції виконують обчислення по набору рядків, пов'язаних з поточним рядком — не згортаючи їх в один результат, як це робить GROUP BY. Це єдина найпотужніша SQL-функція для аналітичних запитів, якої найчастіше бракує молодшим аналітикам.

Будь-яке ранжування, наростаючий підсумок, ковзне середнє, порівняння з попереднім періодом і нумерація рядків — все це пишеться чистіше й ефективніше з віконними функціями, ніж із self-join або підзапитами.

Базовий синтаксис

Всі віконні функції мають однакову структуру: назва функції, потім OVER(), яка визначає вікно (набір рядків для обробки).

назва_функції(вираз)
OVER (
  PARTITION BY колонка   -- розбиття на групи
  ORDER BY колонка       -- порядок всередині кожної групи
  ROWS/RANGE BETWEEN ... AND ...  -- необов'язковий фрейм
)

Клауза PARTITION BY схожа на GROUP BY, але не згортає рядки. ORDER BY всередині OVER() повністю незалежний від фінального ORDER BY запиту.

Функції ранжування: ROW_NUMBER, RANK, DENSE_RANK

-- Пронумерувати замовлення кожного клієнта хронологічно
SELECT
  order_id,
  customer_id,
  order_date,
  revenue,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_num,
  RANK()       OVER (PARTITION BY customer_id ORDER BY revenue DESC) AS revenue_rank,
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY revenue DESC) AS revenue_dense_rank
FROM orders;

Практика: отримати тільки останнє замовлення кожного клієнта

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Агрегатні віконні функції

Всі стандартні агрегатні функції (SUM, AVG, COUNT, MIN, MAX) працюють як віконні разом з OVER().

SELECT
  order_date,
  revenue,
  -- Наростаючий підсумок
  SUM(revenue) OVER (ORDER BY order_date) AS running_total,
  -- % від загального підсумку
  ROUND(revenue / SUM(revenue) OVER () * 100, 1) AS pct_of_total,
  -- Ковзне середнє за 7 днів
  AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM daily_revenue;

LAG і LEAD: порівняння по periodax

LAG() звертається до значення попереднього рядка; LEAD() — до наступного. Замінюють складні self-join для розрахунків MoM/YoY.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  -- Зростання місяць до місяця %
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month) * 100, 1
  ) AS mom_growth_pct
FROM monthly_revenue;

Фрейми вікна: ROWS vs RANGE

Клауза фрейму точно визначає, які рядки включаються в обчислення. Це критично для наростаючих підсумків і ковзних середніх.

-- Типові шаблони фреймів:
SUM(revenue) OVER (ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  -- наростаючий підсумок
AVG(revenue) OVER (ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)          -- ковзне середнє за 3 дні
SUM(revenue) OVER ()                                  -- загальна сума (всі рядки)

Реальні кейси аналітика