LATERAL JOIN в PostgreSQL: последняя запись и top-N на пользователя

Коротко

LATERAL разрешает подзапросу в FROM ссылаться на таблицы, стоящие левее. Логически подзапрос выполняется для каждой строки слева, и внутри него доступны её значения. Отсюда главное применение — «для каждой строки взять первые N строк из другой таблицы по её правилу».

• Последний платёж со всеми полями: LEFT JOIN LATERAL (… WHERE p.user_id = u.user_id ORDER BY paid_at DESC LIMIT 1) ON true. Строк столько же, сколько пользователей: 4 613, платёж есть у 912.
• CROSS JOIN LATERAL выбрасывает тех, для кого подзапрос пуст: остаётся 912 строк. LEFT JOIN LATERAL … ON true их сохраняет.
• Подзапрос с агрегатом без GROUP BY возвращает одну строку всегда, даже на пустом наборе. Поэтому CROSS JOIN LATERAL (SELECT min(…)) никого не отбрасывает: 4 613 строк, из них 912 с датой.
• Три последних события на человека — 13 516 строк, а не 4 613 × 3: у 250 пользователей событий меньше трёх.
• Без индекса по (user_id, paid_at DESC) PostgreSQL для каждой строки слева читает и сортирует всю таблицу платежей. С индексом — одну строку.
• Если нужна только дата — хватит GROUP BY и max. Если одна строка на пользователя — DISTINCT ON в PostgreSQL или arg_max в DuckDB. LATERAL выигрывает, когда нужны N строк с полями или условие зависит от текущей строки.

Что делает LATERAL

Обычный подзапрос в FROM вычисляется сам по себе: он не видит таблиц, стоящих рядом. Написать внутри него WHERE p.user_id = u.user_id нельзя — база ответит, что не знает u. Ключевое слово LATERAL снимает это ограничение: подзапрос может ссылаться на колонки таблиц, перечисленных в FROM раньше него.

Думать о нём удобно как о цикле. Для каждой строки users выполняется подзапрос со значениями этой строки, и его результат присоединяется к ней. Если подзапрос вернул три строки, пользователь повторится трижды. Если ноль — всё зависит от типа соединения, об этом ниже. Планировщик не обязан выполнять запрос буквально циклом, но результат будет таким, как если бы выполнял.

LATERAL есть в PostgreSQL, DuckDB, MySQL 8.0.14+ и Oracle 12c+. В SQL Server то же самое записывается как CROSS APPLY и OUTER APPLY.

Последний платёж пользователя со всеми полями

Подзапрос берёт платежи текущего пользователя, сортирует их по дате от свежих и оставляет одну строку. ON true — обязательная часть синтаксиса LEFT JOIN: условие соединения уже стоит внутри подзапроса, снаружи его повторять нечего.

В сортировке после даты стоит payment_id. В учебной базе у одного пользователя не бывает двух платежей в один день, но запрос не должен на это рассчитывать. Без второго ключа при совпадении дат LIMIT 1 выберет строку случайно, и карточка пользователя будет меняться от запуска к запуску.

Блок выводит первые десять карточек, платёж есть у пользователей 3, 6 и 8. Без LIMIT результат — 4 613 строк, по одной на пользователя. У 912 есть последний платёж, у 3 701 поля платежа пустые. Последний платёж чаще всего — первый платёж на basic (349 человек), продлений basic среди последних платежей 165.

SELECT u.user_id, u.channel,
       lp.paid_at, lp.plan, lp.amount, lp.payment_type
FROM users u
LEFT JOIN LATERAL (
  SELECT p.paid_at, p.plan, p.amount, p.payment_type
  FROM payments p
  WHERE p.user_id = u.user_id
  ORDER BY p.paid_at DESC, p.payment_id DESC
  LIMIT 1
) AS lp ON true
ORDER BY u.user_id
LIMIT 10;

-- без LIMIT — 4 613 строк, платёж есть у 912

LEFT или CROSS: где теряются строки

CROSS JOIN LATERAL соединяет строку слева с каждой строкой подзапроса. Если подзапрос пуст, соединять не с чем, и строка слева исчезает. Для последнего платежа это значит, что в результате останутся только 912 плательщиков. LEFT JOIN LATERAL … ON true сохраняет всех: у кого платежей нет, получает пустые поля.

