LATERAL: последнее событие и топ-N для каждой строки

Бран · продюсер MMORPG «Хроники Пограничья»

Поддержка просит карточку игрока: что он купил последним и как играл в последние разы. Сейчас они открывают пять вкладок. И письмо-возврат хочу всем, даже тем, кто ни разу не платил. Им предложим Набор новичка.

В прошлом уроке мы доставали свойства событий из JSON. Сегодня вопрос другой формы: «для каждого игрока его последняя покупка» и «для каждого игрока три последние сессии». Ты уже умеешь решать такое окном из урока про топ-N в группе. Теперь второй способ: иногда он короче, а с индексом ещё и быстрее.

Библиотекарь у каждой карточки

Представь картотеку читателей. Обычный JOIN работает так: берём всю картотеку, берём весь журнал выдачи, сводим по номеру читателя. А можно иначе: библиотекарь берёт карточку, идёт к полке этого читателя, снимает самую свежую запись и возвращается к следующей карточке. Для каждой карточки свой маленький поход.

Второй способ в SQL называется LATERAL. Это подзапрос во FROM, который видит текущую строку таблицы слева от себя.

Последняя покупка каждого игрока

Деньги берём только из чистых покупок, как в проекте про экономику Пограничья: оплачено, первая строка чека, не тестировщик.

WITH clean AS (
  SELECT *
  FROM purchases
  WHERE status = 'paid'
    AND id IN (SELECT min(id) FROM purchases GROUP BY receipt_id)
    AND player_id NOT IN (SELECT id FROM players WHERE email LIKE '%@studio.dev')
)
SELECT p.id, p.nickname, o.title, last_purchase.amount_rub, last_purchase.created_at
FROM players p
CROSS JOIN LATERAL (
  SELECT c.offer_id, c.amount_rub, c.created_at
  FROM clean c
  WHERE c.player_id = p.id          -- p.id из строки слева
  ORDER BY c.created_at DESC, c.id DESC
  LIMIT 1
) last_purchase
JOIN store_offers o ON o.id = last_purchase.offer_id
ORDER BY last_purchase.created_at DESC
LIMIT 5;

  id  |  nickname   |            title             | amount_rub |       created_at
------+-------------+------------------------------+------------+------------------------
 4027 | Zuelka1     | Ускоритель опыта x2 (7 дней) |     199.00 | 2026-06-30 20:27:31+00
 3753 | GhostSun859 | Премиум на месяц             |     349.00 | 2026-06-30 20:11:48+00
 4015 | FrostRaven  | Мешок гемов                  |     499.00 | 2026-06-30 17:47:07+00
 4059 | StormStorm  | Набор новичка                |     149.00 | 2026-06-30 11:58:05+00
 4018 | DragonMoon  | Боевой пропуск: Пепел        |     699.00 | 2026-06-30 05:41:32+00

Читается почти как вопрос Брана: «для каждого игрока возьми его покупки, от новой к старой, первую». Без слова LATERAL запрос упадёт: обычный подзапрос во FROM считается до соединения и про p.id ничего не знает.

Без последнего LIMIT 5 в ответе 491 строка, по одной на платящего. Чаще всего последней покупкой оказывается Набор новичка за 149 ₽: так у 135 игроков, и 126 из них больше ничего не покупали. Попробовали магазин и не вернулись.

Три способа ответить на один вопрос

Тот же вопрос мы уже умеем решать двумя способами.

Коррелированный подзапрос из урока 29 в SELECT тоже видит строку снаружи. Но он обязан вернуть одно значение. Нужны оффер, сумма и дата, значит, три подзапроса, и каждый заново ищет ту же покупку.

Окно из урока 42 нумерует все покупки внутри игрока, а снаружи оставляет номер 1:

SELECT player_id, offer_id, amount_rub, created_at
FROM (
  SELECT c.*,
         row_number() OVER (PARTITION BY player_id ORDER BY created_at DESC, id DESC) AS rn
  FROM clean c
) numbered
WHERE rn = 1;

Ответ тот же, 491 строка. Окно сначала обрабатывает всю таблицу покупок целиком, а потом отбрасывает лишнее. LATERAL идёт от игроков и для каждого берёт только нужное. Какой путь быстрее, зависит от индексов, к этому вернёмся ниже.

Не терять тех, у кого пары нет

Бран хочет письмо и тем, кто не платил. А CROSS JOIN LATERAL ведёт себя как обычный JOIN: если подзапрос для игрока вернул ноль строк, игрок пропадает. Из 4077 игроков без тестовых аккаунтов осталось 491.

Чтобы сохранить всех, пишут LEFT JOIN LATERAL:

SELECT p.id, p.nickname, last_purchase.amount_rub
FROM players p
LEFT JOIN LATERAL (
  SELECT c.amount_rub
  FROM clean c
  WHERE c.player_id = p.id
  ORDER BY c.created_at DESC, c.id DESC
  LIMIT 1
) last_purchase ON true
WHERE p.email NOT LIKE '%@studio.dev';

ON true выглядит странно, но так и задумано. Условие связи уже стоит внутри подзапроса, в WHERE c.player_id = p.id, а LEFT JOIN по синтаксису обязан иметь ON. Пишем «условие всегда выполнено», и всё решает подзапрос. Теперь в ответе 4077 строк, у 3586 неплатящих сумма пустая, как у клиентов без заказов в уроке про LEFT JOIN.