如何用LAG、FIRST_VALUE等函数基于有序查询结果构建分组？

嘿，我来帮你搞定这个分组需求！先把你的原始查询结果整理成清晰的表格：

从数据来看，我们需要把连续相同val1的行划分为同一个分组，这类场景用LAG结合累加的方式最直接，如果你还需要获取分组的起始信息，FIRST_VALUE也能派上用场，下面给你两种实现方案：

方案1：用LAG标记变化点，生成分组ID #

核心思路是：先判断当前行的val1和上一行是否不同，标记变化点；然后对变化点做累加，得到唯一的分组ID。

SELECT 
    val1, val2, val3, id,
    -- 累加变化标记，生成连续分组的ID
    SUM(change_flag) OVER (ORDER BY id) AS group_id
FROM (
    SELECT 
        val1, val2, val3, id,
        -- 第一行没有上一行，直接标记为变化；后续行和上一行val1不同则标记为1
        CASE 
            WHEN LAG(val1) OVER (ORDER BY id) != val1 
                OR LAG(val1) OVER (ORDER BY id) IS NULL 
            THEN 1 
            ELSE 0 
        END AS change_flag
    FROM your_table_name -- 替换成你的实际表名
) t
ORDER BY id;

执行后会得到这样的结果，每个连续相同val1的块都有了统一的group_id：

方案2：结合FIRST_VALUE获取分组起始信息 #

如果需要每个分组的第一个ID或者第一个时间戳，可以在生成分组ID后，用FIRST_VALUE来提取这些信息：

WITH grouped_data AS (
    SELECT 
        val1, val2, val3, id,
        SUM(change_flag) OVER (ORDER BY id) AS group_id
    FROM (
        SELECT 
            val1, val2, val3, id,
            CASE 
                WHEN LAG(val1) OVER (ORDER BY id) != val1 
                    OR LAG(val1) OVER (ORDER BY id) IS NULL 
                THEN 1 
                ELSE 0 
            END AS change_flag
        FROM your_table_name
    ) t
)
SELECT 
    *,
    -- 获取当前分组的第一个ID
    FIRST_VALUE(id) OVER (PARTITION BY group_id ORDER BY id) AS group_first_id,
    -- 获取当前分组的第一个时间戳
    FIRST_VALUE(val3) OVER (PARTITION BY group_id ORDER BY id) AS group_first_time
FROM grouped_data
ORDER BY id;

关键逻辑说明 #

• LAG(val1) OVER (ORDER BY id)：按id排序，拿到当前行的上一行val1，用来判断是否触发分组变化。
• SUM(change_flag) OVER (ORDER BY id)：对变化标记做累加，每次遇到变化点（change_flag=1），分组ID就会递增，自然把连续相同val1的行归为一组。
• FIRST_VALUE：在每个分组内，按id排序后取第一个值，方便后续对分组做统计、溯源等操作。

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