MySQL Group by consecutive values and count

Question

I have a table that looks like this

id
1
2
4
5
6
10
11

So a bunch of consecutive values, an unknown number of absent fields and then other consecutive values. What I am trying to achieve is to get

id	stint
1	0
2	0
4	1
5	1
6	1
10	2
11	2

By incrementing every time the number of the stint, which I can later use for summing over other columns. Is it possible? Thanks

Answer 1

If your MySQL version support window function.

You can try to use LAG window function in subquery to get previous id column, then use SUM condition aggregate window function.

Query #1

SELECT Id,
       SUM(id - n_Id > 1) OVER(ORDER BY id) stint
FROM (
    SELECT *,LAG(id,1,id) OVER(ORDER BY id) n_Id 
    FROM T
) t1

Id	stint
1	0
2	0
4	1
5	1
6	1
10	2
11	2

---

View on DB Fiddle