Java Interview Questions

Головна / Усі відповіді / Бази даних SQL PostgreSQL

Питання 14 · Розділ 1

Що таке віконні функції (Window Functions)?

4. FILTER для умовної агрегації 5. EXCLUDE для складних статистик (PG 11+) 6. Мінімізуйте кількість унікальних PARTITION BY 7. Incremental Sort (PG 13+) використовуйте з індекса...

Ukrainian Бази даних SQL PostgreSQL Вихідний Markdown
Мовні версії: English Russian Ukrainian

🟢 Junior Level

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

Головна перевага: ви отримуєте і деталі кожного рядка, і агрегати — в одному запиті. З GROUP BY довелося б робити підзапит або JOIN. З віконними функціями: один запит, один прохід по даних.

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

Приклад:

-- Для кожного замовлення показати його суму та середню по користувачу
SELECT
    user_id,
    amount,
    AVG(amount) OVER(PARTITION BY user_id) as user_avg
FROM orders;

-- Результат:
-- user_id | amount | user_avg
-- 1       | 100    | 150      ← середнє по користувачу 1
-- 1       | 200    | 150      ← те саме середнє, рядок не згорнувся
-- 2       | 300    | 250      ← середнє по користувачу 2
-- 2       | 200    | 250

Основні віконні функції:

  • ROW_NUMBER() — номер рядка
  • RANK(), DENSE_RANK() — ранг
  • SUM() OVER(), AVG() OVER() — агрегати без GROUP BY
  • LAG(), LEAD() — доступ до попереднього/наступного рядка

🟡 Middle Level

Анатомія віконної функції

FUNCTION() OVER (
    PARTITION BY column1, column2  -- Розділення на групи (як GROUP BY)
    ORDER BY column3               -- Сортування всередині групи
    ROWS BETWEEN ... AND ...       -- Межі вікна (frame)
)

Ключові відмінності від GROUP BY:

GROUP BY Window Functions
Згортає рядки Зберігає рядки
Один рядок на групу Кожен рядок + обчислення
Не можна змішати деталі та агрегат Можна!

Типи віконних функцій

1. Агрегатні:

SELECT
    name,
    salary,
    department,
    AVG(salary) OVER(PARTITION BY department) as dept_avg,
    SUM(salary) OVER(PARTITION BY department) as dept_total,
    COUNT(*) OVER(PARTITION BY department) as dept_count
FROM employees;

2. Ранжуючі:

SELECT
    name,
    salary,
    ROW_NUMBER() OVER(ORDER BY salary DESC) as row_num,
    RANK() OVER(ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER(ORDER BY salary DESC) as dense_rank
FROM employees;

3. Зсуву:

-- Порівняння з попереднім рядком
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER(ORDER BY month) as prev_month,
    LEAD(revenue, 1) OVER(ORDER BY month) as next_month,
    revenue - LAG(revenue, 1) OVER(ORDER BY month) as growth
FROM monthly_sales;

ROWS vs RANGE vs GROUPS

-- ROWS: фізичні рядки (найшвидший)
SELECT
    date,
    amount,
    AVG(amount) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
FROM sales;

-- RANGE: логічні значення (повільніше, але точніше)
SELECT
    date,
    amount,
    SUM(amount) OVER(ORDER BY date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) as weekly_sum
FROM sales;

Ключова різниця на прикладі: Якщо в стовпці date є дублікати (два замовлення в один день), то ROWS BETWEEN 6 PRECEDING AND CURRENT ROW візьме рівно 6 попередніх РЯДКІВ (ігноруючи дати), а RANGE BETWEEN ... 7 days PRECEDING візьме всі рядки за останні 7 днів (навіть якщо їх 20). ROWS = 6 рядків, RANGE = 7 днів.

Типові помилки

  1. Фільтрація за результатом вікна в тому самому запиті 
    -- ❌ ПОМИЛКА: вікна обчислюються ПІСЛЯ WHERE
-- Порядок виконання SQL:
-- FROM → WHERE → GROUP BY → HAVING → SELECT (включно з віконними функціями) → ORDER BY → LIMIT
-- Оскільки WHERE виконується ДО SELECT, він не бачить результат віконної функції.
-- Тому потрібна обгортка: внутрішній запит обчислює вікно, зовнішній — фільтрує.
SELECT * FROM (
    SELECT user_id, amount,
           ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) as rn
    FROM orders
) sub
WHERE rn = 1;  -- ✅ Правильно: у підзапиті
  2. Змішування різних PARTITION BY 
    -- ❌ Дорого: два різні сортування
