hive 时间维度表

最新推荐文章于 2024-07-16 14:45:52 发布

原创最新推荐文章于 2024-07-16 14:45:52 发布 · 553 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #big data #echarts

这篇博客详细介绍了如何在Hive中创建一个时间维度表，包括日期、年份、季度、月份等多个时间粒度，并提供了计算当年第几周、当月第几周等时间属性的方法。此外，还涵盖了从当年第一天到最后一周的日期计算，为大数据分析提供时间维度支持。

Create table dim_todate as
SELECT mid_date                                                                       as date_key                           --日-月-年
   ,day                                                                           as date_value                       --日月年
   ,year(day)                                                                   as year_num                           --年
   ,case when month(day) < 7 then 'Q1' else 'Q2' end                           as half_year                       --半年
   ,lpad(ceil(month(day)/3),2,0)                                                as season                           --季度
   ,floor(mid_date/10000)*100 + lpad(ceil(month(day)/3),2,0)                   as year_season                       --年-季度
   ,month(day)                                                                   as month_num                       --月
   ,floor(mid_date/100)                                                           as month_year_num                   --年月
   ,substr(day,1,7)                                                               as month_year_no                   --年-月
   ,weekofyear(day) as week_year_num                --当年第几周
   ,year(day)*100 + weekofyear(day)                                           as week_and_year                    --年-周
   ,weekofyear(date_add(day,7))                                                  as lag_week_year_num               --上周当年第几周
   ,case
       when weekofyear(day) - weekofyear(trunc(day,'MM')) > 0 then weekofyear(day) - weekofyear(trunc(day,'MM'))+ 1
       when weekofyear(day) - weekofyear(trunc(day,'MM')) < 0 then weekofyear(day) + 1
       when weekofyear(day) - weekofyear(trunc(day,'MM')) = 0 then 1
   end                                                                            as week_month_num                    --当月第几周
   ,date_add(day,1 - dayofweek(day)) as week_first_no                   --本周第一天_周日
   ,date_add(day,7 - dayofweek(day)) as week_last_no                  -- 本周最后一天_周六
   ,date_add(day,1 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_first_num              -- 本周第一天_周一
   ,date_add(day,7 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_last_num           -- 本周最后一天_周日
   ,date_format(day,'u')                                                           as dayofweek                  --本周第几天 u必须小写
   ,dayofmonth(day,1)                                     as day_month_mun               --本月第几天
   ,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01')) as season_first_day            -- 当季第一天
,trunc(day,'MM') as month_first_day             -- 当月第一天
,trunc(day,'YY') as year_first_day            -- 当年第一天
,last_day(add_months(trunc(day,'YY'),12)) as year_last_day             -- 当年最后一天
,last_day(day) as month_last_day            -- 当月最后一天
,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01'))) as season_last_day             -- 当季最后一天
FROM (select mid_date as day,
   regexp_replace(mid_date,'-','') as mid_date
from (
select date_add(start_date, pos) as mid_date from(
select '1' as uid,
   '1900-01-01' as start_date,
   '2099-12-31' as end_date ) tmp
   lateral view
   posexplode( split(space(datediff(end_date, start_date)), '') ) t as pos, val ) c
       ) T1
ORDER BY T1.mid_date