基于首个事件将数据序列分组为可变宽度窗口的SQL求解问题

实现思路 #

你需要的是按非重叠的动态固定宽度窗口分组聚合，核心是先确定每个窗口的起始key，再将数据匹配到对应窗口后求和即可，主流支持窗口函数/CTE的数据库（MySQL 8.0+, PostgreSQL, SQL Server等）都可以实现。

实现方案1：递归CTE实现（兼容性好，逻辑清晰） #

首先你可以通过递归CTE自动生成所有窗口的起始key，再关联原表做分组聚合，示例代码如下（将your_table替换为你的实际表名，N可以自定义）：

-- 定义窗口宽度N，示例为3
SET @N = 3;

WITH RECURSIVE group_starts AS (
    -- 第一个窗口的起点是全表最小的key
    SELECT MIN(`key`) AS start_key
    FROM your_table
    UNION ALL
    -- 后续每个窗口的起点是第一个大于等于上一个窗口终点的key
    SELECT MIN(t.`key`) AS start_key
    FROM your_table t
    JOIN group_starts gs ON t.`key` >= gs.start_key + @N
)
-- 关联原表按窗口起始key分组求和
SELECT
    gs.start_key AS `key`,
    SUM(t.val) AS sum_of_values
FROM your_table t
JOIN group_starts gs ON t.`key` >= gs.start_key AND t.`key` < gs.start_key + @N
GROUP BY gs.start_key
ORDER BY gs.start_key;

运行上述代码后输出结果和你给出的示例完全一致。

实现方案2：会话变量实现（无需递归，适合大表低版本数据库） #

如果你的数据库不支持递归CTE，也可以用会话变量遍历排序后的key打分组标记再聚合，示例代码如下：

SET @N = 3;
SET @current_start = NULL;
SET @group_id = 0;

SELECT
    MIN(`key`) AS `key`,
    SUM(val) AS sum_of_values
FROM (
    SELECT
        `key`,
        val,
        -- 超出上一个窗口范围则新建分组
        @group_id := IF(@current_start IS NULL OR `key` >= @current_start + @N, @group_id + 1, @group_id) AS group_id,
        -- 更新当前窗口的起始key
        @current_start := IF(@current_start IS NULL OR `key` >= @current_start + @N, `key`, @current_start) AS current_start
    FROM your_table
    ORDER BY `key`
) t
GROUP BY group_id
ORDER BY group_id;

性能优化建议 #

如果数据量较大，建议给key字段建立索引，可以大幅降低窗口起始值查找和关联的耗时。

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