SQL Server 2016基于模糊条件合并患者就诊记录为就医旅程

核心思路 #

通过分组连续就诊区间的方式，先给每个患者的就诊记录按入院日期排序，判断当前就诊与前一次就诊的日期是否相近，将同一段连续旅程的记录标记为同一组，再对每组进行聚合或列转行处理，得到每行代表一段就医旅程的结果。

步骤1：定义日期相近阈值 #

首先确定“日期相近”的业务标准，比如出院后7天内入院算同一段旅程，用变量存储这个阈值：

DECLARE @Threshold INT = 7; -- 根据实际业务调整阈值，如出院后3天内入院则设为3
DECLARE @TableName NVARCHAR(128) = 'YourTableName'; -- 替换为你的实际表名

步骤2：分组标记就医旅程 #

使用窗口函数生成患者就诊排序，再通过累加判断结果生成旅程组ID（trip_id），同一旅程的记录会拥有相同的trip_id：

WITH RankedVisits AS (
    -- 按患者分组，按入院日期排序，生成就诊序号
    SELECT 
        pID,
        vID,
        vdStart,
        vdEnd,
        ROW_NUMBER() OVER (PARTITION BY pID ORDER BY vdStart) AS visit_seq
    FROM @TableName
),
TripGrouped AS (
    -- 生成旅程组ID：第一条记录直接新建组；后续记录若入院日期<=前次出院日期+阈值，归为同组，否则新建组
    SELECT 
        pID,
        vID,
        vdStart,
        vdEnd,
        visit_seq,
        SUM(
            CASE 
                WHEN visit_seq = 1 THEN 1 
                ELSE CASE WHEN vdStart <= LAG(vdEnd) OVER (PARTITION BY pID ORDER BY visit_seq) + @Threshold THEN 0 ELSE 1 END
            END
        ) OVER (PARTITION BY pID ORDER BY visit_seq) AS trip_id
    FROM RankedVisits
)

步骤3：聚合旅程记录（两种可选方式） #

方式1：拼接就诊信息（适合就诊数不固定的场景） #

将同一旅程的就诊ID、日期范围拼接成字符串，同时计算旅程的最早开始和最晚结束日期：

SELECT 
    pID,
    trip_id,
    MIN(vdStart) AS trip_start_date, -- 旅程的最早入院日期
    MAX(vdEnd) AS trip_end_date, -- 旅程的最晚出院日期
    -- 拼接该旅程的所有就诊ID
    STUFF(
        (SELECT ', ' + CAST(vID AS VARCHAR(50)) 
         FROM TripGrouped t2 
         WHERE t2.pID = t1.pID AND t2.trip_id = t1.trip_id 
         ORDER BY t2.visit_seq
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS linked_visit_ids,
    -- 拼接每个就诊的日期范围
    STUFF(
        (SELECT ', ' + CAST(vdStart AS VARCHAR(50)) + '~' + CAST(vdEnd AS VARCHAR(50)) 
         FROM TripGrouped t2 
         WHERE t2.pID = t1.pID AND t2.trip_id = t1.trip_id 
         ORDER BY t2.visit_seq
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS visit_date_ranges
FROM TripGrouped t1
GROUP BY pID, trip_id
ORDER BY pID, trip_start_date;

方式2：列转行生成多列（适合单独列展示每段就诊的场景） #

如果需要将旅程内的每一次就诊单独列为一列（如vID1、vdStart1等），使用动态SQL自动生成对应列：

-- 获取每个旅程内的最大就诊数
DECLARE @MaxVisitCount INT;
SELECT @MaxVisitCount = MAX(trip_visit_seq)
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY pID, trip_id ORDER BY visit_seq) AS trip_visit_seq
    FROM TripGrouped
) t;

-- 动态生成列定义
DECLARE @ColumnDefs NVARCHAR(MAX) = '';
DECLARE @i INT = 1;
WHILE @i <= @MaxVisitCount
BEGIN
    SET @ColumnDefs += CONCAT(
        'MAX(CASE WHEN trip_visit_seq = ', @i, ' THEN vID END) AS vID', @i, ',',
        'MAX(CASE WHEN trip_visit_seq = ', @i, ' THEN vdStart END) AS vdStart', @i, ',',
        'MAX(CASE WHEN trip_visit_seq = ', @i, ' THEN vdEnd END) AS vdEnd', @i, ','
    );
    SET @i += 1;
END;
SET @ColumnDefs = LEFT(@ColumnDefs, LEN(@ColumnDefs) - 1); -- 移除最后一个逗号

-- 生成并执行最终SQL
DECLARE @FinalSQL NVARCHAR(MAX) = CONCAT('
WITH RankedVisits AS (
    SELECT 
        pID,
        vID,
        vdStart,
        vdEnd,
        ROW_NUMBER() OVER (PARTITION BY pID ORDER BY vdStart) AS visit_seq
    FROM ', @TableName, '
),
TripGrouped AS (
    SELECT 
        pID,
        vID,
        vdStart,
        vdEnd,
        visit_seq,
        SUM(
            CASE 
                WHEN visit_seq = 1 THEN 1
                ELSE CASE WHEN vdStart <= LAG(vdEnd) OVER (PARTITION BY pID ORDER BY visit_seq) + ', @Threshold, ' THEN 0 ELSE 1 END
            END
        ) OVER (PARTITION BY pID ORDER BY visit_seq) AS trip_id
    FROM RankedVisits
),
TripVisitSequenced AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY pID, trip_id ORDER BY visit_seq) AS trip_visit_seq
    FROM TripGrouped
)
SELECT 
    pID,
    trip_id,
    MIN(vdStart) AS trip_start_date,
    MAX(vdEnd) AS trip_end_date,
    ', @ColumnDefs, '
FROM TripVisitSequenced
GROUP BY pID, trip_id
ORDER BY pID, trip_start_date;
');

EXEC sp_executesql @FinalSQL;

性能优化建议 #

为确保查询耗时不超过10分钟，建议在表上创建以下索引，加速窗口函数的分组和排序操作：

CREATE NONCLUSTERED INDEX IX_PatientVisits_PID_StartDate 
ON YourTableName (pID, vdStart) 
INCLUDE (vID, vdEnd);

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