SQL需求：基于唯一D_PRESCRIBER_ID回溯6个月统计两类药物处方次数

核心思路 #

针对每条处方记录，按**唯一处方者ID（D_PRESCRIBER_ID）分组，统计该记录日期往前6个月内（不含当前记录）**的'Target'和'Competitor'药物处方次数，同时保留原表所有字段。

适用SQL环境（支持窗口函数） #

适用于PostgreSQL、SQL Server、MySQL 8.0+等支持窗口函数的数据库，性能更优，适合大数据量场景：

SELECT
    -- 保留原表所有字段
    *,
    -- 统计当前记录前6个月内Target药物的处方次数
    COUNT(CASE WHEN drug_type = 'Target' THEN 1 END) OVER (
        PARTITION BY D_PRESCRIBER_ID
        ORDER BY date_of_prescription
        RANGE BETWEEN INTERVAL '6 months' PRECEDING AND 1 PRECEDING
    ) AS target_count,
    -- 统计当前记录前6个月内Competitor药物的处方次数
    COUNT(CASE WHEN drug_type = 'Competitor' THEN 1 END) OVER (
        PARTITION BY D_PRESCRIBER_ID
        ORDER BY date_of_prescription
        RANGE BETWEEN INTERVAL '6 months' PRECEDING AND 1 PRECEDING
    ) AS competitor_count
FROM prescriptions;

代码说明：

• PARTITION BY D_PRESCRIBER_ID：限定仅对同一处方者的记录进行统计
• ORDER BY date_of_prescription：确保窗口按时间顺序筛选
• RANGE BETWEEN INTERVAL '6 months' PRECEDING AND 1 PRECEDING：定义时间窗口为当前记录日期往前6个月，且排除当前记录本身
• CASE语句：仅匹配对应类型的药物，COUNT会自动忽略不符合条件的NULL值，实现精准计数

兼容低版本SQL环境（如MySQL 5.x） #

若数据库不支持窗口函数的RANGE语法，可使用相关子查询实现，兼容性更强：

SELECT
    *,
    -- 统计Target药物次数
    (SELECT COUNT(*)
     FROM prescriptions p2
     WHERE p2.D_PRESCRIBER_ID = p1.D_PRESCRIBER_ID
       AND p2.date_of_prescription >= DATE_SUB(p1.date_of_prescription, INTERVAL 6 MONTH)
       AND p2.date_of_prescription < p1.date_of_prescription
       AND p2.drug_type = 'Target') AS target_count,
    -- 统计Competitor药物次数
    (SELECT COUNT(*)
     FROM prescriptions p2
     WHERE p2.D_PRESCRIBER_ID = p1.D_PRESCRIBER_ID
       AND p2.date_of_prescription >= DATE_SUB(p1.date_of_prescription, INTERVAL 6 MONTH)
       AND p2.date_of_prescription < p1.date_of_prescription
       AND p2.drug_type = 'Competitor') AS competitor_count
FROM prescriptions p1;

重复行处理 #

如果数据存在重复处方记录，上述代码会将重复行计入统计（符合实际处方次数逻辑）。若需去重后统计，可在COUNT中添加DISTINCT（例如COUNT(DISTINCT p2.prescription_id)，需替换为实际唯一标识字段）。

内容的提问来源于stack exchange，提问作者Divya Desadla