如何在DuckDB中从键值型SQL表筛选时间窗口内的目标事件

问题背景 #

有存储在键值型SQL表中的健康记录事件，示例表events结构及数据如下：

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' )

已知不考虑时间窗口的查询语句，需添加时间窗口限制，基于DuckDB实现。

解决方案 #

方法1：分组统计时间跨度 #

先筛选符合基础条件的事件，按患者分组后计算时间跨度，同时验证患者同时具备两类事件：

WITH eligible_events AS (
    SELECT 
        patient_id,
        timestamp,
        domain,
        key,
        value
    FROM events
    WHERE 
        (domain = 'biology' AND key = 'Hemoglobin' AND value = '10')
        OR 
        (domain = 'diagnosis' AND key = 'ICD' AND value IN ('J32', 'J44'))
),
patient_validation AS (
    SELECT 
        patient_id,
        MIN(timestamp) AS earliest_event,
        MAX(timestamp) AS latest_event
    FROM eligible_events
    GROUP BY patient_id
    HAVING 
        -- 确保存在血红蛋白事件
        SUM(CASE WHEN domain = 'biology' AND key = 'Hemoglobin' AND value = '10' THEN 1 ELSE 0 END) > 0
        -- 确保存在至少一个目标ICD事件
        AND SUM(CASE WHEN domain = 'diagnosis' AND key = 'ICD' AND value IN ('J32', 'J44') THEN 1 ELSE 0 END) > 0
)
SELECT patient_id
FROM patient_validation
WHERE latest_event <= earliest_event + INTERVAL '1 month';

方法2：窗口函数优化（更高效） #

利用窗口函数为每个患者的事件计算全局最早/最晚时间，减少子查询嵌套，提升执行效率：

WITH filtered_events AS (
    SELECT 
        patient_id,
        timestamp,
        domain,
        key,
        value,
        MIN(timestamp) OVER (PARTITION BY patient_id) AS patient_first_event,
        MAX(timestamp) OVER (PARTITION BY patient_id) AS patient_last_event,
        -- 标记当前事件是否是目标血红蛋白事件
        CASE WHEN domain = 'biology' AND key = 'Hemoglobin' AND value = '10' THEN 1 ELSE 0 END AS is_hemoglobin,
        -- 标记当前事件是否是目标ICD事件
        CASE WHEN domain = 'diagnosis' AND key = 'ICD' AND value IN ('J32', 'J44') THEN 1 ELSE 0 END AS is_target_icd
    FROM events
    WHERE 
        (domain = 'biology' AND key = 'Hemoglobin' AND value = '10')
        OR 
        (domain = 'diagnosis' AND key = 'ICD' AND value IN ('J32', 'J44'))
),
patient_check AS (
    SELECT DISTINCT
        patient_id,
        patient_first_event,
        patient_last_event,
        SUM(is_hemoglobin) OVER (PARTITION BY patient_id) AS has_hemoglobin,
        SUM(is_target_icd) OVER (PARTITION BY patient_id) AS has_target_icd
    FROM filtered_events
)
SELECT patient_id
FROM patient_check
WHERE 
    patient_last_event <= patient_first_event + INTERVAL '1 month'
    AND has_hemoglobin > 0
    AND has_target_icd > 0;

关键说明 #

• 两种方法均先过滤出符合基础条件的事件，再验证时间窗口和双事件存在性
• DuckDB支持INTERVAL '1 month'直接计算时间范围，比固定31天更精准适配自然月
• 方法2通过窗口函数一次性计算分组内的时间边界和事件统计，适合大数据量场景

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