hive时间维度表

最新推荐文章于 2024-07-25 11:27:49 发布

weixin_43858017

最新推荐文章于 2024-07-25 11:27:49 发布

阅读量222

点赞数 4

文章标签： hive

本文链接：https://blog.csdn.net/weixin_43858017/article/details/140464859

版权

with date_range as (
    select date_add("2021-01-01", a.pos) as d
from (
   -- 日期范围
   select posexplode(split(repeat("@", datediff("2022-12-31", "2021-01-01")), "@"))
     ) a
)


select
    -- date_format(d, 'yyyyMMdd') as date_key,
     d as `date`,
    weekofyear(d) as week_id,
    date_format(d,'EE') as week_day,
    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,

    concat(date_add(d,1 - case when dayofweek(d) = 1 then 7 else dayofweek(d) - 1 end),
         '-',
         date_add(d,7 - case when dayofweek(d) = 1 then 7 else dayofweek(d) - 1 end)) as week_range,

    date_format(d,'M') as month,
    trunc(d,'MM') as month_first_day,
    last_day(d) as month_last_day,
    concat(trunc(d,'MM'),'-',last_day(d)) as month_range ,
    year(d) as year,
    quarter(d) as quarter,
    to_date(concat(year(d),'-',lpad(ceil(month(d)/3) * 3 -2,2,0),'-01')) AS quarter_first_day,
    last_day(to_date(concat(year(d),'-',lpad(ceil(month(d)/3) * 3,2,0),'-01'))) AS quarter_last_day,
    concat(to_date(concat(year(d),'-',lpad(ceil(month(d)/3) * 3 -2,2,0),'-01')),
        '-',
        last_day(to_date(concat(year(d),'-',lpad(ceil(month(d)/3) * 3,2,0),'-01')))) as quarter_range,
    trunc(d,'YY') as year_first_day,
    last_day(add_months(trunc(d,'YY'),11)) as year_last_day,
    concat(trunc(d,'YY'),'-',last_day(add_months(trunc(d,'YY'),11))) as year_range
from date_range;