Преобразование диапазона дат в новые строки с дискретными датами

У меня есть таблица, содержащая name, location, start_date и end_date. Я хочу преобразовать эти строки, содержащие диапазоны дат, в строки дискретных дат, сохраняя при этом соответствующую информацию о строках.

Пример:

Name1, Location1, 2015-4-01, 2015-4-04

Становится:

Name1, Location1, 2015-4-01
Name1, Location1, 2015-4-02
Name1, Location1, 2015-4-03
Name1, Location1, 2015-4-04

Я предполагаю, что мне нужно будет создать это как новую таблицу, используя функции PostgreSQL.


postgresql sql generate-series set-returning-functions
person Bud    schedule 03.05.2015    source источник
comment
Пожалуйста, всегда указывайте свою версию Postgres. Определение таблицы тоже было бы полезно. Могут ли столбцы быть NULL? И т. д.   -  person Erwin Brandstetter    schedule 03.05.2015
comment
Отмеченный! Спасибо Эрвин!   -  person Bud    schedule 03.05.2015

Ответы (2)


arrow_upward
3
arrow_downward

Создайте новую таблицу с тремя обязательными столбцами:

CREATE TABLE new_tbl (
  nam    varchar,
  loc    varchar,
  dt     date);

Поскольку вы хотите, чтобы в новой таблице были записи в диапазоне start_date - end_date с интервалом в день включительно, самое простое решение - сгенерировать набор из ваших дат:

generate_series(start_date::timestamp, end_date::timestamp, '1 day')

Это вы можете просто вставить в оператор INSERT в новой таблице:

INSERT INTO new_tbl
  SELECT nam, loc, generate_series(start_date::timestamp, end_date::timestamp, '1 day')::date
  FROM old_tbl;

Поскольку функция generate_series работает с параметрами timestamp, вам необходимо явно преобразовать dates, а затем преобразовать сгенерированные timestamps обратно в dates, чтобы они соответствовали определению вашего столбца.

person Patrick    schedule 03.05.2015

arrow_upward
2
arrow_downward

В современном Postgres (9.3+) лучше всего использовать функции, возвращающие набор, в соединении LATERAL в списке FROM:

Предполагая, что start_date и end_date определены NOT NULL:

CREATE TABLE AS
SELECT name, location, day::date
FROM   tbl, generate_series(start_date, end_date, interval '1 day') day;

LATERAL здесь подразумевается.
Руководство около LATERAL подзапросов.

Некоторые связанные ответы (их много):

О LATERAL (в ответ на комментарий):

person Erwin Brandstetter    schedule 03.05.2015
comment
Когда вы говорите, что лучше всего использовать функции, возвращающие набор, в LATERAL join, вы имеете в виду, что это предпочтительный способ ведения дел, или здесь есть проблема с производительностью/переносимостью/и т. д.? - person Patrick; 03.05.2015
comment
@Patrick: функции, возвращающие набор, в списке SELECT являются совершенно нестандартным расширением Postgres. Они отлично работают для простых случаев, но демонстрируют сомнительное поведение при объединении нескольких экземпляров или в некоторых крайних случаях. LATERAL — это стандартный синтаксис SQL, обеспечивающий чистые решения для всех комбинаций. Это позволяет использовать новые функции, такие как WITH ORDINALITY. Производительность для основных случаев аналогична. Я добавил несколько ссылок в ответ. - person Erwin Brandstetter; 03.05.2015