SQL仅在眼镜蛇感染时选择药物。

Question

我有两张桌子。表'lab_obs‘记录的结果时，病人被测试的贪心。表中的“药物”记录了所有患者服用的所有药物(无论测试与否)。以下是简化的模式：

CREATE TABLE lab_obs (ob_day DATE, 
                          ob VARCHAR(20),
                          patient_id VARCHAR(20)
                         );

CREATE TABLE medications (given_day DATE, 
                          med VARCHAR(20),
                          patient_id VARCHAR(20)
                         );                         

INSERT INTO lab_obs VALUES ('2020-03-01', 'positive', 'p11');
INSERT INTO lab_obs VALUES ('2020-05-01', 'negative', 'p11');
INSERT INTO lab_obs VALUES ('2020-03-02', 'negative', 'p12');
INSERT INTO lab_obs VALUES ('2020-06-01', 'negative', 'p11');
INSERT INTO lab_obs VALUES ('2020-06-01', 'negative', 'p12');

INSERT INTO medications VALUES ('2020-03-05', 'covid_med_11', 'p11');
INSERT INTO medications VALUES ('2020-03-05', 'non_covid_med_12', 'p12');
INSERT INTO medications VALUES ('2020-02-05', 'non_covid_med_11', 'p11');
INSERT INTO medications VALUES ('2020-04-05', 'non_covid_med_41', 'p41');

我想写一个SQL，它将给我(patient_id，med)测试阳性和随后测试阴性的病人，以及在阳性测试和随后的阴性测试之间所服用的所有药物。基本上，这个查询应该给我从Covid康复的病人服用的所有药物!！

Answer 1

我想这就是你想要的。使用延迟函数检查以前的记录。我在SQL server中运行了它。此外，请使用代码进行观察，因为拼写可能导致问题，例如，积极而不是积极。

SELECT m.patient_id, m.med , m.given_day
FROM medications m
JOIN (SELECT patient_id, 
       CASE WHEN ob = 'negative'
             AND lag(ob) OVER (partition by patient_id order by l.ob_day) = 'postive'
            THEN 1
            ELSE 0 
            END as qualify
      FROM lab_obs l) l ON l.patient_id = m.patient_id AND l.qualify = 1

Answer 2

尝试使用PostgreSQL (不确定您使用的是哪个关系数据库管理系统)。为此，您需要窗口函数和CTE：

WITH lagged_obs AS (
    SELECT patient_id,
           ob_day,
           ob,
           LAG(ob) OVER (PARTITION BY patient_id ORDER BY ob_day) AS lagged_ob
    FROM lab_obs
)
, patient_status_change AS (
    -- Look for status changes, useful for consecutive tests with same result
    SELECT patient_id,
           ob_day AS ob_change_day,
           ob
    FROM lagged_obs
    WHERE ob IS DISTINCT FROM lagged_ob
)
, patient_infected_periods AS (
    -- Build time periods
    SELECT patient_id,
           ob_change_day AS start_date,
           LEAD(ob_change_day)
               OVER (PARTITION BY patient_id ORDER BY ob_change_day) AS end_date,
           ob AS period_status
    FROM patient_status_change
)
SELECT pip.patient_id, m.given_day, m.med
FROM patient_infected_periods pip
    INNER JOIN medications m
    ON pip.patient_id=m.patient_id AND pip.start_date <= m.given_day AND m.given_day < pip.end_date
WHERE pip.period_status='postive'

Answer 3

这有点棘手。如果某人只检测一次阳性，那么：

select m.*
from medications m
where m.given_day >= (select min(l.ob_day)
                      from lab_obs l
                      where l.patient_id = m.patient_id and l.ob = 'positive'
                     ) and
      m.given_day < (select min(l.ob_day)
                     from (select l.*, min(case when ob = 'positive' then ob_day end) as pos_ob_day
                           from lab_obs l
                          ) l
                     where l.patient_id = m.patient_id and
                           l.ob = 'negative' and
                           l.ob_date > pos_ob_day
                        );

这适用于消极-积极-消极和积极-消极的情况。

如果有人可以来回切换，那么我建议像这样使用lag()：

select m.*, l.*
from medications m join
     (select l.*,
             lead(ob_day) over (partition by patient_id order by ob_day) as next_ob_day
      from (select l.*,
                   lag(ob) over (partition by patient_id order by ob_day) as prev_ob
           from lab_obs l
          ) l
      where prev_ob is null or prev_ob <> ob
     ) l
     on m.patient_id = l.patient_id and
        m.given_date >= l.ob_day and
        m.given_date < l.next_ob_day and
        l.ob = 'positive';

l子查询是确定何时某人第一次测试阳性，然后用不同的测试值确定下一个测试日期。