FULL JOIN и CROSS JOIN в SQL: сверка и сетка без пропусков

Короткий ответ: какой JOIN выбрать

FULL JOIN оставляет строки обоих наборов: совпавшие, только слева и только справа. В нашем недельном срезе получились 8 292 совпадения, 30 531 бронь без записи покупки и ноль событий покупки без брони. Это не доказательство потери 30 531 событий: журнал фиксирует покупки в приложении, а бронирования приходят и из других каналов.

CROSS JOIN создаёт все сочетания двух небольших справочников. Сетка 24 часа × 3 платформы даёт 72 клетки; после LEFT JOIN ошибок три клетки остаются с нулём. Если соединить только наблюдавшиеся ошибки, эти часы исчезнут и график не сможет отличить «ноль» от «нет строки».

Если задача — обычное присоединение атрибута к факту, сначала откройте разбор INNER и LEFT JOIN. Здесь речь о сверке двух независимых множеств и о создании ожидаемых комбинаций.

Сначала определите две стороны и ключ

Левая сторона — bookings: одна строка на book_ref. Правая — журнал app_events: событие purchase, у которого номер лежит в строковом JSON properties. На правой стороне возможна повторная доставка одного события с новым event_id, поэтому сначала берём разные номера броней. Без этого одна и та же бронь может выглядеть как несколько совпадений.

У обоих наборов граница одинаковая: с 1 июля включительно до 8 июля исключительно. Время в базе московское без зоны. Сравнение по полуоткрытому интервалу не пропускает секунды в конце 7 июля и не включает 8 июля. Не подменяйте границу условием <= '2026-07-07': это полночь в начале дня.

События начинаются 1 июля, а брони — 17 июня. Если взять июнь, их несходство задано устройством учебной базы. Даже в июле разница между каналами остаётся: журнал приложения не обещает покрыть все брони.

FULL JOIN: три зоны одного результата

Соединяем номер брони с номером из события. CASE определяет зону по отсутствию строки стороны, а не по произвольному полю вроде суммы: у реальной строки сумма тоже может быть NULL. В итоговом списке деталей выводите COALESCE(b.book_ref, e.book_ref) — иначе у записей только справа ключ окажется пустым, хотя именно его нужно расследовать.

После FULL JOIN группировка даёт три численности. В нашем срезе зона «только событие» равна нулю, но она нужна в схеме и в запросе: завтра загрузка может нарушить это свойство. Пустая зона — результат проверки, а не причина убрать её из постановки.

В ноутбуке те же три зоны даёт merge(how="outer", indicator=True). В pandas пустые ключи соединяются друг с другом — в отличие от SQL-равенства NULL. Поэтому фильтр пустых ключей нужен до сверки или отдельный отчёт о них: один NaN слева и один справа pandas назовёт both, а PostgreSQL вернёт две непарные строки.

WITH b AS (
  SELECT book_ref FROM bookings
  WHERE book_date >= TIMESTAMP '2026-07-01'
    AND book_date < TIMESTAMP '2026-07-08'
), e AS (
  SELECT DISTINCT properties::json ->> 'book_ref' AS book_ref
  FROM app_events
  WHERE event_name = 'purchase'
    AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-08'
), zones AS (
  SELECT CASE WHEN b.book_ref IS NULL THEN 'Только событие'
              WHEN e.book_ref IS NULL THEN 'Только бронь'
              ELSE 'Совпали' END AS zone
  FROM b FULL JOIN e ON b.book_ref = e.book_ref
), labels(zone) AS (
  VALUES ('Совпали'), ('Только бронь'), ('Только событие')
)
SELECT l.zone, count(z.zone) AS rows_count
FROM labels l LEFT JOIN zones z ON z.zone = l.zone
GROUP BY l.zone ORDER BY l.zone;

import json
import pandas as pd

b = pd.read_parquet('bookings.parquet', columns=['book_ref', 'book_date'])
e = pd.read_parquet('app_events.parquet', columns=['event_time', 'event_name', 'properties'])
b['book_date'] = pd.to_datetime(b['book_date'])
e['event_time'] = pd.to_datetime(e['event_time'])
start, end = pd.Timestamp('2026-07-01'), pd.Timestamp('2026-07-08')
b = b.loc[b.book_date.ge(start) & b.book_date.lt(end), ['book_ref']]
e = e.loc[e.event_name.eq('purchase') & e.event_time.ge(start) & e.event_time.lt(end), ['properties']].copy()
e['book_ref'] = e.properties.map(lambda raw: json.loads(raw).get('book_ref'))
zones = b.merge(e[['book_ref']].drop_duplicates(), on='book_ref', how='outer', indicator=True)
print(zones['_merge'].value_counts().to_dict())
left = pd.DataFrame({'book_ref': [None]})
right = pd.DataFrame({'book_ref': [None]})
print(left.merge(right, on='book_ref', how='outer', indicator=True)['_merge'].value_counts().to_dict())

