MySQL Group by consecutive values and count

Question

I have a table that looks like this

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

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

user_5176071 · Answer

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