数据仓库开发之路之三--时间维度的创建

在数据仓库中,无一例外地需要和时间维度打交道,因此设计合理的时间维度,也是一个数据仓库项目开始必备的资源储备,如果有这方面的积累,就不用到处寻找合适的设计模型以及存储过程的代码了,否则可能需要花费一定的时间去寻找符合该项目合适的存储过程,或者自己动手编写。
一般来说,时间维度的创建要根据实际的数据仓库应用来,基本上可以分为天、月的时间维度表,更细的可以分为半小时时间段,小时时间段等等,一般数据量大的时间维度都是利用存储过程来生成的。
下面介绍一些时间维度表的设计结构。
<1> 时间维度表1(两列都是字段)



<1> 时间维度表2
a.日期维度


b.半小时时间维度




c.时间段维度


3、比较完整的时间维度设计


构建时间维度的存储过程包体如下所示

create or replace package body PKG_LOAD_DIM_TIME
as
  /************************************
  ** 是否合理自然日
  ** 如果是合理自然日则返回1,否则返回0
  ************************************/
  function F_Is_Day_ID
  (
    p_TIME_ID DIW.DW_DIM_TIME.DAY_CODE%type
  ) return number
  is
    v_Date date;
  begin
    v_Date := to_date(p_TIME_ID, 'YYYYMMDD');
    return 1;
  exception
    when others then
      return 0;
  end F_Is_Day_ID;

  /************************************
  ** 加载时间维度数据
  ************************************/
  procedure P_BUILD_DIM_TIME
  (
    p_START_DATE DIW.DW_DIM_TIME.DAY_CODE%type,  --开始日期
    p_END_DATE DIW.DW_DIM_TIME.DAY_CODE%type     --结束日期
  )
  is
    v_START_DATE date;  --开始日期
    v_END_DATE date;    --结束日期
    v_DATE date;

    v_YEAR varchar2(4);
    v_QUARTER varchar2(1);
    v_MONTH varchar2(2);
    v_TEN_DAY varchar2(1);
    v_WEEK varchar2(2);
    v_WEEK_YEAR varchar2(4);
    v_DAY varchar2(2);

  begin
    --BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','BICODE.PKG_LOAD_DIM_TIME.P_BUILD_DIM_TIME执行开始...');
    execute immediate 'TRUNCATE TABLE DIW.ODS_DIM_TIME';

    if F_Is_Day_ID(p_START_DATE)=1 and F_Is_Day_ID(p_END_DATE)=1 then

        v_START_DATE := TO_DATE(p_START_DATE,'YYYYMMDD');
        v_END_DATE := TO_DATE(p_END_DATE,'YYYYMMDD');

        if v_END_DATE - v_START_DATE >= 0 then
          --开始生成
          for i in 0..v_END_DATE - v_START_DATE loop

            v_DATE := v_START_DATE+i;
            v_YEAR := TO_CHAR(v_DATE,'YYYY');
            v_QUARTER := TO_CHAR(v_DATE,'Q');
            v_MONTH := TO_CHAR(v_DATE,'MM');
            v_TEN_DAY := case
                           when TO_CHAR(v_DATE,'DD')<'11' then '1'
                           when TO_CHAR(v_DATE,'DD')<'21' then '2'
                           when TO_CHAR(v_DATE,'DD')<'32' then '3'
                         end;
            --周方案一:星期被年分开,实际是7天分段不是自然周
            --v_WEEK := TO_CHAR(v_DATE,'WW');
            --v_WEEK_YEAR := v_YEAR;

            --周方案二:本星期星期四所在年
            --v_WEEK := TO_CHAR(v_DATE,'IW');
            --select TO_CHAR(NEXT_DAY(v_DATE-7,2)+3,'YYYY') into v_WEEK_YEAR from dual;
            ----v_WEEK_YEAR := TO_CHAR(NEXT_DAY(v_DATE-7,2)+3,'YYYY');--奇怪的错误??

            --周方案三:自然周,周被年分,没找到函数支持。
            select
              LPAD(TO_CHAR(TRUNC((v_DATE
              -to_date(v_YEAR||'0101','yyyymmdd')
              +DECODE(TO_NUMBER(to_CHAR(to_date(v_YEAR||'0101','yyyymmdd'),'D')),1,7,
              TO_NUMBER(to_CHAR(to_date(v_YEAR||'0101','yyyymmdd'),'D'))-1)-1)/7+1)),2,'0')
              INTO v_WEEK
            from dual;
            v_WEEK_YEAR := v_YEAR;

            v_DAY := TO_CHAR(v_DATE,'DD');

            insert into DIW.DW_DIM_TIME
            (
              DAY_CODE,
              DAY_LONG_DESC,
              DAY_MEDIUM_DESC,
              DAY_SHORT_DESC,
              WEEK_CODE,
              WEEK_LONG_DESC,
              WEEK_MEDIUM_DESC,
              WEEK_SHORT_DESC,
              TEN_DAY_CODE,
              TEN_DAY_LONG_DESC,
              TEN_DAY_MEDIUM_DESC,
              TEN_DAY_SHORT_DESC,
              MONTH_CODE,
              MONTH_LONG_DESC,
              MONTH_MEDIUM_DESC,
              MONTH_SHORT_DESC,
              QUARTER_CODE,
              QUARTER_LONG_DESC,
              QUARTER_MEDIUM_DESC,
              QUARTER_SHORT_DESC,
              HALF_YEAR_CODE,
              HALF_LONG_DESC,
              HALF_MEDIUM_DESC,
              HALF_SHORT_DESC,
              YEAR_CODE,
              YEAR_LONG_DESC,
              YEAR_MEDIUM_DESC,
              YEAR_SHORT_DESC,
              ALL_TIME_CODE,
              ALL_TIME_DESC,
              DAY_TIMESPAN,
              DAY_END_DATE,
              WEEK_TIMESPAN,
              WEEK_END_DATE,
              TEN_DAY_TIMESPAN,
              TEN_DAY_END_DATE,
              MONTH_TIMESPAN,
              MONTH_END_DATE,
              QUARTER_TIMESPAN,
              QUARTER_END_DATE,
              HALF_YEAR_TIMESPAN,
              HALF_YEAR_END_DATE,
              YEAR_TIMESPAN,
              YEAR_END_DATE
            )
            values
            (
              v_YEAR||v_MONTH||v_DAY,
              v_YEAR||'年'||v_MONTH||'月'||v_DAY||'日',
              /*v_MONTH||'月'||*/v_DAY||'日',
              v_YEAR||'-'||v_MONTH||'-'||v_DAY,
              v_WEEK_YEAR||'W'||v_WEEK,
              v_WEEK_YEAR||'年第'||v_WEEK||'周',
              '第'||v_WEEK||'周',
              v_WEEK_YEAR||'-W'||v_WEEK,
              v_YEAR||v_MONTH||'X'||v_TEN_DAY,
              v_YEAR||'年'||v_MONTH||'月'||decode(v_TEN_DAY,'1','上','2','中','下')||'旬',
              decode(v_TEN_DAY,'1','上','2','中','下')||'旬',
              v_YEAR||'-'||v_MONTH||'-X'||v_TEN_DAY,
              v_YEAR||v_MONTH,
              v_YEAR||'年'||v_MONTH||'月',
              v_MONTH||'月',
              v_YEAR||'-'||v_MONTH,
              v_YEAR||'Q'||v_QUARTER,
              v_YEAR||'年第'||v_QUARTER||'季度',
              '第'||v_QUARTER||'季度',
              v_YEAR||'-'||'Q'||v_QUARTER,
              v_YEAR||'H'||decode(v_QUARTER,'1','1','2','1','2'),
              v_YEAR||'年'||decode(v_QUARTER,'1','上','2','上','下')||'半年',
              decode(v_QUARTER,'1','上','2','上','下')||'半年',
              v_YEAR||'-'||'H'||decode(v_QUARTER,'1','1','2','1','2'),
              v_YEAR,
              v_YEAR||'年',
              v_YEAR||'年',
              v_YEAR,
              'ALL',
              'ALL_TIME',
              1,
              v_YEAR||v_MONTH||v_DAY,
              case
                when
                  TO_CHAR(NEXT_DAY(v_DATE-7,2),'YYYY')<v_WEEK_YEAR
                then
                  NEXT_DAY(v_DATE,2)-TO_DATE(v_WEEK_YEAR||'0101','YYYYMMDD')
                when
                  TO_CHAR(NEXT_DAY(v_DATE,2),'YYYY')>v_WEEK_YEAR
                then
                  TO_DATE(v_WEEK_YEAR||'1231','YYYYMMDD')-NEXT_DAY(v_DATE-7,2)+1
                else 7
              end,
              case
                when
                  TO_CHAR(NEXT_DAY(v_DATE,2),'YYYY')>v_WEEK_YEAR
                then v_WEEK_YEAR||'1231'
                else TO_CHAR(NEXT_DAY(v_DATE,2)-1,'YYYYMMDD')
              end,
              decode(v_TEN_DAY,'3',LAST_DAY(v_DATE)-TO_DATE(v_YEAR||v_MONTH||'21','YYYYMMDD')+1,10),
              decode(v_TEN_DAY,'3',TO_CHAR(LAST_DAY(v_DATE),'YYYYMMDD'),'2',v_YEAR||v_MONTH||'20',v_YEAR||v_MONTH||'10'),
              TO_NUMBER(TO_CHAR(LAST_DAY(v_DATE),'DD')),
              TO_CHAR(LAST_DAY(v_DATE),'YYYYMMDD'),
              decode(v_QUARTER,'1',TO_DATE(v_YEAR||'0331','YYYYMMDD')-TO_DATE(v_YEAR||'0101','YYYYMMDD')+1,'2',91,92),
              decode(v_QUARTER,'1',v_YEAR||'0331','2',v_YEAR||'0630','3',v_YEAR||'0930',v_YEAR||'1231'),
              decode(v_QUARTER,'3',184,'4',184,TO_DATE(v_YEAR||'0630','YYYYMMDD')-TO_DATE(v_YEAR||'0101','YYYYMMDD')+1),
              decode(v_QUARTER,'1',v_YEAR||'0630','2',v_YEAR||'0630',v_YEAR||'1231'),
              TO_DATE(v_YEAR||'1231','YYYYMMDD')-TO_DATE(v_YEAR||'0101','YYYYMMDD')+1,
              v_YEAR||'1231'
            )
            ;
          end loop;
          commit;
        end if;
    end if;
    
    --BICODE.PKG_ETL_CONTROLLER.P_UPDATE_ETL_TIME_INFO('DW','DIM_TIME');
    --BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','BICODE.PKG_LOAD_DIM_TIME.P_BUILD_DIM_TIME执行结束...');

    exception
      when others then
        rollback;
        --BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('异常','BICODE.PKG_LOAD_DIM_TIME.P_BUILD_DIM_TIME执行失败...');    
    
  end P_BUILD_DIM_TIME;
