spark-sql创建时间维度表

逃离我推掉我的手 2023-01-13 06:12 166阅读 0赞

## 1、概述： ##

在数据分析，数据仓库和数据开发的过程中，经常会用到各种时间粒度，尤其是会按照各种时间粒度（进行上钻或者下钻）出统一逻辑报表数据，此时将面临分析时间粒度爆炸的问题（使用grouping set解决，见另一篇文章），所以有必要产出一个时间维度表。

## 2、时间维度表建表语句 ##

CREATE TABLE if not exists test_db.dim_date_df(
     `dt` string comment '日期,格式为20200325,同分区格式',
     `new_dt` date comment '日期,格式为2020-03-25',
     `dt_name` string comment '日期,格式为2020年3月25日',
     `year_name` int comment '自然年,格式为2020',
     `month_name` int comment '自然月,格式为10',
     `half_month` string comment '上半月[1,15]/下半月[16,月最后一天],格式为2020年12月上半月',
     `day_name` int comment '自然日,格式为25',
     `year_month` string comment '年月,格式202003',
     `month_first_day` date comment '当月第一天,格式为20200301',
     `month_last_day` date comment '当月最后一天,格式为2020031',
     `quarter` int comment '季度-1/2/3/4',
     `cn_quarter` string comment '季度-中文写法,格式2021年第1季度',
     `en_quarter` string comment '季度-英文写法,格式2021Q1',
     `week_number` int comment '周序号,日期所属周为当年第几周',
     `year_week_number` string comment '年+周序号拼接,格式2021年第1周',
     `week_first_day` date comment '本周第一天日期_周一',
     `week_last_day` date comment '本周最后一天日期_周日',
     `daynumber_of_week` string comment '日所在周序号，周一为序号1，周二为2，以此类推',
     `en_dayname_of_week` string comment '日在周英文命名:Monday、Tuesday',
     `cn_dayname_of_week` string comment '日在周中文命名:星期一、星期二',
     `daynumber_of_year` string comment '日所在年序号'
     )
     USING PARQUET
     COMMENT '日期维度表';

## 3、生成表数据SQL ##

set hivevar:start_day=2010-01-01;
    set hivevar:end_day=2030-01-01;
    with dates as (
      select date_add("${start_day}", a.pos) as d
      from (select posexplode(split(repeat("o", datediff("${end_day}", "${start_day}")), "o"))) a
    )
    insert into table test_db.dim_date_df
    select
      date_format(d,'yyyyMMdd')  dt --日期,格式为 20200325
      ,d as new_dt  --日期,格式为 2020-03-25
      ,concat(year(d),"年",month(d),"月",day(d),'日') dt_name
      ,year(d) as year_name --自然年
      ,month(d) as month_name  --自然月
      ,case when day(d)<16 then concat(date_format(d,'yyyy'),'年',date_format(d,'MM'),'月上半月') else concat(date_format(d,'yyyy'),'年',date_format(d,'MM'),'月下半月') end  as half_month  --上半月[1,15]/下半月[16,月底最后一天]
      ,day(d) as day_name --自然日
      ,date_format(d,'yyyyMM') year_month  --年月,格式202003
      ,trunc(d,'MM') as month_first_day  -- 当月第一天
      ,last_day(d) as month_last_day     -- 当月最后一天
      ,quarter(d) as quarter -- 季度-1/2/3/4
      ,concat(year(d),'年第',quarter(d),'季度') as cn_quarter
      ,concat(year(d),'Q',quarter(d)) as en_quarter
      ,weekofyear(d) week_number
      ,concat(date_format(date_add(d,1 - case when dayofweek(d) = 1 then 7 else dayofweek(d) - 1 end),'yyyy'),'年第',weekofyear(d),'周') year_week_number -- 取周一的年份,之后拼接周序号		
      ,date_add(d,1 - case when dayofweek(d) = 1 then 7 else dayofweek(d) - 1 end) as week_first_day -- 本周第一天_周一
      ,date_add(d,7 - case when dayofweek(d) = 1 then 7 else dayofweek(d) - 1 end) as week_last_day  -- 本周最后一天_周日
      ,date_format(d, 'u') as daynumber_of_week
      ,date_format(d, 'EEEE') as en_dayname_of_week
      ,case date_format(d, 'EEEE') 
            when 'Monday'    then '星期一'
            when 'Tuesday'   then '星期二'
            when 'Wednesday' then '星期三'
            when 'Thursday'  then '星期四'
            when 'Friday'    then '星期五'
            when 'Saturday'  then '星期六'
            when 'Sunday'    then '星期日'
      end as cn_dayname_of_week
      ,date_format(d, 'D') as daynumber_of_year
    from dates
    order by year_name,month_name,day_name
    ;

## 4、数据展示 ##

