Используя postgresql версии › 10, я столкнулся с проблемой при создании ряда дат с использованием встроенной функции generate_series. По сути, это не соответствует day of the month правильно.
У меня есть много разных частот (предоставленных пользователем), которые необходимо рассчитать между заданной датой начала и окончания. Датой начала может быть любая дата и, следовательно, любой день месяца. Это создает проблемы, когда такие частоты, как monthly, сочетаются с датой начала 2018-01-31 или 2018-01-30, как показано в выходных данных ниже.
Я создал решение и хотел опубликовать его здесь, чтобы другие могли использовать его, поскольку я не смог найти другого решения.
Однако после некоторых тестов я увидел, что мое решение имеет другую производительность по сравнению со встроенным generate_series при использовании в (абсурдно) больших диапазонах дат. Кто-нибудь знает, как это можно улучшить?
TL;DR: если возможно, избегайте циклов, поскольку они снижают производительность, прокрутите вниз, чтобы улучшить реализацию.
Встроенный выход
select generate_series(date '2018-01-31',
date '2018-05-31',
interval '1 month')::date
as frequency;
генерирует:
frequency
------------
2018-01-31
2018-02-28
2018-03-28
2018-04-28
2018-05-28
Как видно из вывода, день месяца не учитывается и усекается до минимального дня, встречающегося на пути, в данном случае: 28 due to the month of februari.
Ожидаемый результат
В результате этой проблемы я создал пользовательскую функцию:
create or replace function generate_date_series(
starts_on date,
ends_on date,
frequency interval)
returns setof date as $$
declare
interval_on date := starts_on;
count int := 1;
begin
while interval_on <= ends_on loop
return next interval_on;
interval_on := starts_on + (count * frequency);
count := count + 1;
end loop;
return;
end;
$$ language plpgsql immutable;
select generate_date_series(date '2018-01-31',
date '2018-05-31',
interval '1 month')
as frequency;
генерирует:
frequency
------------
2018-01-31
2018-02-28
2018-03-31
2018-04-30
2018-05-31
Сравнение производительности
Независимо от того, какой диапазон дат указан, встроенный generate_series имеет производительность в среднем 2 мс для:
select generate_series(date '1900-01-01',
date '10000-5-31',
interval '1 month')::date
as frequency;
в то время как пользовательская функция generate_date_series имеет производительность в среднем 120 мс для:
select generate_date_series(date '1900-01-01',
date '10000-5-31',
interval '1 month')::date
as frequency;
Вопрос
На самом деле таких диапазонов никогда не будет, и поэтому это не проблема. Для большинства запросов пользовательский generate_date_series будет иметь такую же производительность. Хотя мне интересно, в чем причина разницы.
Есть ли причина, по которой встроенная функция может достигать постоянной производительности в среднем 2 мс независимо от предоставленного диапазона?
Есть ли лучший способ реализовать generate_date_series, который работает не хуже встроенного generate_series?
Улучшенная реализация без циклов
(получено из ответа @eurotrash)
create or replace function generate_date_series(
starts_on date,
ends_on date,
frequency interval)
returns setof date as $$
select (starts_on + (frequency * count))::date
from (
select (row_number() over ()) - 1 as count
from generate_series(starts_on, ends_on, frequency)
) series
$$ language sql immutable;
с улучшенной реализацией функция generate_date_series имеет производительность в среднем 45 мс для:
select generate_date_series(date '1900-01-01',
date '10000-5-31',
interval '1 month')::date
as frequency;
Реализация, предоставленная @eurorash, дает мне в среднем 80 мс, что, как я полагаю, связано с двойным вызовом функции generate_series.
