使用CTE匹配出院后天数与处方供应天数的高效方案问询

针对你的需求，结合大数据量的性能优化要求，用CTE+预过滤+数字序列生成的组合是最优方案，以下分两个需求逐一说明：

核心前置准备：生成1-30天的日偏移序列 #

先构造包含1到30天的连续数字序列，这是两个需求的基础。用非递归方式生成比递归CTE性能更优，避免不必要的开销：

-- PostgreSQL 写法
WITH DayOffsets AS (
    SELECT generate_series(1, 30) AS DayNum
),
-- SQL Server 写法替换为：
-- DayOffsets AS (
--     SELECT number AS DayNum FROM master.dbo.spt_values WHERE type = 'P' AND number BETWEEN 1 AND 30
-- ),
-- MySQL 写法替换为：
-- DayOffsets AS (
--     SELECT 1 AS DayNum UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
--     SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
--     SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
--     SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
--     SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
--     SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
--     SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL
--     SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
-- ),

需求二：生成出院后1-30天的每日处方记录 #

先过滤掉出院30天后开处方的患者数据，再关联日偏移序列生成每日标记，这样能大幅减少后续计算的数据量：

FilteredPatients AS (
    SELECT
        PatientID,
        DischargeDate,
        RxDate,
        -- 计算处方覆盖的最后一天（DaysSupply为供应天数，比如RxDate+DaysSupply-1）
        RxDate + INTERVAL '1 day' * (DaysSupply - 1) AS RxEndDate
    FROM PatientTable
    -- 提前过滤：只保留出院30天内开的处方
    WHERE RxDate <= DischargeDate + INTERVAL '30 days'
)
-- 生成每日记录
SELECT
    fp.PatientID,
    fp.DischargeDate,
    do.DayNum,
    -- 标记当日是否在处方覆盖区间内
    CASE
        WHEN fp.RxDate <= fp.DischargeDate + INTERVAL '1 day' * do.DayNum
             AND fp.DischargeDate + INTERVAL '1 day' * do.DayNum <= fp.RxEndDate
        THEN 1
        ELSE 0
    END AS HasAntibiotic
FROM FilteredPatients fp
-- 关联日偏移序列（仅30行，笛卡尔积开销极低）
JOIN DayOffsets do ON 1=1
ORDER BY fp.PatientID, do.DayNum;

需求一：转置为含30个日期间隔列的表 #

基于上述每日记录的结果，用CASE+GROUP BY实现转置（兼容性最好，所有主流数据库支持），比直接原表转置更高效：

-- 延续上面的DayOffsets和FilteredPatients CTE
DailyRecords AS (
    SELECT
        fp.PatientID,
        fp.DischargeDate,
        do.DayNum,
        CASE
            WHEN fp.RxDate <= fp.DischargeDate + INTERVAL '1 day' * do.DayNum
                 AND fp.DischargeDate + INTERVAL '1 day' * do.DayNum <= fp.RxEndDate
            THEN 1
            ELSE 0
        END AS HasAntibiotic
    FROM FilteredPatients fp
    JOIN DayOffsets do ON 1=1
)
-- 转置为30列
SELECT
    PatientID,
    DischargeDate,
    MAX(CASE WHEN DayNum = 1 THEN HasAntibiotic END) AS Day1,
    MAX(CASE WHEN DayNum = 2 THEN HasAntibiotic END) AS Day2,
    MAX(CASE WHEN DayNum = 3 THEN HasAntibiotic END) AS Day3,
    MAX(CASE WHEN DayNum = 4 THEN HasAntibiotic END) AS Day4,
    MAX(CASE WHEN DayNum = 5 THEN HasAntibiotic END) AS Day5,
    MAX(CASE WHEN DayNum = 6 THEN HasAntibiotic END) AS Day6,
    MAX(CASE WHEN DayNum = 7 THEN HasAntibiotic END) AS Day7,
    MAX(CASE WHEN DayNum = 8 THEN HasAntibiotic END) AS Day8,
    MAX(CASE WHEN DayNum = 9 THEN HasAntibiotic END) AS Day9,
    MAX(CASE WHEN DayNum = 10 THEN HasAntibiotic END) AS Day10,
    MAX(CASE WHEN DayNum = 11 THEN HasAntibiotic END) AS Day11,
    MAX(CASE WHEN DayNum = 12 THEN HasAntibiotic END) AS Day12,
    MAX(CASE WHEN DayNum = 13 THEN HasAntibiotic END) AS Day13,
    MAX(CASE WHEN DayNum = 14 THEN HasAntibiotic END) AS Day14,
    MAX(CASE WHEN DayNum = 15 THEN HasAntibiotic END) AS Day15,
    MAX(CASE WHEN DayNum = 16 THEN HasAntibiotic END) AS Day16,
    MAX(CASE WHEN DayNum = 17 THEN HasAntibiotic END) AS Day17,
    MAX(CASE WHEN DayNum = 18 THEN HasAntibiotic END) AS Day18,
    MAX(CASE WHEN DayNum = 19 THEN HasAntibiotic END) AS Day19,
    MAX(CASE WHEN DayNum = 20 THEN HasAntibiotic END) AS Day20,
    MAX(CASE WHEN DayNum = 21 THEN HasAntibiotic END) AS Day21,
    MAX(CASE WHEN DayNum = 22 THEN HasAntibiotic END) AS Day22,
    MAX(CASE WHEN DayNum = 23 THEN HasAntibiotic END) AS Day23,
    MAX(CASE WHEN DayNum = 24 THEN HasAntibiotic END) AS Day24,
    MAX(CASE WHEN DayNum = 25 THEN HasAntibiotic END) AS Day25,
    MAX(CASE WHEN DayNum = 26 THEN HasAntibiotic END) AS Day26,
    MAX(CASE WHEN DayNum = 27 THEN HasAntibiotic END) AS Day27,
    MAX(CASE WHEN DayNum = 28 THEN HasAntibiotic END) AS Day28,
    MAX(CASE WHEN DayNum = 29 THEN HasAntibiotic END) AS Day29,
    MAX(CASE WHEN DayNum = 30 THEN HasAntibiotic END) AS Day30
FROM DailyRecords
GROUP BY PatientID, DischargeDate;

关键优化点 #

1. 预过滤数据：通过FilteredPatients CTE提前排除出院30天后开处方的记录，减少后续关联和计算的数据量
2. 高效生成日序列：用非递归方式生成1-30天的数字序列，避免递归CTE的性能损耗
3. 索引优化：给PatientTable创建联合索引：CREATE INDEX idx_patient_discharge_rx ON PatientTable(PatientID, DischargeDate, RxDate, DaysSupply);，让过滤和关联操作直接走索引，避免全表扫描
4. 避免重复计算：在CTE中提前计算RxEndDate和出院后30天的阈值，减少查询中的重复运算

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