☆用心生活☆-wonder-{改变自己才能让自己过上不一样的日子。为自己加油!}

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.数据效果图

 

 

 

 

posted @ 2020-05-26 20:58  ☆用心生活☆  阅读(465)  评论(0编辑  收藏  举报
本作者写的任何文章均属原创,若要转载,请注明出处;若
进行与文章有关的商业行为,本作者保留拥有诉诸法律追究
侵权的任何行为。文章只代表本人某一学习时间段内对某一
技术的说明,不代表具有绝对的正确性,若要学习,请与各
位高手编程人员讨论学习。本文章也不代表自己某一时期某
一公司的任何专著和机密文件。仅仅提供自我学习和他人学
习的文章参考,以及自我知识记录。wanzegui325#163.com