Senior: питання на співбесіді з теми «SQL-запити»

CTE (Common Table Expression) - іменований підзапит у блоці WITH, до якого основний запит звертається як до таблиці. Головна користь - читабельність: складний запит розбивається на кроки з іменами.

WITH paid AS (
    SELECT user_id, SUM(total) AS spent
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT u.name, p.spent
FROM users u
JOIN paid p ON p.user_id = u.id
WHERE p.spent > 10000;

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

WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories WHERE id = 42          -- початок
    UNION ALL
    SELECT c.id, c.parent_id, c.name, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id      -- крок
)
SELECT * FROM tree;

Що треба знати:

• У PostgreSQL до версії 12 CTE завжди матеріалізувався й був «бар'єром оптимізації». З 12-ї простий CTE вбудовується в запит, а поведінку можна задати явно: AS MATERIALIZED / AS NOT MATERIALIZED.
• У рекурсії потрібна умова зупинки, інакше цикли в даних дадуть нескінченний запит. Захист - лічильник глибини чи масив пройдених вузлів.
• MySQL підтримує CTE з версії 8.0.

Докладніше в документації: Запити WITH (CTE)

Віконна функція рахує значення по набору рядків («вікну»), але, на відміну від GROUP BY, не схлопує рядки: кожен рядок лишається у результаті й отримує свою цифру.

SELECT
    created_at::date AS day,
    total,
    SUM(total) OVER (ORDER BY created_at) AS running_total,
    RANK()     OVER (ORDER BY total DESC) AS place,
    total - LAG(total) OVER (ORDER BY created_at) AS diff_from_previous
FROM orders;

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

• ROW_NUMBER() - порядковий номер без повторів.
• RANK() - однакові значення ділять місце, далі пропуск (1, 2, 2, 4). DENSE_RANK() - без пропусків (1, 2, 2, 3).
• LAG() / LEAD() - значення з попереднього чи наступного рядка.
• Будь-який агрегат (SUM, AVG, COUNT) з OVER.

PARTITION BY ділить вікно на групи - наприклад, рейтинг усередині кожної категорії: RANK() OVER (PARTITION BY category_id ORDER BY sales DESC).

Рамка вікна - пастка для старших: з ORDER BY агрегат за замовчуванням рахує від початку до поточного рядка з урахуванням рівних значень (RANGE). Рядки з однаковим created_at отримають однаковий накопичений підсумок. Для «рядок за рядком» потрібно явно: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Так само рахують і ковзне середнє: AVG(total) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

Докладніше в документації: Віконні функції

Проблема: групування за датою повертає лише дні, у які були дані. Для графіка «замовлень по днях» дні без замовлень просто зникають, і лінія графіка бреше.

SELECT DATE(created_at) AS day, COUNT(*) FROM orders GROUP BY day;
-- 2026-10-01: 15, 2026-10-03: 9   (2 жовтня пропущено, хоча там 0)

Рішення - згенерувати повний календар і приєднати до нього дані:

WITH RECURSIVE calendar AS (
    SELECT DATE('2026-10-01') AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY
    FROM calendar
    WHERE day < '2026-10-31'
)
SELECT c.day, COUNT(o.id) AS orders
FROM calendar c
LEFT JOIN orders o
    ON o.created_at >= c.day
   AND o.created_at < c.day + INTERVAL 1 DAY
GROUP BY c.day
ORDER BY c.day;

Ключові деталі:

• LEFT JOIN від календаря, а не від даних - щоб дні без замовлень лишилися.
• COUNT(o.id), а не COUNT(*) - COUNT(*) порахує рядок календаря й дасть 1 замість 0.
• Умова з'єднання діапазоном, а не DATE(o.created_at) = c.day - так працює індекс на created_at.
• Ліміт рекурсії: cte_max_recursion_depth за замовчуванням 1000. Для календаря на кілька років його збільшують для сесії.

Часові пояси: «день» залежить від поясу. Якщо created_at зберігається в UTC, а звіт - за київським часом, межі днів треба зсунути (CONVERT_TZ або обчислені межі), інакше замовлення о 01:00 за Києвом потраплять у попередній день.

Альтернативи:

• Постійна таблиця-календар з датами на роки вперед - корисна, якщо звітів багато, і в неї можна додати атрибути (вихідні, свята, номер тижня).
• Заповнення пропусків у застосунку - згенерувати діапазон дат у PHP (CarbonPeriod) і злити з результатом запиту.

У PostgreSQL для цього є generate_series().

Докладніше в документації: WITH (CTE)

Рамка вікна визначає, які рядки враховує агрегат для поточного рядка. Є два способи її задати, і для часових даних різниця принципова.

ROWS - за кількістю рядків:

SELECT day, revenue,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_rows
FROM daily_revenue;

Бере 7 рядків. Якщо в даних є пропущені дні, «7 рядків» охоплять більше ніж 7 днів - і середнє буде неправильним.

RANGE з інтервалом - за значенням колонки сортування:

SELECT day, revenue,
       AVG(revenue) OVER (
           ORDER BY day
           RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
       ) AS avg_7_days
FROM daily_revenue;

Бере всі рядки, у яких day в межах 6 днів до поточного - незалежно від того, скільки рядків туди потрапило. Пропущені дні не «розтягують» вікно. MySQL підтримує RANGE з INTERVAL для дат і часу.

Що ще враховувати:

