SQL для аналітики: CTE, Window Functions і типові помилки

SQL: CTE та Window Functions

У 2026 році SQL залишається базовим інструментом аналітики незалежно від платформи — чи це PostgreSQL, BigQuery, Snowflake, Redshift, або Databricks SQL. Дві конструкції, які найчастіше відрізняють “просто SELECT” від професійного аналітичного рівня, — це CTE (Common Table Expressions) та віконні функції (window functions).

Нижче — практичне пояснення, як вони працюють, де дають найбільший ефект, і які помилки найчастіше спотворюють метрики.

CTE (WITH): для читабельності та багатокрокової логіки

CTE (Common Table Expression) — це іменований тимчасовий результат, який існує лише під час виконання запиту. Це найкращий спосіб структурувати складну логіку, розбиваючи її на зрозумілі блоки.

Коли CTE корисний в аналітиці:

• Декомпозиція: розбиття складного запиту на етапи: “очистили → агрегували → порахували KPI”.
• DRY (Don’t Repeat Yourself): повторне використання одного й того ж підзапиту в кількох місцях основного запиту.
• Підготовка даних: попередній розрахунок гранулярності (наприклад, згортання до “дня”) перед застосуванням віконних функцій.
• Читабельність: колегам простіше проводити Code Review, коли логіка читається зверху вниз.

Приклад: CTE для підготовки метрик

WITH base_orders AS (
  SELECT
    user_id,
    DATE(created_at) AS order_date,
    revenue
  FROM orders
  WHERE created_at >= '2026-01-01'
),
daily_metrics AS (
  SELECT
    order_date,
    COUNT(DISTINCT user_id) AS active_buyers,
    SUM(revenue) AS total_revenue
  FROM base_orders
  GROUP BY 1
)
SELECT *
FROM daily_metrics
ORDER BY order_date;

WITH base_orders AS (
  SELECT
    user_id,
    DATE(created_at) AS order_date,
    revenue
  FROM orders
  WHERE created_at >= '2026-01-01'
),
daily_metrics AS (
  SELECT
    order_date,
    COUNT(DISTINCT user_id) AS active_buyers,
    SUM(revenue) AS total_revenue
  FROM base_orders
  GROUP BY 1
)
SELECT *
FROM daily_metrics
ORDER BY order_date;

Window functions: аналітика “по рядках” без втрати деталей

На відміну від GROUP BY, який “згортає” рядки, віконна функція виконує обчислення для кожного рядка, маючи доступ до сусідніх даних (вікна). Це дозволяє зберігати первинну деталізацію і водночас додавати аналітичні атрибути.

1) Ранжування: ROW_NUMBER, RANK, DENSE_RANK

Типовий кейс: знайти “топ-N подій на користувача” або “останнє замовлення клієнта”. Три функції ранжування відрізняються поведінкою при однакових значеннях:

• ROW_NUMBER() — завжди присвоює унікальні номери (1, 2, 3, 4…), навіть якщо значення однакові.
• RANK() — при однакових значеннях присвоює однаковий ранг, але наступний номер “перескакує” (1, 2, 2, 4…).
• DENSE_RANK() — також присвоює однаковий ранг однаковим значенням, але без пропусків (1, 2, 2, 3…).

