hive sql创建日期维度表

with dates as (
    select date_add("2021-04-01", a.pos) as d
    from (
        select posexplode(split(repeat("o", datediff("2021-04-13", "2021-04-01")), "o"))) a
)
select
date_format(d, 'yyyyMMdd') as date_key
,d as `date`
,date_format(d,'yyyMM') as month
,month(d) as month_short
,quarter(d) as quarter
,year(d) as year
,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 dayname_of_week
,date_format(d, 'u') as daynumber_of_week
,day(d) as daynumer_of_month
,date_format(d, 'D') as daynumber_of_year
,weekofyear(d) as year_weeks
,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 
from dates;