sql - (SQL) 每个 ID，从第一行开始，返回值 N 大于前一个返回行的所有连续行

Question

我有以下示例数据集：

ID	价值	行索引（仅供参考，最终输出中不需要存在）
一个	4	1
一个	7	2
一个	12	3
一个	12	4
一个	13	5
b	1	6
b	2	7
b	3	8
b	4	9
b	5	10

我想编写一个 SQL 脚本，它返回下一行，其值为 N 或大于先前返回的行，从每个 ID 的第一行开始，并按 [Value] 升序排列。N = 3 的决赛桌示例应如下所示：

ID	价值	行索引
一个	4	1
一个	7	2
一个	12	3
b	1	6
b	4	9

这个脚本可以用矢量化的方式编写吗？还是必须使用循环？任何建议将不胜感激。谢谢！

标签： sqlsql-servertsql

Answer 1

SQL 表表示无序集。没有“先前”值的定义，除非您有一个指定排序的列。使用这样的列，您可以使用lag()：

select t.*
from (select t.*,
             lag(value) over (partition by id order by <ordering column>) as prev_value
      from t
     ) t
where prev_value is null or prev_value <= value - 3;

编辑：

我想我误解了你想要做什么。您似乎想从每个 id 的第一行开始。然后获取值为 3 或更高的下一行。然后保持该值并获得下一个大于或等于 3 的值。等等。

您可以使用递归 CTE 在 SQL 中执行此操作：

with ts as (
      select distinct t.id, t.value, dense_rank() over (partition by id order by value) as seqnum
      from t
     ),
     cte as (
      select id, value, value as grp_value, 1 as within_seqnum, seqnum
      from ts
      where seqnum = 1
      union all
      select ts.id, ts.value, 
             (case when ts.value >= cte.grp_value + 3 then ts.value else cte.grp_value end),
             (case when ts.value >= cte.grp_value + 3 then 1 else cte.within_seqnum + 1 end),
             ts.seqnum
      from cte join
           ts
           on ts.id = cte.id and ts.seqnum = cte.seqnum + 1
     )
select *
from cte
where within_seqnum = 1
order by id, value;

这是一个 db<>fiddle。