SELECT
    AVG(amount) OVER(PARTITION BY user_id) as user_avg,
    SUM(amount) OVER(PARTITION BY department) as dept_total
FROM orders;
-- → Дві фази сортування!
  3. COUNT(*) OVER() на великих даних 
    -- ❌ Змушує прочитати ВСІ рядки розділу
SELECT id, name, COUNT(*) OVER() as total FROM users LIMIT 10;

-- ✅ Краще окремий запит для total
SELECT COUNT(*) FROM users;  -- Один раз
SELECT id, name FROM users LIMIT 10;  -- Окремо

Практичні приклади

Running Total (накопичувальний підсумок):

SELECT
    date,
    amount,
    SUM(amount) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING) as running_total
FROM transactions;

Відсоток від групи:

SELECT
    name,
    salary,
    department,
    ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY department), 2) as pct_of_dept
FROM employees;

🔴 Senior Level

Коли НЕ використовувати віконні функції

  1. Коли потрібен один агрегат (загальна сума, середнє) — використовуйте GROUP BY, це простіше і швидше
  2. Коли робите COUNT(*) OVER() лише для пагінації — окремий SELECT COUNT(*) швидше, бо не читає всі рядки
  3. Коли багато різних PARTITION BY в одному запиті — кожна унікальна комбінація = окрема фаза сортування, що вбиває продуктивність

Фізика виконання та оптимізації

Порядок обчислення в SQL:

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT / Window Functions  ← Вікна обчислюються ТУТ
6. DISTINCT
7. ORDER BY
8. LIMIT

Важливо: Вікна обчислюються ПІСЛЯ WHERE, GROUP BY та HAVING. Тому не можна фільтрувати за результатом вікна в тому самому запиті.

ROWS vs RANGE vs GROUPS: Deep Dive

ROWS:

-- Фізичні рядки: 6 ПОПЕРЕДНІХ + ПОТОЧНИЙ
AVG(amount) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- Швидко: просто бере N рядків
-- Проблема: якщо є дублікати у ORDER BY → недетерміновано

RANGE:

-- Логічні значення: всі рядки з тим самим значенням
SUM(amount) OVER(ORDER BY amount RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Повільніше: перевіряє значення, а не лише позиції
-- Потрібно для коректної роботи з дублікатами

GROUPS (PG 11+):

-- Групи рядків з однаковим значенням
AVG(amount) OVER(ORDER BY date GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- Об'єднує "peer" рядки у групи

На PG 10 і нижче GROUPS недоступний. Альтернатива: підзапит з RANGE або ручна фільтрація.

EXCLUDE (PG 11+)

-- Виключення поточного рядка з розрахунку
SELECT
    name,
    salary,
    AVG(salary) OVER(
        PARTITION BY department
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        EXCLUDE CURRENT ROW
    ) as others_avg
FROM employees;

Варіанти EXCLUDE:

  • EXCLUDE CURRENT ROW — без поточного рядка
  • EXCLUDE GROUP — без поточного + peer рядків
  • EXCLUDE TIES — без peer, але з поточним
  • EXCLUDE NO OTHERS — за замовчуванням

На PG 10 і нижче EXCLUDE недоступний. Альтернатива: CASE WHEN для виключення поточного рядка або підзапит.

Incremental Sort (PG 13+)

-- Індекс за (department, created_at)
CREATE INDEX idx_orders_dept_date ON orders(department, created_at);

-- Запит з PARTITION BY + ORDER BY
SELECT
    department,
    created_at,
    amount,
    SUM(amount) OVER(PARTITION BY department ORDER BY created_at) as running_total
FROM orders;

-- PG 13+: Incremental Sort
-- Дані вже відсортовані за department (з індексу)
-- → Сортуємо лише за created_at всередині кожного department
-- → У 5-10 разів швидше повного сортування!
-- Incremental Sort працює, бо дані вже відсортовані за першою
-- частиною ключа (з індексу). Замість повного сортування за (department, created_at)
-- база сортує лише за created_at всередині кожної групи department.
-- Якщо груп 10, кожне сортування у 10 разів менше.
-- На PG 12 і нижче — повне сортування всіх даних, навіть якщо частина вже відсортована.

Ціна Window Frame

-- Дешевий фрейм: від початку до поточного рядка (дефолт)
SUM(amount) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING)
-- → Інкрементальний розрахунок, база не "вичитує" рядки, що вийшли

-- Дорогий фрейм: ковзне вікно
AVG(amount) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
-- → Для кожного рядка перераховується вікно з 7 елементів
-- → Вимагає управління станом (додавання нового, видалення старого)

Іменовані вікна (WINDOW clause)

-- DRY для віконних функцій
SELECT
    department,
    AVG(salary) OVER w as avg_sal,
    SUM(salary) OVER w as total_sal,
    COUNT(*) OVER w as emp_count
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY hire_date);

