一个sql生成hive日期维度表

目录

​​1、日期维度表​​

​​2、生成语句​​

​​3、用例​​

---

在进行日期处理时，有时候会很麻烦，于是小编开发了一张日期维表，供大家参考。

1、日期维度表

num	字段名	字段中文名	描述	数据类型
1	date	日期	日期 yyyMMdd格式	bigint
2	week	星期，数字型	星期，数字型 0-6	bigint
3	week_cn	星期中文名	星期中文名 星期一……	string
4	year_weeks	一年中的第几周	一年中的第几周 1 2 3……	bigint
5	mon_dt	本周周一日期	本周周一日期	bigint
6	sun_dt	本周周日日期	本周周日日期	bigint
7	month	年月	年月，yyyyMM格式	bigint
8	month_short	月份简写	月份简写，MM格式1~12	bigint
9	month_cn	月份中文名	月份中文名 一月……	string
10	quarter	季度	季度，yyyyQ1\2\3\4	string
11	quarter_short	季度 数字型	季度 数字型 1-4	bigint

 

2、生成语句

set hive.execution.engine=tez;
 with dates as (
 select date_add("2010-01-01", a.pos) as d
 from (select posexplode(split(repeat("o", datediff("2030-12-31", "2010-01-01")), "o"))) a
 )
 insert overwrite table dim.dim_date
 select
     d
   , date_format(d, 'yyyyMMdd000000') as to_pt            -- 指定分区格式
   , date_format(d, 'yyyyMMdd')       as date_yyyymmdd
   , trunc(d,'MM')                    as month_first_day 
   , last_day(d)                      as month_last_day
   , date_format(last_day(d),'yyyyMMdd000000')   as month_last_pt
   , date_format(d, 'yyyyMM')  as month_yyyymm
   , date_format(d, 'yyyy-MM') as month_yyyy_mm
   , month(d) as month
   , date_format(d, 'u') as week
   , date_format(d, 'E') as week_long   
   , weekofyear(d) as week_of_year
   , year(d) as year
   , floor(substr(d,6,2)/3.1)*3+1 as quarter
   -- , concat_group('"',date_format(d, 'yyyyMM'),'"') as date_yyyymmdd_list   -- 低版本hive group_concat 不可用
 from dates

3、用例

1. 取月末：where date_pk = month_last_day；
2. 取周末：where week_int in (6,7)；
3. 取每月最后一天pt+ 当月昨天pt：where pt IN ( SELECT max(to_pt) FROM dim.dim_date where to_pt <= '${-1d_pt}' group by month_yyyymm  )；
4. 其它用法......