SQL для аналітиків даних: віконні функції, CTE та розширені запити

SQL залишається основною мовою для роботи з даними у сфері аналітики. Незалежно від того, чи йдеться про побудову звітів у BigQuery, оптимізацію воронок у Redshift чи аналіз когорт у PostgreSQL, глибоке розуміння віконних функцій, CTE та розширених запитів визначає рівень аналітика даних. Ця стаття розглядає ключові SQL-патерни, які найчастіше зустрічаються на технічних співбесідах та у повсякденній роботі з даними: від RANK та LAG до рекурсивних CTE та аналізу послідовностей.

Віконні функції SQL та конструкція OVER

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

Конструкція PARTITION BY розбиває дані на групи (аналогічно GROUP BY, але без згортання рядків), а ORDER BY визначає порядок обробки рядків всередині кожної групи. Додатковий фрейм (ROWS BETWEEN ...) дозволяє точно контролювати, які саме рядки входять до обчислення.

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  -- Ranks employees within each department by salary
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  -- Running total of salaries within department
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

У цьому прикладі RANK() присвоює ранг кожному працівнику в межах його департаменту за рівнем зарплати, а SUM() з фреймом ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW обчислює кумулятивну суму зарплат. Обидві функції працюють незалежно для кожного департаменту завдяки PARTITION BY department.

ROW_NUMBER, RANK та DENSE_RANK: порівняння

Три функції ранжування — ROW_NUMBER, RANK та DENSE_RANK — часто викликають плутанину на технічних співбесідах. Різниця між ними проявляється саме при обробці однакових значень (ties):

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

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

Результат наочно демонструє відмінності при наявності однакових значень revenue:

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

Вибір між цими функціями залежить від бізнес-вимог. ROW_NUMBER підходить для пагінації та дедуплікації. RANK — для конкурсних рейтингів, де позиція має відображати кількість записів вище. DENSE_RANK — для сценаріїв, де послідовність рангів важливіша за абсолютну позицію, наприклад, при визначенні "топ-3 категорій".

LAG та LEAD для аналізу змін між періодами

Функції LAG та LEAD забезпечують доступ до значень попередніх та наступних рядків відповідно, без необхідності self-join. Це ключовий інструмент для розрахунку показників "період до періоду" (month-over-month, year-over-year), що є повсякденною задачею аналітика даних.

-- period_over_period.sql
SELECT
  month,
  revenue,
  -- Previous month revenue
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  -- Month-over-month growth rate
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  -- Next month revenue (forward-looking)
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

Функція NULLIF у знаменнику запобігає помилці ділення на нуль для першого місяця, де LAG повертає NULL. Другий параметр LAG та LEAD (тут 1) визначає зміщення: LAG(revenue, 3) поверне значення за три місяці тому. Також доступний третій параметр — значення за замовчуванням при відсутності рядка.

NTILE та квантильна сегментація

Функція NTILE розподіляє рядки на задану кількість рівних (або майже рівних) груп. Це потужний інструмент для сегментації клієнтів, визначення перцентилів та побудови RFM-аналізу.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  -- Split customers into 4 quartiles by spend
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  -- Decile scoring for finer granularity
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

У цьому запиті клієнти з квартилем 1 — це топ-25% за витратами (VIP-сегмент), а квартиль 4 — нижні 25%. Децильна шкала (NTILE(10)) дає більш детальну гранулярність для точного таргетування маркетингових кампаній. Варто пам'ятати, що при нерівномірному розподілі рядків NTILE присвоює додаткові рядки першим групам.

Common Table Expressions: заміна вкладених підзапитів

CTE (Common Table Expressions) визначаються через конструкцію WITH і створюють іменовані тимчасові набори даних, доступні в межах основного запиту. Головна перевага CTE — це декомпозиція складних запитів на послідовні логічні кроки, що суттєво покращує читабельність та підтримуваність SQL-коду.

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

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  -- Step 1: Aggregate raw orders into monthly totals
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  -- Step 2: Rank categories within each month
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
-- Step 3: Final output — top 3 categories per month
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

Цей запит демонструє типовий патерн: перший CTE (monthly_revenue) агрегує сирі дані, другий (ranked_categories) застосовує віконну функцію RANK для ранжування, а фінальний SELECT фільтрує лише топ-3 категорії за місяць. Кожен крок ізольований і може бути протестований окремо.

