Не могли бы помочь с sql-запросом?

Question

[VlLight]

Добрый день.
Есть таблица appointments в БД PostgreSQL, хранящая данные о посещениях клиентов, примерно следующей структуры:

appointment_id - идентификатор посещения
time_start - дата-время посещения
move_id
move_to_id
status - статус посещения, принимающий (значимые в данном контексте) возможные значения  - 
1 - записан (клиент собирается прийти), 4 - завершён (клиент пришёл), 5 - отменён (клиент не пришёл))

Посещение может быть перенесено на другую дату. Перенос посещения в БД отражается следующим образом:
- создаётся новая запись посещения с заданной датой, в поле move_id новой записи заносится идентификатор старой записи
- в поле status старой записи посещения заносится значение 5 (отменён), а в поле move_to_id - идентификатор новой записи посещения
Есть задача получить из таблицы все перенесённые посещения с их историей за заданный интервал дат.
И на этом моменте я обнаруживаю, что не могу ничего придумать :)
Вероятно, хотелось бы получить из таблицы выборку примерно следующего вида:
origin_appointment_id
time_start
status
move_to_id
где origin_appointment_id - идентификатор первого посещения каждой цепочки переносов (от первого посещения, входящего в заданный интервал до последнего посещения, входящего в заданный интервал).
Буду благодарен, если кто-нибудь поделится ссылкой с подходящими примерами (или скажет, что это в данной структуре таблицы нереализуемо средствами sql, буду ковырять скриптом).

Comments

[Rsa97]

Рекурсивное CTE.

Answer 1

[YepBro]

WITH RECURSIVE appointment_chain AS (
    -- Начальная часть рекурсивного запроса: выбираем все посещения, которые были перенесены и находятся в заданном интервале
    SELECT 
        a.appointment_id AS origin_appointment_id,
        a.time_start,
        a.status,
        a.move_to_id,
        a.appointment_id AS current_appointment_id
    FROM 
        appointments a
    WHERE 
        a.time_start BETWEEN '2023-10-01' AND '2023-10-31' -- Заданный интервал дат
        AND a.move_id IS NULL -- Начинаем с первого посещения в цепочке

    UNION ALL

    -- Рекурсивная часть: присоединяем следующие посещения в цепочке
    SELECT 
        ac.origin_appointment_id,
        a.time_start,
        a.status,
        a.move_to_id,
        a.appointment_id AS current_appointment_id
    FROM 
        appointments a
    INNER JOIN 
        appointment_chain ac ON a.move_id = ac.current_appointment_id
    WHERE 
        a.time_start BETWEEN '2023-10-01' AND '2023-10-31' -- Заданный интервал дат
)
SELECT 
    origin_appointment_id,
    time_start,
    status,
    move_to_id
FROM 
    appointment_chain
ORDER BY 
    origin_appointment_id, time_start;