一个sql生成hive日期维度表
目录
在进行日期处理时，有时候会很麻烦，于是小编开发了一张日期维表，供大家参考。
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、用例
- 取月末：where date_pk = month_last_day；
- 取周末：where week_int in (6,7)；
- 取每月最后一天pt+ 当月昨天pt：where pt IN ( SELECT max(to_pt) FROM dim.dim_date where to_pt <= '${-1d_pt}' group by month_yyyymm )；
- 其它用法......
文章来源: notomato.blog.csdn.net，作者：kissme丶，版权归原作者所有，如需转载，请联系作者。
原文链接：notomato.blog.csdn.net/article/details/111865354
- 点赞
- 收藏
- 关注作者
评论（0）