WITH ranked_orders AS (
  SELECT
    user_id,
    created_at,
    revenue,
    -- Нумеруємо замовлення кожного юзера від найновішого
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT *
FROM ranked_orders
WHERE rn = 1; -- Отримуємо лише останні замовлення

WITH ranked_orders AS (
  SELECT
    user_id,
    created_at,
    revenue,
    -- Нумеруємо замовлення кожного юзера від найновішого
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT *
FROM ranked_orders
WHERE rn = 1; -- Отримуємо лише останні замовлення

Важливо: Якщо ви забудете ORDER BY у віконній функції, порядок нумерації буде непередбачуваним і залежатиме від фізичного розташування рядків у таблиці.

2) Running Total (Наростаючий підсумок)

Класичний патерн для фінансових звітів.

SELECT
  order_date,
  total_revenue,
  -- Сума накопиченням від початку періоду
  SUM(total_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM daily_metrics
ORDER BY order_date;

SELECT
  order_date,
  total_revenue,
  -- Сума накопиченням від початку періоду
  SUM(total_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM daily_metrics
ORDER BY order_date;

3) Порівняння періодів: LAG / LEAD

Кейс: розрахунок зміни Day-over-Day (DoD) або пошук “провалів” у метриках.

SELECT
  order_date,
  total_revenue,
  -- Отримуємо значення за попередній день
  LAG(total_revenue) OVER (ORDER BY order_date) AS prev_day_revenue,
  -- Рахуємо різницю
  total_revenue - LAG(total_revenue) OVER (ORDER BY order_date) AS revenue_delta
FROM daily_metrics
ORDER BY order_date;

SELECT
  order_date,
  total_revenue,
  -- Отримуємо значення за попередній день
  LAG(total_revenue) OVER (ORDER BY order_date) AS prev_day_revenue,
  -- Рахуємо різницю
  total_revenue - LAG(total_revenue) OVER (ORDER BY order_date) AS revenue_delta
FROM daily_metrics
ORDER BY order_date;

4) Moving Average (Ковзне середнє)

Для згладжування графіків та трендів важливо вміти працювати з Window Frames. Фрейм визначає, які саме рядки враховуються для обчислень всередині вікна.

SELECT
  order_date,
  total_revenue,
  -- Середнє за поточний день + 6 попередніх (7-day moving average)
  AVG(total_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7_days
FROM daily_metrics
ORDER BY order_date;

SELECT
  order_date,
  total_revenue,
  -- Середнє за поточний день + 6 попередніх (7-day moving average)
  AVG(total_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7_days
FROM daily_metrics
ORDER BY order_date;

Зверніть увагу: ROWS BETWEEN рахує фізичні рядки в результаті. Якщо у ваших даних є пропуски дат (наприклад, немає даних за вихідні), ковзне середнє буде рахуватися не за календарні дні, а за наявні рядки. У такому випадку спочатку потрібно заповнити пропуски через CTE з календарною таблицею:

WITH date_spine AS (
  -- Генеруємо всі дати в діапазоні
  SELECT DATE(d) AS order_date
  FROM GENERATE_SERIES('2026-01-01', '2026-12-31', INTERVAL '1 day') AS d
),
filled_metrics AS (
  SELECT
    ds.order_date,
    COALESCE(dm.total_revenue, 0) AS total_revenue
  FROM date_spine ds
  LEFT JOIN daily_metrics dm USING (order_date)
)
SELECT
  order_date,
  total_revenue,
  AVG(total_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7_days
FROM filled_metrics
ORDER BY order_date;

WITH date_spine AS (
  -- Генеруємо всі дати в діапазоні
  SELECT DATE(d) AS order_date
  FROM GENERATE_SERIES('2026-01-01', '2026-12-31', INTERVAL '1 day') AS d
),
filled_metrics AS (
  SELECT
    ds.order_date,
    COALESCE(dm.total_revenue, 0) AS total_revenue
  FROM date_spine ds
  LEFT JOIN daily_metrics dm USING (order_date)
)
SELECT
  order_date,
  total_revenue,
  AVG(total_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7_days
FROM filled_metrics
ORDER BY order_date;

Типові помилки, які спотворюють аналітику

1. “Розмноження” рядків через JOIN (Fan-out effect)

Найчастіший “вбивця” метрик. Якщо ви робите LEFT JOIN до таблиці, де ключ не унікальний (наприклад, один юзер має кілька статусів або кілька записів подій), кількість рядків збільшиться, і SUM(revenue) покаже завищену цифру.

Суть проблеми: Уявіть, що ви рахуєте виручку по користувачах і джойните до таблиці зі статусами користувачів. Якщо один user_id має 3 записи в таблиці статусів (наприклад, “active”, “premium”, “verified”), то кожне замовлення цього юзера “розмножиться” на 3 рядки в результаті джойну. Якщо ви потім застосуєте SUM(revenue) з GROUP BY user_id, виручка буде порахована тричі — метрика буде завищена втричі.

Рішення: Завжди перевіряйте кардинальність ключів перед JOIN. Якщо права таблиця не має унікального ключа по джойн-колонці, є два підходи:

• Агрегація до джойну: Спочатку порахуйте метрики (SUM, COUNT) в окремому CTE, де кожен user_id унікальний, а потім джойніть до таблиці статусів.
• Дедуплікація правої таблиці: Якщо вам потрібен лише один статус на користувача (наприклад, найактуальніший), спочатку відфільтруйте або дедуплікуйте праву таблицю через віконну функцію (ROW_NUMBER), а потім робіть джойн.

2. Плутанина між GROUP BY та Window Functions

Пам’ятайте: GROUP BY змінює кількість рядків у результаті (зменшує їх), а віконні функції — ні. Не намагайтеся використовувати віконні функції там, де потрібно просто згорнути дані.

3. Забутий або неправильний PARTITION BY

Якщо ви забудете PARTITION BY user_id у функції ROW_NUMBER(), ви пронумеруєте всю таблицю від 1 до мільйона як єдине суцільне вікно, замість того щоб отримати “1, 2, 3…” для кожного користувача окремо. Це ламає логіку “першого замовлення клієнта” або “топ-3 продукти в кожній категорії”.

4. Неявний Window Frame (для агрегатних функцій)

За замовчуванням, коли ви використовуєте агрегатні функції (SUM, AVG, COUNT) з ORDER BY у вікні, система автоматично застосовує фрейм RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Це означає “від початку партиції до поточного рядка”. Для SUM це дає running total (наростаючий підсумок), що часто є бажаною поведінкою. Однак для ковзних метрик (Moving Average, Rolling Sum) це не те, що потрібно — там потрбіно явно вказувати ROWS BETWEEN N PRECEDING AND CURRENT ROW.

Важливо: Для ranking functions (ROW_NUMBER, RANK, DENSE_RANK) та offset functions (LAG, LEAD) поняття фрейму не застосовується — вони завжди працюють з повною партицією або конкретним зсувом.

5. SELECT * у продакшн-вітринах

Хоча це зручно для аналізу “на льоту”, у збережених запитах (View/Table) це зло. При зміні схеми джерела (додаванні колонки) ваш ETL може впасти або почати споживати зайві ресурси (наприклад BigQuery знімає гроші за прочитані колонки). Завжди перелічуйте поля явно.

Спробуйте самі: SQL-скрипт для створення тестової бази

Щоб ви могли перевірити всі приклади зі статті ми підготували для вас готовий скрипт, який ви можете запустити в PostgreSQL. Цей код створює таблиці та наповнює їх даними, які підходять для відтворення логіки роботи та типових помилок, описаних у статті.

-- 1. Створюємо таблицю замовлень
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT,
    created_at TIMESTAMP,
    revenue DECIMAL(10, 2)
);

-- 2. Створюємо таблицю статусів (для тестування помилки Fan-out)
CREATE TABLE user_statuses (
    user_id INT,
    status VARCHAR(50)
);

-- 3. Наповнюємо orders даними
INSERT INTO orders (user_id, created_at, revenue) VALUES
-- User 101: активний, купує часто (для тесту ROW_NUMBER)
(101, '2026-01-01 10:00:00', 100.00),
(101, '2026-01-01 15:30:00', 50.00),
(101, '2026-01-02 09:15:00', 120.00),
(101, '2026-01-04 11:00:00', 80.00), -- Пропуск дати 3 січня

-- User 102: звичайний покупець
(102, '2026-01-01 12:00:00', 200.00),
(102, '2026-01-04 16:45:00', 150.00),

-- User 103: для тесту RANK vs DENSE_RANK (однаковий час і сума)
(103, '2026-01-02 14:00:00', 300.00),
(103, '2026-01-05 10:00:00', 100.00),
(103, '2026-01-05 10:00:00', 100.00); 

-- 4. Наповнюємо user_statuses дублями
-- User 101 має ДВА статуси. JOIN без агрегації задвоїть його виручку.
INSERT INTO user_statuses (user_id, status) VALUES
(101, 'active'),
(101, 'premium'), -- Дублікат по ключу user_id
(102, 'basic'),
(103, 'active');

-- 1. Створюємо таблицю замовлень
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT,
    created_at TIMESTAMP,
    revenue DECIMAL(10, 2)
);

-- 2. Створюємо таблицю статусів (для тестування помилки Fan-out)
CREATE TABLE user_statuses (
    user_id INT,
    status VARCHAR(50)
);

-- 3. Наповнюємо orders даними
INSERT INTO orders (user_id, created_at, revenue) VALUES
-- User 101: активний, купує часто (для тесту ROW_NUMBER)
(101, '2026-01-01 10:00:00', 100.00),
(101, '2026-01-01 15:30:00', 50.00),
(101, '2026-01-02 09:15:00', 120.00),
(101, '2026-01-04 11:00:00', 80.00), -- Пропуск дати 3 січня

-- User 102: звичайний покупець
(102, '2026-01-01 12:00:00', 200.00),
(102, '2026-01-04 16:45:00', 150.00),

-- User 103: для тесту RANK vs DENSE_RANK (однаковий час і сума)
(103, '2026-01-02 14:00:00', 300.00),
(103, '2026-01-05 10:00:00', 100.00),
(103, '2026-01-05 10:00:00', 100.00); 

-- 4. Наповнюємо user_statuses дублями
-- User 101 має ДВА статуси. JOIN без агрегації задвоїть його виручку.
INSERT INTO user_statuses (user_id, status) VALUES
(101, 'active'),
(101, 'premium'), -- Дублікат по ключу user_id
(102, 'basic'),
(103, 'active');

Підсумок: SQL як мова мислення, а не запитів

SQL перестав бути просто мовою для витягування даних. Завдяки CTE та віконним функціям він перетворився на потужний інструмент моделювання логіки. Вміння писати чисті, структуровані запити, які не “ламають” метрики при масштабуванні — це головний критерій, що відрізняє Junior-спеціаліста від впевненого аналітика чи інженера.

Розуміння того, як працює PARTITION BY, чому JOIN може задвоїти дані та як правильно побудувати вікно для ковзного середнього, дозволяє вам переносити складну бізнес-логіку безпосередньо в базу даних, де вона працює найшвидше.

Що далі?

Технічні навички (як-от CTE та Window Functions) — це “фундамент”, але не вся “будівля”. Якщо ви хочете зрозуміти, як ці інструменти вписуються в ширший контекст професії, чому аналітик має мислити як інженер і які ще вимоги (окрім SQL) ставить ринок, радимо прочитати наш детальний розбір:

Хто такий дата аналітик у 2026 році і з чого розпочати кар’єру

У цій статті ми розглядаємо повний стек навичок — від продуктового мислення до автоматизації, яка стає стандартом індустрії.