end PKG_LOAD_DIM_TIME;

有时候需要精确度到月份的时间维度表,由于上面的日期时间维度表相对信息比较丰富,可以建立一个视图来影射一个月度时间维度表,如下图所示:


具体的视图Sql如下所示:

CREATE OR REPLACE VIEW DW_DIM_TIME_MONTH_V AS
SELECT DISTINCT
  T.MONTH_CODE,
  T.MONTH_LONG_DESC,
  T.MONTH_MEDIUM_DESC,
  T.MONTH_SHORT_DESC,
  T.QUARTER_CODE,
  T.QUARTER_LONG_DESC,
  T.QUARTER_MEDIUM_DESC,
  T.QUARTER_SHORT_DESC,
  T.HALF_YEAR_CODE,
  T.HALF_LONG_DESC,
  T.HALF_MEDIUM_DESC,
  T.HALF_SHORT_DESC,
  T.YEAR_CODE,
  T.YEAR_LONG_DESC,
  T.YEAR_MEDIUM_DESC,
  T.YEAR_SHORT_DESC
FROM DIW.DW_DIM_TIME T;
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 204,793评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 87,567评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 151,342评论 0 338
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,825评论 1 277
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,814评论 5 368
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,680评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,033评论 3 399
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,687评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 42,175评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,668评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,775评论 1 332
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,419评论 4 321
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,020评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,978评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,206评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,092评论 2 351
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,510评论 2 343

推荐阅读更多精彩内容