BI-日期维度表-SQL SERVER
1.创建日期维度表
create table [dbo].[dw_dim_date]( [date_key] [int] not null, [date_date] [date] null, [year_key] [int] null, [year_code] [nvarchar](30) null, [year_month_key] [int] null, [year_quater_key] [int] null, [year_week_key] [int] null, [year_start_day] [int] null, [year_end_day] [int] null, [quater_key] [int] null, [quater_code] [nvarchar](30) null, [quater_year_name_short_en] [nvarchar](30) null, [quater_year_name_full_en] [nvarchar](30) null, [quater_name_cn] [nvarchar](30) null, [quater_name_en] [nvarchar](30) null, [quarter_start_day] [int] null, [quarter_end_day] [int] null, [month_key] [int] null, [month_code] [nvarchar](30) null, [month_year_name_short_en] [nvarchar](30) null, [month_year_name_full_en] [nvarchar](30) null, [month_name_short_en] [nvarchar](30) null, [month_name_full_en] [nvarchar](30) null, [month_start_day] [int] null, [month_end_day] [int] null, [week_key] [int] null, [week_code] [nvarchar](30) null, [week_year_name_short_en] [nvarchar](30) null, [week_year_name_full_en] [nvarchar](30) null, [week_start_day] [int] null, [week_end_day] [int] null, [week_period_start_end] [nvarchar](30) null, [week_day_key] [int] null, [week_day_name_cn] [nvarchar](30) null, [week_day_name_en] [nvarchar](30) null, [week_day_name_short_en] [nvarchar](30) null, [day_key] [int] null, [day_code] [nvarchar](30) null, [day_month_year_name_short_en] [nvarchar](30) null, [day_month_year_name_full_en] [nvarchar](30) null, [day_month_name_short_en] [nvarchar](30) null, [day_month_name_full_en] [nvarchar](30) null, [day_name_short_en] [nvarchar](30) null, [day_name_full_en] [nvarchar](30) null, [day_start_day] [int] null, [day_end_day] [int] null, [etl_batch_id] [int] null, [etl_created_date] [datetime] null, [etl_created_by] [nvarchar](400) null, [etl_modified_date] [datetime] null, [etl_modified_by] [nvarchar](400) null, [etl_del_flag] [nvarchar](20) null, [etl_del_date] [datetime] null, [etl_del_by] [nvarchar](400) null, primary key clustered ( [date_key] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [PRIMARY] ) on [PRIMARY] go
2.日期维度表存储过程
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
------------------------------------------
--Author:zegui wan
--Created Date:
--Object Name:[dbo].[p_etl_InitData_dw_dim_date]
--Description: date dimension data init and save
--UT CODE:
--exec [dbo].[p_etl_InitData_dw_dim_date] @batch_id = -1,@init_begin_date = '1900-01-01', @init_end_date = '2099-12-31'
--SELECT COUNT(1) FROM dw_dim_date with(nolock) where year_key=2019
------------------------------------------
create proc [dbo].[p_etl_InitData_dw_dim_date]
(
--declare
@batch_id int = -1,
@init_begin_date date = '1900-01-01',
@init_end_date date = '2099-12-31'
)
as
begin
set nocount on;
begin transaction tran1;
begin try
SET DATEFIRST 1 --7标识：周一为一周的开始日期
--declare @init_begin_date date;
--declare @init_end_date date;
--set @init_begin_date = '2018-01-01';
--set @init_end_date = '2018-01-01';
delete from dbo.dw_dim_date
where
date_date >= @init_begin_date
and date_date <= @init_end_date;
declare @WhileCnt int;
set @WhileCnt = datediff(day, @init_begin_date, @init_end_date);
--SELECT @WhileCnt
declare @WhileStep int = 0;
declare
@current_date date, --当前日期-日期格式
@current_date_key int; --当前日期-整数形式
declare @day_month_year_name_short_en nvarchar(30);
declare @day_month_year_name_full_en nvarchar(30);
declare @first_date_of_the_next_year date; --下一年第一天
declare @year_key int; --所属年份
declare @year_code nvarchar(30);--
declare @year_month_key int; -- 所属年月 YYYYMM格式
declare @month_key int; --月份key：1,2,3,4,...,12
declare @month_code nvarchar(30); --年份code：01,02
declare @month_name_short_en nvarchar(30); --月份名称简写
declare @month_name_full_en nvarchar(30); --月份名称全写
declare @month_year_name_short_en nvarchar(30); --月份名称简写带年份
declare @month_year_name_full_en nvarchar(30); --月份名称全写带年份
declare @month_of_the_year int; --当年第几月
declare @first_day_of_the_year int; --当年第一天
declare @first_day_of_the_year_date date; --当年第一天
declare @last_day_of_the_year int; --当年最后一天
declare @last_day_of_the_year_date date; --当年最后一天
declare @first_date_of_the_quater date; --当季第一天日期
declare @first_day_of_the_quater int; --当季第一天
declare @last_date_of_the_quater date; --当季最后一天
declare @last_day_of_the_quater int; --当季最后一天
declare @first_date_of_the_month date; --当月第一天
declare @first_day_of_the_month int; --当月第一天
declare @last_date_of_the_month date; --当月最后一天
declare @last_day_of_the_month int; --当月最后一天
declare @week_key int; --所属第几周
declare @week_code nvarchar(30); --
declare @week_year_name_short_en nvarchar(30); --周年名称-短
declare @week_year_name_full_en nvarchar(30); --周年名称-全称
declare @day_of_the_week int; --所属星期几
declare @week_day_key int; --所属星期几
declare @week_day_name_en nvarchar(30); --所属星期几英文
declare @week_day_name_short_en nvarchar(30); --所属星期几英文
declare @week_day_name_cn nvarchar(30); --所属星期几中午
declare @first_date_of_the_week date; --当周第一天
declare @first_date_of_the_week_normal date;--周的正常第一天
declare @first_day_of_the_week int; --当周第一天
declare @last_date_of_the_week date; --当周最后一天
declare @last_date_of_the_week_normal date;--当周正常最后一天
declare @last_day_of_the_week int; --当周最后一天
declare @week_period_start_end nvarchar(30); --周起止日期
declare @quater_key int; --所属季度
declare @quater_code nvarchar(30); --所属记得CODE
declare @quater_year_name_short_en nvarchar(30); --所属记得CODE
declare @quater_year_name_full_en nvarchar(30); --所属记得CODE
declare @quater_name_en nvarchar(30); --所属季度英文名称
declare @quater_name_cn nvarchar(30); --所属季度中文名称
declare @year_quater_key int;
declare @year_week_key int;
declare @year_start_day int;
declare @year_end_day int;
declare @day_key int; --demo: 1
declare @day_code nvarchar(30); --demo: 01
declare @day_month_name_short_en nvarchar(30); --demo: 01-Jan
declare @day_month_name_full_en nvarchar(30); --demo: 01-January
declare @day_name_short_en nvarchar(30); --demo: 01
declare @day_name_full_en nvarchar(30); --demo: 01
declare @day_start_day int; --demo: 20190101
declare @day_end_day int; --demo: 20190101
while @WhileStep <= @WhileCnt
begin
set @current_date = dateadd(day, @WhileStep, @init_begin_date); --CONVERT(DATE, GETDATE()); --获取当前日期
--SELECT @current_date
set @current_date_key = convert(nvarchar, @current_date, 112); --获取当前日期KEY
set @first_date_of_the_next_year
= cast((convert(nvarchar(30), (year(@current_date) + 1)) + '-01-01') as date);
------------------------logical start-------------------------------------
set @year_key = @current_date_key / 10000; --获取所属年度
set @year_code=@current_date_key / 10000;
set @year_month_key = @current_date_key / 100; --获取所属年月
set @year_month_key = @current_date_key / 100;
set @year_quater_key = @year_key * 100 + datepart(quarter, @current_date);
set @month_key = datepart(month, @current_date);
set @month_code = right((N'0' + convert(nvarchar(2), datepart(month, @current_date))), 2);
set @month_name_full_en = datename(month, @current_date); --获取月份名称
set @month_name_short_en = left(datename(month, @current_date), 3); --获取月份名称-简写
set @month_year_name_short_en
= @month_name_short_en + N'-' + right(convert(nvarchar(30), @year_key), 2);
set @month_year_name_full_en = @month_name_short_en + N'-' + convert(nvarchar(30), @year_key);
set @month_of_the_year = month(@current_date); --获取所属几月
set @first_day_of_the_year = (@current_date_key / 10000) * 10000 + 101; --获取当年第一天
set @first_day_of_the_year_date = convert(date, left(@current_date_key, 4) + '-01-01');
set @last_day_of_the_year_date = convert(date, left(@current_date_key, 4) + '-12-31');
--SELECT @first_day_of_the_year_date
--SELECT @first_day_of_the_year
set @last_day_of_the_year
= convert(nvarchar(30), dateadd(day, -1, @first_date_of_the_next_year), 112); -- 当年最后一天(思路：下年的第一天减去一天)
--SET @first_day_of_the_year=SELECT @current_date
--SELECT DATEDIFF(DAY,'2020-01-01','2021-01-01')+1 --闰年366天
--SELECT CONVERT(char(8),getdate(),120) --获取8位YYYY-MM-格式字符串日期
--SELECT @last_day_of_the_year AS [获取当年最后一天];
--获取当季第一天日期
set @first_date_of_the_quater
= convert(
date,
convert(
char(8),
dateadd(
month,
datepart(quarter, @current_date) * 3 - month(@current_date)
- 2, @current_date
), 120
) + '01'
);
set @first_day_of_the_quater = convert(nvarchar(30), @first_date_of_the_quater, 112); --当季第一天转换为整数
--SELECT @first_day_of_the_quater
--获取季度的最后一天
set @last_date_of_the_quater
= dateadd(
day, -1,
convert(
char(8),
dateadd(
month,
1 + datepart(quarter, @current_date) * 3
- month(@current_date), @current_date
), 120
) + '01'
);
set @last_day_of_the_quater = convert(nvarchar(30), @last_date_of_the_quater, 112);
--SELECT @last_day_of_the_quater AS [获取季度的最后一天]
--获取当月第一天
set @first_date_of_the_month = convert(date, convert(char(8), @current_date, 120) + '01');
set @first_day_of_the_month = convert(nvarchar(30), @first_date_of_the_month, 112);
--SELECT @first_day_of_the_month AS [获取当月第一天]
--获取当月最后一天(获取下一月的第一天，再减1，即为当月的最后一天)
set @last_date_of_the_month
= dateadd(day, -1, convert(char(8), dateadd(month, 1, @current_date), 120) + '01');
set @last_day_of_the_month = convert(nvarchar(30), @last_date_of_the_month, 112);
--SELECT @last_day_of_the_month AS [获取当月最后一天]
--获取第几周
set @week_key = datepart(week, @current_date);
--SELECT @week_key AS [所属第几周]
set @week_code = convert(nvarchar(30), N'W' + convert(nvarchar(10), @week_key));
set @year_week_key = @year_key * 100 + @week_key;
--获取星期几
set @day_of_the_week = datepart(weekday, @current_date);
--SELECT @day_of_the_week
--SELECT @day_of_the_week AS [星期几]
--星期几英文
select
@week_day_name_en = datename(weekday, @current_date)
set @week_day_name_short_en = left(@week_day_name_en, 3); --三字母简写
--星期几中文
select
@week_day_name_cn = case @week_day_name_en
when N'Sunday' then
N'星期日'
when N'Monday' then
N'星期一'
when N'Tuesday' then
N'星期二'
when 'Wednesday' then
N'星期三'
when 'Thursday' then
N'星期四'
when N'Friday' then
N'星期五'
when N'Saturday' then
N'星期六'
else 'Unknow'
end;
--星期几转换数字
select @week_day_key = datepart(weekday, @current_date)
--SELECT @day_of_the_week AS day_of_the_week
--本周一的日期
set @first_date_of_the_week_normal=dateadd(day,-datepart(weekday, @current_date)+1,@current_date)
--获取当前星期几减1，表示星期几数字:获取当前周的第一天
set @first_date_of_the_week
= case
when @first_date_of_the_week_normal < @first_day_of_the_year_date then
@first_day_of_the_year_date
else @first_date_of_the_week_normal
end;
set @first_day_of_the_week = convert(nvarchar(30), @first_date_of_the_week, 112);
--SELECT @day_of_the_week as day_of_the_week, @first_date_of_the_week as first_date_of_the_week,@first_day_of_the_week AS [当前周第一天]
--获取当前星期几减1，表示星期几数字:获取当前周的最后一天
--本周日的日期
set @last_date_of_the_week_normal=dateadd(day,6,@first_date_of_the_week_normal)
set @last_date_of_the_week
= case
when @last_date_of_the_week_normal > @last_day_of_the_year_date then
@last_day_of_the_year_date
else @last_date_of_the_week_normal
end;
set @last_day_of_the_week = convert(nvarchar(30), @last_date_of_the_week, 112);
--SELECT @last_date_of_the_week_normal,@last_day_of_the_week AS [当前周最后一天];
set @week_period_start_end
= convert(nvarchar(30), @first_day_of_the_week) + N'-'
+ convert(nvarchar(30), @last_day_of_the_week);
--获取所属季度
set @quater_key = datepart(quarter, @current_date);
set @quater_code = case @quater_key
when 1 then
N'Q1'
when 2 then
N'Q2'
when 3 then
N'Q3'
else N'Q4'
end;
set @quater_year_name_short_en = @quater_code + N'-' + right(convert(nvarchar(30), @year_key), 2);
set @quater_year_name_full_en = @quater_code + N'-' + convert(nvarchar(30), @year_key);
--SELECT @quater_key AS [Quater_KEY]
--季节英文文
set @quater_name_en = case @quater_key
when 1 then
N'Spring'
when 2 then
N'Summer'
when 3 then
N'Autumn'
else N'Winter'
end;
--SELECT @quater_name_en;
--季节中文
set @quater_name_cn = case @quater_key
when 1 then
N'春季'
when 2 then
N'夏季'
when 3 then
N'秋季'
else N'冬季'
end;
--SELECT @quater_name_cn;
set @week_year_name_full_en
= convert(
nvarchar(30),
N'W' + convert(nvarchar(10), @week_key) + N'-' + convert(nvarchar(4), @year_key)
);
set @week_year_name_short_en
= convert(
nvarchar(30),
N'W' + convert(nvarchar(10), @week_key) + N'-'
+ right(convert(nvarchar(10), @year_key), 2)
);
--SELECT @month_year_name_full_en
--SELECT @day_month_year_name_full_en
--SELECT CONVERT(NVARCHAR(2),DAY(@current_date))+N'-'+@month_year_name_full_en
set @day_key = datepart(day, @current_date);
set @day_code = right((N'0' + convert(nvarchar(30), datepart(day, @current_date))),2);
set @day_month_year_name_full_en = @day_code + N'-' + @month_year_name_full_en;
set @day_month_year_name_short_en = @day_code + N'-' + @month_year_name_short_en;
set @day_month_name_short_en = @day_code + N'-' + @month_name_short_en;
set @day_month_name_full_en = @day_code + N'-' + @month_name_full_en;
set @day_name_short_en = @day_code;
set @day_name_full_en = @day_code;
set @day_start_day = @current_date_key;
set @day_end_day = @current_date_key;
insert into dbo.dw_dim_date
(
date_key,
date_date,
year_key,
year_code,
year_month_key,
year_quater_key,
year_week_key,
year_start_day,
year_end_day,
quater_key,
quater_code,
quater_year_name_short_en,
quater_year_name_full_en,
quater_name_cn,
quater_name_en,
quarter_start_day,
quarter_end_day,
month_key,
month_code,
month_year_name_short_en,
month_year_name_full_en,
month_name_short_en,
month_name_full_en,
month_start_day,
month_end_day,
week_key,
week_code,
week_year_name_short_en,
week_year_name_full_en,
week_start_day,
week_end_day,
week_period_start_end,
week_day_key,
week_day_name_cn,
week_day_name_en,
week_day_name_short_en,
day_key,
day_code,
day_month_year_name_short_en,
day_month_year_name_full_en,
day_month_name_short_en,
day_month_name_full_en,
day_name_short_en,
day_name_full_en,
day_start_day,
day_end_day,
etl_batch_id,
etl_created_date,
etl_created_by,
etl_modified_date,
etl_modified_by,
etl_del_flag,
etl_del_date,
etl_del_by
)
select
@current_date_key as date_key,
@current_date as date_date,
@year_key as year_key,
@year_code as year_code,
@year_month_key as year_month_key,
@year_quater_key as year_quater_key,
@year_week_key as year_week_key,
@first_day_of_the_year as year_start_day,
@last_day_of_the_year as year_end_day,
@quater_key as quater_key,
@quater_code as quater_code,
@quater_year_name_short_en as quater_year_name_short_en,
@quater_year_name_full_en as quater_year_name_full_en,
@quater_name_cn as quater_name_cn,
@quater_name_en as quater_name_en,
@first_day_of_the_quater as quarter_start_day,
@last_day_of_the_quater as quarter_end_day,
@month_key as month_key,
@month_code as month_code,
@month_year_name_short_en as month_year_name_short_en,
@month_year_name_full_en as month_year_name_full_en,
@month_name_short_en as month_name_short_en,
@month_name_full_en as month_name_full_en,
@first_day_of_the_month as month_start_day,
@last_day_of_the_month as month_end_day,
@week_key as week_key,
@week_code as week_code,
@week_year_name_short_en as week_year_name_short_en,
@week_year_name_full_en as week_year_name_full_en,
@first_day_of_the_week as week_start_day,
@last_day_of_the_week as week_end_day,
@week_period_start_end as week_period_start_end,
@week_day_key as week_day_key,
@week_day_name_cn as week_day_name_cn,
@week_day_name_en as week_day_name_en,
@week_day_name_short_en as week_day_name_en_short,
@day_key as day_key,
@day_code as day_code,
@day_month_year_name_short_en as day_month_year_name_short_en,
@day_month_year_name_full_en as day_month_year_name_full_en,
@day_month_name_short_en as day_month_name_short_en,
@day_month_name_full_en as day_month_name_full_en,
@day_name_short_en as day_name_short_en,
@day_name_full_en as day_name_full_en,
@day_start_day as day_start_day,
@day_end_day as day_end_day,
'-1' as etl_batch_id, --int
getdate() as etl_created_date, --datetime
N'system' as etl_created_by, --nvarchar(200)
getdate() as etl_modified_date, --datetime
N'system' as etl_modified_by, --nvarchar(200)
N'N' as etl_del_flag, --nvarchar(10)
null as etl_del_date, --datetime
null as etl_del_by --nvarchar(200)
;
--循环自增
set @WhileStep = @WhileStep + 1;
end;
commit transaction tran1;
end try
begin catch
rollback transaction tran1;
end catch;
set nocount off;
end;
GO
3.执行存储过程：初始化数据
exec [dbo].[p_etl_InitData_dw_dim_date] @batch_id = -1,@init_begin_date = '1900-01-01', @init_end_date = '2099-12-31'
4.数据效果图
