如何在SQL窗口函数中实现时间范围分组及间隔重复分组？

要实现连续出现的相同Part归为同一组，被其他Part打断后再次出现则归为新组的需求，可以通过以下步骤实现：

核心思路 #

通过对比当前行与前一行的Part值，标记分组的起始点，再累加起始点标记得到唯一的分组ID。

完整SQL语句 #

SELECT 
    Part,
    time,
    SUM(CASE WHEN prev_part != Part OR prev_part IS NULL THEN 1 ELSE 0 END) 
        OVER(ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS expected_result
FROM (
    SELECT 
        Part,
        time,
        LAG(Part) OVER(ORDER BY time) AS prev_part
    FROM your_table_name
) AS subquery
ORDER BY time;

步骤拆解 #

1. 获取前一行的Part值
   用LAG(Part) OVER(ORDER BY time)函数，按时间排序后，获取当前行的上一行Part值，记为prev_part。第一行的prev_part为NULL，这是第一个分组的起始点。

2. 标记分组起始点
   通过CASE语句判断：

   • 如果prev_part为NULL，或者当前Part与prev_part不同，说明是新分组的开始，标记为1
   • 否则标记为0

3. 累加标记生成分组ID
   用SUM() OVER(ORDER BY time ...)窗口函数，按时间顺序累加起始点标记，得到连续递增的分组ID，也就是预期的expected_result。

示例验证 #

运行上述SQL后，会完全匹配你给出的预期结果：

• 前两行Part=a连续，分组ID为1
• 第三行Part=b为新分组，ID为2
• 第四、五行Part=c连续，ID为3
• 最后四行Part=b被c打断后再次出现，属于新分组，ID为4

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