Рекурсивні CTE для ієрархічних даних

Рекурсивні CTE — це механізм для обробки ієрархічних та графових структур даних у SQL: організаційних дерев, категорій товарів, BOM (bill of materials). Вони складаються з базового випадку (anchor member) та рекурсивного кроку, з'єднаних через UNION ALL.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  -- Base case: top-level managers (no manager above them)
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: join employees to their managers
  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

Базовий випадок знаходить керівників верхнього рівня (manager_id IS NULL). Рекурсивний крок приєднує кожного наступного працівника до вже знайденого ланцюжка. Стовпець depth відстежує рівень вкладеності, а management_chain формує повний шлях від CEO до поточного працівника.

Розширені аналітичні патерни: пропуски, острівці та кумулятивні суми

Задача "Gaps and Islands" — один із класичних патернів розширених SQL-запитів. Суть полягає у визначенні послідовних серій (островів) та розривів (пропусків) у даних. Типовий приклад — пошук серій послідовних днів активності користувача.

Техніка базується на ключовому спостереженні: якщо від дати відняти номер рядка (ROW_NUMBER), послідовні дати дадуть однакове значення, утворюючи "групу" для подальшої агрегації.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    -- Subtracting row_number from date creates a constant for consecutive days
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

Перший CTE усуває дублікати (кілька сесій за один день). Другий CTE обчислює streak_group: для послідовних дат (1-го, 2-го, 3-го січня) різниця між датою та ROW_NUMBER залишається сталою. Фінальний запит групує за цим ідентифікатором серії та фільтрує лише серії довжиною від 3 днів.

Поєднання віконних функцій із CASE для умовної аналітики

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

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  -- 7-day moving average
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  -- Cumulative sum resetting each quarter
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  -- Flag if current row exceeds 2x the category average
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

Зверніть увагу на три різні віконні специфікації в одному запиті. Ковзне середнє використовує фрейм ROWS BETWEEN 6 PRECEDING AND CURRENT ROW для 7-денного вікна. Кумулятивна сума перезапускається щокварталу завдяки додаванню DATE_TRUNC('quarter', order_date) до PARTITION BY. Виявлення аномалій порівнює кожне значення із середнім по категорії без обмеження фрейму.

Оптимізація продуктивності аналітичних запитів

При написанні аналітичних запитів з кількома віконними функціями, що використовують однакову специфікацію вікна, слід застосовувати іменовані вікна (WINDOW clause). Це не лише скорочує дублювання коду, але й допомагає оптимізатору запитів ефективніше планувати виконання.

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

Додаткові рекомендації щодо оптимізації віконних функцій:

• Індекси: створюйте складні індекси, що відповідають комбінації PARTITION BY + ORDER BY. Наприклад, CREATE INDEX idx_dept_salary ON employees(department, salary DESC).
• Матеріалізація CTE: у PostgreSQL CTE за замовчуванням є optimization fence (матеріалізуються). Починаючи з PostgreSQL 12, можна використовувати NOT MATERIALIZED для інлайнування.
• Фільтрація до віконних функцій: максимально звужуйте набір даних у WHERE або у попередньому CTE перед застосуванням віконних функцій.
• EXPLAIN ANALYZE: завжди перевіряйте фактичний план виконання, особливо при роботі з великими таблицями та множинними вікнами.

Висновок

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

• Ранжування та сегментація: ROW_NUMBER, RANK, DENSE_RANK, NTILE для побудови рейтингів та когортного аналізу
• Аналіз динаміки: LAG та LEAD для розрахунку темпів зростання та порівняння між періодами
• Модульність запитів: CTE для декомпозиції складної логіки на читабельні послідовні кроки
• Ієрархічні дані: рекурсивні CTE для обходу деревоподібних структур
• Патерни послідовностей: техніка "Gaps and Islands" для виявлення серій та розривів
• Умовна аналітика: поєднання віконних функцій із CASE для ковзних середніх, кумулятивних сум та детекції аномалій

Для поглибленого вивчення та практики з реальними задачами, що зустрічаються на технічних співбесідах, варто ознайомитися з повною програмою підготовки на сторінці Data Analytics.