Какой из вариантов правильный, решает вопрос, а не синтаксис. «Карточки всех пользователей» — LEFT: пропавшие неплательщики выглядели бы как пропавшие пользователи. «Выручка по тарифу последнего платежа» — CROSS: неплательщикам в этом отчёте места нет. Ошибка здесь тихая: запрос с CROSS выполняется, и число строк просто меньше.

Подзапрос с агрегатом никогда не бывает пустым

Есть случай, где CROSS строки не выбрасывает, хотя от него этого ждут. Подзапрос SELECT min(paid_at) FROM payments p WHERE p.user_id = u.user_id без GROUP BY возвращает ровно одну строку всегда. Для пользователя без платежей это строка с NULL, а не пустой результат: агрегат по пустому набору всё равно возвращает значение.

Поэтому CROSS JOIN LATERAL (SELECT min(paid_at) AS first_paid …) вернёт 4 613 строк, и у 3 701 из них first_paid будет пустым. Автор, рассчитывавший, что CROSS оставит только плательщиков, получит в отчёте всех пользователей. Если такой подзапрос стоит в цепочке, дальше, скорее всего, будет сравнение с NULL, которое тихо отбросит строки уже в другом месте.

Лечится явным фильтром, WHERE first_paid IS NOT NULL снаружи или JOIN LATERAL (…) ON first_paid IS NOT NULL, либо заменой агрегата на ORDER BY … LIMIT 1, у которого пустой результат действительно пуст.

SELECT count(*) AS rows, count(fp.first_paid) AS with_date
FROM users u
CROSS JOIN LATERAL (
  SELECT min(p.paid_at) AS first_paid
  FROM payments p
  WHERE p.user_id = u.user_id
) AS fp;

-- rows = 4 613, with_date = 912

Top-N на сущность: три последних события

Та же конструкция с LIMIT 3 даёт три последних события каждого пользователя: для ленты активности в карточке, для поиска «что человек делал перед уходом». Блок берёт пятерых и возвращает 14 строк, а не 15: у пользователя 1168 событий два. По всей базе результат — 13 516 строк. Не 4 613 × 3 = 13 839: у 250 пользователей событий меньше трёх, и подзапрос вернул им одну или две строки.

Оконная функция даёт тот же результат: row_number() OVER (PARTITION BY user_id ORDER BY event_time DESC, event_id DESC) <= 3 — тоже 13 516 строк. Разница в том, как они приходят к ответу. Окно нумерует все 35 341 событие и выбрасывает лишние. LATERAL с подходящим индексом читает по три строки на пользователя. Когда пользователей мало, а событий много, это важно. Когда наоборот — окно дешевле: один проход вместо тысяч маленьких запросов.

Как и с рейтингами, у топа на сущность нет гарантированной длины. Три строки — это максимум, а не обещание. Отчёт, который ждёт ровно три строки на пользователя, должен это проверить или дорисовать пустые места явно.

SELECT u.user_id, le.event_name, le.event_time
FROM users u
CROSS JOIN LATERAL (
  SELECT e.event_id, e.event_name, e.event_time
  FROM events e
  WHERE e.user_id = u.user_id
  ORDER BY e.event_time DESC, e.event_id DESC
  LIMIT 3
) AS le
WHERE u.user_id BETWEEN 1166 AND 1170
ORDER BY u.user_id, le.event_time DESC, le.event_id DESC;

-- 14 строк, без WHERE — 13 516: у 250 пользователей событий меньше трёх

Когда LATERAL не нужен

LATERAL — инструмент для строк с полями и для условий, зависящих от текущей строки. Для более простых задач есть более простые записи, и их стоит предпочесть: их легче читать, и оптимизатор знает их лучше.

Нужна только дата последнего платежа — SELECT user_id, max(paid_at) FROM payments GROUP BY user_id. Нужна одна строка на пользователя со всеми полями — в PostgreSQL и DuckDB есть DISTINCT ON (user_id) … ORDER BY user_id, paid_at DESC: 912 строк, по одной на плательщика. В DuckDB есть агрегат arg_max(plan, paid_at) — «тариф в строке с максимальной датой», в ClickHouse он называется argMax: у 514 плательщиков последний тариф basic, столько же, сколько даёт LATERAL. Нужны N строк и запрос должен работать в любой базе — оконная функция с row_number(). Что эти записи возвращают при ничьей и NULL — в разборе строки с максимумом в группе.

Индекс решает, будет ли это быстро

