Zeitdimension per Table Function erstellen

Heute geht's um die Erstellung einer Zeitdimension mithilfe einer Table-Function. Dazu verwendet man im Wesentlichen die Möglichkeiten der Funktion to_char, wie das folgende Beispiel zeigt.

Zunächst wird der Objekt-Typ erstellt:

create type time_dim_t as object
(
 v_day date,
 v_day_name varchar2(30),
 v_day_of_week integer,
 v_day_of_month integer,
 v_day_of_year integer,
 v_week integer,
 v_week_start date,
 v_week_end date,
 v_iso_week integer,
 v_iso_week_start date,
 v_iso_week_end date,
 v_month integer,
 v_month_name varchar2(30),
 v_month_start date,
 v_month_end date,
 v_month_days integer,
 v_quarter integer,
 v_quarter_name varchar(2),
 v_quarter_start date,
 v_quarter_end date,
 v_quarter_days integer,
 v_year integer,
 v_year_start date,
 v_year_end date,
 v_year_days integer,
 v_is_leap_year varchar2(1)
);
/

Hinzu kommt der Table-Typ:

create type time_dim_tab as table of time_dim_t;
/

Damit sind alle Vorbereitungen getroffen, um die Table-Function zu erstellen:

create or replace function time_dim
(
 p_year_start_in in integer,
 p_year_end_in in integer
) 
return time_dim_tab pipelined
as
 v_day date;
 v_day_name varchar2(30); 
 v_day_of_week integer;
 v_day_of_month integer;
 v_day_of_year integer; 
 v_week integer;
 v_week_start date;
 v_week_end date;
 v_iso_week integer;
 v_iso_week_start date;
 v_iso_week_end date;
 v_month integer;
 v_month_name varchar2(30);
 v_month_start date;
 v_month_end date;
 v_month_days integer;
 v_quarter integer;
 v_quarter_name varchar(2);
 v_quarter_start date;
 v_quarter_end date;
 v_quarter_days integer;
 v_year integer;
 v_year_start date;
 v_year_end date;
 v_year_days integer;
 v_is_leap_year varchar2(1);
begin

 begin

  for y in p_year_start_in .. p_year_end_in loop
   
   v_year := y;
   v_year_start := to_date('01.01.' || v_year, 'DD.MM.YYYY');
   v_year_end := to_date('31.12.' || v_year, 'DD.MM.YYYY');
   v_year_days := v_year_end - v_year_start + 1;
  
   if (v_year_days = 366) then
    v_is_leap_year := 'Y';
   else
    v_is_leap_year := 'N';
   end if;
   
   for q in 1 .. 4 loop
   
    v_quarter := q;
    v_quarter_name := 'Q' || v_quarter;
    case v_quarter_name
     when 'Q1' then
      v_quarter_start := to_date('01.01.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('31.03.' || v_year, 'DD.MM.YYYY');
     when 'Q2' then
      v_quarter_start := to_date('01.04.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('30.06.' || v_year, 'DD.MM.YYYY');
     when 'Q3' then
      v_quarter_start := to_date('01.07.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('30.09.' || v_year, 'DD.MM.YYYY');
     when 'Q4' then
      v_quarter_start := to_date('01.10.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('31.12.' || v_year, 'DD.MM.YYYY');
    end case;
    v_quarter_days := v_quarter_end - v_quarter_start + 1;
    
    for m in to_number(to_char(v_quarter_start, 'MM')) .. 
             to_number(to_char(v_quarter_end, 'MM')) loop
   
     v_month := m;
     v_month_start := to_date('01.' || v_month || '.' || v_year, 'DD.MM.YYYY');
     v_month_end := last_day(v_month_start);
     v_month_days := v_month_end - v_month_start + 1;
     v_month_name := to_char(v_month_start, 'MONTH');
 
     for d in 1 .. v_month_days loop
     
      v_day := to_date(d || '.' || v_month || '.' || v_year, 'DD.MM.YYYY');
      v_day_of_week := to_number(to_char(v_day, 'D'));
      v_day_of_month := to_number(to_char(v_day, 'DD'));
      v_day_of_year := to_number(to_char(v_day, 'DDD'));
      v_day_name := to_char(v_day, 'DAY');
      v_week := to_number(to_char(v_day, 'WW'));
      v_week_start := trunc(v_day, 'WW');
      v_week_end := v_week_start + interval '6' day;
      v_iso_week := to_number(to_char(v_day, 'IW'));
      v_iso_week_start := trunc(v_day, 'IW');
      v_iso_week_end := v_iso_week_start + interval '6' day;
 
      pipe row
     (
      time_dim_t
      (
       v_day,
       v_day_name,
       v_day_of_week,
       v_day_of_month,
       v_day_of_year,
       v_week,
       v_week_start,
       v_week_end,
       v_iso_week,
       v_iso_week_start,
       v_iso_week_end,
       v_month,
       v_month_name,
       v_month_start,
       v_month_end,
       v_month_days,
       v_quarter,
       v_quarter_name,
       v_quarter_start,
       v_quarter_end,
       v_quarter_days,
       v_year,
       v_year_start,
       v_year_end,
       v_year_days,
       v_is_leap_year 
      )
     );  
     end loop;
    
    end loop;
    
   end loop;
   
  end loop;

 end;

end;

Ein Aufruf der Table-Function für die Jahre 2011 und 2012 sieht dann wie folgt aus:

select * from table(time_dim(2011, 2012));

Auf der Grundlage dieser Table-Function kann dann eine Zeitdimension erstellt werden; sei es als Star- oder Snowflake-Schema, was durch entsprechende Projektionen erreicht werden kann.

Eine Ergänzung wäre die Angabe von Feiertagen, welche oftmals im iCal-Format bereitstehen. Vielleicht widme ich ein weiteres Posting diesem Thema, um zu zeigen, wie man dieses Format in SQL bereitstellen kann.