Нахождение последовательной полосы и количества показов

У меня есть таблица MySQL, которая показывает следующее:

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

Я пытаюсь получить самый простой способ группировки каждый раз, когда в столбце частоты есть последовательные 1. Затем я хотел бы показать их.

Пример

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

Спасибо


mysql php gaps-and-islands
person user8423855    schedule 06.08.2017    source источник
comment
Я бы попытался сначала найти «способ», а затем посмотреть, есть ли более простой метод. Но в целом это выигрышная серия   -  person Strawberry    schedule 06.08.2017
comment
если вы уверены в последовательностях, вы можете выбрать все идентификаторы записей с частотой 0 (лучше всего использовать отдельный столбец, в котором вы устанавливаете и контролируете идентификатор, в дополнение к любому полю автоинкремента PK). Тогда просто вычти.   -  person inarilo    schedule 06.08.2017
comment
Да, я знаю, что это выигрышная серия. Я просто не могу получить ответ. Я знаю, что это, вероятно, простое решение   -  person user8423855    schedule 06.08.2017
comment
Можете ли вы работать с MariaDB 10 или MySQL 8? (т. е. базы данных, реализующие WINDOW функции).   -  person joanolo    schedule 06.08.2017
comment
Человек, у вас есть подробное пошаговое объяснение здесь: stackoverflow.com/a/11541494/842935 Опубликуйте свой прогресс .   -  person dani herrera    schedule 06.08.2017

Ответы (1)


arrow_upward
0
arrow_downward

Это типичная проблема пробелов и островов.

Вы можете решить эту проблему, сравнив общий ранг записей с их относительными рангами в группах записей, имеющих одинаковую частоту. Разница между рангами дает вам группу, к которой принадлежит каждая запись.

Остальное — просто фильтрация и агрегирование групп с частотой 1.

Запрос:

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

Демо на DB Fiddle с вашими примерами данных:

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

Примечание: оконная функция row_number() доступна, начиная с MySQL 8.0.

person GMB    schedule 24.10.2019