Self join в SQL: как соединить таблицу саму с собой

Коротко: self join — обычный JOIN с двумя алиасами

В SQL нет отдельного оператора SELF JOIN. Вы пишете FROM flights a JOIN flights b, а в ON определяете, какие две строки одной таблицы составляют пару. Алиасы здесь не косметика: a.departure_airport и b.departure_airport играют разные роли в условии.

Для неупорядоченной пары ставьте a.flight_id < b.flight_id: это сразу исключает строку с самой собой и обратную копию пары. Затем добавьте общую сущность, например аэропорт, и временное окно. На 548 вылетах одного дня получается 375 пар; без окна — 4 284. Детальный разбор кардинальности JOIN объясняет, почему контроль числа строк обязателен.

Рабочий вопрос: два отправления рядом во времени

Диспетчерскому отчёту нужны не «стыковки пассажиров», которые разбираются в уроке курса, а нагрузка на один аэропорт: какие два разных рейса отправляются в пределах получаса. Берём только вылеты 15 июля по запланированному времени. Календарный фильтр применяем к обоим участникам через общий CTE day_flights: так пары не выходят за границу дня.

Разность времени здесь абсолютная, потому что меньший flight_id не обязательно вылетает раньше. Пример из результата: рейс 21 отправляется из Домодедово в 09:35, а рейс 1009 — в 09:30. Упорядочивание пары по id даёт одну копию, но не временную последовательность. Для последовательности нужна отдельная проверка времени.

WITH day_flights AS (
  SELECT flight_id, flight_no, departure_airport, scheduled_departure
  FROM flights
  WHERE scheduled_departure >= TIMESTAMP '2026-07-15'
    AND scheduled_departure < TIMESTAMP '2026-07-16'
), pairs AS (
  SELECT a.flight_id AS first_id, b.flight_id AS second_id,
         a.departure_airport,
         a.scheduled_departure AS first_time,
         b.scheduled_departure AS second_time
  FROM day_flights a JOIN day_flights b
    ON a.departure_airport = b.departure_airport
   AND a.flight_id < b.flight_id
   AND abs(extract(epoch FROM a.scheduled_departure - b.scheduled_departure)) <= 1800
)
SELECT count(*) AS pairs, count(DISTINCT first_id) AS first_flights
FROM pairs;

Зерно результата: строка — пара, а не рейс

После соединения одна строка означает пару flight_id. Один рейс может входить в несколько пар, поэтому count(*) уже не равен числу рейсов. В нашем результате 375 пар, но разных first_id только 195. Если далее присоединить билет или сумму по рейсу, она повторится в каждой паре этого рейса. Сумму по рейсам считают в отдельном слое до self join или по заранее выбранной одной паре.

В таблице деталей сохраняйте оба идентификатора и обе временные отметки. Пара без идентификаторов не проверяема: два вылета с одинаковым временем и аэропортом будут выглядеть одинаково. Визуальная таблица ниже показывает реальный фрагмент результата, включая случай, где первый по id вылетает позже второго.

В pandas самосоединение через merge по аэропорту сначала создаёт 9 116 кандидатов; лишь потом фильтры оставляют 375 пар. Такой промежуточный объём так же опасен для памяти, как SQL-соединение без временного условия. Для одного ближайшего предшествующего рейса подходит merge_asof: это другой вопрос, и он возвращает 214 строк с найденной парой, не все 375 пар.

import pandas as pd

f = pd.read_parquet('flights.parquet', columns=['flight_id', 'scheduled_departure', 'departure_airport'])
f['scheduled_departure'] = pd.to_datetime(f['scheduled_departure'])
start, end = pd.Timestamp('2026-07-15'), pd.Timestamp('2026-07-16')
f = f.loc[f.scheduled_departure.ge(start) & f.scheduled_departure.lt(end)].copy()
candidates = f.merge(f, on='departure_airport', suffixes=('_a', '_b'))
gap = (candidates.scheduled_departure_a - candidates.scheduled_departure_b).abs()
pairs = candidates.loc[candidates.flight_id_a.lt(candidates.flight_id_b) & gap.le(pd.Timedelta(minutes=30))]
print(len(f), len(candidates), len(pairs))

ordered = f.sort_values('scheduled_departure')
prior = ordered.rename(columns={'flight_id': 'previous_id', 'scheduled_departure': 'previous_time'})
nearest = pd.merge_asof(ordered, prior, left_on='scheduled_departure', right_on='previous_time',
                        by='departure_airport', direction='backward', allow_exact_matches=False,
                        tolerance=pd.Timedelta(minutes=30))
print(int(nearest.previous_id.notna().sum()))

