按时间未中断的连续xvalue分组聚合数据的SQL需求

这个需求在时序数据处理里很典型——我们需要按「连续未中断日期内的相同xvalue」分组，而不是把所有相同xvalue的行一概合并。普通的GROUP BY xvalue会忽略时间连续性，所以得借助窗口函数来识别连续的分组块。

核心思路 #

1. 先按日期排序，识别每一行是否属于一个新的连续分组（即当前行的xvalue和前一行是否不同）
2. 给每个连续的相同xvalue组分配一个唯一的分组ID
3. 最后按这个分组ID聚合计算所需的结果（比如最小日期、yvalue总和）

具体SQL实现 #

支持窗口函数的数据库（PostgreSQL、MySQL 8.0+、SQL Server等） #

用LAG()窗口函数获取前一行的xvalue，再通过累计求和生成分组ID：

WITH grouped_data AS (
    SELECT
        date,
        xvalue,
        yvalue,
        -- 标记新分组的开始：当前xvalue和前一行不同时记1，否则0，累计求和得到分组ID
        SUM(CASE WHEN xvalue = LAG(xvalue) OVER (ORDER BY date) THEN 0 ELSE 1 END)
            OVER (ORDER BY date) AS group_id
    FROM t
)
SELECT
    MIN(date) AS start_date,
    xvalue,
    SUM(yvalue) AS total_y
FROM grouped_data
GROUP BY group_id, xvalue
ORDER BY start_date;

老版本MySQL（不支持窗口函数） #

用用户变量来模拟分组ID的生成：

SELECT
    MIN(date) AS start_date,
    xvalue,
    SUM(yvalue) AS total_y
FROM (
    SELECT
        date,
        xvalue,
        yvalue,
        -- 当xvalue和上一行相同时，沿用当前group_id，否则group_id+1
        @group_id := IF(@prev_x = xvalue, @group_id, @group_id + 1) AS group_id,
        @prev_x := xvalue
    FROM t, (SELECT @group_id := 0, @prev_x := NULL) AS init_vars
    ORDER BY date
) AS grouped_data
GROUP BY group_id, xvalue
ORDER BY start_date;

结果验证 #

针对你给出的示例数据，运行上述代码后会得到完全符合预期的结果：

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