为时序关联事件分配通用键值：医疗场景SQL实现需求

核心思路 #

利用窗口函数计算每个事件所属的「出院分组」，再通过分组聚合获取每个区块的初始事件ID，最后关联回原表得到结果。这种基于窗口函数的集合操作效率极高，适配千万级数据集的处理需求。

实现SQL #

WITH EventGroups AS (
    SELECT
        PatientID,
        EventID,
        EventType,
        EventTime,
        -- 统计当前事件及之前的出院事件数量，作为区块分组标识
        SUM(CASE WHEN EventType = 'Discharge' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY PatientID ORDER BY EventTime, EventID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM #History
),
GroupStartIDs AS (
    SELECT
        PatientID,
        GroupID,
        MIN(EventID) AS InitialEventID
    FROM EventGroups
    GROUP BY PatientID, GroupID
)
SELECT
    h.PatientID,
    h.EventID,
    h.EventType,
    h.EventTime,
    gs.InitialEventID
FROM #History h
JOIN EventGroups eg ON h.PatientID = eg.PatientID AND h.EventID = eg.EventID
JOIN GroupStartIDs gs ON eg.PatientID = gs.PatientID AND eg.GroupID = gs.GroupID
ORDER BY h.PatientID, h.EventTime, h.EventID;

逻辑说明 #

• EventGroups CTE：按患者分组，按事件时间（搭配EventID避免时间重复导致的排序歧义）排序，通过累加窗口函数统计到当前事件为止的出院事件总数。每出现一次出院事件，GroupID会递增1，出院后的所有事件自动归属新的区块。
• GroupStartIDs CTE：对每个患者的每个区块分组，提取该组内最小的EventID作为区块的初始事件ID。
• 最终关联：将原表与两个CTE关联，为每条记录匹配对应的区块初始事件ID。

性能优化提示 #

• 建议在PatientID、EventTime、EventID上创建联合索引，窗口函数的排序操作会直接复用该索引，大幅提升千万级数据的处理速度。
• 若EventID本身是严格递增且与事件时间顺序完全一致，可仅用EventID作为排序字段，进一步简化索引结构。

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