Почему зоны не равны диагнозам

Результат 30 531 отвечает только на вопрос «у какой брони нет события покупки в этом журнале и в этом недельном окне». Он не говорит, сколько событий потерял трекер: бронь может быть оформлена вне приложения, а событие пройти около границы периода. Для диагноза нужно отдельно сверить каналы продаж, время создания брони и время события, задержки доставки и долю записей с отсутствующим ключом.

Проверка пригодности ключа идёт до JOIN: count(*), count(DISTINCT book_ref), count(*) FILTER (WHERE book_ref IS NULL) на каждой стороне. Если ключ повторяется, сверяют предварительно агрегированные наборы на нужном зерне или явно описывают отношение один-ко-многим. Подробный пример с денежной метрикой — в материале о кардинальности JOIN.

Ловушка 1: фильтр справа в WHERE удаляет левую зону

Неправильно: после полного соединения добавить WHERE e.book_ref IS NOT NULL, потому что «нам нужны покупки». Запрос выполнится и оставит только 8 292 совпадения вместо 38 823 строк с бронью. Все 30 531 бронь без покупки исчезнут из анализа. Если условие ограничивает правый источник, ставьте его в CTE e до соединения; если анализируете конкретную зону, назовите её явно.

То же происходит с WHERE e.event_time >= … после JOIN: у строки без правой пары e.event_time равен NULL, а условие не истинно. Для сверки это наиболее опасная тихая ошибка: таблица выглядит аккуратно, но проблема отсутствующих записей удалена самим запросом.

WITH b AS (
  SELECT book_ref FROM bookings
  WHERE book_date >= TIMESTAMP '2026-07-01' AND book_date < TIMESTAMP '2026-07-08'
), e AS (
  SELECT DISTINCT properties::json ->> 'book_ref' AS book_ref
  FROM app_events
  WHERE event_name = 'purchase'
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-08'
)
SELECT count(*) AS misleading_rows
FROM b FULL JOIN e ON b.book_ref = e.book_ref
WHERE e.book_ref IS NOT NULL;

Ловушка 2: NULL в ключе не находит пару

Обычное равенство NULL = NULL не даёт TRUE. На синтетическом примере с одной пустой строкой слева и одной справа FULL JOIN ON a.key = b.key вернёт две строки, а не одну совпавшую. Если пустой ключ означает «неизвестно», именно такое поведение честно: две неизвестные сущности нельзя считать одной.

Неправильно заранее заменить оба пустых ключа на общий текст вроде unknown: тогда две разные записи сольются в одну. Исправление — вынести пустые ключи в отдельный отчёт качества; только при доказанной бизнес-семантике применять IS NOT DISTINCT FROM и сразу объяснить, почему два NULL представляют одну сущность. В нашем недельном срезе book_ref у обеих сторон заполнен, но запрос нельзя копировать на другие события без этой проверки.

Ловушка 3: дубли справа раздувают совпадение

Неправильно соединить bookings с сырыми purchase и считать count(*) числом броней. За неделю в журнале 8 345 строк, но лишь 8 292 разных номера. Без DISTINCT получится 38 876 строк после соединения — на 53 больше, чем броней. Дубли доставки живут под разными event_id, поэтому DISTINCT event_id не поможет.

Исправление зависит от цели. Для существования покупки берите разные book_ref до JOIN. Для проверки числа доставок храните все события отдельно и считайте их по номеру. Для суммы брони предварительно проверьте, что на каждую бронь пришёл один факт оплаты, иначе деньги умножатся вместе со строками. Одна универсальная кнопка DISTINCT в финальном SELECT не исправляет ошибочное зерно промежуточных расчётов.

Ловушка 4: FULL JOIN по неравенству в PostgreSQL

Соблазнительно написать FULL JOIN ... ON a.created_at < b.created_at, чтобы сохранить все строки и одновременно подобрать более поздние события. PostgreSQL 14 отвечает: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions. На двух синтетических наборах (1),(2) и (1),(3) ошибка воспроизводится без авиаданных.

Исправление — сначала соединить по ключу равенства, если он есть, и ограничить время в отдельном шаге; для поиска ближайшей записи на каждую строку использовать LEFT JOIN LATERAL с упорядочением и лимитом. Не заменяйте FULL на INNER ради синтаксиса: так исчезнут обе зоны без пары. Разбор LATERAL показывает, как выбрать одну подходящую строку.

CROSS JOIN: как построить обязательные клетки

