SQL如何按ID分组筛选值比上一返回行大等于N的行？

核心结论 #

该需求完全可以通过向量化SQL实现，不需要编写循环逻辑，目前主流的关系型数据库和分布式SQL引擎都支持原生的向量化算子完成该计算。

需求本质说明 #

这个需求属于组内带阈值的跳跃取数场景，要求筛选的行依赖前一行被选中的值，以前受限于SQL能力可能需要用游标循环实现，但现在借助递归CTE或者数组高阶函数可以纯SQL向量化完成。

具体实现方案 #

方案1：递归CTE实现（适配MySQL 8.0+、PostgreSQL、SQL Server等主流关系型数据库） #

首先对每组按Value升序排序加行号，再通过递归每次找到同组下一个满足阈值的行即可，代码如下（示例中N=3）：

WITH sorted_data AS (
    -- 第一步：按ID分组，组内按Value升序排序并生成行号
    SELECT 
        ID,
        Value,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value ASC) AS rn
    FROM 你的表名
),
RECURSIVE filtered_data AS (
    -- 锚点节点：取每个分组的第一行作为初始选中行
    SELECT 
        ID,
        Value,
        rn,
        Value AS last_kept_val
    FROM sorted_data
    WHERE rn = 1

    UNION ALL

    -- 递归节点：每次找到同组下一个满足差值>=N的行
    SELECT 
        s.ID,
        s.Value,
        s.rn,
        s.Value AS last_kept_val
    FROM filtered_data f
    INNER JOIN sorted_data s
        ON s.ID = f.ID
        AND s.rn > f.rn
        AND s.Value >= f.last_kept_val + 3 -- 此处3为参数N，可替换为任意值
    -- 保证只取第一个满足条件的行，避免重复匹配
    WHERE NOT EXISTS (
        SELECT 1 FROM sorted_data s2
        WHERE s2.ID = f.ID
        AND s2.rn > f.rn
        AND s2.rn < s.rn
        AND s2.Value >= f.last_kept_val + 3
    )
)
-- 输出最终结果
SELECT ID, Value FROM filtered_data ORDER BY ID, Value;

该方案适配性强，只要数据库支持递归CTE即可使用，执行时由数据库引擎自动优化为向量化运算，效率远高于手动写的循环游标。

方案2：数组高阶函数实现（适配Spark SQL、Hive 2.3+、Presto等分布式大数据引擎） #

针对大数据量场景，可以用分组聚合+数组遍历的方式实现，完全没有递归深度限制，性能更高：

WITH grouped_data AS (
    -- 按ID分组，组内Value排序后生成数组
    SELECT 
        ID,
        ARRAY_SORT(COLLECT_LIST(Value)) AS sorted_vals
    FROM 你的表名
    GROUP BY ID
),
filtered_data AS (
    -- 遍历排序后的数组，只保留满足差值>=N的元素
    SELECT 
        ID,
        REDUCE(
            sorted_vals,
            CAST(ARRAY() AS ARRAY<INT>),
            (acc, curr) -> IF(
                SIZE(acc) = 0 OR curr >= acc[SIZE(acc)-1] + 3, -- 此处3为参数N
                ARRAY_APPEND(acc, curr),
                acc
            ),
            acc -> acc
        ) AS kept_vals
    FROM grouped_data
)
-- 数组炸开得到最终行级结果
SELECT ID, val 
FROM filtered_data
LATERAL VIEW EXPLODE(kept_vals) tmp AS val
ORDER BY ID, val;

该方案基于纯向量化的数组运算实现，没有递归的额外开销，适合处理百万级以上的大规模数据集。

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