Question 20222 in Mysql Question posted in
The official MySQL documentation can be found here.

Counting consecutive blocks in SQL – Mysql

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!

Tags:

2

Answers


  1. 0

    You can use something like this:

    select
    count(*),
    cc
from (
        select count(*) cc
        from (
                select *, sum(part_) over (order by id) part_id
                from (
                        select *, if (num_ = 1 + lag (num_, 1, num_) over (order by id), 0, 1) part_
                        from my_table
                    ) t2
            ) t3
        group by part_id
    ) t4
group by cc

    Here:

    • t2: calculates if row differs from previous more than by 1,
    • t3: creates unique partition id for every continuous sequence,
    • t4: counts size of partitions,
    • outer: counts number of sequences with sizes.

    Demo with step by step wrapping of queries here.

    Login or Signup to reply.
  2. 0

    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().

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search