如何按sign分组连续记录，统计日期范围、次数及总值？

需求说明 #

按sign字段对连续出现的记录分组，每组需展示：

• 该sign的日期范围（起始日期-结束日期）
• 记录出现次数
• Value字段的总和
  若sign被其他sign中断，需单独作为一组统计。

原始数据 #

date         sign    Value
2022-10-01    C    6.00
2022-10-02    C    6.00
2022-10-03    C    6.00
2022-10-04    W    8.00
2022-10-05    W    8.00
2022-10-06    W    8.00
2022-10-07    W    8.00
2022-10-10    C    8.00
2022-10-11    C    8.00
2022-10-12    C    8.00
2022-10-26    B    8.00
2022-10-27    B    8.00
2022-10-28    Y    8.00

理想输出 #

C 2022-10-01 - 2022-10-03  3   18
W 2022-10-04 - 2022-10-07  4   32
C 2022-10-10 - 2022-10-12  3   24
B 2022-10-26 - 2022-10-27  2   16
Y 2022-10-28 - 2022-10-28  1   8

解决方案（SQL） #

这是典型的间隙与孤岛问题，通过窗口函数生成分组ID区分连续的同sign组：

SELECT
    sign,
    CONCAT(MIN(date), ' - ', MAX(date)) AS date_range,
    COUNT(*) AS occurrence_count,
    SUM(Value) AS total_value
FROM (
    SELECT
        date,
        sign,
        Value,
        -- 全局行号（按日期排序）
        ROW_NUMBER() OVER (ORDER BY date) AS global_row,
        -- 同sign内的行号（按日期排序）
        ROW_NUMBER() OVER (PARTITION BY sign ORDER BY date) AS sign_row,
        -- 差值相同则为连续同sign组
        ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY sign ORDER BY date) AS group_id
    FROM your_table_name -- 替换为你的实际表名
) t
GROUP BY sign, group_id
ORDER BY MIN(date);

逻辑说明 #

1. 内层查询中，global_row是所有记录按日期排序的全局行号，sign_row是每个sign分组内按日期排序的行号。
2. 两个行号的差值group_id会将连续的同sign记录归为同一组：连续的同sign记录，全局行号和组内行号增长同步，差值固定；当sign切换时，差值发生变化，形成新分组。
3. 外层查询按sign和group_id分组，聚合计算每组的日期范围、出现次数和Value总和，最后按起始日期排序即可得到理想结果。

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