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

> Повний посібник з віконних функцій SQL (ROW_NUMBER, RANK, LAG/LEAD), Common Table Expressions та розширених технік запитів, необхідних для співбесід аналітиків даних та повсякденної роботи.

- Published: 2026-04-09
- Updated: 2026-04-09
- Author: SharpSkill
- Tags: sql, data-analytics, window-functions, cte, interview
- Reading time: 10 min

---

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

> **Коротка довідка для повторення**
>
> Віконні функції обробляють набір рядків, пов'язаних із поточним рядком, без згортання результату в один рядок (на відміну від GROUP BY). Ключовий елемент синтаксису — це конструкція `OVER(PARTITION BY ... ORDER BY ...)`, яка визначає "вікно" для обчислень. CTE (Common Table Expressions) — це іменовані тимчасові результати запитів, оголошені через `WITH`, що замінюють вкладені підзапити та роблять SQL-код читабельним і модульним.

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

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

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

```sql
-- 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).

```sql
-- 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), що є повсякденною задачею аналітика даних.

```sql
-- 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-аналізу.

```sql
-- 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 може посилатися на попередні, створюючи конвеєр обробки даних.

```sql
-- 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`.

```sql
-- 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 до поточного працівника.

> **Продуктивність рекурсивних CTE**
>
> Рекурсивні CTE можуть спричинити суттєве навантаження на базу даних при глибокій ієрархії або великих обсягах даних. Завжди додавайте обмеження глибини рекурсії (наприклад, `WHERE oh.depth < 20` у рекурсивному кроці) та перевіряйте план виконання запиту через `EXPLAIN ANALYZE`. Стовпець, що використовується для з'єднання (manager_id), повинен бути проіндексований.

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

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

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

```sql
-- 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-виразами та складними фреймами. Це дозволяє створювати багатовимірні аналітичні запити: ковзні середні, кумулятивні суми з перезапуском по кварталах та автоматичну детекцію аномалій.

```sql
-- 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. Виявлення аномалій порівнює кожне значення із середнім по категорії без обмеження фрейму.

> **Порядок виконання віконних функцій**
>
> Віконні функції обчислюються після WHERE, GROUP BY та HAVING, але перед ORDER BY та LIMIT. Це означає, що не можна використовувати результат віконної функції в умові WHERE того ж запиту. Для фільтрації за результатом віконної функції необхідно обгорнути запит у підзапит або CTE, а потім застосувати WHERE у зовнішньому запиті.

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

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

```sql
-- 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](/technologies/data-analytics).

---

Source: SharpSkill (https://sharpskill.dev), tech interview preparation for your real stack.
HTML version of this page: https://sharpskill.dev/uk/blog/data-analytics/sql-window-functions-ctes-advanced-queries