Логически LATERAL выполняет подзапрос для каждой строки слева. Будет ли каждое такое выполнение дешёвым, решает индекс. Без него PostgreSQL для каждого пользователя сканирует всю таблицу платежей, фильтрует строки этого пользователя и сортирует их, чтобы взять одну. С индексом по (user_id, paid_at DESC) он спускается по индексу к нужному пользователю и читает первую запись.

Разница видна в плане. На синтетических таблицах в 20 000 пользователей и 300 000 платежей PostgreSQL 16 без индекса строит Nested Loop с Seq Scan on p и Sort внутри цикла. После CREATE INDEX ON p (user_id, paid_at DESC) внутри цикла остаётся Index Scan с Limit. На ста пользователях это разница между чтением тридцати миллионов строк и чтением ста. Песочница принимает только SELECT, поэтому блок ниже — для своего PostgreSQL: индекс строится на временной копии платежей, транзакция откатывается, а план меняется так же.

Индекс должен совпадать с подзапросом: сначала колонка из условия связи (user_id), потом колонка сортировки в том же направлении (paid_at DESC). Индекс только по user_id поможет найти строки, но сортировать их всё равно придётся.

BEGIN;
-- временная копия: учебные таблицы не меняются
CREATE TEMP TABLE pay_copy ON COMMIT DROP AS SELECT * FROM payments;

-- без индекса EXPLAIN ниже показывает для каждой строки u:
--   Limit -> Sort (paid_at DESC) -> Seq Scan on pay_copy p  Filter: (user_id = u.user_id)
CREATE INDEX pay_copy_user_paid_idx ON pay_copy (user_id, paid_at DESC);
ANALYZE pay_copy;

EXPLAIN (COSTS OFF)
SELECT u.user_id, lp.paid_at, lp.plan
FROM users u
LEFT JOIN LATERAL (
  SELECT p.paid_at, p.plan
  FROM pay_copy p
  WHERE p.user_id = u.user_id
  ORDER BY p.paid_at DESC
  LIMIT 1
) AS lp ON true;
-- с индексом: для каждой строки u
--   Limit -> Index Scan using pay_copy_user_paid_idx on pay_copy p
--            Index Cond: (user_id = u.user_id)
ROLLBACK;

Скрытый LATERAL: функции в FROM

Функция, возвращающая набор строк, в FROM может ссылаться на колонки таблиц левее, даже если слово LATERAL не написано. Так её трактуют PostgreSQL и DuckDB. Самый полезный пример — календарь для каждого пользователя: generate_series(u.signup_date, u.signup_date + 6, INTERVAL '1 day') разворачивает каждого в семь строк, с дня регистрации по шестой день после неё. 4 613 пользователей дают 32 291 строку.

Такая сетка — основа для retention по дням. К каждой паре «пользователь + день жизни» присоединяется факт активности, и доля активных считается одинаково для всех дней, включая те, в которые никто не пришёл. Без сетки дни без активности просто исчезли бы. Для зрелых когорт, с регистрацией по 22 августа, в день регистрации приложение открывают 100%, на следующий день — 53,5%, на шестой — 21,3%.

WITH grid AS (
  SELECT u.user_id,
         CAST(d.day AS date) AS day,
         CAST(d.day AS date) - u.signup_date AS day_n
  FROM users u
  CROSS JOIN LATERAL generate_series(
    u.signup_date, u.signup_date + 6, INTERVAL '1 day') AS d(day)
  WHERE u.signup_date <= DATE '2026-08-22'
),
opens AS (
  SELECT DISTINCT user_id, event_time::DATE AS day
  FROM events
  WHERE event_name = 'app_open'
)
SELECT g.day_n,
       round(100.0 * count(o.user_id) / count(*), 1) AS active_pct
FROM grid g
LEFT JOIN opens o ON o.user_id = g.user_id AND o.day = g.day
GROUP BY g.day_n
ORDER BY g.day_n;

Цепочка LATERAL: условие из предыдущего шага

Подзапросы LATERAL можно ставить друг за другом, и каждый следующий видит результат предыдущего. Это удобно, когда условие второго шага зависит от первого. Вопрос: какое последнее содержательное действие, кроме открытия приложения, человек сделал до первой оплаты?

