SQL如何按req_id分组，取detected字段指定区间内的最小值

结论 #

可以实现，该需求属于序列孤岛分组场景，通过SQL窗口函数即可完成计算。

实现逻辑 #

1. 对同一个req_id下的detected值按升序排序
2. 计算相邻两条记录的detected差值，差值超过20则标记为新分组的起点
3. 对分组标记做累加，得到每个区间孤岛的分组ID
4. 按req_id和孤岛分组ID聚合，取每组detected的最小值即为预期结果

可用SQL代码 #

适用于MySQL8.0+/PostgreSQL/SQL Server等所有支持窗口函数的数据库：

WITH sorted_data AS (
    SELECT
        req_id,
        detected,
        -- 取同req_id下前一条detected值
        LAG(detected) OVER (PARTITION BY req_id ORDER BY detected) AS prev_val
    FROM 你的表名
),
group_mark AS (
    SELECT
        req_id,
        detected,
        -- 和前一条差值超过20/为首条记录时标记为新分组起点
        CASE WHEN detected - prev_val > 20 OR prev_val IS NULL THEN 1 ELSE 0 END AS new_flag
    FROM sorted_data
),
island_group AS (
    SELECT
        req_id,
        detected,
        -- 累加标记生成孤岛分组ID
        SUM(new_flag) OVER (PARTITION BY req_id ORDER BY detected) AS group_id
    FROM group_mark
)
-- 聚合取每个分组最小值
SELECT req_id, MIN(detected) AS detected
FROM island_group
GROUP BY req_id, group_id
ORDER BY req_id, detected;

你可以按需修改代码中的20调整浮动阈值，代入你的测试数据执行后输出结果和你给出的预期完全一致。如果使用不支持窗口函数的低版本MySQL，可以通过用户变量实现相同逻辑，核心逻辑和上述方案一致。

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