Создать строку для каждого месяца между двумя датами в PostgreSQL

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

rowID | person      | startdate   | enddate
1     | 12345       | 2014-04-01  | 2014-11-30
2     | 67890       | 2014-03-01  | 2014-05-01

Я хочу, чтобы результаты в моей таблице назначения были:

person | month
12345  | 2014-04-01
12345  | 2014-05-01
12345  | 2014-06-01
12345  | 2014-07-01
12345  | 2014-08-01
12345  | 2014-09-01
12345  | 2014-10-01
12345  | 2014-11-01
67890  | 2014-03-01
67890  | 2014-04-01
67890  | 2014-05-01

Большое спасибо за помощь.

Источник

2 ответа

Решение

Нет необходимости в CTE или боковом соединении:

select
    person,
    generate_series(
        date_trunc('month', startdate), 
        enddate, '1 month'
    )::date as month
from rfem
order by 1, 2
;
 person |   month    
--------+------------
  12345 | 2014-04-01
  12345 | 2014-05-01
  12345 | 2014-06-01
  12345 | 2014-07-01
  12345 | 2014-08-01
  12345 | 2014-09-01
  12345 | 2014-10-01
  12345 | 2014-11-01
  67890 | 2014-03-01
  67890 | 2014-04-01
  67890 | 2014-05-01
Источник

Рассчитайте минимальные и максимальные даты для каждого человека с первыми днями месяцев, а затем создайте месячные диапазоны между этими датами, используя generate_series:

WITH date_ranges AS (
SELECT 
  person,
  min(date_trunc('month', startdate))::timestamptz AS min_start,
  max(date_trunc('month', enddate))::timestamptz AS max_end
FROM person_table
GROUP BY 1
)
SELECT 
  dr.person,
  ser.month::DATE as month
FROM date_ranges AS dr,
     generate_series(min_start, max_end, '1 month') AS ser(month)

Выход

 person |   month
--------+------------
  12345 | 2014-04-01
  12345 | 2014-05-01
  12345 | 2014-06-01
  12345 | 2014-07-01
  12345 | 2014-08-01
  12345 | 2014-09-01
  12345 | 2014-10-01
  12345 | 2014-11-01
  67890 | 2014-03-01
  67890 | 2014-04-01
  67890 | 2014-05-01

Как это устроено? неявный LATERAL JOIN для вызова функции форсирует вычисление для каждой строки из ввода.

Это решение учитывает, что у вас может быть более 1 строки на каждого человека с датами, и он принимает максимально возможный диапазон.

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