Counting consecutive blocks in SQL

Question

I want to read a table with a SQL query in MySQL, which gives me the consecutive blocks of a row. The query may only last a maximum of 120 seconds, which I already exceed with my queries, I also can't manage to count consecutive numbers at all.

So if for example the table would look like this:

ID	additionalInformation	Number	anotherInformation
1	...	600	...
2	...	601	...
3	...	602	...
4	...	604	...
5	...	606	...
6	...	607	...
7	...	609	...

Should the query output the following to me:

count	amount
2	1
1	2
1	3

I have already tried procedures and subselect and also selects in Where, but nothing works for days. I don't know what to try next... Please help!

Answer 1

You can use ROW_NUMBER() to provide a continuous sequence which you can then use to find the sequence runs within the column being analysed:

SELECT COUNT(*) AS `count`, amount
FROM (
    SELECT grp, COUNT(*) AS amount
    FROM (
        SELECT *, Number - ROW_NUMBER() OVER (ORDER BY ID ASC) AS grp
        FROM tbl
    ) t1
    GROUP BY grp
) t2
GROUP BY amount;

-- IF YOUR ID RANGE IS CONTIGUOUS YOU COULD USE IT INSTEAD OF ROW_NUMBER()
-- BUT YOU SHOULD NEVER RELY ON AN AI ID BEING CONTIGUOUS
SELECT COUNT(*) AS `count`, amount
FROM (
    SELECT Number - ID AS grp, COUNT(*) AS amount
    FROM tbl
    GROUP BY grp
) t2
GROUP BY amount;

Here's a db<>fiddle showing the steps.

If there's a possibility of numbers being repeated within the Number column, you can use DENSE_RANK() instead of ROW_NUMBER().