hive sql生成数仓日期维表

目录

一、建表ddl

二、加工sql

三、示例结果数据

---

一、建表ddl

create table dim_date(
id                    bigint comment '序号',
day_yyyy_mm_dd        string comment '日期(yyyy-MM-dd)',
day_yyyymmdd          string comment '日期(yyyymmdd)',
month_yyyymm          string comment '年月(yyyyMM)',
month_yyyy_mm         string comment '年月(yyyy-MM)',
date_month            string comment '月份(MM)',
month_first_day       string comment '当月月初',
month_last_day        string comment '当月月末',
day_of_month          string comment '本月第几天',
week_days             string comment '星期(数字)',
week_short            string comment '星期(英文缩写)',
week_long             string comment '星期(英文)',
week_cn               string comment '星期(中文)',
week_of_year          string comment '当年第几周',
year_week             string comment '年周',
season                string comment '季度',
year_desc             string comment '年'
)
comment '日期维表';

二、加工sql

ps:以20220926到20221002这周数据为例，数据范围可自行调整

set hive.execution.engine=tez;
with dates as (
select date_add("2022-09-26", a.pos) as d
from (select posexplode(split(repeat("o", datediff("2022-10-02", "2022-09-26")), "o"))) a
)

insert overwrite table dim_date
select
    row_number() over(order by 1)    as id
  , d                                as day_yyyy_mm_dd
  , date_format(d, 'yyyyMMdd')       as date_yyyymmdd
  , date_format(d, 'yyyyMM')         as month_yyyymm
  , date_format(d, 'yyyy-MM')        as month_yyyy_mm
  , month(d)                         as month_desc
  , trunc(d,'MM')                    as month_first_day 
  , last_day(d)                      as month_last_day
  , dayofmonth(d)                    as day_of_month             
  , date_format(d, 'u')              as week_days   
  , date_format(d, 'E')              as week_short
  , date_format(d, 'EEEE')           as week_long
  , case when date_format(d, 'u') =1 then '星期一'
         when date_format(d, 'u') =2 then '星期二'
         when date_format(d, 'u') =3 then '星期三'
         when date_format(d, 'u') =4 then '星期四'
         when date_format(d, 'u') =5 then '星期五'
         when date_format(d, 'u') =6 then '星期六'
         when date_format(d, 'u') =7 then '星期日'
    end                              as week_cn
  , weekofyear(d)                    as week_of_year
  , concat(year(date_sub(next_day(d,'monday'),4)), '-', weekofyear(d)) as year_week
  , lpad(ceil(month(d)/3),2,0)       as season
  , year(d)                          as year_desc
from dates
;

三、示例结果数据

​​​​​​​