SQL间隔与岛屿（Gaps and Islands）在3行数据下失效问题求解

原始数据 #

SELECT * FROM foobar;

执行结果：

id | quantity |    time
----+----------+------------
  1 |       50 | 2022-01-01
  2 |      100 | 2022-01-02
  3 |       50 | 2022-01-03
  4 |       50 | 2022-01-04

需求 #

• 每次quantity变化时创建新分组
• 连续相同的quantity合并到同一分组

预期结果 #

id | quantity |    time    | group_id
----+----------+------------+----------
  1 |       50 | 2022-01-01 |        1
  2 |      100 | 2022-01-02 |        2
  3 |       50 | 2022-01-03 |        3
  4 |       50 | 2022-01-04 |        3

原方法失效原因 #

你尝试的ROW_NUMBER()差值法，核心是用global_rank - qty_counter作为分组标识，但这种方法仅适用于同一quantity的行不会被其他quantity打断的场景。当相同quantity非连续出现时（比如示例中第1行和第3-4行的50被100打断），不同岛屿的行会得到相同的差值，导致错误合并。

正确解决方案 #

使用LAG()函数获取前一行的quantity，判断当前行与前一行是否不同；若不同则标记为新分组的起点，最后用累加窗口函数生成连续的group_id。

完整SQL查询：

SELECT
    id,
    quantity,
    time,
    SUM(is_new_group) OVER (ORDER BY time) AS group_id
FROM (
    SELECT
        *,
        CASE
            WHEN LAG(quantity) OVER (ORDER BY time) != quantity THEN 1
            WHEN LAG(quantity) OVER (ORDER BY time) IS NULL THEN 1
            ELSE 0
        END AS is_new_group
    FROM foobar
) AS subquery;

逻辑说明 #

1. 子查询中：
   • LAG(quantity) OVER (ORDER BY time)获取按时间排序的前一行quantity
   • CASE语句：第一行（无前置行）或当前行与前一行quantity不同时，标记is_new_group = 1（新分组起点），否则为0
2. 外层查询：
   • SUM(is_new_group) OVER (ORDER BY time)按时间累加is_new_group值，生成连续的group_id，每次遇到新分组起点时累加1，同一连续岛屿的行共享同一个group_id

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