Generation date range with specified increments

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

Итак есть начало диапазона - date_begin, окончание диапазона - date_end (включая/не включая ее) и приращение - increase. Также был добавлен признак необходимости включения/исключения в/из диапазон(а) окончания диапазона (date_end), назовём его include_date_end.

Допустимые значения для include_date_end:

• 0 - не включать date_end в последовательность;
• 1 - включать

Представим всё это в виде запроса:

     select 
            to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_begin, 
            to_date('02.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_end, 
            1/24 increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
            0 include_date_end -- 0 - не включать date_end; 1 - включать
       from dual 

Для генерации диапазона используем возможности запроса connect by:

select level
  from dual
  connect by
    0 + level <= 7

Преобразуем запрос под наши нужды, используя with для удобства:

 -- Generation date range with specified increments
 -- increase 1/24 - один час
 -- 0 include_date_end - не включать date_end
with 
  range_vars as ( 
    select 
           to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_begin, 
           to_date('02.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_end, 
           1/24 increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
           0 include_date_end -- 0 - не включать date_end; 1 - включать
      from dual 
  ),
  range_dates as (
    select 
           date_begin + (level * increase)  - increase date_increase 
      from range_vars 
      connect by 
        date_begin + (level * increase) <= date_end + case include_date_end 
                                                        when 1 then increase
                                                        else 0
                                                      end 
  ) 
select to_char(date_increase, 'DD.MM.YYYY HH24:MI:SS') date_increase
  from range_dates

Полученный выше запрос генерирует диапазон дат между 01 октября 2010 года и 02 октября 2010 года с приращение в один час(1/24). Как видно из результата date_end не включена в диапазон:

DATE_INCREASE      
-------------------
01.10.2010 00:00:00
01.10.2010 01:00:00
01.10.2010 02:00:00
01.10.2010 03:00:00
01.10.2010 04:00:00
01.10.2010 05:00:00
01.10.2010 06:00:00
01.10.2010 07:00:00
01.10.2010 08:00:00
01.10.2010 09:00:00
01.10.2010 10:00:00
01.10.2010 11:00:00
01.10.2010 12:00:00
01.10.2010 13:00:00
01.10.2010 14:00:00
01.10.2010 15:00:00
01.10.2010 16:00:00
01.10.2010 17:00:00
01.10.2010 18:00:00
01.10.2010 19:00:00
01.10.2010 20:00:00

DATE_INCREASE      
-------------------
01.10.2010 21:00:00
01.10.2010 22:00:00
01.10.2010 23:00:00

24 rows selected.

Можно указывать любое приращение, вот генерация диапазона по дням (включая date_end):

-- Generation date range with specified increments
 -- increase 1 - одни сутки
 -- 1 include_date_end - включать date_end
with 
  range_vars as ( 
    select 
           to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_begin, 
           to_date('11.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_end, 
           1 increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
           1 include_date_end -- 0 - не включать date_end; 1 - включать
      from dual 
  ),
  range_dates as (
    select 
           date_begin + (level * increase)  - increase date_increase 
      from range_vars 
      connect by 
        date_begin + (level * increase) <= date_end + case include_date_end 
                                                        when 1 then increase
                                                        else 0
                                                      end 
  ) 
select to_char(date_increase, 'DD.MM.YYYY HH24:MI:SS') date_increase
  from range_dates;

DATE_INCREASE      
-------------------
01.10.2010 00:00:00
02.10.2010 00:00:00
03.10.2010 00:00:00
04.10.2010 00:00:00
05.10.2010 00:00:00
06.10.2010 00:00:00
07.10.2010 00:00:00
08.10.2010 00:00:00
09.10.2010 00:00:00
10.10.2010 00:00:00
11.10.2010 00:00:00

11 rows selected.

И на закуску для удобства использования создана pipelined function:

create or replace type t_date_table is table of date;
/

Type created.

create or replace function get_range_dates(
  p_date_begin date, p_date_end date,
  p_increase number, p_include_date_end number
) return t_date_table pipelined
as
begin
  for rec in (
    -- Generation date range with specified increments
    with 
      range_vars as ( 
        select 
               p_date_begin date_begin, 
               p_date_end date_end, 
               p_increase increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
               p_include_date_end include_date_end -- 0 - не включать date_end; 1 - включать
          from dual 
      ),
      range_dates as (
        select 
               date_begin + (level * increase)  - increase date_increase 
          from range_vars 
          connect by 
            date_begin + (level * increase) <= date_end + case include_date_end 
                                                            when 1 then increase
                                                            else 0
                                                          end 
      ) 
    select date_increase
      from range_dates
  ) loop
    pipe row (rec.date_increase);
  end loop;
      
  return;
end;
/

Function created.

-- Generation date range with specified increments
-- increase 1/2 - полдня
-- 1 include_date_end - включать date_end
select to_char(column_value, 'DD.MM.YYYY HH24:MI:SS') date_increase 
  from table(
         get_range_dates(
           to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS'), -- date_begin 
           to_date('05.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS'), -- date_end 
           1/2, -- increase полдня
           1 -- include_date_end
         )
       );

DATE_INCREASE      
-------------------
01.10.2010 00:00:00
01.10.2010 12:00:00
02.10.2010 00:00:00
02.10.2010 12:00:00
03.10.2010 00:00:00
03.10.2010 12:00:00
04.10.2010 00:00:00
04.10.2010 12:00:00
05.10.2010 00:00:00

9 rows selected.