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

Question

[Pudjak]

Имеется вот такая таблица. Сортировка идёт по дате, времени, чеку.
Нужно получить один из этих 2х вариантов таблиц:


То есть, либо просто подряд идущих одинаковых записей Date-Check. Либо максимальное число идущих подряд записей для каждого дня.
Пытался сделать через ROW_NUMBERS() OVER (PATRITION BY date ORDER BY date, time, check), ну тут, конечно, получается он игнорит check и просто проставляет 1,2,3,4,5. Если добавить PATRITION BY date, check, то он, блин, сортирует по check потом и тоже не то выходит. Нужна сортировка по дате, времени, только потом по чеку, но группировка по дате и чеку

Answer 1

[Taulan Khatuaev]

-- Выбираем все столбцы из вашей таблицы и добавляем два новых столбца:
-- NewGroup - показывает, начинается ли новая группа записей
-- GroupCount - показывает количество подряд идущих групп для каждой записи
SELECT
  Date,
  Check,
  Time,
  Product,
  Qty,
  Price,
  -- Если предыдущая запись имеет ту же дату и чек, что и текущая, то это не новая группа
  -- В противном случае, это новая группа
  CASE
    WHEN LAG(Date) OVER (ORDER BY Date, Time, Check) = Date AND LAG(Check) OVER (ORDER BY Date, Time, Check) = Check THEN 0
    ELSE 1
  END AS NewGroup,
  -- Используем функцию SUM с условием для подсчета количества подряд идущих групп
  SUM(CASE
    WHEN LAG(Date) OVER (ORDER BY Date, Time, Check) = Date AND LAG(Check) OVER (ORDER BY Date, Time, Check) = Check THEN 0
    ELSE 1
  END) OVER (ORDER BY Date, Time, Check) AS GroupCount
FROM
  YourTable
ORDER BY
  Date, Time, Check

пример запроса для получения максимального числа подряд идущих записей для каждого дня

-- Используем подзапрос с предыдущим запросом для получения максимального числа подряд идущих групп для каждой даты
SELECT
  Date,
  MAX(GroupCount) AS MaxGroupCount
FROM (
  SELECT
    Date,
    Check,
    Time,
    Product,
    Qty,
    Price,
    -- Аналогично предыдущему запросу, создаем столбец NewGroup и используем функцию SUM с условием для подсчета количества подряд идущих групп
    CASE
      WHEN LAG(Date) OVER (ORDER BY Date, Time, Check) = Date AND LAG(Check) OVER (ORDER BY Date, Time, Check) = Check THEN 0
      ELSE 1
    END AS NewGroup,
    SUM(CASE
      WHEN LAG(Date) OVER (ORDER BY Date, Time, Check) = Date AND LAG(Check) OVER (ORDER BY Date, Time, Check) = Check THEN 0
      ELSE 1
    END) OVER (ORDER BY Date, Time, Check) AS GroupCount
  FROM
    YourTable
) t
-- Группируем по дате и выбираем максимальное значение GroupCount для каждой даты
GROUP BY
  Date

Comments

[Pudjak]

Ругается на SUM(CASE WHEN LAG(DATE). "Window function calls cannot be nested"

Answer 2

[AUser0]

Напишите малюсенький скрипт на любом языке, пусть читает построчно, суммирует как вздумается, и заносит результат в новую таблицу. Всё!

Answer 3

[Сергей c0re]

Пытался сделать через ROW_NUMBERS() OVER (PATRITION BY date ORDER BY date, time, check), ну тут, конечно, получается он игнорит check и просто проставляет 1,2,3,4,5.

вы же написали ниже что группировка по дате и чеку, а делаете PATRITION BY date

Если добавить PATRITION BY date, check, то он, блин, сортирует по check потом и тоже не то выходит

правильно, потому что необходимо группировать по тем критериям по которым необходимо и делать сортировку по негруппируемому полю по которому надо сделать счет - time

ROW_NUMBERS() OVER (PATRITION BY date, check ORDER BY time)

тогда он посчитает как надо.
а уж если необходимо еще как-то отсортировать или отобрать, то заворачиваете этот запрос в подзапрос или CTE и делаете из него запрос с WHERE , ORDER и т.п. и т.д.