日期维度表是数据仓库设计中的重要部分，它在数据分析和商业智能领域有着很广泛的应用：
- 数据完整性和一致性：日期维度表提供了一系列连续的日期值，确保数据仓库中时间维度的完整性和一致性。这样，不同的事实表与日期维度表进行关联时，就可以使用一致的时间标识。
- 时间相关分析：日期维度表提供了时间序列数据的结构化视图，通过将时间作为一个独立的维度，可以进行各种时间相关的分析，如趋势分析、周期性分析、季节性影响分析等。
- 预测和规划：日期维度表通常包含一些未来日期，通过日期表我们不仅可以对历史数据进行分析，揭示数据的周期性趋势、季节性变化和长期发展规律，还可以对未来进行预测，为业务决策和规划提供有力支持。
因此，制作日期维度表信息尽量要全，相应的数字排序字段也要有，并且做到即拿即用。在生成日期表前，我们需要先熟悉下常见的日期函数并进行测试，以当前日期为例进行调试：
select -- 获取当前日期 curdate() calendar_date -- 获取当前年份 , year(curdate()) calendar_year -- 获取当前年份开始日期，makedate(year,dayofyear) dayofyear 参数为该年的第几天 , makedate(year(curdate()), 1) year_begin_dt -- 获取当前年份结束日期，根据当前年加1 结合获取当前年开始日期 减去1天即可 , makedate(year(curdate()) + 1, 1) - interval 1 day year_end_dt -- 获取当前年份第几周，weekofyear(date) 等价于 week(date,3)，3：1-53，周一为一周起始点，在这里选择1 , week(curdate(),1) calendar_week -- 获取当前年份周数字，yearweek(date,mode) mode 参数与week(date,mode) 一致 , yearweek(curdate(), 1) calendar_year_week -- 获取当前星期几数字，0(monday)-6(sunday),加1修正 , weekday(curdate()) + 1 calendar_weekday -- 获取当前星期几英文名称 , date_format(curdate(), '%W') calendar_weekday_name -- 获取当前周的起始日期,减去当前日期对应的星期几数字 , date_sub(curdate(), interval weekday(curdate()) day) week_begin_dt -- 获取当前周的结束日期，加上本周剩余天数(6减去当前日期对应的星期几数字) , date_add(curdate(), interval 6 - weekday(curdate()) day) week_end_dt -- 获取当前月份数字 , month(curdate()) calendar_month -- 获取当前月份英文名称，也可用date_format(date,'%m) , monthname(curdate()) calendar_month_name -- 获取当前月份起始日期，当前日期减去天数减1、结合月份拼接或者上个月最后一天减1皆可 , curdate() - interval day(curdate()) - 1 day month_begin_dt -- 获取当前月份结束日期 , last_day(curdate()) month_end_dt -- 获取当前年份月份数字，加0或者cast(text as ubsigned)转化为数字,也可用extract(year_month from date) , concat(year(curdate()), date_format(curdate(), '%m')) + 0 calendar_year_month -- 获取当前季度数字 , quarter(curdate()) calendar_quarter -- 获取当前季度开始日期，先获取当前年份开始日期，再加上已过去季度的月份数(即当前所处季度数减1 乘以 3个月) , makedate(year(curdate()), 1) + interval (quarter(curdate()) - 1) * 3 month quarter_begin_dt -- 获取当前季度结束日期，先获取当前年份开始日期，再加上当前季度整体月份减去1(1除去自己这月份)，最后取最后一天即可 , last_day(makedate(year(curdate()), 1) + interval quarter(curdate()) * 3 - 1 month) quarter_end_dt -- 获取当前年份季度数字，季度为1-4数字也可使用concat前面补0 , concat(year(curdate()), lpad(quarter(curdate()), 2, '0')) + 0 calendar_year_quarter -- 获取当前年份季度的第几周数字，先获取当前日期是年度的第几周 减去 当前季度开始日期处于年度第几周 , week(curdate(),1) - week(makedate(year(curdate()), 1) + interval (quarter(curdate()) - 1) * 3 month,1) week_in_calendar_quarter -- 获取当前年份季度周数字，根据前面列的实现进行补0拼接即可 , concat(year(curdate()), lpad(quarter(curdate()), 2, '0'), lpad(week(curdate(),1) - week(makedate(year(curdate()), 1) + interval (quarter(curdate()) - 1) * 3 month,1),2, '0')) + 0 calendar_year_quarter_week -- 获取当前年的第几天 , date_format(curdate(), '%j') day_in_calendar_year -- 获取当前季度的第几天 , datediff(curdate(), makedate(year(curdate()), 1) + interval (quarter(curdate()) - 1) * 3 month) + 1 day_in_calendar_quarter -- 获取当前月的第几天 , day(curdate()) day_in_calendar_month ;
日期函数如果还不熟悉，可前往Mysql官方文档
弄明白上面的函数，实现的每一列，那么我们就可以正式创建日期表。
首先，我们开始创建日期表；
create table dwd.dim_pub_calendar_info ( id int auto_increment primary key, calendar_date date not null comment '日期', calendar_year int comment '年', year_begin_dt date comment '年开始日期', year_end_dt date comment '年结束日期', calendar_week int comment '该年中第几周', calendar_year_week int comment '该年份中第几周', calendar_weekday int comment '星期几数字', calendar_weekday_name varchar(255) comment '星期几英文名称', week_begin_dt date comment '周开始日期', week_end_dt date comment '周结束日期', calendar_month int comment '月份', calendar_month_name varchar(255) comment '月份名称', month_begin_dt date comment '月开始日期', month_end_dt date comment '月结束日期', calendar_year_month int comment '该年份中第几月份', calendar_quarter int comment '季度', quarter_begin_dt date comment '季度开始日期', quarter_end_dt date comment '季度结束日期', calendar_year_quarter int comment '该年份中第几季度', week_in_calendar_quarter int comment '该年季度中第几周', calendar_year_quarter_week int comment '该年份季度第几周数字', day_in_calendar_year int comment '年中第几天', day_in_calendar_quarter int comment '该年季度中第几天', day_in_calendar_month int comment '该年月度中第几天', create_time datetime default current_timestamp not null comment '创建时间', update_time datetime default current_timestamp not null on update current_timestamp comment '修改时间', index dim_pub_calendar_info_calendar_date (calendar_date) ) comment '日期表';
生成2023年至2024年的日期表；
-- 使用变量定义日期范围起始，也可使用存储过程生成； set @start_date = '2023-01-01'; set @end_date = '2024-12-31'; insert into dwd.dim_pub_calendar_info( calendar_date, calendar_year, year_begin_dt, year_end_dt, calendar_week, calendar_year_week , calendar_weekday, calendar_weekday_name, week_begin_dt, week_end_dt, calendar_month , calendar_month_name, month_begin_dt, month_end_dt, calendar_year_month, calendar_quarter , quarter_begin_dt, quarter_end_dt, calendar_year_quarter, week_in_calendar_quarter , calendar_year_quarter_week, day_in_calendar_year, day_in_calendar_quarter, day_in_calendar_month) with recursive date_list(calendar_date) as (select @start_date calendar_date union all select date_add(calendar_date, interval 1 day) calendar_date from date_list where date_add(calendar_date, interval 1 day) <= @end_date) select calendar_date , year(calendar_date) calendar_year , makedate(year(calendar_date), 1) year_begin_dt , makedate(year(calendar_date) + 1, 1) - interval 1 day year_end_dt , week(calendar_date,1) calendar_week , yearweek(calendar_date, 1) calendar_year_week , weekday(calendar_date) + 1 calendar_weekday , date_format(calendar_date, '%w') calendar_weekday_name , date_sub(calendar_date, interval weekday(calendar_date) day) week_begin_dt , date_add(calendar_date, interval 6 - weekday(calendar_date) day) week_end_dt , month(calendar_date) calendar_month , monthname(calendar_date) calendar_month_name , calendar_date - interval day(calendar_date) - 1 day month_begin_dt , last_day(calendar_date) month_end_dt , concat(year(calendar_date), date_format(calendar_date, '%m')) + 0 calendar_year_month , quarter(calendar_date) calendar_quarter , makedate(year(calendar_date), 1) + interval (quarter(calendar_date) - 1) * 3 month quarter_begin_dt , last_day(makedate(year(calendar_date), 1) + interval quarter(calendar_date) * 3 - 1 month) quarter_end_dt , concat(year(calendar_date), lpad(quarter(calendar_date), 2, '0')) + 0 calendar_year_quarter , week(calendar_date,1) - week(makedate(year(calendar_date), 1) + interval (quarter(calendar_date) - 1) * 3 month,1) week_in_calendar_quarter , concat(year(calendar_date), lpad(quarter(calendar_date), 2, '0'), lpad(week(calendar_date,1) - week(makedate(year(calendar_date), 1) + interval (quarter(calendar_date) - 1) * 3 month,1), 2, '0')) + 0 calendar_year_quarter_week , date_format(calendar_date, '%j') + 0 day_in_calendar_year , datediff(calendar_date, makedate(year(calendar_date), 1) + interval (quarter(calendar_date) - 1) * 3 month) + 1 day_in_calendar_quarter , day(calendar_date) day_in_calendar_month from date_list ;
以上是有关日期表的测试和制作，也可以使用存储过程，或者其它工具都可以实现，实现方法不嫌多，赶紧实操起来。
