Middle: питання на співбесіді з теми «Можливості SQL у PostgreSQL»

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

Задача: три останні замовлення кожного користувача.

SELECT u.id, u.email, o.id AS order_id, o.total, o.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT id, total, created_at
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o;

LIMIT 3 усередині застосовується окремо для кожного користувача - звичайним JOIN чи GROUP BY такого не виразити.

LEFT JOIN LATERAL ... ON true - зберегти користувачів без замовлень:

SELECT u.id, last_order.total
FROM users u
LEFT JOIN LATERAL (
    SELECT total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) last_order ON true;

Коли LATERAL доречний:

• топ-N у групі, особливо з індексом (user_id, created_at DESC): для кожного користувача - коротке сканування індексу, а не сортування всієї таблиці;
• функції, що повертають набори рядків з аргументами з іншої таблиці:

SELECT p.id, tag
FROM posts p, LATERAL unnest(p.tags) AS tag;

• кілька обчислених значень без повторення виразів у SELECT;
• розгортання JSON (jsonb_array_elements, jsonb_to_recordset) для кожного рядка.

Порівняння для «топ-N у групі»:

Ризик: LATERAL - це фактично вкладений цикл. На мільйоні зовнішніх рядків без індексу для внутрішнього запиту він виконає мільйон повних сканувань. Перевіряйте план через EXPLAIN ANALYZE.

У Laravel: joinLateral() і leftJoinLateral() у конструкторі запитів:

$latest = DB::table('orders')
    ->whereColumn('user_id', 'users.id')
    ->orderByDesc('created_at')
    ->limit(3);

User::query()->joinLateral($latest, 'recent_orders')->get();

Докладніше в документації: Табличні вирази: LATERAL

FILTER - умова для окремого агрегату. Кілька різних підрахунків за один прохід таблиці:

SELECT
    count(*)                                      AS total,
    count(*) FILTER (WHERE status = 'paid')       AS paid,
    count(*) FILTER (WHERE status = 'refunded')   AS refunded,
    sum(total) FILTER (WHERE status = 'paid')     AS revenue
FROM orders
WHERE created_at >= date_trunc('month', now());

Те саме в стандартному SQL пишуть через sum(CASE WHEN ... THEN 1 ELSE 0 END) - FILTER коротший і читабельніший. Він працює з будь-якими агрегатами: sum, avg, array_agg, jsonb_agg.

generate_series - згенерувати послідовність чисел чи дат:

SELECT generate_series(1, 5);
SELECT generate_series('2026-10-01'::date, '2026-10-07', interval '1 day');

Звіт по днях без пропусків. Якщо згрупувати замовлення за днем, дні без замовлень просто зникнуть з результату - а на графіку потрібен нуль:

SELECT d::date AS day,
       count(o.id) AS orders,
       coalesce(sum(o.total), 0) AS revenue
FROM generate_series(date_trunc('day', now()) - interval '29 days',
                     date_trunc('day', now()),
                     interval '1 day') AS d
LEFT JOIN orders o
       ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;

• серія дає всі дні періоду;
• LEFT JOIN зберігає дні без замовлень;
• count(o.id), а не count(*) - інакше порожній день порахується як 1.

Зведена таблиця (pivot) через FILTER:

SELECT date_trunc('week', created_at) AS week,
       count(*) FILTER (WHERE channel = 'web')    AS web,
       count(*) FILTER (WHERE channel = 'mobile') AS mobile
FROM orders
GROUP BY 1
ORDER BY 1;

Часові пояси: date_trunc('day', created_at) обрізає за поясом сесії бази. Для звіту за київським часом - date_trunc('day', created_at, 'Europe/Kyiv') (для timestamptz), інакше «день» починатиметься о 02:00 чи 03:00 за Києвом.

Продуктивність: умова за діапазоном created_at >= ... AND created_at < ... використовує індекс, а WHERE date(created_at) = ... - ні (функція над колонкою).

У Laravel такі запити зручно писати через selectRaw і DB::select, а для дашбордів - кешувати: звіт за місяць не має перераховуватися на кожне відкриття сторінки.

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

MERGE (PostgreSQL 15+, стандартний SQL) порівнює цільову таблицю з джерелом і для кожного рядка виконує дію залежно від того, знайдено збіг чи ні:

MERGE INTO products AS p
USING staging_products AS s
ON p.sku = s.sku
WHEN MATCHED AND s.discontinued THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET price = s.price, updated_at = now()
WHEN NOT MATCHED THEN
    INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);

PostgreSQL 17+: WHEN NOT MATCHED BY SOURCE - рядки цільової таблиці, яких немає в джерелі (наприклад, видалити товари, що зникли з фіда), і RETURNING з функцією merge_action(), що показує, яку дію виконано для рядка.

Порівняння:

Головна відмінність - поведінка при конкуренції. ON CONFLICT гарантовано обробляє ситуацію, коли інший запит одночасно вставляє той самий ключ: він чекає й оновлює. MERGE спершу визначає, чи є збіг, а потім виконує дію - якщо паралельний запит встиг вставити рядок, MERGE спробує вставити його вдруге й отримає помилку унікальності.

Коли що обирати:

• ON CONFLICT - upsert з високою конкуренцією: лічильники, кеш-таблиці, синхронізація окремих записів з багатьох процесів;
• MERGE - пакетна синхронізація з таблицею-джерелом: імпорт фіда, ETL з проміжної таблиці, де потрібні і вставки, і оновлення, і видалення, а паралельних записувачів немає (чи їх виключено блокуванням).

Типовий сценарій імпорту:

1. COPY файлу в тимчасову таблицю;
2. MERGE з неї в робочу таблицю;
3. одна транзакція - або все застосовано, або нічого.

У Laravel конструктор запитів не має методу для MERGE - використовують DB::statement() з параметрами.

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