LATERAL JOIN в PostgreSQL: продвинутые запросы с зависимыми подзапросами

LATERAL позволяет подзапросу ссылаться на столбцы из предыдущих элементов FROM. Это открывает мощные возможности, недоступные с обычными подзапросами.

Зачем нужен LATERAL

Обычный подзапрос не видит другие таблицы из FROM:

-- ❌ Ошибка: обычный подзапрос не может ссылаться на users.id
SELECT u.name, (
    SELECT amount FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) AS last_order
FROM users u;
-- Это скалярный подзапрос — работает, но по одному за раз

-- Но в FROM — нельзя без LATERAL:
SELECT u.name, o.amount
FROM users u,
     (SELECT amount FROM orders WHERE user_id = u.id LIMIT 1) o;  -- ❌ u.id недоступен

LATERAL решает это:

-- ✅ LATERAL: подзапрос видит u.id
SELECT u.name, o.amount
FROM users u,
     LATERAL (SELECT amount FROM orders WHERE user_id = u.id
              ORDER BY created_at DESC LIMIT 1) o;

Синтаксис

-- Неявный LATERAL (CROSS JOIN LATERAL)
SELECT ... FROM table1 t1, LATERAL (subquery using t1) sub;

-- Явный (для LEFT JOIN — сохранить строки без совпадений)
SELECT ... FROM table1 t1
LEFT JOIN LATERAL (subquery using t1) sub ON true;

TOP-N на группу (самый частый случай)

-- Последние 3 заказа для каждого пользователя
SELECT u.id, u.name, o.id AS order_id, o.amount, o.created_at
FROM users u
LEFT JOIN LATERAL (
    SELECT id, amount, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o ON true
ORDER BY u.id, o.created_at DESC;

Сравнение с альтернативами:

-- Альтернатива 1: ROW_NUMBER() (стандартный SQL)
SELECT id, name, order_id, amount, created_at
FROM (
    SELECT u.id, u.name, o.id AS order_id, o.amount, o.created_at,
           ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.created_at DESC) AS rn
    FROM users u LEFT JOIN orders o ON o.user_id = u.id
) t
WHERE rn <= 3;

-- LATERAL часто быстрее если есть индекс на (user_id, created_at DESC):
-- для каждого пользователя делает Index Scan с LIMIT
-- ROW_NUMBER() вынужден прочитать все строки

Расчёт на каждую строку

-- Для каждого продукта: сколько продано за последние 30 дней и 7 дней
SELECT
    p.id,
    p.name,
    stats30.quantity AS qty_30d,
    stats30.revenue AS rev_30d,
    stats7.quantity AS qty_7d,
    stats7.revenue AS rev_7d
FROM products p
LEFT JOIN LATERAL (
    SELECT SUM(oi.quantity) AS quantity, SUM(oi.quantity * oi.price) AS revenue
    FROM order_items oi JOIN orders o ON o.id = oi.order_id
    WHERE oi.product_id = p.id AND o.created_at >= NOW() - INTERVAL '30 days'
) stats30 ON true
LEFT JOIN LATERAL (
    SELECT SUM(oi.quantity) AS quantity, SUM(oi.quantity * oi.price) AS revenue
    FROM order_items oi JOIN orders o ON o.id = oi.order_id
    WHERE oi.product_id = p.id AND o.created_at >= NOW() - INTERVAL '7 days'
) stats7 ON true;

UNNEST с LATERAL

-- Развернуть массив тегов с порядковым номером
SELECT p.id, p.name, tag_info.tag, tag_info.position
FROM products p
CROSS JOIN LATERAL UNNEST(p.tags) WITH ORDINALITY AS tag_info(tag, position);

-- Без LATERAL (работает так же, UNNEST неявно LATERAL):
SELECT p.id, p.name, tag, position
FROM products p
CROSS JOIN UNNEST(p.tags) WITH ORDINALITY AS t(tag, position);

generate_series с LATERAL

-- Для каждого пользователя: выручка за каждый из последних 12 месяцев
SELECT
    u.id,
    u.name,
    m.month,
    COALESCE(SUM(o.amount), 0) AS revenue
FROM users u
CROSS JOIN LATERAL generate_series(
    DATE_TRUNC('month', NOW()) - INTERVAL '11 months',
    DATE_TRUNC('month', NOW()),
    INTERVAL '1 month'
) AS m(month)
LEFT JOIN orders o ON o.user_id = u.id
    AND DATE_TRUNC('month', o.created_at) = m.month
    AND o.status = 'completed'
WHERE u.is_active = true
GROUP BY u.id, u.name, m.month
ORDER BY u.id, m.month;

Ближайший сосед (для каждой строки)

-- Для каждого заказа: ближайший предыдущий заказ того же пользователя
SELECT
    o.id,
    o.user_id,
    o.created_at,
    o.amount,
    prev.id AS prev_order_id,
    prev.created_at AS prev_order_date,
    o.created_at - prev.created_at AS days_between
FROM orders o
LEFT JOIN LATERAL (
    SELECT id, created_at
    FROM orders
    WHERE user_id = o.user_id
      AND created_at < o.created_at
    ORDER BY created_at DESC
    LIMIT 1
) prev ON true
ORDER BY o.user_id, o.created_at;

Применение функций к каждой строке

-- Для каждого текста: первые 3 слова
SELECT
    id,
    content,
    words.first_three
FROM articles
CROSS JOIN LATERAL (
    SELECT array_to_string(
        (regexp_split_to_array(content, '\s+'))[1:3],
        ' '
    ) AS first_three
) words;

Когда LATERAL быстрее ROW_NUMBER()

-- Таблица: 10 миллионов заказов, 100 000 пользователей
-- Задача: последний заказ каждого пользователя

-- ROW_NUMBER() — читает ВСЕ 10М строк, потом фильтрует
-- Время: ~15 сек

-- LATERAL с индексом на (user_id, created_at DESC) — для каждого пользователя
-- делает Index Scan с LIMIT 1 → читает ~100K строк
-- Время: ~0.5 сек

-- Индекс для LATERAL оптимизации:
CREATE INDEX ON orders (user_id, created_at DESC);

LATERAL vs Коррелированный подзапрос

-- Коррелированный подзапрос: одно значение
SELECT u.name,
       (SELECT MAX(amount) FROM orders WHERE user_id = u.id) AS max_order
FROM users u;

-- LATERAL: несколько столбцов и строк
SELECT u.name, last.amount, last.created_at
FROM users u
LEFT JOIN LATERAL (
    SELECT amount, created_at
    FROM orders WHERE user_id = u.id
    ORDER BY created_at DESC LIMIT 1
) last ON true;

-- LATERAL эффективнее когда нужно несколько полей из подзапроса
-- (избегает N коррелированных подзапросов)

Итог: когда использовать LATERAL

LATERAL — мощный инструмент PostgreSQL. Используйте его когда подзапрос должен принимать значения из текущей строки и возвращать несколько строк или столбцов.