基于数值跳变重置的SQL表最小值查询需求

需求分析 #

我们需要将example_table的numbers列按规则划分区间：当当前行的numbers与当前区间最小值的差值超过100时，重置区间并以当前行的numbers作为新区间的最小值。最终要获取最后一个区间的最小值（本例预期结果为610）。

SQL实现 #

场景1：ID为连续递增数值 #

如果表中ID是连续的整数，可以直接使用递归CTE遍历每行并跟踪区间最小值：

WITH RECURSIVE interval_tracker AS (
    -- 初始化：取第一行数据，当前区间最小值为自身，分组ID为1
    SELECT 
        ID, 
        numbers, 
        numbers AS current_interval_min, 
        1 AS group_id
    FROM example_table
    WHERE ID = (SELECT MIN(ID) FROM example_table)
    
    UNION ALL
    
    -- 递归遍历后续行，判断是否需要重置区间
    SELECT 
        e.ID, 
        e.numbers, 
        -- 若当前数值与当前区间最小值差超过100，重置区间最小值为当前数值；否则保持原最小值
        CASE WHEN e.numbers - it.current_interval_min > 100 
             THEN e.numbers 
             ELSE it.current_interval_min 
        END AS current_interval_min,
        -- 重置区间时分组ID+1，否则保持原分组ID
        CASE WHEN e.numbers - it.current_interval_min > 100 
             THEN it.group_id + 1 
             ELSE it.group_id 
        END AS group_id
    FROM example_table e
    JOIN interval_tracker it ON e.ID = it.ID + 1
)
-- 获取最后一个分组的区间最小值
SELECT current_interval_min AS last_interval_min
FROM interval_tracker
WHERE group_id = (SELECT MAX(group_id) FROM interval_tracker);

场景2：ID不连续（通用方案） #

如果ID可能存在断号，先通过ROW_NUMBER()生成连续序号，再用递归CTE处理：

WITH numbered_rows AS (
    -- 给每行按ID排序生成连续序号
    SELECT 
        ID, 
        numbers, 
        ROW_NUMBER() OVER(ORDER BY ID) AS row_num
    FROM example_table
),
RECURSIVE interval_tracker AS (
    -- 初始化：取第一行数据
    SELECT 
        row_num, 
        numbers, 
        numbers AS current_interval_min, 
        1 AS group_id
    FROM numbered_rows
    WHERE row_num = 1
    
    UNION ALL
    
    -- 递归遍历后续行
    SELECT 
        nr.row_num, 
        nr.numbers, 
        CASE WHEN nr.numbers - it.current_interval_min > 100 
             THEN nr.numbers 
             ELSE it.current_interval_min 
        END AS current_interval_min,
        CASE WHEN nr.numbers - it.current_interval_min > 100 
             THEN it.group_id + 1 
             ELSE it.group_id 
        END AS group_id
    FROM numbered_rows nr
    JOIN interval_tracker it ON nr.row_num = it.row_num + 1
)
-- 获取最后一个分组的区间最小值
SELECT current_interval_min AS last_interval_min
FROM interval_tracker
WHERE group_id = (SELECT MAX(group_id) FROM interval_tracker);

逻辑说明 #

1. 递归初始化：从表中第一行数据开始，将其numbers作为第一个区间的最小值，分组ID设为1。
2. 递归遍历：对每一行后续数据，判断当前numbers与当前区间最小值的差值是否超过100：
   • 若超过100：重置区间，将当前numbers设为新区间的最小值，分组ID加1。
   • 若未超过：保持当前区间的最小值和分组ID不变。
3. 结果提取：找到最大的分组ID对应的区间最小值，即为最后一个区间的最小值。

内容的提问来源于stack exchange，提问作者Mr.Database