Объединение записей в цепочки?

#sql #postgresql

Вопрос:

В моих необработанных данных я вижу, что мой пациент отправил SMS-сообщения своему врачу.

 messageid|PhoneNbr|Patient|Created|Status
asd134|555-1212|Joe Smith|2020-08-26 20:34:10.728000|Outbound
ffg5|555-1212|Joe Smith|2020-08-26 20:35:29.177000|Inbound
xv33|555-1212|Joe Smith|2020-08-26 20:35:37.294000|Outbound
dd234|555-1212|Joe Smith|2020-08-27 17:12:02.593000|Outbound
dfg234|555-1212|Joe Smith|2020-08-27 17:14:20.010000|Inbound
nu6|555-1212|Joe Smith|2020-08-27 17:14:31.977000|Outbound
rx3|555-1212|Joe Smith|2020-08-27 19:33:06.487000|Outbound
zxc4|555-1212|Joe Smith|2020-08-27 20:24:09.833000|Inbound
yt123|555-1212|Joe Smith|2020-08-27 20:24:10.033000|Outbound
ac21|555-1212|Joe Smith|2020-08-27 20:24:47.454000|Outbound

Чтобы отслеживать, когда сообщение получает ответ, мне нужно отслеживать изменения статуса
(исходящий-> входящий-> исходящий-> входящий …), удаляя все строки, в которых статус не изменяется, создавая следующий вывод

 messageid|PhoneNbr|Patient|Created|Status
asd134|555-1212|Joe Smith|2020-08-26 20:34:10.728000|Outbound
ffg5|555-1212|Joe Smith|2020-08-26 20:35:29.177000|Inbound
xv33|555-1212|Joe Smith|2020-08-26 20:35:37.294000|Outbound
dfg234|555-1212|Joe Smith|2020-08-27 17:14:20.010000|Inbound
nu6|555-1212|Joe Smith|2020-08-27 17:14:31.977000|Outbound
zxc4|555-1212|Joe Smith|2020-08-27 20:24:09.833000|Inbound
yt123|555-1212|Joe Smith|2020-08-27 20:24:10.033000|Outbound

Мой запрос не «связывает» мои статусы. Он просто повторяет необработанные данные

 SELECT 
a.messageid,
a.PhoneNbr,
a.Patient,
a.Created,
a.Status
FROM message AS a LEFT JOIN message AS b on a.messageid = b.messageid

Как я могу получить желаемый результат?

1. Все ваши идентификаторы сообщений уникальны. Как связаны сообщения? Ваше объединение приведет только к тому, что каждая запись присоединится к самой себе, а ваш запрос вернет только ваш исходный набор данных.

2. Вы правы. Сообщения уникальны. идентификаторы сообщений генерируются системой.

Ответ №1:

Вы можете использовать функцию PostgreSQL LAG () для определения «предыдущего» статуса, а затем выбрать только те строки, в которых произошло изменение статуса:

 SELECT *
FROM (
    SELECT
        a.messageid,
        a.PhoneNbr,
        a.Patient,
        a.Created,
        a.Status,
        LAG(a.Status, 1) OVER (PARTITION BY Patient ORDER BY Created) AS PrevStatus
    FROM message
) subquery
WHERE subquery.Status <> subquery.PrevStatus
OR subquery.PrevStatus IS NULL