Пример создания календаря (time dimension table)
Ниже представлен скрипт создания календаря (time dimension table) - таблицы содержащей даты и их атрибуты, состав которых позволяет построить иерархию по времени. Скрипт создания примера календаря разработан для базы данных Oracle.
create table DT as
select CurrDate as DAY_ID,
1 AS DAY_TM_SPN,
CurrDate AS DAY_END_DT,
to_char(CurrDate,'DY') as WK_DAY_NM_SHT,
to_char(CurrDate,'Day') as WK_DAY_NM_LNG,
to_number(trim(leading '0' from to_char(CurrDate,'D'))) as WK_DAY_NUM,
to_number(trim(leading '0' from to_char(CurrDate,'DD'))) as DAY_NUM_OF_MON,
to_number(trim(leading '0' from to_char(CurrDate,'DDD'))) as DAY_NUM_OF_YR,
upper(to_char(CurrDate,'Mon') || '-' || to_char(CurrDate,'YYYY')) as MON_ID,
max(to_number(to_char(CurrDate, 'DD')))
over (partition by to_char(CurrDate,'Mon')) as MON_TM_SPN,
max(CurrDate) over (partition by to_char(CurrDate,'Mon')) as MON_END_DT,
to_char(CurrDate,'Mon') || ' ' || to_char(CurrDate,'YYYY') as MON_DSC_SHT,
RTRIM(TO_CHAR(CurrDate,'Month')) || ' ' || to_char(CurrDate,'YYYY') as MON_DSC_LNG,
to_char(CurrDate,'Mon') as MON_NM_SHR,
to_char(CurrDate,'Month') as MON_NM_LNG,
to_number(trim(leading '0' from to_char(CurrDate,'MM'))) as MON_NUM_OF_YR,
'Q' || upper(to_char(CurrDate,'Q') || '-' || to_char(CurrDate,'YYYY')) as QRT_ID,
count(*) over (partition by to_char(CurrDate,'Q')) as QRT_TM_SPN,
max(CurrDate) over (partition by to_char(CurrDate,'Q')) as QRT_END_DT,
to_number(to_char(CurrDate,'Q')) as QRT_NUM_OF_YR,
(case when to_number(to_char(CurrDate,'Q')) in (1,2) then 1 else 2 end) as HALF_OF_YR,
to_char(CurrDate,'YYYY') as YR_ID,
count(*) over (partition by to_char(CurrDate,'YYYY')) as YR_TM_SPN,
max(CurrDate) over (partition by to_char(CurrDate,'YYYY')) asYR_END_DT
from
(select level n,
-- Календарь формируется начиная со следующей после указанной даты.
to_date('31/12/2010','DD/MM/YYYY') + numtodsinterval(level,'day') CurrDate
from dual
-- Количество дней в календаре.
connect by level <= 365)
order by CurrDate;
COMMENT ON TABLE DT IS 'Календарь. Измерение времени. Time dimension table';
COMMENT ON COLUMN DT.DAY_ID IS 'День. Дата календаря';
COMMENT ON COLUMN DT.DAY_TM_SPN IS 'Длительность в днях';
COMMENT ON COLUMN DT.DAY_END_DT IS 'Дата окончания';
COMMENT ON COLUMN DT.WK_DAY_NM_SHT IS 'Краткое наименование дня недели';
COMMENT ON COLUMN DT.WK_DAY_NM_LNG IS 'Полное наименование дня недели';
COMMENT ON COLUMN DT.WK_DAY_NUM IS 'Номер дня в недели';
COMMENT ON COLUMN DT.DAY_NUM_OF_MON IS 'Номер дня в месяце';
COMMENT ON COLUMN DT.DAY_NUM_OF_YR IS 'Номер дня в году';
COMMENT ON COLUMN DT.MON_ID IS 'Месяц';
COMMENT ON COLUMN DT.MON_TM_SPN IS 'Длительность месяца в днях';
COMMENT ON COLUMN DT.MON_END_DT IS 'Дата окончания месяца';
COMMENT ON COLUMN DT.MON_DSC_SHT IS 'Краткое наименование месяца с годом';
COMMENT ON COLUMN DT.MON_DSC_LNG IS 'Полное наименование месяца с годом';
COMMENT ON COLUMN DT.MON_NM_SHR IS 'Краткое наименование месяца';
COMMENT ON COLUMN DT.MON_NM_LNG IS 'Полное наименование месяца';
COMMENT ON COLUMN DT.MON_NUM_OF_YR IS 'Номер месяца в году';
COMMENT ON COLUMN DT.QRT_ID IS 'Квартал';
COMMENT ON COLUMN DT.QRT_TM_SPN IS 'Длительность квартала в днях';
COMMENT ON COLUMN DT.QRT_END_DT IS 'Дата окончания квартала';
COMMENT ON COLUMN DT.QRT_NUM_OF_YR IS 'Номер квартала в году';
COMMENT ON COLUMN DT.HALF_OF_YR IS 'Номер полугодия';
COMMENT ON COLUMN DT.YR_ID IS 'Год';
COMMENT ON COLUMN DT.YR_TM_SPN IS 'Длительность года';
COMMENT ON COLUMN DT.YR_END_DT IS 'Дата окончания года';