Redshift中按患者/NDC计算取药间隔天数的SQL实现需求

场景描述 #

患者使用多种药品（以ndc标识），每种药品的每次取药对应fill_nbr（取药序号）和rx_date（取药日期）：fill_nbr=1为首次取药，fill_nbr=2为第二次取药（首次续药），以此类推。

需求 #

按patient_id和ndc分组，计算每一次取药与下一次取药的间隔天数；若无后续取药，days_between_refills设为0。

现有问题 #

当前手动关联连续fill_nbr的方式（比如只关联fill_nbr=1和2）无法处理大量续药的情况，部分患者同一种药品续药超200次，重复编写关联条件效率极低且不现实。

原手动关联代码：

SELECT t1.patient_id, t1.ndc, t1.fill_nbr, DATEDIFF(day,t1.rx_date,t2.rx_date) as days_between_fill
FROM sandbox.table1 t1
INNER JOIN sandbox.table2 t2
    ON t1.ndc = t2.ndc
    AND t1.patient_id = t2.patient_id 
WHERE t1.fill_nbr = 1 and t2.fill_nbr = 2 

解决方案 #

用Redshift支持的**窗口函数LEAD()**可以自动获取同组内下一次取药的日期，无需多次JOIN，一步完成所有续药间隔计算：

SELECT
    patient_id,
    ndc,
    fill_nbr,
    rx_date,
    -- 计算与下一次取药的间隔天数，无后续取药则设为0
    COALESCE(DATEDIFF(day, rx_date, next_rx_date), 0) AS days_between_refills
FROM (
    SELECT
        patient_id,
        ndc,
        fill_nbr,
        rx_date,
        -- 按患者+药品分组，按取药序号排序，获取下一次取药日期
        LEAD(rx_date) OVER (PARTITION BY patient_id, ndc ORDER BY fill_nbr) AS next_rx_date
    FROM sandbox.table1 -- 若table2是补充表，可在此子查询中按需关联
) AS subquery

代码说明： #

• PARTITION BY patient_id, ndc：限定仅在同一患者、同一药品的范围内计算后续取药
• ORDER BY fill_nbr：保证按取药顺序匹配下一次的日期
• LEAD(rx_date)：获取当前行的下一行rx_date，最后一行（无后续取药）返回NULL
• COALESCE(..., 0)：将NULL（无后续取药场景）转换为0，符合需求

这个方案可以自动处理任意次数的续药，不管是2次还是200次，都无需修改SQL逻辑。

内容的提问来源于stack exchange，提问作者sherri pytorch