Как сгенерировать строки с датами в sql?

Question

[Clementino]

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

Пример. Исходная таблица:
user_id reg_date
1 01.11.2020
2 01.12.2020

Результат:
user_id date
1 01.11.2020
1 01.12.2020
1 31.12.2020
1 30.01.2021
1 01.03.2021
2 01.12.2020
2 31.12.2020
2 30.01.2021
2 01.03.2021

Сложность получается в том, что для каждого пользователя может быть разное кол-во результирующих строк, в зависимости от его даты регистрации.
Как это сгенерировать именно в sql запросе, не прибегая к переменным/циклам, не могу придумать...

Answer 1

[Михаил Е]

Да уж ))
Ну вообще думаю будет как-то так :
Пример на MSSQL
Пример на Psql13

Результат:

+========+============+=====================+
| userid | regdate    | newdate             |
+========+============+=====================+
| 1      | 2020-11-01 | 2020-11-01 00:00:00 |
+--------+------------+---------------------+
| 1      | 2020-11-01 | 2020-12-01 00:00:00 |
+--------+------------+---------------------+
| 1      | 2020-11-01 | 2020-12-31 00:00:00 |
+--------+------------+---------------------+
| 1      | 2020-11-01 | 2021-01-30 00:00:00 |
+--------+------------+---------------------+
| 1      | 2020-11-01 | 2021-03-01 00:00:00 |
+--------+------------+---------------------+
| 2      | 2020-12-01 | 2020-12-01 00:00:00 |
+--------+------------+---------------------+
| 2      | 2020-12-01 | 2020-12-31 00:00:00 |
+--------+------------+---------------------+
| 2      | 2020-12-01 | 2021-01-30 00:00:00 |
+--------+------------+---------------------+
| 2      | 2020-12-01 | 2021-03-01 00:00:00 |
+--------+------------+---------------------+

Comments

[ky0]

Жесть какая.

[Михаил Е]

ky0 Какая задача - такой и ответ ))

[Clementino]

Михаил Е, ну, по поводу "какая задача", я как раз и хотел понять можно ли это сделать средствами sql при этом чтобы это было адекватно и стоило того)
А то сидишь и думаешь, что может я просто не догадался до какого-то простого и изящного варианта :)

И все-таки по поводу приведенного кода, сложновато вникнуть сходу, можете в двух словах описать логику?) Зачем в CTE_Numbers цифры от 0 до 9?)

[Михаил Е]

Clementino Если в 2-х словах то мы создаём CTE_Numbers с цифрами 0-9,
далее в теле запроса соединяя их 4 раза получаем последовательность чисел с 1 до 9999
(Первая таблица 1-9,
2-я таблица умножается на 10, т.е. 10-99,
3-я : 100-999,
4-я даёт 1000-9999 путём умножения)
, и добавляем их к дате регистрации.
Получаем таблицу с датами от даты регистрации до DateAdd(day, 1-9999,regdata).
После чего обрезаем её по 2-м условиям (получившаяся дата должна быть меньше чем сейчас, и кратность даты должна быть = 30.

[Михаил Е]

Clementino, "Какая задача" - я не имел ввиду какое-то уничижение, скорее "Нестандартная, нетривиальная"

[Clementino]

Михаил Е, спасибо) конечно, хотелось не этого, но формально это решение вопроса так что..))
Заодно меня ваше решение натолкнуло на одну мысль, сейчас попробую его сократить) Думаю, можно приджойнить timeseries с интервалом в один день, по условию, что эти даты будут между датой регистрации и текущим днем, ну а потом оставить только те строки, где разница между датой регистрации и датой кратна 30)

[Михаил Е]

Clementino, ну в принципе этот запрос можно ещё заоптимизровать, например если мы знаем что кратность всегда 30, и дат будет менее 10000 тогда отсечь сразу числа, не кратные 30, должно быть оптимальнее (Будет не по 9999 чисел на каждую строку, а по 333 максимум). С timeseries, если получится, скиньте тож сюда, интересна реализация)

[Clementino]

Михаил Е, да, получилось.

select u.user_id, u.reg_date, t.date_id
from users u
left join times t on ((t.date_id between u.reg_date and now()::date) AND (datediff(day, u.reg_date, t.date_id) % 30) = 0)

Это vertica, т.к. именно для неё решение нужно, но в других субд вместо datediff можно чуть более громоздкие конструкции использовать)
times тут это просто таблица времён с детализацией до дня. Если в кхд такой нет, то можно сгенерировать на лету (функции generate_series в postgresql, timeseries в vertica, например).

[Михаил Е]

Clementino, да, "generate_series" в PGSQL - удобная ), к сожалению я чаще работаю с MSSQL, там такого по умолчанию нету ... там основной способ как раз через CTE_Numbers или создавать рекурсивный аналог generate_series из PGSQL). с generate_series - получится оптимальнее )

Answer 2

[ky0]

Рекурсивные функции и SQL плохо совместимы - подобная логика обычно выносится вне СУБД, чего и вам советую.

Comments

[Clementino]

ky0 А можете посоветовать подходящий инструмент для решения подобной задачи?)
Я в качестве альтернативы думал над организацией чего-то вроде etl процесса в talend/аналогичном софте. Т.е. несколько шагов, сначала грузятся селектом пользователи с их датой регистрации, потом шаг на котором создается этот костяк с датами каждые 30 дней с регистрации по каждому пользователю, далее уже на эту таблицу делаются джойны, ну и результат сохраняется в csv, к примеру.
Или может есть какие-то более подходящие варианты.