-5

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!

O. Jones
  • 103,626
  • 17
  • 118
  • 172
KarasuRou
  • 1
  • 3
  • 2
    What Is the query? – Leandro Bardelli Apr 02 '23 at 21:59
  • 2
    Hi, should really provide details of what you have already tried, so that others can assist you to understand where you're potentially going astray. (StackOverflow is not really here to just get someone to do your work for you). Also, you may need to provide some additional detail (or your query will help explain) how you get from your example table to the output result - there's doesn't appear to be an obvious correlation – Craig Apr 02 '23 at 22:08

2 Answers2

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

user1191247
  • 10,808
  • 2
  • 22
  • 32
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.

markalex
  • 8,623
  • 2
  • 7
  • 32