spark-sql> desc dim_date_df;
    col_name	data_type	comment
    dt	string	日期,格式为20200325,同分区格式
    new_dt	date	日期,格式为2020-03-25
    year_name	int	自然年,格式为2020
    month_name	int	自然月,格式为10
    half_month	string	上半月[1,15]/下半月[16,月最后一天],格式为2020年12月上半月
    day_name	int	自然日,格式为25
    year_month	string	年月,格式202003
    month_first_day	date	当月第一天,格式为20200301
    month_last_day	date	当月最后一天,格式为2020031
    quarter	int	季度-1/2/3/4
    cn_quarter	string	季度-中文写法,格式2021年第1季度
    en_quarter	string	季度-英文写法,格式2021Q1
    week_number	int	周序号,日期所属周为当年第几周
    year_week_number	string	年+周序号拼接,格式2021年第1周
    week_first_day	date	本周第一天日期_周一
    week_last_day	date	本周最后一天日期_周日
    daynumber_of_week	string	日所在周序号，周一为序号1，周二为2，以此类推
    en_dayname_of_week	string	日在周英文命名:Monday、Tuesday
    cn_dayname_of_week	string	日在周中文命名:星期一、星期二
    daynumber_of_year	string	日所在年序号
    Time taken: 0.057 seconds, Fetched 20 row(s)
    
    
    spark-sql> select * from dim_date_df where dt>'20201225' and dt<'20210108'  ;
    dt	new_dt	year_name	month_name	half_month	day_name	year_month	month_first_day	month_last_day	quarter	cn_quarter	en_quarter	week_number	year_week_number	week_first_day	week_last_day	daynumber_of_week	en_dayname_of_week	cn_dayname_of_week	daynumber_of_year
    20201226	2020-12-26	2020	12	2020年12月下半月	26	202012	2020-12-01	2020-12-31	4	2020年第4季度	2020Q4	52	2020年第52周	2020-12-21	2020-12-27	6	Saturday	星期六	361
    20201227	2020-12-27	2020	12	2020年12月下半月	27	202012	2020-12-01	2020-12-31	4	2020年第4季度	2020Q4	52	2020年第52周	2020-12-21	2020-12-27	7	Sunday	星期日	362
    20201228	2020-12-28	2020	12	2020年12月下半月	28	202012	2020-12-01	2020-12-31	4	2020年第4季度	2020Q4	53	2020年第53周	2020-12-28	2021-01-03	1	Monday	星期一	363
    20201229	2020-12-29	2020	12	2020年12月下半月	29	202012	2020-12-01	2020-12-31	4	2020年第4季度	2020Q4	53	2020年第53周	2020-12-28	2021-01-03	2	Tuesday	星期二	364
    20201230	2020-12-30	2020	12	2020年12月下半月	30	202012	2020-12-01	2020-12-31	4	2020年第4季度	2020Q4	53	2020年第53周	2020-12-28	2021-01-03	3	Wednesday	星期三	365
    20201231	2020-12-31	2020	12	2020年12月下半月	31	202012	2020-12-01	2020-12-31	4	2020年第4季度	2020Q4	53	2020年第53周	2020-12-28	2021-01-03	4	Thursday	星期四	366
    20210101	2021-01-01	2021	1	2021年01月上半月	1	202101	2021-01-01	2021-01-31	1	2021年第1季度	2021Q1	53	2020年第53周	2020-12-28	2021-01-03	5	Friday	星期五	1
    20210102	2021-01-02	2021	1	2021年01月上半月	2	202101	2021-01-01	2021-01-31	1	2021年第1季度	2021Q1	53	2020年第53周	2020-12-28	2021-01-03	6	Saturday	星期六	2
    20210103	2021-01-03	2021	1	2021年01月上半月	3	202101	2021-01-01	2021-01-31	1	2021年第1季度	2021Q1	53	2020年第53周	2020-12-28	2021-01-03	7	Sunday	星期日	3
    20210104	2021-01-04	2021	1	2021年01月上半月	4	202101	2021-01-01	2021-01-31	1	2021年第1季度	2021Q1	1	2021年第1周	2021-01-04	2021-01-10	1	Monday	星期一	4
    20210105	2021-01-05	2021	1	2021年01月上半月	5	202101	2021-01-01	2021-01-31	1	2021年第1季度	2021Q1	1	2021年第1周	2021-01-04	2021-01-10	2	Tuesday	星期二	5
    20210106	2021-01-06	2021	1	2021年01月上半月	6	202101	2021-01-01	2021-01-31	1	2021年第1季度	2021Q1	1	2021年第1周	2021-01-04	2021-01-10	3	Wednesday	星期三	6
    20210107	2021-01-07	2021	1	2021年01月上半月	7	202101	2021-01-01	2021-01-31	1	2021年第1季度	2021Q1	1	2021年第1周	2021-01-04	2021-01-10	4	Thursday	星期四	7
    Time taken: 6.332 seconds, Fetched 13 row(s)

## 5、验证和结论 ##

能满足计算时间粒度：日/周/月/半月1-15日，15+/季度/年
    当计算业务逻辑的部分 关联 日期维度表之后的，各日期粒度对应所需的 grouping set 的标识字段如下
    日,  dt/new_dt
    周,  year_week_number
    月,  year_month
    半月1-15日/15+, half_month
    季度, cn_quarter/en_quarter
    年,  year_name

# #