• Пропущені дні в самих даних: RANGE правильно визначить межі, але AVG порахує середнє лише по наявних днях. Якщо день без продажів має рахуватися як 0 - спершу доповнити дані календарем (рекурсивний CTE).
• Початок ряду: для перших днів у вікні менше 7 значень. Якщо це спотворює графік, - показувати середнє лише з 7-го дня (COUNT(*) OVER (...) для перевірки кількості).
• Однакові значення сортування: з RANGE рядки з однаковим day потрапляють у рамку разом (вони «рівні»). З ROWS - ні. Для щоденних агрегатів це не проблема, для сирих подій з однаковими мітками часу - важливо.
• Іменовані вікна зменшують повтори, коли кілька функцій мають однакове вікно:

SELECT day,
       AVG(revenue) OVER w AS avg_7,
       SUM(revenue) OVER w AS sum_7
FROM daily_revenue
WINDOW w AS (ORDER BY day RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW);

Швидкодія: для мільйонів рядків віконні функції з рамкою дорогі. Для дашбордів агрегати рахують заздалегідь у таблицю денних підсумків, а вікно застосовують уже до неї.

Докладніше в документації: Рамки віконних функцій

У старих версіях MySQL підзапит у WHERE ... IN (SELECT ...) часто виконувався як корельований - для кожного рядка зовнішньої таблиці. Звідси міф «підзапити в MySQL повільні, завжди пишіть JOIN». Сучасний оптимізатор перетворює їх на ефективні плани.

Semi-join - для IN і EXISTS: «рядки зовнішньої таблиці, для яких є хоч один збіг». На відміну від звичайного JOIN, не розмножує рядки при кількох збігах.

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Стратегії semi-join, з яких оптимізатор обирає за вартістю:

• Table pullout - підзапит перетворюється на звичайний JOIN, якщо збіг гарантовано один (наприклад, за первинним ключем).
• FirstMatch - при обході зупинитися на першому збігу.
• LooseScan - пройти індекс підзапиту, беручи лише перше значення кожної групи.
• Materialization - обчислити підзапит один раз у тимчасову таблицю з індексом і шукати в ній.
• Duplicate Weedout - виконати як звичайний JOIN, а дублікати прибрати потім.

Antijoin (MySQL 8.0.17+) - для NOT IN, NOT EXISTS: «рядки, для яких збігів немає». Раніше такі запити часто виконувалися значно гірше.

Як побачити, що сталося:

EXPLAIN FORMAT=TREE
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

У плані видно Semi-join, Materialize чи Nested loop semijoin. Після EXPLAIN (звичайного) SHOW WARNINGS показує, на який запит оптимізатор переписав оригінал.

Коли оптимізація не спрацьовує:

• підзапит з LIMIT, UNION, агрегатами в певних формах;
• підзапит у UPDATE/DELETE з тієї ж таблиці;
• NOT IN з колонкою, що може бути NULL (семантика NULL заважає antijoin - і результат може бути порожнім).

Керування - підказки оптимізатору (/*+ SEMIJOIN(FIRSTMATCH) */, NO_SEMIJOIN) і optimizer_switch. Але це крайній захід: спершу перевірити індекси й статистику (ANALYZE TABLE).

Докладніше в документації: Semi-join і antijoin

У MySQL (як і в PostgreSQL) немає оператора PIVOT. Рядки перетворюють на колонки умовною агрегацією: агрегат по групі, де враховуються лише рядки, що відповідають умові колонки.

-- продажі: рядок на кожну пару (товар, місяць)
SELECT product_id,
       SUM(CASE WHEN MONTH(created_at) = 1 THEN total ELSE 0 END) AS jan,
       SUM(CASE WHEN MONTH(created_at) = 2 THEN total ELSE 0 END) AS feb,
       SUM(CASE WHEN MONTH(created_at) = 3 THEN total ELSE 0 END) AS mar
FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-04-01'
GROUP BY product_id;

Короткий запис для підрахунків - у MySQL логічний вираз дорівнює 1 чи 0:

SELECT DATE(created_at) AS day,
       SUM(status = 'paid') AS paid,
       SUM(status = 'cancelled') AS cancelled,
       SUM(status = 'refunded') AS refunded
FROM orders
GROUP BY day;

У PostgreSQL для цього є COUNT(*) FILTER (WHERE status = 'paid').

Головне обмеження - колонки мають бути відомі заздалегідь. SQL-запит повертає фіксований набір колонок, тож «стільки колонок, скільки є статусів» одним статичним запитом не зробити.

Варіанти для динамічних колонок:

• Побудувати запит у застосунку: отримати список значень, згенерувати вирази SUM(CASE ...) і виконати.
• Підготовлений запит у збереженій процедурі (PREPARE / EXECUTE зі зібраного рядка) - працює, але важко підтримувати й небезпечно, якщо значення приходять від користувача.
• Повернути «довгий» формат (рядок на кожну пару) і розвернути в таблицю на клієнті - часто найпростіше й найгнучкіше: бібліотеки графіків і таблиць саме цього й очікують.

Зворотна операція (unpivot) - колонки в рядки - робиться через UNION ALL по колонках або JSON_TABLE з масиву значень.

Обережно з NULL: ELSE 0 дає нуль там, де даних немає; без ELSE вийде NULL. Для звіту важливо вирішити, що з цього означає «нуль продажів», а що - «немає даних».

Докладніше в документації: Агрегатні функції