-- → Одне визначення вікна для всіх функцій
-- → Менше сортувань, швидше виконання

FILTER з віконними функціями

SELECT
    department,
    salary,
    SUM(salary) FILTER (WHERE status = 'ACTIVE')
        OVER(PARTITION BY department) as active_payroll
FROM employees;

Edge Cases

  1. Порожнє вікно 
    -- Без PARTITION BY та ORDER BY
SELECT name, salary,
       SUM(salary) OVER() as total_payroll  -- Сума по всій таблиці
FROM employees;
  2. NULL в ORDER BY 
    -- NULL за замовчуванням LAST у DESC, FIRST у ASC
SELECT name, salary,
       RANK() OVER(ORDER BY salary DESC NULLS LAST) as rank
FROM employees;
  3. Вкладені вікна — НЕЛЬЗЯ 
    -- ❌ ПОМИЛКА: віконні функції не можна вкладати
SELECT AVG(SUM(amount) OVER(PARTITION BY user_id)) OVER() FROM orders;

-- ✅ Рішення: CTE
WITH user_totals AS (
    SELECT user_id, SUM(amount) as total
    FROM orders GROUP BY user_id
)
SELECT AVG(total) FROM user_totals;

Performance Tuning

Мінімізація унікальних вікон:

-- ❌ Дорого: 3 різних PARTITION BY → 3 сортування
SELECT
    AVG(amount) OVER(PARTITION BY user_id) as u_avg,
    AVG(amount) OVER(PARTITION BY department) as d_avg,
    AVG(amount) OVER(PARTITION BY region) as r_avg
FROM orders;

-- ✅ Краще: однакові PARTITION BY → 1 сортування
SELECT
    AVG(amount) OVER(PARTITION BY user_id) as u_avg,
    SUM(amount) OVER(PARTITION BY user_id) as u_total,
    COUNT(*) OVER(PARTITION BY user_id) as u_count
FROM orders;

Індекс для WindowAgg:

