如何在每行不同的起点之前计数事件?

Question

假设我有一个表，其中包含患者列表和他们的手术日期。

ID_PATIENT  SURG_DATE
xxxxxxxxx1  07MAR2006:00:00:00
xxxxxxxxx2  11FEB2006:00:00:00
xxxxxxxxx3  14JAN2006:00:00:00
xxxxxxxxx4  01JAN2005:00:00:00

假设，我有第二个表，其中包含对应于的药物代码列表某种类型的治疗(例如;治疗糖尿病)

MED_CODE
3484027
3484028

现在，我有一张表，上面有所有的药物和配药日期。

ID_PATIENT  MED_CODE    DEL_DATE
xxxxxxxxx1  3484027 29DEC2005:00:00:00
xxxxxxxxx1  3484028 12JUN2005:00:00:00
xxxxxxxxx2  3484027 10JAN2005:00:00:00
xxxxxxxxx2  1234567 10MAR2005:00:00:00
xxxxxxxxx2  3484027 14APR2005:00:00:00
xxxxxxxxx3  3484027 12FEB2005:00:00:00
xxxxxxxxx3  3484028 14AUG2005:00:00:00
xxxxxxxxx3  3484027 17NOV2005:00:00:00
xxxxxxxxx4  3484027 17NOV2004:00:00:00
xxxxxxxxx4  3484027 20NOV2004:00:00:00
xxxxxxxxx4  3484027 13JAN2005:00:00:00

我要做的是在我的病人表中创建一个新的列显示患者是否在一年内至少服用过三次药物手术前。因此在示例数据中只有第三个患者符合要求，因为:

• 第一位患者只接受了两次治疗。
• 第二位患者服用了三种药物，但其中一种不在糖尿病清单中。
• 第四名患者服用了三种药物，但其中一种是在手术后服用的。

下面是PL/SQL中生成示例数据的代码:

create table PATIENTS
(
ID_PATIENT varchar2(10)
,  SURG_DATE date
)
insert into PATIENTS values ('xxxxxxxxx1', to_date('20060307', 'yyyymmdd'))
insert into PATIENTS values ('xxxxxxxxx2', to_date('20060211', 'yyyymmdd'))
insert into PATIENTS values ('xxxxxxxxx3', to_date('20060114', 'yyyymmdd'))
insert into PATIENTS values ('xxxxxxxxx4', to_date('20050101', 'yyyymmdd'))
create table DIABETES_MEDS
(
MED_CODE varchar2(10)
)
insert into DIABETES_MEDS values ('3484027')
insert into DIABETES_MEDS values ('3484028')
create table MEDS
(
ID_PATIENT varchar2(10)
,  MED_CODE   varchar2(7)
,  DEL_DATE   date
)
insert into MEDS values ('xxxxxxxxx1', '3484027', to_date('20051229', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx1', '3484028', to_date('20050612', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx2', '3484027', to_date('20050110', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx2', '1234567', to_date('20050310', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx2', '3484027', to_date('20050414', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx3', '3484027', to_date('20050212', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx3', '3484028', to_date('20050814', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx3', '3484027', to_date('20051117', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx4', '3484027', to_date('20041117', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx4', '3484027', to_date('20041120', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx4', '3484027', to_date('20050113', 'yyyymmdd'))

Answer 1

这将为您提供符合条件的患者:

SELECT COUNT(1), m.id_patient
FROM meds m
, patients p
WHERE m.id_patient = p.id_patient
AND m.med_code   IN (SELECT med_code FROM diabetes_meds)
AND m.del_date   >= ADD_MONTHS(p.surg_date,-12)
AND m.del_date   <= p.surg_date
GROUP BY m.id_patient
HAVING COUNT(1) >= 3;

，一旦你创建了新的列，你可以用一个类似的MERGE填充它:

MERGE INTO patients p
USING ( SELECT COUNT(1), m.id_patient
FROM meds m
, patients p
WHERE m.id_patient = p.id_patient
AND m.med_code   IN (SELECT med_code FROM diabetes_meds)
AND m.del_date   >= ADD_MONTHS(p.surg_date,-12)
AND m.del_date   <= p.surg_date
GROUP BY m.id_patient
HAVING COUNT(1) >= 3 ) meds
ON (p.id_patient = meds.id_patient)
WHEN MATCHED THEN UPDATE SET <p.had_meds> = 'Y'; -- or whatever type of flag you want to use