Как найти события во временном окне из таблицы SQL «ключ/значение»?
У меня есть события записей о состоянии здоровья, хранящиеся как ключ/значение в таблице SQL. Например, следующие события таблицы.
PATIENT_ID TIMESTAMP DOMAIN KEY VALUE
1 2021-01-01 biology Hemoglobin 10
1 2021-01-05 diagnosis ICD J32
1 2021-01-10 diagnosis ICD J44
2 2018-01-01 biology Hemoglobin 10
2 2019-01-01 diagnosis ICD J32
2 2020-01-01 diagnosis ICD J44
Из этой таблицы я хотел бы выбрать всех пациентов со следующими критериями во временном окне в 1 месяц:
biology:hemoglobin = '10' AND ( diagnosis:ICD ='J32' OR diagnosis:ICD = 'J44' )
Из приведенного выше набора входных данных у пациента 1 и пациента 2 произошло одно и то же событие, но в разное время. Я хочу оставить только пациента 1, где все события происходят в узком временном окне (день, месяц или что-то еще).
Графический пример:
----------------timeline---------------->
patient 1 ^ ^ ^
patient 2 ^^ ^
[--time window --]
Не принимая во внимание временное окно, я думаю, что смогу преобразовать приведенный выше запрос в следующий SQL-запрос:
SELECT patient_id FROM events WHERE domain='biology' AND key='hemoglobin' AND value = '10'
INTERSECT
(
SELECT patient_id FROM events WHERE domain='diagnosis' AND key='ICD' AND value = J32
UNION
SELECT patient_id FROM events WHERE domain='diagnosis' AND key='ICD' AND value = J44
)
Но эти условия должны быть верными только в пределах определенного временного окна. Я использую Duckdb, видел, что есть функция ОКНО, но не знаю, полезна ли она в данном случае.
Спасибо
Возможно, рассмотрите ГРУППУ ПО Году (TimeStamp), Месяцу (TimeStamp).. см. uckdb.org/docs/sql/functions/datepart
Вместо того, чтобы говорить об интервале в один месяц (месяцы различаются на 28-31 день)...можно просто сказать, что результаты должны быть в пределах 30 дней друг от друга.
Извините, я исправил это, добавив новые данные, ожидаемые результаты и графический пример.
J34 = Другие и неуточненные заболевания носа и носовых пазух. J32 = Хронический синусит.
10 – крайне низкий результат для гемоглобина. Для мужчин нормальный уровень колеблется от 14,0 граммов на децилитр (г/дл) до 17,5 грамм/дл. У женщин нормальный уровень колеблется от 12,3 до 15,3 г/дл. Тяжелый низкий уровень гемоглобина у мужчин составляет 13 г/дл или ниже. У женщин тяжелый низкий уровень гемоглобина составляет 12 г/дл.
Я подозреваю, что этот запрос предназначен для пациентов, у которых проблемы с дыханием И проблемы с транспортировкой кислорода по организму из-за низкого гемоглобина. Любое состояние само по себе можно контролировать, но сочетание требует особого внимания.
Нет смысла искать результат по гемоглобину, равный ровно 10. Условие фильтра гемоглобина <= 10 было бы более полезным.
Ответы 2
DuckDB поддерживает функции даты PostgreSQL. В этом решении я сначала нахожу пациентов с результатами Гемоглобин = 10. Затем я присоединяю к ним пациентов, у которых МКБ = J32 или J44 и временные метки с окном в 1 месяц.
WITH Hem10 as
(
SELECT PATIENT_ID,ts
FROM Events
WHERE Domain='biology' AND Key='Hemoglobin' AND VALUE='10'
)
SELECT *
FROM events e
INNER JOIN Hem10 h on H.Patient_id=e.Patient_ID
WHERE Domain='diagnosis' AND KEY= 'ICD' AND ( VALUE ='J32' OR VALUE = 'J44' )
AND e.ts BETWEEN h.ts + INTERVAL '-1 month' AND h.ts + INTERVAL '1 month'
Ответ обновлен для использования нового ввода
В вашем вопросе нет примера вывода, поэтому трудно быть уверенным, что именно вы подразумеваете под условиями, которые должны быть истинными только в пределах определенного временного окна,
но если вам нужно фильтровать по временной метке, вы можете просто добавить условие в предложение where.
Кроме того, я бы предложил использовать оператор in для фильтрации по нескольким значениям:
select e.patient_id
from events as e
where
e.domain = 'biology' and
e.key = 'hemoglobin' and
e.value = '10' and
<timeframe condition here>
intersect
select e.patient_id
from events as e
where
e.domain = 'diagnosis' and
e.key = 'ICD' and
e.value in ('J32', 'J44') and
<timeframe condition here>
Предоставьте полный минимально воспроизводимый пример , т. е. добавьте еще несколько образцов данных таблицы, а также укажите ожидаемый результат.