多重复列场景下如何用LAG函数正确获取上一个encounter_group_id

原始表结构及数据 #

目标结果 #

当前使用的SQL语句 #

select  encounter_id, 
        encouter_group_id, 
        LAG(encounter_group_id) OVER (ORDER BY encntr_group_id),
        activity
from encounter_activity

当前错误结果 #

解决方案 #

当前SQL的问题是LAG函数仅取上一行的组ID，未按encounter_id分区，且同一个组内的行无法共享正确的上一个组ID。通过嵌套窗口函数可实现需求：先为每个组的首行获取上一个组ID，再将该值填充到整个组的所有行中。

修正后的SQL：

SELECT 
    encounter_id,
    encounter_group_id,
    FIRST_VALUE(prev_group) OVER (PARTITION BY encounter_id, encounter_group_id) AS prev_group_id,
    activity
FROM (
    SELECT 
        encounter_id,
        encounter_group_id,
        -- 按encounter_id分区、组ID排序，获取当前组的上一个组ID
        LAG(encounter_group_id) OVER (PARTITION BY encounter_id ORDER BY encounter_group_id) AS prev_group,
        activity
    FROM encounter_activity
) t
ORDER BY encounter_id, encounter_group_id, activity;

逻辑说明 #

1. 子查询中，PARTITION BY encounter_id确保仅在同一个encounter内计算上一个组，ORDER BY encounter_group_id保证组的顺序正确；
2. 外层查询使用FIRST_VALUE(prev_group) OVER (PARTITION BY encounter_id, encounter_group_id)，将当前组首行的上一个组ID填充到该组的所有行中，达成目标结果。

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