date dimension generator by MaxComputeSQL

dim_date_2029.sql

-- DROP TABLE IF EXISTS dim_date_base;
CREATE TABLE IF NOT EXISTS dim_date_base (
	`dt` BIGINT COMMENT '日期dt，示例：20160821',
	`date_id` BIGINT COMMENT '日期id，示例：20160821',
	`date` STRING COMMENT '日期，示例：2016-08-21',

	`day_num` BIGINT COMMENT '日计数，起始日期开始',
	`day_of_week` BIGINT COMMENT '所在周的第几天，从每周一开始',
	`day_of_month` BIGINT COMMENT '所在月的第几天，从每月1号开始',
	`day_of_year` BIGINT COMMENT '所在年的第几天，从1月1号开始',

	`week_num` BIGINT COMMENT '自然周计数，从起始日期开始(周一至周日)',
	`week_of_month` BIGINT COMMENT '一年中的第几周',
	`week_of_year` BIGINT COMMENT '当月中的第几周',
	`week_begin_date` STRING COMMENT '自然周起始日期',
	`week_begin_dt` BIGINT COMMENT '自然周起始日期datekey',
	`week_end_date` STRING COMMENT '自然周结束日期',
	`week_end_dt` BIGINT COMMENT '自然周结束日期datekey',
	`year_week_num` STRING COMMENT '年内周计数，如2016wk32',

	`month_num` BIGINT COMMENT '月计数，从起始日期开始',
	`mo` BIGINT COMMENT '同month_num，计算方式(year-1970)*12+month-1',
	`month` BIGINT COMMENT '月，如8',
	`year_mo` BIGINT COMMENT '年月，如201608',
	`month_begin_date` STRING COMMENT '本月起始日期',
	`month_begin_dt` BIGINT COMMENT '本月起始日期datekey',
	`month_end_date` STRING COMMENT '本月结束日期',
	`month_end_dt` BIGINT COMMENT '本月结束日期datekey',

	`quarter_num` BIGINT COMMENT '季度计数，从起始日期开始',
	`quarter` BIGINT COMMENT '季度',
	`year_quarter` BIGINT COMMENT '年与季度的组合，如20163',
	`year_quarter_name` STRING COMMENT '年与季度的组合，如2016q3',

	`year` BIGINT COMMENT '年',
    
	`is_weekend` BIGINT COMMENT '是否周末',
	`is_workday` BIGINT COMMENT '是否工作日'
	-- `is_holiday` BIGINT COMMENT '是否节假日',
	-- `festival_name` STRING COMMENT '节假日名称，如中秋节',
	-- `day_of_week_cn` STRING COMMENT '星期'
)
COMMENT '日期维度表-基础表 时间跨度为2017-2029';

with
base_id as ( 
    -- 要实现时间跨度为20170101至20291231, 需构造一张行数刚好等于天数的母表, 即 4748=365*13+3 (3个闰日)
    -- 选择一张至少有 4748 行记录的表, 这里取名为 x
    select
        (count(1) over (partition by 1 order by 1)) - 1 id --从0开始自增
    from x
    limit 4748
),
base_timeline as ( -- 日期自增
    select
        dateadd('2017-01-01 00:00:00', id, 'dd') time
        -- dateadd('1970-01-01 00:00:00', id, 'dd') time
    from base_id
), 
base_dt as ( -- 日期组件
    select
        time
        , to_char(time, 'yyyymmdd') dt
        , to_char(time, 'yyyy-mm-dd') date
        , to_char(time, 'yyyy') year
        , to_char(time, 'mm') month
        , to_char(time, 'dd') day
        , floor(int(to_char(time, 'mm'))/4)+1 quarter

        , to_char(time, 'yyyy-01-01 00:00:00') year_begin_time
        , to_char(time, 'yyyy0101') year_begin_dt
        , to_char(time, 'yyyy-mm-01 00:00:00') month_begin_time
        , to_char(time, 'yyyymm01') month_begin_dt

        , datediff(time, '1970-01-01 00:00:00', 'dd')+1 day_num
        , datediff(time, '1970-01-01 00:00:00', 'mm')+1 month_num
        , floor(datediff(time, '1969-12-29 00:00:00', 'dd')/7)+1 week_num  --19700101是周四, 这周的周一是19691229
        , floor(datediff(time, '1970-01-01 00:00:00', 'mm'))+1 quarter_num
    from base_timeline
),
base_dtx as ( 
    -- 冗余 year/month_begin_week_num字段用于算 week_of_year/month
    select
        t.*
        , ybd.week_num year_begin_week_num
        , mbd.week_num month_begin_week_num
    from base_dt t -- today
    join base_dt ybd on t.year_begin_dt=ybd.dt
    join base_dt mbd on t.month_begin_dt=mbd.dt
),
dim_date as (
    select
        dt dt
        , dt date_id
        , date date

        -- day
        , day_num day_num
        , weekday(time)+1 day_of_week
        , int(day) day_of_month
        , datediff(time, year_begin_time, 'dd')+1 day_of_year
        
        -- week
        , week_num week_num
        , week_num - month_begin_week_num + 1 week_of_month
        , week_num - year_begin_week_num + 1 week_of_year
        -- , dateadd(time, -weekday(time), 'dd') week_begin_datetime
        , to_char(dateadd(time, -weekday(time), 'dd'), 'yyyy-mm-dd') week_begin_date
        , to_char(dateadd(time, -weekday(time), 'dd'), 'yyyymmdd') week_begin_dt
        -- , dateadd(time, 6-weekday(time), 'dd') week_end_datetime
        , to_char(dateadd(time, 6-weekday(time), 'dd'), 'yyyy-mm-dd') week_end_date
        , to_char(dateadd(time, 6-weekday(time), 'dd'), 'yyyymmdd') week_end_dt
        , concat(year, 'wk', week_num - year_begin_week_num + 1) year_week -- year_week_num

        -- month
        , month_num month_num
        , month_num mo
        , month
        , concat(year, month) year_mo
        , to_char(month_begin_time, 'yyyy-mm-dd') month_begin_date
        , month_begin_dt
        , to_char(dateadd(dateadd(month_begin_time, 1, 'mm'), -1, 'dd'), 'yyyy-mm-dd') month_end_date
        , to_char(dateadd(dateadd(month_begin_time, 1, 'mm'), -1, 'dd'), 'yyyymmdd') month_end_dt

        -- quarter
        , quarter_num
        , quarter
        , concat(year, quarter) year_quarter
        , concat(year, 'q', quarter) year_quarter_name

        -- year
        , year

        -- 假期相关 这里只有周的工作日和非工作日, 假期逻辑需单独实现
        , if(weekday(time)>=5, 1, 0) is_weekend
        , if(weekday(time)>=5, 1, 0) is_workday

        -- , is_holiday
        -- , festival_name
        -- , day_of_week_cn

    from base_dtx t
)
insert overwrite table dim_date_base
select * from dim_date;
-- select * from base_dtx;