Первый LATERAL находит дату первого платежа. Второй берёт последнее событие этого пользователя не позже этой даты. Среди 912 плательщиков чаще всего перед оплатой был экспорт (206 человек), затем отчёт (187) и приглашение коллеги (186), реже — только создание рабочего пространства (115). Ещё 218 человек до первой оплаты не сделали ничего, кроме открытий. CROSS JOIN их отбросил, и это стоит показать в отчёте отдельной строкой, а не потерять.

SELECT le.event_name, count(*) AS payers
FROM users u
JOIN LATERAL (
  SELECT min(p.paid_at) AS first_paid
  FROM payments p
  WHERE p.user_id = u.user_id
) AS fp ON fp.first_paid IS NOT NULL
CROSS JOIN LATERAL (
  SELECT e.event_name
  FROM events e
  WHERE e.user_id = u.user_id
    AND e.event_name <> 'app_open'
    AND e.event_time::DATE <= fp.first_paid
  ORDER BY e.event_time DESC
  LIMIT 1
) AS le
GROUP BY le.event_name
ORDER BY payers DESC;

-- export_completed 206, report_created 187, invite_sent 186, workspace_created 115

Проверка: сколько строк вернулось и почему

У каждого запроса с LATERAL есть ожидаемое число строк, и его стоит назвать до запуска. LEFT JOIN LATERAL … LIMIT 1 — ровно столько, сколько строк слева. CROSS JOIN LATERAL … LIMIT 1 — столько, у скольких подзапрос не пуст. LIMIT N — не больше N на строку слева. Подзапрос с агрегатом без GROUP BY — ровно столько, сколько слева, при любом типе соединения.

Если число не сходится с ожиданием, ошибка найдена. Больше строк, чем слева, при LIMIT 1 — значит, подзапрос связан не с той строкой или LIMIT потерялся. Меньше при LEFT — значит, внешний WHERE отбросил строки с пустыми полями подзапроса. Это та же ловушка, что с обычным LEFT JOIN и условием в WHERE.

Частые ошибки с LATERAL

Ошибки с LATERAL почти всегда видны по числу строк. Поэтому проверка из предыдущего раздела — лучшая защита от всех пунктов ниже.

• CROSS там, где нужен LEFT: пользователи без платежей пропадают из карточек.
• Агрегат без GROUP BY в подзапросе и ожидание, что CROSS отбросит пустых: строк столько же, сколько слева.
• ORDER BY без второго ключа перед LIMIT 1: при совпадении дат «последняя» запись выбирается случайно.
• Ожидание ровно N строк от LIMIT N: у кого событий меньше, получит меньше.
• Нет индекса по «ключ связи + колонка сортировки»: полное сканирование подчинённой таблицы на каждой строке.
• Фильтр по полям подзапроса во внешнем WHERE при LEFT JOIN LATERAL: строки с пустыми полями отбрасываются, и LEFT становится CROSS.
• LATERAL там, где хватает max или DISTINCT ON: запрос сложнее, чем задача.

Практика на учебной базе

Три задачи. База та же, что в песочнице симулятора SQL-аналитика, поэтому ответы должны совпасть.

Первая. Для каждого пользователя выведите тариф последнего платежа, сохранив неплательщиков. Сколько строк получилось и у скольких тариф заполнен? Ответ: 4 613 и 912. Замените LEFT на CROSS и объясните новое число строк.

Вторая. Выведите три последних события каждого пользователя и посчитайте строки. Ответ: 13 516. Получите то же число через row_number() и объясните, почему оно меньше 13 839.

Третья. Постройте сетку «пользователь × дни 0–6» через generate_series для зрелых когорт, с регистрацией по 22 августа, и посчитайте долю открывших приложение на следующий день после регистрации. Ответ: 53,5% (2 199 из 4 109).

Итог

LATERAL — это «для каждой строки слева выполнить маленький запрос справа». Он нужен, когда от другой таблицы требуется не значение, а несколько строк с полями, выбранных по правилу, зависящему от текущей строки. Прежде чем его писать, решите три вещи: LEFT или CROSS, какой второй ключ разрешает ничью перед LIMIT и есть ли индекс, который сделает каждое выполнение дешёвым. После запуска сверьте число строк с ожидаемым.

Подзапросы и оконные функции, на которых стоит эта тема, разобраны в одиннадцатой и двенадцатой главах симулятора SQL-аналитика. Задания там проверяются автоматически на той же учебной базе. Первые главы курса — устройство базы, SELECT и WHERE — открыты бесплатно.