Для мониторинга ошибок за 1 июля нужны все сочетания 24 часов и трёх платформ: android, ios, web. Таблица событий даёт только наблюдавшиеся ошибки. Сначала делаем справочник часов и список платформ, затем их CROSS JOIN, а уже после присоединяем агрегат ошибок. COALESCE(n, 0) переводит отсутствие факта в ноль только в пределах ожидаемой сетки.

Получается 72 клетки, из них 3 без ошибки. Это нули за наблюдаемый период, а не подтверждение, что сбор событий исправен: если упал весь трекер, тоже может не быть ошибок в журнале. Отличить это можно контрольным событием вроде app_open и мониторингом объёма потока.

WITH hours AS (
  SELECT x AS hour FROM generate_series(0, 23) AS g(x)
), platforms AS (
  SELECT DISTINCT platform FROM app_events
), errors AS (
  SELECT extract(hour FROM event_time) AS hour, platform, count(*) AS n
  FROM app_events
  WHERE event_name = 'error'
    AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-02'
  GROUP BY 1, 2
), grid AS (
  SELECT h.hour, p.platform, coalesce(e.n, 0) AS errors
  FROM hours h CROSS JOIN platforms p
  LEFT JOIN errors e ON e.hour = h.hour AND e.platform = p.platform
)
SELECT count(*) AS cells,
       count(*) FILTER (WHERE errors = 0) AS zero_cells,
       max(errors) AS busiest_cell
FROM grid;

import pandas as pd

e = pd.read_parquet('app_events.parquet', columns=['event_time', 'event_name', 'platform'])
e['event_time'] = pd.to_datetime(e['event_time'])
hours = pd.DataFrame({'hour': range(24)})
platforms = pd.DataFrame({'platform': sorted(e.platform.dropna().unique())})
grid = hours.merge(platforms, how='cross')
start, end = pd.Timestamp('2026-07-01'), pd.Timestamp('2026-07-02')
errors = e.loc[e.event_name.eq('error') & e.event_time.ge(start) & e.event_time.lt(end), ['event_time', 'platform']].copy()
errors['hour'] = errors.event_time.dt.hour
facts = errors.groupby(['hour', 'platform']).size().rename('n').reset_index()
grid = grid.merge(facts, on=['hour', 'platform'], how='left')
grid['n'] = grid['n'].fillna(0).astype(int)
print(len(grid), int(grid.n.eq(0).sum()), int(grid.n.max()))

Ловушка 5: CROSS JOIN взрывает число строк

Неправильно соединять каждый час с каждой строкой app_events, а потом надеяться, что GROUP BY соберёт ответ. Уже один день × весь журнал — это 24 × 1 494 723 = 35 873 352 строки до фильтра и агрегации. Здесь необходимы три платформы, а не полтора миллиона фактов: 24 × 3 = 72 строки.

Оцените мощность до запуска: перемножьте count(*) входов и выпишите зерно каждой стороны. Если справа факты, сначала агрегируйте до часа и платформы и присоединяйте агрегат через LEFT JOIN. Для сетки дат и категорий можно сузить обе оси до периода и релевантных категорий. Иначе переполнение начинается раньше, чем появится красивый результат.

Ловушка 6: WHERE после LEFT JOIN съедает нули

Неправильно поставить WHERE e.n > 0 после присоединения агрегата ошибок. Из 72 клеток останутся 69; три часа и платформы с нулём не появятся на графике. Исправление — фильтр event_name = 'error' в CTE errors, а LEFT JOIN с COALESCE в последнем шаге. Если вам действительно нужны только положительные клетки, не называйте такую выборку полной сеткой.

Похожая ошибка — группировать по дате из правой таблицы e, а не из календаря. Для пропущенного дня справа NULL, и серия распадётся. Ось результата берётся из сгенерированного справочника. В статье о календарных интервалах SQL показаны границы дат; отдельный разбор ряда дат появится в следующей волне.

FULL JOIN или UNION ALL с последующей группировкой

Практический критерий выбора — форма финальной таблицы. Если расследователь откроет список из двух колонок «сумма в выгрузке A» и «сумма в выгрузке B», FULL JOIN даёт эту форму прямо. Если он хочет один реестр ключей и флаги наличия в трёх или четырёх источниках, UNION ALL с меткой источника легче расширить. В обоих случаях предварительная нормализация ключа важнее названия операции.

FULL JOIN удобен, когда нужно посмотреть поля слева и справа рядом: суммы, времена, статус и почему конкретный ключ разошёлся. UNION ALL с меткой источника и группировкой по ключу удобен, когда источников больше двух или достаточно присутствия и счётчиков. В обоих вариантах до сравнения нужно привести ключ к одному типу и определить зерно.