Ловушка 1: одна пара появляется дважды

Неправильно писать a.flight_id <> b.flight_id и считать результат уникальными парами. Тогда (21, 387) и (387, 21) — две строки об одних и тех же рейсах. На нашем дне это 750 строк вместо 375. Исправление a.flight_id < b.flight_id выбирает одно каноническое направление, если вопрос симметричный.

Если направление важно — например, «какой рейс вылетел после другого», сравнивать идентификаторы нельзя. Тогда ограничьте время a.scheduled_departure < b.scheduled_departure и отдельно решите, что делать с одновременными вылетами. Отбор по id и отбор по времени отвечают на разные вопросы; их нельзя подменять друг другом.

Ловушка 2: строка соединяется сама с собой

Неправильно написать a.flight_id <= b.flight_id: условие допускает 548 самопар. Итог — 923 строки: 375 настоящих пар плюс 548 рейсов, дважды записанных в одной строке. Такие пары особенно коварны в отчёте «сколько похожих рейсов у каждого»: каждый рейс минимум один раз окажется похож сам на себя.

Исправление — строгое < для пар без направления либо <> вместе с отдельным правилом направления. Проверьте инвариант first_id <> second_id на деталях. Запрос без такого контроля может выдержать небольшую демонстрационную таблицу, а на большой базе ошибочно показать, что у каждого объекта есть сосед.

Ловушка 3: забыто временное окно

Неправильно соединить все рейсы одного аэропорта за день и лишь в описании сказать «рядом». Это 4 284 пары, хотя в пределах получаса только 375. В каждой крупной группе сравниваются все со всеми; при удвоении размера группы число потенциальных пар растёт примерно вчетверо.

Исправление — указать интервал в ON, а для производственной таблицы сузить дату ещё до соединения. Если окно одностороннее, используйте b.scheduled_departure > a.scheduled_departure AND b.scheduled_departure <= a.scheduled_departure + INTERVAL '30 minutes'. На этой базе такой запрос возвращает 352 пары: 23 пары с одинаковым временем больше не подходят. Это осмысленное изменение определения, а не оптимизация исходного вопроса.

Ловушка 4: забыта общая сущность

Неправильно соединять рейсы только по разнице во времени. В один получасовой интервал вылетают разные аэропорты; они не создают нагрузку на одну стойку или полосу. На 15 июля без условия равенства departure_airport получается 12 716 пар вместо 375.

Исправление — до временного условия явно сформулировать, что должно быть общим: аэропорт вылета, конкретный маршрут, компания или клиент. Проверяйте несколько первых строк на предмет смешанных аэропортов. Сам факт, что разница времени мала, не доказывает связи двух событий.

Ловушка 5: порядок id принимают за хронологию

Условие a.flight_id < b.flight_id не говорит, что A раньше B. Реальная пара 21 и 1009 нарушает такую интерпретацию: 09:35 позже 09:30. Если назвать столбцы earlier и later на основании id, отчёт будет содержать обратные последовательности. В запросе выше имена first_id и second_id относятся только к каноническому порядку ключей.

Исправление для задачи о времени — сравнивать timestamp, а при равенстве времени явно добавлять детерминированный второй ключ. Для неупорядоченных пар достаточно id, но в тексте не называйте одну сторону предыдущим рейсом. Именно так маленькое семантическое смещение переходит в неверные рекомендации.

Ловушка 6: `BETWEEN` на границе и пары через полночь

Если фильтровать только a.scheduled_departure на 15 июля, а b брать из всей таблицы, в результате появятся пары с отправлением B уже 16 июля или 14 июля. Условие по времени в 30 минут это не запретит. Для вопроса «пары внутри дня» обе стороны берутся из одного CTE day_flights; для вопроса «пиковая нагрузка около полуночи» наоборот нужно разрешить переход даты.

Неправильно писать scheduled_departure BETWEEN '2026-07-15' AND '2026-07-16', если 16 июля должно быть исключено: BETWEEN включает обе границы. Полуоткрытый интервал >= 15 июля AND < 16 июля делает зерно дня недвусмысленным. В нашем CTE он даёт 548 рейсов; вторую сторону из другого диапазона пришлось бы описывать отдельно.

Когда LAG или LEAD проще

Если нужен лишь предыдущий рейс в отсортированной последовательности одного аэропорта, LAG не строит все возможные пары. Он приклеит ровно одну соседнюю строку к каждой строке раздела. Для ответа «все пары в пределах 30 минут» это недостаточно: три отправления в одном получасе дают до трёх пар, а предыдущий сосед показывает только две связи.

