SQL оконные функции группировка по максимальному заданному количеству строку

Question

Дана таблица вида:

date	cnt
20220323	10
20220225	21
20220220	17
20220218	25
20220207	76
20220128	14
20220125	10

date - это дата записи
cnt - количество записей в таблице за эту дату.

Требуется сгруппировать таблицу так, чтобы за любой период количество записей не превышало 60. Если количество записей больше 60, то остается только одна эта дата. Итоговая таблица должна получиться вида:

date_to	date_from	count
20220323	20220220	48
20220218	20220218	25
20220207	20220207	76
20220128	20220125	24

Явно просятся оконные функции, но как сгруппировать значения по максимальной сумме?

Answer 1

Пример реализации для MySQL 8+.

WITH RECURSIVE
cte AS (
    SELECT *
    FROM ( SELECT `date`, 
                  cnt, 
                  cnt `count`, 
                  1 grp_no
           FROM test
           ORDER BY `date` LIMIT 1 ) first_row
    UNION ALL
    SELECT next_row.`date`, 
           next_row.cnt,
           CASE WHEN next_row.cnt + cte.`count` > 60
                THEN next_row.cnt
                ELSE next_row.cnt + cte.`count`
                END,
           cte.grp_no + (next_row.cnt + cte.`count` > 60)
    FROM cte
    JOIN LATERAL ( SELECT *
                   FROM test
                   WHERE test.`date` > cte.`date`
                   ORDER BY test.`date` LIMIT 1 ) next_row
)
SELECT MAX(`date`) date_to,
       MIN(`date`) date_from,
       MAX(`count`) `count`
FROM cte
GROUP BY grp_no;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1ff82c94d115512d10f23ca0a8ed7df8

БЛОГ НА HUSL

SQL оконные функции группировка по максимальному заданному количеству строку

Ответы (1 шт):