医疗数据SQL查询需求：多时段多次检测患者统计分析

针对你提出的统计需求，以下是可直接使用的SQL查询方案，包含零值场景的处理，同时兼容主流SQL数据库（需根据实际数据库调整日期函数）：

核心思路 #

1. 生成全量维度组合：通过笛卡尔积生成指定的3个诊所、3种检测项目、3个时段的所有可能组合，确保零值场景（无符合条件患者的组合）能被展示。
2. 预处理患者检测数据：按诊所、检测项目、患者分组，计算每个患者的检测次数、首次/末次检测日期及首次检测结果。
3. 关联筛选符合条件的患者：将全量维度与患者数据关联，筛选出每个时段内至少完成2次检测的患者。
4. 统计最终结果：按维度分组统计患者数量及首次检测结果的均值。

完整SQL代码 #

WITH dims AS (
    -- 生成所有需要的维度组合：诊所、检测项目、时段（周数）
    SELECT practice_name, test_code, period_weeks
    FROM (
        VALUES ('Practice 1'), ('Practice 2'), ('Practice 3')
    ) AS practices(practice_name)
    CROSS JOIN (
        VALUES ('1234-9'), ('5768-8'), ('2468-0')
    ) AS tests(test_code)
    CROSS JOIN (
        VALUES (12), (24), (48)
    ) AS periods(period_weeks)
),
patient_stats AS (
    -- 预处理每个患者的检测数据
    SELECT
        practice_name,
        test_code,
        patient_id,
        COUNT(*) AS test_count,
        MIN(result_date) AS first_test_date,
        MAX(result_date) AS last_test_date,
        -- 获取患者该类检测的首次结果
        FIRST_VALUE(test_result) OVER (
            PARTITION BY practice_name, test_code, patient_id 
            ORDER BY result_date
        ) AS first_test_result
    FROM laboratory
    WHERE
        practice_name IN ('Practice 1', 'Practice 2', 'Practice 3')
        AND test_code IN ('1234-9', '5768-8', '2468-0')
    GROUP BY practice_name, test_code, patient_id
),
period_patients AS (
    -- 关联维度与患者数据，筛选符合条件的患者
    SELECT
        d.practice_name,
        d.test_code,
        d.period_weeks,
        ps.patient_id,
        ps.first_test_result
    FROM dims d
    LEFT JOIN patient_stats ps
        ON d.practice_name = ps.practice_name
        AND d.test_code = ps.test_code
        AND ps.test_count >= 2
        -- 此处条件根据时段定义调整：
        -- 场景1：统计「最近N周内」有至少2次检测的患者
        AND ps.last_test_date >= CURRENT_DATE - INTERVAL '1 week' * d.period_weeks
        -- 场景2：统计「首次检测后N周内完成至少2次」的患者（替换上面的条件）
        -- AND ps.last_test_date <= ps.first_test_date + INTERVAL '1 week' * d.period_weeks
)
-- 最终统计结果
SELECT
    practice_name,
    test_code,
    period_weeks,
    COUNT(DISTINCT patient_id) AS patient_count,
    -- 用COALESCE将无数据的均值转为0（可选，根据需求调整）
    COALESCE(AVG(first_test_result), NULL) AS avg_first_test_result
FROM period_patients
GROUP BY practice_name, test_code, period_weeks
ORDER BY practice_name, test_code, period_weeks;

关键说明 #

1. 维度组合生成：dims CTE确保了所有诊所、检测项目、时段的组合都被包含，即使某个组合没有符合条件的患者，也会在结果中显示patient_count = 0。
2. 时段条件调整：
   • 默认使用「最近N周内有至少2次检测」的逻辑，适合基于当前时间的统计需求。
   • 如果你的需求是「患者在首次检测后的N周内完成至少2次检测」，请注释掉场景1的条件，启用场景2的条件。
3. 数据库兼容性：
   • PostgreSQL：直接使用上述代码。
   • MySQL：将INTERVAL '1 week' * d.period_weeks改为INTERVAL d.period_weeks WEEK，CURRENT_DATE可保留。
   • SQL Server：将CURRENT_DATE - INTERVAL '1 week' * d.period_weeks改为DATEADD(WEEK, -d.period_weeks, GETDATE())。
4. NULL值处理：avg_first_test_result在无符合条件患者时会返回NULL，如果需要显示为0，可将COALESCE(AVG(...), NULL)改为COALESCE(AVG(...), 0)，但需注意均值为0可能不符合业务逻辑，建议保留NULL并标注为「无有效数据」。

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