Добрый день. <br/> У меня есть таблица appointments в БД PostgreSQL, которая содержит данные о посещениях клиентов. Структура таблицы выглядит следующим образом: <br/> <pre><code>appointment_id - идентификатор посещения time_start - дата и время посещения move_id - идентификатор записи, с которой связано перенесенное посещение move_to_id - идентификатор новой записи при переносе status - статус посещения, который может принимать следующие значения: 1 - записан (клиент планирует прийти), 4 - завершён (клиент пришёл), 5 - отменён (клиент не пришёл)</code></pre> <br/> Как происходит перенос посещения в БД: <br/> - создаётся новая запись с датой и временем посещения, где в поле move_id указывается идентификатор старой записи; <br/> - в старой записи поле status обновляется на 5 (отменён), а поле move_to_id заполняется идентификатором новой записи. <br/> У меня стоит задача извлечь из таблицы все перенесённые посещения с их историей за определенный временной интервал. <br/> Я не знаю, с чего начать и как это реализовать :) <br/> Я хотел бы получить выборку данных в следующем формате: <br/> origin_appointment_id <br/> time_start <br/> status <br/> move_to_id <br/> где origin_appointment_id - это идентификатор первого посещения в цепочке переносов (от первоначального посещения, имеющегося в заданном интервале, до последнего). <br/> Буду признателен за ссылки на примеры или информацию о том, возможно ли это реализовать с помощью SQL в данной структуре таблицы; в противном случае, придется писать скрипт.
Да, задача извлечения перенесённых посещений с их историей из таблицы `appointments` в PostgreSQL может быть реализована с помощью SQL-запросов. Для этого можно использовать рекурсивный CTE (Common Table Expression), чтобы получить все записи, связанные с первоначальным посещением.
Ниже представлен пример SQL-запроса, который возвращает нужные данные:
```sql
WITH RECURSIVE appointment_history AS (
-- Начинаем с выборки первоначальных записей в заданном временном интервале
SELECT
appointment_id AS origin_appointment_id,
time_start,
status,
move_to_id
FROM appointments
WHERE time_start BETWEEN '2023-01-01' AND '2023-12-31' -- Задайте свои даты
UNION ALL
-- Рекурсивно присоединяем записи, которые являются переносами
SELECT
ah.origin_appointment_id,
a.time_start,
a.status,
a.move_to_id
FROM
appointment_history ah
JOIN appointments a ON ah.move_to_id = a.appointment_id
)
SELECT
origin_appointment_id,
time_start,
status,
move_to_id
FROM appointment_history
ORDER BY origin_appointment_id, time_start;
```
### Пояснение:
1. **CTE (Common Table Expression)**: Рекурсивный CTE `appointment_history` начинается с выборки записей, которые попадают в заданный временной интервал. Это первоначальные записи, от которых будет происходить "перенос".
2. **UNION ALL**: Затем выполняется рекурсивное объединение, которое присоединяет все записи, связанные с первичными записями через поле `move_to_id`.
3. **JOIN**: Используется `JOIN` для поиска всех перенесённых посещений, связанных с уже найденными записями.
4. **Финальный SELECT**: В конце выбираются данные из CTE `appointment_history`, чтобы получить необходимый формат.
### Замените временной интервал:
Не забудьте заменить `BETWEEN '2023-01-01' AND '2023-12-31'` на тот временной интервал, который вам нужен.
### Дополнительные замечания:
- Убедитесь, что ваш временной интервал не слишком большой, так как рекурсивные запросы могут иметь ограничения по глубине.
- Обратите внимание на состояние записей: если вам нужно только активные перенесённые записи, добавьте соответствующие условия в ваш запрос.
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;