-- Індекс за (PARTITION BY columns, ORDER BY columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Запит без Sort
SELECT user_id, created_at, amount,
       SUM(amount) OVER(PARTITION BY user_id ORDER BY created_at) as running_total
FROM orders;
-- Plan: WindowAgg → Index Scan (без Sort!)

Production Experience

Реальний сценарій #1: Running Total для звіту

  • Фінтех: щоденний баланс по 1 млн транзакцій
  • Запит: 30 секунд (Sort на диску)
  • Рішення:
    • Індекс за (account_id, date)
    • ROWS UNBOUNDED PRECEDING (дешевий фрейм)
    • work_mem = 256MB
  • Результат: 2 секунди

Реальний сценарій #2: Змішані вікна вбивають продуктивність

  • Аналітика: 5 віконних функцій з різними PARTITION BY
  • Час: 2 хвилини (5 сортувань!)
  • Рішення: розбили на 2 CTE
  • Результат: 15 секунд

Monitoring

-- 1. Перевірка плану з віконними функціями
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... OVER(PARTITION BY ... ORDER BY ...);
-- Шукайте: WindowAgg → Sort (або Index Scan якщо є індекс)

-- 2. Перевірка пам'яті для Sort
EXPLAIN (ANALYZE)
SELECT ... OVER(...);
-- "external merge Disk: X kB" → spill! Збільште work_mem

-- 3. Пошук повільних запитів з вікнами
SELECT query, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%OVER(%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Best Practices

  1. Індекс за (PARTITION BY, ORDER BY) → без Sort
  2. ROWS замість RANGE де можливо (швидше)
  3. WINDOW clause для перевизначення вікон
  4. FILTER для умовної агрегації
  5. EXCLUDE для складних статистик (PG 11+)
  6. Мінімізуйте кількість унікальних PARTITION BY
  7. Incremental Sort (PG 13+) використовуйте з індексами
  8. Підзапит/CTE для фільтрації за результатом вікна

Резюме для Senior

  • Віконні функції обчислюються ПІСЛЯ WHERE, GROUP BY, HAVING
  • ROWS швидше RANGE, але RANGE коректніше з дублікатами
  • Incremental Sort (PG 13+) економить при partial сортуванні
  • EXCLUDE (PG 11+) для складних статистик
  • FILTER підтримується у віконних функціях
  • Ціна фрейма: UNBOUNDED PRECEDING дешевше ковзного вікна
  • Мінімізуйте унікальні PARTITION BY → менше Sort фаз
  • Індекс за (PARTITION BY, ORDER BY) → WindowAgg без Sort

🎯 Шпаргалка для інтерв’ю

Обов’язково знати:

  • Віконні функції: обчислюють по групі рядків, НЕ згортаючи (на відміну від GROUP BY)
  • Синтаксис: FUNCTION() OVER(PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...)
  • 3 типи: агрегатні (SUM, AVG), ранжуючі (ROW_NUMBER, RANK), зсуву (LAG, LEAD)
  • ROWS = фізичні рядки (швидше), RANGE = логічні значення (коректніше з дублікатами)
  • GROUPS (PG 11+): групує «peer» рядки з однаковим значенням
  • EXCLUDE (PG 11+): EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES
  • Фільтрація за результатом вікна → ТІЛЬКИ в підзапиті/CTE (вікна обчислюються ПІСЛЯ WHERE)
  • Індекс за (PARTITION BY, ORDER BY) → WindowAgg без Sort
  • Incremental Sort (PG 13+): дані частково відсортовані → сортуємо решту
  • Іменовані вікна (WINDOW clause): DRY для віконних функцій
  • Віконні функції не можна вкладати → CTE

Часті уточнюючі запитання:

  • «Чому не можна фільтрувати за віконною функцією у WHERE?» → Вікна обчислюються ПІСЛЯ WHERE
  • «ROWS vs RANGE — що обрати?» → ROWS швидше, RANGE коректніше при дублікатах
  • «Як прискорити віконну функцію?» → Індекс за (PARTITION BY, ORDER BY)
  • «Що таке Incremental Sort?» → PG 13+: сортує лише ту частину, що бракує

Червоні прапорці (НЕ говорити):

  • ❌ «Віконні функції згортають рядки» (ні, це GROUP BY)
  • ❌ «Можна фільтрувати за віконною функцією у WHERE» (ні, після WHERE обчислюються)
  • ❌ «RANGE завжди кращий за ROWS» (ROWS швидше, RANGE лише при дублікатах)

Пов’язані теми:

  • [[Що робить ROW_NUMBER()]] → унікальна нумерація
  • [[Що роблять RANK() та DENSE_RANK()]] → ранжування
  • [[Що робить GROUP BY]] → відмінність від віконних функцій
  • [[В чому різниця між WHERE та HAVING]] → порядок виконання