Посчитать количество подряд идущих одинаковых записей SQL

Рейтинг: 1Ответов: 1Опубликовано: 07.05.2023
Date Time Check
2023-04-07 11:00 true
2023-04-07 12:00 true
2023-04-07 13:00 false
2023-04-07 14:00 false
2023-04-07 15:00 true

Из такой таблицы нужно получить следующую:

Date Time Check Count
2023-04-07 11:00 true 1
2023-04-07 12:00 true 2
2023-04-07 13:00 false 1
2023-04-07 14:00 false 2
2023-04-07 15:00 true 1

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

Date Check Count
2023-04-07 true 2
2023-04-07 false 2

В стартовой таблице идёт сортировка по дате времени и чеку. Если сделаю ROW_NUMBERS() OVER (PARTITION BY DATE) AS Count, получу в итоге просто 1, 2, 3, 4, 5 в 4й колонке.

Если PARTITION BY DATE, Check, то он отсортирует по дате, а потом по чеку, в итоге получу тоже не то, что нужно. Как сделать, чтобы сортировка была по дате и времени, но при этом подсчет одинаковых подряд записей шёл по дате и чеку?

sql postgresql
Источник: Stack Overflow на русском

Ответы

▲ 2
select date, time, "Check", row_number() over(partition by date, grp order by time) "Count"
  from (
    select date, time, "Check", sum(grp) over(partition by date order by time) grp
      from (
        select *, case "Check" when lag("Check") over(partition by date order by time, "Check") then 0 else 1 end grp
          from tab
      ) x
  ) y

Сначала сравниваем подряд идущие Check в пределах группы по дате и сортировки по времени, при несовпадении даем 1, иначе 0. Так мы "размечаем" начало каждой интересующей нас на самом деле группы. На втором шаге суммируем эту разметку и получаем одинаковые номера групп у идущих подряд одинаковых значений. Далее нам остается только пронумеровать записи в этих, вновь полученных группах.