在数据仓库中，无一例外地需要和时间维度打交道，因此设计合理的时间维度，也是一个数据仓库项目开始必备的资源储备，如果有这方面的积累，就不用到处寻找合适的设计模型以及存储过程的代码了，否则可能需要花费一定的时间去寻找符合该项目合适的存储过程，或者自己动手编写。
一般来说，时间维度的创建要根据实际的数据仓库应用来，基本上可以分为天、月的时间维度表，更细的可以分为半小时时间段，小时时间段等等，一般数据量大的时间维度都是利用存储过程来生成的。
下面介绍一些时间维度表的设计结构。
<1> 时间维度表1（两列都是字段）
维度属性 | 维度属性 |
日期关键字 | 日历年月 |
日期完全描述 | 日历季度 |
星期 | 日历半年度 |
纪元日编号 | 日历年 |
纪元周编号 | 财政周 |
纪元月编号 | 年度财政周数 |
日历日期编号 | 财政月 |
日历周编号 | 年度财政月数 |
日历月编号 | 财政年月 |
财政月日编号 | 财政季度 |
财政周编号 | 财政季年度 |
财政月编号 | 财政半年度 |
周末指示符 | 财政年 |
月末指示符 | 节假日指示符 |
日历周结束日期 | 星期指示符 |
年度日历周数 | 销售时令 |
日历月名 | 重大事件 |
年度日历月数 | 其它 |
<1> 时间维度表2 a.日期维度
| 名称
| 代码
| 数据类型
| 主外键
|
| 序列号 | C_ID | int | P |
| 日期 | C_DATE | datetime |
|
| 星期 | C_WEEK | varchar(12) |
|
| 月份 | C_MONTH | varchar(2) |
|
| 年 | C_YEAR | varchar(4) |
|
| 季度 | C_QUARTER | varchar(4) |
|
| 名称
| 代码
| 数据类型
| 主外键
|
| 序列号 | C_ID | Varchar(50) |
|
| 时段 | C_SHIDUAN | Varchar(4000) |
|
| 名称
| 代码
| 数据类型
| 主外键
|
| 序列号 | C_ID | int | P |
| 时间段 | C_Section | varchar(50) |
|
| 开始时间 | C_Begin | varchar(50) |
|
| 结束时间 | C_End | varchar(50) |
|
3、比较完整的时间维度设计
| Name
| Type
| Nullable
| Comments
|
| DAY_CODE | VARCHAR2(8) | N | 日代码 |
| DAY_LONG_DESC | VARCHAR2(30) | N | 日完整名称 |
| DAY_MEDIUM_DESC | VARCHAR2(30) | N | 日中等长度名 |
| DAY_SHORT_DESC | VARCHAR2(30) | N | 日短名 |
| WEEK_CODE | VARCHAR2(8) | N | 周代码 |
| WEEK_LONG_DESC | VARCHAR2(30) | N | 周完整名称 |
| WEEK_MEDIUM_DESC | VARCHAR2(30) | N | 周中等长度名 |
| WEEK_SHORT_DESC | VARCHAR2(30) | N | 周短名 |
| TEN_DAY_CODE | VARCHAR2(8) | N | 旬代码 |
| TEN_DAY_LONG_DESC | VARCHAR2(30) | N | 旬完整名称 |
| TEN_DAY_MEDIUM_DESC | VARCHAR2(30) | N | 旬中等长度名 |
| TEN_DAY_SHORT_DESC | VARCHAR2(30) | N | 旬短名 |
| MONTH_CODE | VARCHAR2(8) | N | 月代码 |
| MONTH_LONG_DESC | VARCHAR2(30) | N | 月完整名称 |
| MONTH_MEDIUM_DESC | VARCHAR2(30) | N | 月中等长度名 |
| MONTH_SHORT_DESC | VARCHAR2(30) | N | 月短名 |
| QUARTER_CODE | VARCHAR2(8) | N | 季代码 |
| QUARTER_LONG_DESC | VARCHAR2(30) | N | 季完整名称 |
| QUARTER_MEDIUM_DESC | VARCHAR2(30) | N | 季中等长度名 |
| QUARTER_SHORT_DESC | VARCHAR2(30) | N | 季短名 |
| HALF_YEAR_CODE | VARCHAR2(8) | N | 半年代码 |
| HALF_LONG_DESC | VARCHAR2(30) | N | 半年完整名称 |
| HALF_MEDIUM_DESC | VARCHAR2(30) | N | 半年中等长度名 |
| HALF_SHORT_DESC | VARCHAR2(30) | N | 半年短名 |
| YEAR_CODE | VARCHAR2(8) | N | 年代码 |
| YEAR_LONG_DESC | VARCHAR2(30) | N | 年完整名称 |
| YEAR_MEDIUM_DESC | VARCHAR2(30) | N | 年中等长度名 |
| YEAR_SHORT_DESC | VARCHAR2(30) | N | 年短名 |
| ALL_TIME_CODE | VARCHAR2(8) | N | 全部时间代码 |
| ALL_TIME_DESC | VARCHAR2(30) | N | 全部时间名称 |
| DAY_TIMESPAN | NUMBER(3) | N | 日时间跨天 |
| DAY_END_DATE | VARCHAR2(8) | N | 结束日期 |
| WEEK_TIMESPAN | NUMBER(3) | N | 周跨天数 |
| WEEK_END_DATE | VARCHAR2(8) | N | 周结束日期 |
| TEN_DAY_TIMESPAN | NUMBER(3) | N | 旬跨天数 |
| TEN_DAY_END_DATE | VARCHAR2(8) | N | 旬结束日期 |
| MONTH_TIMESPAN | NUMBER(3) | N | 月跨天数 |
| MONTH_END_DATE | VARCHAR2(8) | N | 月结束日期 |
| QUARTER_TIMESPAN | NUMBER(3) | N | 季跨天数 |
| QUARTER_END_DATE | VARCHAR2(8) | N | 季结束日期 |
| HALF_YEAR_TIMESPAN | NUMBER(3) | N | 半年跨天数 |
| HALF_YEAR_END_DATE | VARCHAR2(8) | N | 半年结束日期 |
| YEAR_TIMESPAN | NUMBER(3) | N | 年跨天数 |
| YEAR_END_DATE | VARCHAR2(8) | N | 年结束日期 |
| BDISABLED | CHAR(1) | N |
|
代码
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如下所示：
代码
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;
1473
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
