Finding Consecutive Streak and Display Count

Question

I have a MySQL table that shows the following:

ID              DATE    FREQUENCY
--        ----------    ---------
 1        2017-08-01            1
 2        2017-08-02            1
 3        2017-08-03            0
 4        2017-08-04            1
 5        2017-08-05            1
 6        2017-08-06            1

I am trying to get the easiest way to group every time there are consecutive 1's on the frequency column. Then I would like to display them.

Example

2 (There are 2 consecutive 1's)
3 (There are also 3 consecutive 1's)

Thank you

I would try to find 'a way' first, and then look to to see if there's a simpler method. But in general, this is a winning streak pattern — Strawberry, Aug 06 '17 at 08:07
if you are sure of the sequences, you can select all record ids that have frequency 0 (best to use a separate column where you set and control the id, in addition to any autoincrement PK field). Then just subtract. — inarilo, Aug 06 '17 at 08:08
Yes I know its a winning streak pattern. I just can't seem to get the answer. I know its probably an easy solution — user8423855, Aug 06 '17 at 08:20
Can you work with MariaDB 10 or MySQL 8? (i.e.: databases that implement `WINDOW` functions). — joanolo, Aug 06 '17 at 09:23
Man, you have a detailed step-by-step explanation here: https://stackoverflow.com/a/11541494/842935 Post your progress. — dani herrera, Aug 06 '17 at 09:41

score 0 · Answer 1 · answered Oct 24 '19 at 00:18

This is a typical gaps-and-island problem.

You can solve it by comparing the overal rank of records to their relative ranks in groups of records having the same frequency. The difference between the ranks gives you the group each record belongs to.

The rest is just filtering and aggregating groups that have a frequency of 1.

Query:

select 
    min(id) min_id,
    max(id) max_id,
    min(date) min_date,
    max(date) max_date,
    count(*) streak_length
from (
    select 
        t.*,
        row_number() over(order by date) rn1,
        row_number() over(partition by frequency order by date) rn2
    from mytable t
) t
where frequency = 1
group by rn1 - rn2
order by min_date

Demo on DB Fiddle with your sample data:

min_id | max_id | min_date   | max_date   | streak_length
-----: | -----: | :--------- | :--------- | ------------:
     1 |      2 | 2017-08-01 | 2017-08-02 |             2
     4 |      6 | 2017-08-04 | 2017-08-06 |             3

Note: window function row_number() is available starting MySQL 8.0.

Finding Consecutive Streak and Display Count

1 Answers1