Self join выбирают, когда для каждой строки может быть несколько партнёров по диапазону или условию. Окно выбирают, когда связь однозначно определяется соседней позицией в порядке. Разбор LAG и LEAD показывает окна на последовательности событий; рамки окон решают уже другой вопрос — какие строки участвуют в агрегате.

Что проверять на большой таблице

Зафиксируйте срез, размер входа и размер результата. Для 15 июля это 548 строк до соединения и 375 пар после всех условий. Полезны также count(DISTINCT a.flight_id), выборка нескольких граничных пар с разницей ровно 30 минут и проверка, что нет a.flight_id = b.flight_id. Эти числа ловят смену смысла раньше, чем ошибка окажется на дашборде.

При больших объёмах план выполнения и индексы имеют значение: равенство аэропорта и диапазон времени должны ограничивать кандидатов до перебора пар. Но индекс не исправит вопрос, заданный без окна. Сначала определение пары и контроль зерна, затем план запроса. Для чтения плана см. EXPLAIN ANALYZE в PostgreSQL.

В других СУБД

Сам принцип self join одинаков в PostgreSQL и DuckDB: одна таблица встречается в FROM дважды под разными алиасами. Использованный интервал записан в PostgreSQL как INTERVAL '30 minutes'; этот вариант выполнен и в DuckDB. Диалекты могут различаться способами вычислить число секунд между timestamp, поэтому сравнение b.time <= a.time + INTERVAL '30 minutes' обычно читается проще, чем переносить extract(epoch FROM a.time - b.time) без проверки.

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

Проверка пар на границе получаса

В условии используется <= 1800 секунд: рейсы с разницей ровно 30 минут включены. Если бизнес-вопрос говорит «меньше 30 минут», нужно < 1800, и пограничные пары исчезнут. Не заменяйте точную разность округлёнными минутами: 30 минут 20 секунд после округления могут выглядеть как 30, хотя порог превышен. Для читателя отчёта лучше записать правило словами рядом с графиком.

Полуночная граница требует отдельного решения. Сейчас обе копии берутся из одного CTE 15 июля; рейс 15 июля 23:50 и рейс 16 июля 00:10 не составят пару, хотя между ними 20 минут. Это правильно для вопроса «два вылета в пределах одного отчётного дня» и неправильно для вопроса «нагрузка в любой скользящий получас». Во втором случае расширьте выборку кандидатов для b до 16 июля и оставьте дату отчёта только у a; затем убедитесь, что одна междневная пара не посчитана дважды при сборке суточных отчётов.

Проверка нескольких граничных деталей полезнее, чем утверждение «у нас в данных такого нет». Даже если в текущем срезе ровно 30 минут встречаются редко, следующее обновление расписания может добавить их. Явное правило на границе предотвращает спор между аналитиками, которые получили похожие, но не равные числа.

Как не превратить пару в денежную метрику

У рейса может быть много билетов, а у билета несколько перелётов. Если к 375 парам присоединить ticket_flights, результат перестанет быть таблицей пар: каждый билет скопирует строку своей пары. Последующая sum(amount) ответит на вопрос «сумма по всем копиям билетов внутри пар», а не на вопрос о выручке рейсов или аэропортов. Размножение здесь может быть большим и одновременно выглядеть правдоподобно, если заголовок таблицы остался прежним.

Для отчёта о нагрузке сначала отдельно вычислите число пассажиров на каждом рейсе, получив одну строку на flight_id. После этого присоединяйте два таких агрегата к паре по first_id и second_id. Если хотите суммарное число уникальных пассажиров двух рейсов, проверьте, может ли один билет входить в оба рейса: простое сложение двух чисел может задвоить такого пассажира. Если нужна лишь проверка наличия билетов, EXISTS часто сохраняет зерно пары без присоединения всех билетов.

Та же дисциплина действует в маркетплейсе для пар товаров одного продавца и в продуктовой аналитике для пар событий пользователя. Сначала фиксируйте зерно «одна строка — пара», потом выбирайте, какие атрибуты или агрегаты каждой стороны можно безопасно добавить.

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

Self join — отдельный вид JOIN? Нет. Это обычный INNER или LEFT JOIN, где справа и слева стоит одна физическая таблица с разными алиасами.

Как убрать дубли пар? Для неупорядоченных пар уникальных идентификаторов используйте a.id < b.id. Для направленных связей задайте направление предметным условием и оставьте a.id <> b.id только как защиту от самой строки.

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

Можно ли заменить self join оконной функцией? Для единственного предыдущего или следующего элемента — часто да. Для перечисления всех партнёров в диапазоне — нет: LAG видит только одну соседнюю позицию.