Если в одной стороне несколько строк на ключ, UNION ALL не спасает сам по себе: он сохранит дубли честно. Это полезно для диагностики, но не годится для фразы «число уникальных броней» без следующего слоя агрегации. Не смешивайте численность строк, ключей и событий. Для контроля качества данных см. список инвариантов и сверок.

В других СУБД

В PostgreSQL 14 работают FULL OUTER JOIN, CROSS JOIN, generate_series и LATERAL. DuckDB выполняет сам запрос зон и сетку часов; функции для JSON-массива здесь не нужны. MySQL поддерживает CROSS JOIN, но не имеет нативного FULL OUTER JOIN: нужна явная комбинация двух односторонних соединений с удалением повторов по проверенному ключу. Синтаксис для других движков не стоит переносить механически.

В этой статье все запросы с авиаданными выполнены в PostgreSQL; учебная база курса использует DuckDB. Для properties применяется properties::json ->> 'book_ref'. В сравнении извлечённого JSON с колонкой в DuckDB заключайте извлечение в скобки из-за приоритета ->>.

Как перейти от зоны к конкретной причине

Таблица зон — начало расследования, а не его конец. Из 30 531 броней без события выберите несколько book_ref и проверьте канал оформления, время брони, границы выгрузки и наличие любого другого события этого клиента. Если журнал видит поиск и оформление, но не видит purchase, это одна гипотеза. Если клиент вообще не использовал приложение, отсутствие purchase в приложении ожидаемо. Если событие пришло 8 июля, а бронь датирована 7-м, причина может быть в границе окна или задержке доставки.

Полезно строить разрез зоны по дню брони и каналу, но не по полю, которого у отсутствующей стороны нет. GROUP BY e.platform отправит все несопоставленные брони в одну пустую категорию и скроет различия каналов. Поле атрибута берут из той стороны, где оно существует, а отсутствующие значения подписывают как «нет события». Следите и за знаменателем доли: 30 531 / 38 823 — доля броней без события, а не доля событий без брони.

После сверки зафиксируйте контрольный инвариант: число броней должно равняться «совпали + только бронь», то есть 8 292 + 30 531 = 38 823. Число разных событий покупки — «совпали + только событие», здесь 8 292 + 0 = 8 292. Если после правки фильтра инвариант перестал сходиться, ошибка находится в слоях подготовки ключа, границы периода или кардинальности JOIN. Такая простая арифметика лучше обнаруживает потерю строк, чем просмотр большого списка деталей.

Как проектировать сетку, когда ось неполна

В примере список платформ получен через SELECT DISTINCT platform FROM app_events за весь доступный журнал. Это уместно, если каталог платформ стабилен. Для длительного мониторинга лучше отдельный справочник допустимых платформ: если вся платформа перестанет присылать события, она исчезнет и из списка, построенного по журналу. Сетка тогда не покажет ноль — не будет самой оси. Аналогично календарь не должен строиться только по датам имеющихся ошибок.

Осторожно и с неподвижной сеткой на слишком длинный период. 24 часа × 3 платформы — 72 клетки за день; 365 дней × 24 часа × 3 платформы — 26 280 клеток ещё до присоединения разрезов ошибок. Если добавить 40 регионов, станет 1 051 200 клеток. Не всякая комбинация осмысленна: у отдельных платформ регион может быть запрещён или недоступен. Справочник применимых комбинаций уменьшает шум и не превращает структурный пропуск в ложный ноль.

При чтении графика подпишите период и задержку данных. Ноль в последнем, ещё не завершившемся часе не сравним с завершёнными часами. Учебный срез заканчивается 11 августа в 18:00; окно после этого времени нельзя автоматически заполнить нулями и назвать падением ошибок. Сетка отвечает за форму результата, а не за зрелость данных и качество трекера.

Частые вопросы

Чем FULL JOIN отличается от LEFT JOIN? LEFT сохраняет всю левую сторону и её совпадения; FULL дополнительно сохраняет строки, которые есть только справа. Для сверки двух выгрузок именно правая зона часто сообщает о пропаже в первом источнике.

Почему после FULL JOIN стало больше строк, чем в обоих наборах? Вероятнее всего, ключ не уникален на одной или обеих сторонах. Если по одному ключу слева две строки, а справа три, совпавшая зона получит шесть строк. Проверьте зерно до расчёта сумм.

Когда нужен CROSS JOIN? Когда вы сознательно строите все ожидаемые сочетания маленьких осей: час × платформа, дата × категория, тариф × канал. Сначала посчитайте произведение размеров; затем присоедините факты.

Почему ноль не равен отсутствующей строке? Ноль — измеренный результат для конкретной ожидаемой клетки. Отсутствие строки может означать, что ось не построена или измерение не пришло. Сетка делает этот выбор явным.