Есть ли альтернатива присваиванию через := в запросах?

Question

Есть следующая задача. Дано: Произвольная последовательность дат. Задача: Необходимо объединить даты в пределах месяца в группы. То есть на выходе необходимо получить таблицу из двух колонок: Дата и наименование группы. При этом каждая дата может принадлежать строго к одной группе. Например, Дано:

Даты:
01.01.2018
15.01.2018
03.02.2018

На выходе мы должны получить:

Дата          Группа
01.01.2018    01.01.2018 - 15.01.2018
15.01.2018    01.01.2018 - 15.01.2018
03.02.2018    03.02.2018 - 03.02.2018

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

Еще пример: Дано:

Даты:
2020-01-01 
2020-01-12
2020-01-23
2020-02-14
2020-02-25
2020-03-06

Результат:

Дата       Группа
2020-01-01 2020-01-01 - 2020-01-23
2020-01-12 2020-01-01 - 2020-01-23
2020-01-23 2020-01-01 - 2020-01-23
2020-02-14 2020-02-14 - 2020-03-06
2020-02-25 2020-02-14 - 2020-03-06
2020-03-06 2020-02-14 - 2020-03-06

Я могу решить эту задачу, с помощью использования переменных @ и присваивания через :=. Но mysql 8 при выполнении таких запросов пишет, что в следующих релизах будет убрано присваивание через :=. Поэтому я пытаюсь найти какой-то альтернативный вариант. Мой выбор пал на оконные функции.

Но через оконные функции у меня получилось сделать только с наложением групп друг на друга.То есть:

Дата          Группа
01.01.2018    01.01.2018 - 15.01.2018
15.01.2018    15.01.2018 - 03.02.2018
03.02.2018    03.02.2018 - 03.02.2018

Код запроса:

SELECT DISTINCT
  nextdate
, CONCAT(FIRST_VALUE(nextdate) OVER w, LAST_VALUE(nextdate) OVER w) AS group_name
FROM test_table
WHERE nextdate IS NOT NULL
WINDOW w AS (ORDER BY nextdate RANGE BETWEEN CURRENT ROW AND INTERVAL 1 month FOLLOWING)

Очень прошу вашей помощи, так как уже несколько дней не могу придумать решение и уже готов сдаться и вернуться к @a:=...

Answer 1

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

WITH RECURSIVE Tab AS(
    SELECT ts, row_number() over(order by ts) RN FROM test
), Rec AS(
    select ts, ts start, 1 RN from Tab where RN=1
  union all
    select T.ts,
           case when T.ts <= R.start+interval 1 month then R.start else T.ts end,
           T.RN
      from Tab T, Rec R where T.RN=R.RN+1
)
select ts, start, max(ts) over(partition by start) end
  from Rec

fiddle

Для некоторого ускорения работы возможно поможет получение в первой части следующей за текущей даты (lead()) и использование в рекурсивной части исходной таблицы по условию равенства дат, по индексу это возможно быстрее, чем работа с виртуальным номером строки, который не проиндексирован. Но не факт ...

БЛОГ НА HUSL

Есть ли альтернатива присваиванию через := в запросах?

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