按Account Number分区填充列前值及Protocol2列写入ACS的SQL问题咨询

• 需在Protocol2列中填充对应"ACS"值
• 需按Account Number分区，为Monitoring列填充同分组的上一条记录值

参考表格：

1. Drip2 CTE的GROUP BY同时包含AccountNumber和DateTime，导致聚合得到的MinInfusionDateTime、MaxInfusionDateTime和原DateTime值完全一致，没有实际聚合意义，且和HeparinDrip关联时会出现不必要的重复行
2. 现有CASE语句仅为满足AdminID、InfusionSeqID、Rate条件的行赋值Protocol2，其余行该字段为空，没有实现同账号下全量填充
3. 未对Monitoring字段实现取同账号上一条记录的逻辑

-- 先修正Drip2的聚合逻辑，按账号维度聚合最小/最大输液时间，若不需要这两个字段可直接删除该CTE
Drip2 as (
    Select
        AccountNumber,
        min(DateTime) as MinInfusionDateTime,
        max(DateTime) as MaxInfusionDateTime
    from
        HeparinDrip
    group by
        AccountNumber -- 移除原错误的DateTime分组字段
),
Drip3 as (
    Select
        Name,
        h1.AccountNumber,
        RxNumber,
        DateTime,
        -- 取同账号按时间排序的上一条Monitoring值，首行无前置数据时返回NULL，可自行用COALESCE设置默认值
        LAG(Monitoring) OVER (PARTITION BY h1.AccountNumber ORDER BY DateTime) as Monitoring,
        Rate,
        Bolus,
        -- 先按规则生成对应协议值，再用窗口函数把值填充到同账号所有行
        MAX(
            Case 
                when AdminID = '1' and InfusionSeqID = '1' and Rate = '12' then 'ACS' 
                when AdminID = '1' and InfusionSeqID = '1' and Rate = '18' then 'VTE' 
                Else '' 
            end
        ) OVER (PARTITION BY h1.AccountNumber) as Protocol2,
        AdminID,
        InfusionSeqID,
        h2.MinInfusionDateTime,
        h2.MaxInfusionDateTime
    from
        HeparinDrip h1
        inner join Drip2 h2 on h1.AccountNumber = h2.AccountNumber
)

• Protocol2字段：先用原有CASE规则判断出对应协议值，再通过MAX()窗口函数按账号分区，把非空的协议值同步到同账号的所有行
• Monitoring字段：通过LAG()窗口函数按账号分区、按时间升序排序，直接取当前行的上一行Monitoring值

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