Как заполнить все периоды между двумя датами в MySQL?

У меня есть следующая ситуация, когда у меня есть несколько человек с начальной и конечной датой:

ID | start_date | end_date
1    2015-02-15   2015-04-20
2    2015-03-10   2015-06-15
...   ...            ...

Теперь мне нужно получить таблицу с отдельными лицами и всеми последовательными 30-дневными периодами между их датами начала и окончания (начиная с даты начала_дата). Результат должен выглядеть так:

ID | period | from_date   | to_date
1     1       2015-02-15    2015-03-17
1     2       2015-03-18    2015-04-17
2     1       2015-03-10    2015-04-09
2     2       2015-04-10    2015-05-10
2     3       2015-05-11    2015-06-10

Есть ли у вас какие-либо идеи, как создать такую ​​таблицу умным способом в MySQL? Если MySQL слишком громоздок для такой обработки данных, R или Excel также будут работать для меня.

Источник

3 ответа

Решение

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

Примерно так (не проверено, поэтому прошу прощения за любые опечатки):-

SELECT a.id, b.aNum, DATE_ADD(a.start_date, INTERVAL (b.aNum * 30) DAY) AS from_date, DATE_ADD(a.start_date, INTERVAL ((b.aNum + 1) * 30) DAY) AS to_date
FROM sometable a
CROSS JOIN
(
    SELECT tens.aCnt * 10 + units.aCnt AS aNum
    FROM
    (SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) units
    CROSS JOIN
    (SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) tens
) b
WHERE  DATE_ADD(a.start_date, INTERVAL (b.aNum * 30) DAY) <= end_date

Эта версия работает только до 100 групп по 30 дней, но ее легко расширить (но чем медленнее будет работать группа, тем медленнее она будет)

Источник

Сложный вопрос, я должен сказать.

Вот моя попытка использовать data.table пакет в р. Во-первых, я позабочусь о том, чтобы у вас были правильные форматы даты в ваших данных

library(data.table)
indx <- grep("date", names(df))
setDT(df)[, (indx) := lapply(.SD, as.Date), .SDcols = indx]

Затем мы рассчитаем 30-дневные интервалы для каждого идентификатора, добавив кумулятивный индекс как в начальный, так и в конечный столбцы.

df[, 
     {
      temp <- seq.Date(start_date, end_date, by = "30 days")
      indx <- seq_along(temp[-(1L:2L)])
      .(
        Period = c(indx, length(temp) - 1L),
        from = c(temp[1L], temp[-c(1L, length(temp))] + indx),
        to = c(temp[2L], temp[-c(1L:2L)] + indx)
       )
      }
, by = ID]

#    ID Period       from         to
# 1:  1      1 2015-02-15 2015-03-17
# 2:  1      2 2015-03-18 2015-04-17
# 3:  2      1 2015-03-10 2015-04-09
# 4:  2      2 2015-04-10 2015-05-10
# 5:  2      3 2015-05-11 2015-06-10
Источник

Я немного скорректировал код Kickstart, чтобы решить все мои требования из исходного поста, возможно, он поможет кому-то с подобной проблемой:

SELECT a.pid, b.aNum+1 as period, DATE_ADD(a.start_date, INTERVAL (b.aNum * 31) DAY) AS from_date, 
DATE_ADD(DATE_ADD(a.start_date, INTERVAL (b.aNum * 31) DAY), INTERVAL 30 DAY) AS to_date
FROM any_table a
CROSS JOIN
(
SELECT hundreds.aCnt*100 + tens.aCnt * 10 + units.aCnt AS aNum
FROM
(SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) units
CROSS JOIN
(SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) tens
 CROSS JOIN
(SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) hundreds
) b
WHERE  DATE_ADD(a.start_date, INTERVAL (b.aNum * 30)+30 DAY) <= end_date

Теперь следующий период начинается через один день после окончания предыдущего, а последний 30-дневный период индивидуума заканчивается до окончания_даты.

Источник
Другие вопросы по тегам