SQL query to partition rows into groups where lag (difference between rows) is greater than some value

user_13174372 0
Viewed 91

Suppose I have a table like

id
1
3
4
10
12
19

and I'd like to group the ids (in sorted order) into the same group if they differ by 5 or less, and a new group if they differ by 6 or more. So the output would be:

id group
1 1
3 1
4 1
10 2
12 2
19 3

Is this possible in SQL? It will be a query in Trino, and I see they have commands like lag and partition. Has anyone made a query like this that can help out?

partition trino lag group-by sql
1 Answers

You can use a cte with lead:

with cte(id, l1) as (
   select t.id, abs(coalesce(lead(t.id) over (order by t.id), 0) - t.id) < 6 from tbl t
)
select c.id, (select sum(c1.id < c.id and c1.l1 = 0) from cte c1) + 1 from cte c
user_73267380
Related