Oracle生成日期维度
一、创建一张表用于保存日期维度数据
日期维度表
create table t_date
(
sys_date number primary key not null ,--系统日期
year number not null ,--年份
year_start number not null ,--年初日期
year_end number not null ,--年末日期
last_year number not null ,--上年年份
last_year_start number not null ,--上年年初日期
last_year_end number not null ,--上年年末日期
quarter number not null ,--季度
quarter_start number not null ,--季初日期
quarter_end number not null ,--季末日期
last_quarter_start number not null ,--上季季初日期
last_quarter_end number not null ,--上季季末日期
next_quarter_start number not null ,--下季季初日期
next_quarter_end number not null ,--下季季末日期
month number not null ,--月份
month_start number not null ,--月初日期
month_end number not null ,--月末日期
last_month_start number not null ,--上月月初日期
last_month_end number not null ,--上月月末日期
next_month_start number not null ,--下月月初日期
next_month_end number not null ,--下月月末日期
year_week number not null ,--周年数(本年度第几周)
month_week number not null ,--月周数(本月第几周)
day_week number not null --周数(星期几)
);
二、创建相关日期计算函数
计算年初日期
create or replace function f_get_year_start
(
sys_date in number --系统日期
)
return number
as
/*计算年初日期*/
begin
return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'yyyy'),'yyyyMMdd'));
end;
计算年末日期
create or replace function f_get_year_end
(
sys_date in number --系统日期
)
return number
as
/*计算年末日期*/
begin
return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'yyyy'),12)-1,'yyyyMMdd'));
end;
计算上年年初日期
create or replace function f_get_last_year_start
(
sys_date in number --系统日期
)
return number
as
/*计算上年年初日期*/
begin
return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'yyyy'),-12),'yyyyMMdd'));
end;
计算上年年末日期
create or replace function f_get_last_year_end
(
sys_date in number --系统日期
)
return number
as
/*计算上年年末日期*/
begin
return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'yyyy')-1,'yyyyMMdd'));
end;
计算季度
create or replace function f_get_quarter
(
sys_date in number --系统日期
)
return number
as
/*计算季度*/
begin
return to_number(to_char(to_date(sys_date,'yyyyMMdd'),'Q'));
end;
计算季初日期
create or replace function f_get_quarter_start
(
sys_date in number --系统日期
)
return number
as
/*计算季初日期*/
begin
return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'Q'),'yyyyMMdd'));
end;
计算季末日期
create or replace function f_get_quarter_end
(
sys_date in number --系统日期
)
return number
as
/*计算季末日期*/
begin
return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'Q'),3)-1,'yyyyMMdd'));
end;
计算上季季初日期
create or replace function f_get_last_quarter_start
(
sys_date in number --系统日期
)
return number
as
/*计算上季季初日期*/
begin
return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'Q'),-3),'yyyyMMdd'));
end;
计算上季季末日期
create or replace function f_get_last_quarter_end
(
sys_date in number --系统日期
)
return number
as
/*计算上季季末日期*/
begin
return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'Q')-1,'yyyyMMdd'));
end;
计算下季季初日期
create or replace function f_get_next_quarter_start
(
sys_date in number --系统日期
)
return number
as
/*计算下季季初日期*/
begin
return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'Q'),3),'yyyyMMdd'));
end;
计算下季季末日期
create or replace function f_get_next_quarter_end
(
sys_date in number --系统日期
)
return number
as
/*计算下季季末日期*/
begin
return f_get_quarter_end(f_get_next_quarter_start(sys_date));
end;
计算月初日期
create or replace function f_get_month_start
(
sys_date in number --系统日期
)
return number
as
/*计算月初日期*/
begin
return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'MM'),'yyyyMMdd'));
end;
计算月末日期
create or replace function f_get_month_end
(
sys_date in number --系统日期
)
return number
as
/*计算月末日期*/
begin
return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'MM'),1)-1,'yyyyMMdd'));
end;
计算上月月初日期
create or replace function f_get_last_month_start
(
sys_date in number --系统日期
)
return number
as
/*计算上月月初日期*/
begin
return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'MM'),-1),'yyyyMMdd'));
end;
计算上月月末日期
create or replace function f_get_last_month_end
(
sys_date in number --系统日期
)
return number
as
/*计算上月月末日期*/
begin
return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'MM')-1,'yyyyMMdd'));
end;
计算下月月初日期
create or replace function f_get_next_month_start
(
sys_date in number --系统日期
)
return number
as
/*计算下月月初日期*/
begin
return to_number(to_char(add_months(trunc(to_date(sys_date,'yyyyMMdd'),'MM'),1),'yyyyMMdd'));
end;
计算下月月末日期
create or replace function f_get_next_month_end
(
sys_date in number --系统日期
)
return number
as
/*计算下月月末日期*/
begin
return f_get_month_end(f_get_next_month_start(sys_date));
end;
计算本周第一天日期
create or replace function f_get_monday
(
sys_date in number --系统日期
)
return number
as
/*计算本周第一天日期(星期一)*/
v_sunday number;
begin
v_sunday:=to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'D'),'yyyyMMdd'));
if sys_date=v_sunday then /*如果当前日期是星期日,则本周第一天日期为当前日期-6*/
return to_number(to_char(to_date(sys_date,'yyyyMMdd')-6,'yyyyMMdd'));
else
return to_number(to_char(trunc(to_date(sys_date,'yyyyMMdd'),'D')+1,'yyyyMMdd'));
end if;
end;
计算两个日期之间的天数差
create or replace function f_get_days
(
start_date in number ,--起始日期
end_date in number --结束日期
)
return number
as
/*计算两个日期间的天数差*/
begin
return abs(to_date(end_date,'yyyyMMdd')-to_date(start_date,'yyyyMMdd'));
end;
计算年周数(当前日期在本年度的第几周)
create or replace function f_get_year_week
(
sys_date in number --系统日期
)
return number
as
/*计算年周数(本年度第几周)*/
begin
return f_get_days(f_get_monday(f_get_year_start(sys_date)),f_get_monday(sys_date))/7+1;
end;
计算月周数(当前日期在本月的第几周)
create or replace function f_get_month_week
(
sys_date in number --系统日期
)
return number
as
/*计算月周数(本月第几周)*/
begin
return f_get_days(f_get_monday(f_get_month_start(sys_date)),f_get_monday(sys_date))/7+1;
end;
计算周数(当前日期是星期几)
create or replace function f_get_day_week
(
sys_date in number --系统日期
)
return number
as
/*计算周数(星期几)*/
begin
return f_get_days(f_get_monday(sys_date),sys_date)+1;
end;
三、创建存储过程，用于生成日期维度数据
存储过程
create or replace procedure sp_generate_date
(
start_date in number ,--起始日期
end_date in number --结束日期
)
as
v_date date;--日期类型变量
v_sys_date number;--系统日期
v_year number;--年份
v_year_start number;--年初日期
v_year_end number;--年末日期
v_last_year number;--上年年份
v_last_year_start number;--上年年初日期
v_last_year_end number;--上年年末日期
v_quarter number;--季度
v_quarter_start number;--季初日期
v_quarter_end number;--季末日期
v_last_quarter_start number;--上季季初日期
v_last_quarter_end number;--上季季末日期
v_next_quarter_start number;--下季季初日期
v_next_quarter_end number;--下季季末日期
v_month number;--月份
v_month_start number;--月初日期
v_month_end number;--月末日期
v_last_month_start number;--上月月初日期
v_last_month_end number;--上月月末日期
v_next_month_start number;--下月月初日期
v_next_month_end number;--下月月末日期
v_year_week number;--年周数(本年度第几周)
v_month_week number;--月周数(本月第几周)
v_day_week number;--周数(星期几)
begin
v_date:=to_date(start_date,'yyyyMMdd');
while (v_date<=to_date(end_date,'yyyyMMdd')) loop
v_sys_date:=to_number(to_char(v_date,'yyyyMMdd'));
v_year:=to_number(substr(v_sys_date,1,4));
v_year_start:=F_GET_YEAR_START(v_sys_date);
v_year_end:=F_GET_YEAR_END(v_sys_date);
v_last_year:=to_number(substr(F_GET_LAST_YEAR_START(v_sys_date),1,4));
v_last_year_start:=F_GET_LAST_YEAR_START(v_sys_date);
v_last_year_end:=F_GET_LAST_YEAR_END(v_sys_date);
v_quarter:=F_GET_QUARTER(v_sys_date);
v_quarter_start:=F_GET_QUARTER_START(v_sys_date);
v_quarter_end:=F_GET_QUARTER_END(v_sys_date);
v_last_quarter_start:=F_GET_LAST_QUARTER_START(v_sys_date);
v_last_quarter_end:=F_GET_LAST_QUARTER_END(v_sys_date);
v_next_quarter_start:=F_GET_NEXT_QUARTER_START(v_sys_date);
v_next_quarter_end:=F_GET_NEXT_QUARTER_END(v_sys_date);
v_month:=to_number(substr(v_sys_date,5,2));
v_month_start:=F_GET_MONTH_START(v_sys_date);
v_month_end:=F_GET_MONTH_END(v_sys_date);
v_last_month_start:=F_GET_LAST_MONTH_START(v_sys_date);
v_last_month_end:=F_GET_LAST_MONTH_END(v_sys_date);
v_next_month_start:=F_GET_NEXT_MONTH_START(v_sys_date);
v_next_month_end:=F_GET_NEXT_MONTH_END(v_sys_date);
v_year_week:=F_GET_YEAR_WEEK(v_sys_date);
v_month_week:=F_GET_MONTH_WEEK(v_sys_date);
v_day_week:=F_GET_DAY_WEEK(v_sys_date);
insert into T_DATE(sys_date, year, year_start, year_end, last_year, last_year_start, last_year_end, quarter,
quarter_start, quarter_end, last_quarter_start, last_quarter_end, next_quarter_start,
next_quarter_end, month, month_start, month_end, last_month_start, last_month_end,
next_month_start, next_month_end, year_week, month_week, day_week)
VALUES (v_sys_date,v_year,v_year_start,v_year_end,v_last_year,v_last_year_start,v_last_year_end,v_quarter,
v_quarter_start,v_quarter_end,v_last_quarter_start,v_last_quarter_end,v_next_quarter_start,
v_next_quarter_end,v_month,v_month_start,v_month_end,v_last_month_start,v_last_month_end,
v_next_month_start,v_next_month_end,v_year_week,v_month_week,v_day_week);
v_date:=v_date+1;
end loop;
commit ;
end;
四、调用存储过程，生成日期维度数据
调用存储过程
call SP_GENERATE_DATE(20000101,29991231);
