Как получить историю перенесённых посещений в PostgreSQL: рекурсивный SQL-запрос

При работе с таблицей appointments в PostgreSQL, где перенос посещения фиксируется созданием новой записи и обновлением старой (статус 5, поле move_to_id), возникает задача: выбрать все перенесённые посещения за заданный интервал дат вместе с их полной историей - от первого до последнего звена цепочки. Рассмотрим реализацию на чистом SQL с использованием рекурсивного CTE.

Структура таблицы и логика переноса

Таблица appointments содержит ключевые поля: appointment_id (уникальный идентификатор), time_start (дата и время визита), move_id (ссылка на старую запись, которую перенесли), move_to_id (ссылка на новую запись, куда перенесли), status (1 - записан, 4 - завершён, 5 - отменён). Перенос моделируется так: для старой записи статус становится 5, а move_to_id указывает на новую; в новой записи move_id хранит appointment_id старой.

Рекурсивный запрос для построения цепочек

Чтобы получить все перенесённые посещения с историей, нужно найти начальные точки цепочек (записи, у которых move_id IS NULL и move_to_id IS NOT NULL), а затем рекурсивно пройти по ссылкам move_to_id. В каждой итерации мы собираем звенья, у которых time_start попадает в заданный интервал.

WITH RECURSIVE chain AS (  -- Начальные записи: первое перенесённое посещение (нет родителя)  SELECT      appointment_id AS origin_appointment_id,      appointment_id,      time_start,      status,      move_to_id,      1 AS level  FROM appointments  WHERE move_id IS NULL    AND move_to_id IS NOT NULL    AND time_start BETWEEN '2024-01-01' AND '2024-12-31'    UNION ALL  -- Рекурсивная часть: следующее звено цепочки  SELECT      c.origin_appointment_id,      a.appointment_id,      a.time_start,      a.status,      a.move_to_id,      c.level + 1  FROM appointments a  INNER JOIN chain c ON a.appointment_id = c.move_to_id  WHERE a.time_start BETWEEN '2024-01-01' AND '2024-12-31')SELECT   origin_appointment_id,   time_start,   status,   move_to_idFROM chainORDER BY origin_appointment_id, level;

Объяснение работы запроса

• База рекурсии: выбираем записи, которые являются первыми в цепочке (move_id IS NULL), имеют перенос (move_to_id IS NOT NULL) и попадают в интервал дат. Они становятся корнями.
• Рекурсивный шаг: для каждого корня находим следующее звено, где appointment_id совпадает с move_to_id предыдущего. Так собираем всю цепочку.
• Фильтр по дате: условие time_start BETWEEN ... применяется на каждом уровне, поэтому в результат попадут только те звенья, которые входят в интервал. Если нужно, чтобы цепочка начиналась строго внутри интервала, можно изменить логику.

Вариант с группировкой по цепочкам

Если требуется только первое и последнее посещение в цепочке (в рамках интервала), можно после рекурсии сгруппировать данные по origin_appointment_id и выбрать MIN(time_start) и MAX(time_start), а также соответствующие статусы и move_to_id последнего звена.

WITH RECURSIVE chain AS (...),ranked AS (  SELECT       *,      ROW_NUMBER() OVER (PARTITION BY origin_appointment_id ORDER BY time_start) AS rn_first,      ROW_NUMBER() OVER (PARTITION BY origin_appointment_id ORDER BY time_start DESC) AS rn_last  FROM chain)SELECT   origin_appointment_id,   MAX(CASE WHEN rn_first = 1 THEN time_start END) AS first_time,   MAX(CASE WHEN rn_first = 1 THEN status END) AS first_status,   MAX(CASE WHEN rn_last = 1 THEN move_to_id END) AS last_move_to_idFROM rankedWHERE rn_first = 1 OR rn_last = 1GROUP BY origin_appointment_id;

Ограничения и альтернативы

Рекурсивный запрос эффективен, если глубина цепочек невелика (до нескольких сотен). При большом количестве переносов или высокой нагрузке рассмотрите материализованные представления или ETL-скрипты на Python/Go, которые обходят граф и кешируют результат. В любом случае, задача решается средствами SQL без дополнительных инструментов.