眾所周知 hive 的時間處理異常繁瑣且在一些涉及日期的統(tǒng)計場景中會寫較長的 sql，例如：周累計、周環(huán)比等；本文將使用維表的形式降低時間處理的復(fù)雜度，提前計算好標(biāo)準(zhǔn)時間字符串未來可能需要轉(zhuǎn)換的形式。

一、表設(shè)計

結(jié)合業(yè)務(wù)場景常用的時間字符串格式為 yyyyMMdd，因此我們將這種格式字段作為維表的關(guān)聯(lián)鍵，用來派生剩下的字段，例如 yyyy-MM-dd、yyyy/MM/dd、yyyy、MM、dd 以及令人頭疼的周（w），因此當(dāng)前版本的時間維表 DDL 如下

create table dim_xxx.dim_dateformat
(dt          string comment '日期，yyyymmdd',dt_format1  string comment '日期，yyyy-mm-dd',dt_format2  string comment '日期，yyyy/mm/dd',dt_year     string comment '所在年份',dt_month    string comment '所在月份',dt_day      string comment '所在日',dt_week_str string comment '星期（英文）',dt_week_num string comment '星期（數(shù)字）',dt_abs_week bigint comment '絕對周，從 19700101 為第一周',dt_rel_week string comment '相對周，從本年的第一個周一為第一周'
) comment '日期維表'stored as parquet;

需要解釋一下 dt_abs_week 和 dt_rel_week 字段，該字段用于提升周累計、周環(huán)比的計算效率。dt_abs_week 絕對周是約定 19700101 為第一周，后續(xù)每遇到一個周一加一；dt_rel_week 主要用來對外展示，例如：

1. 截止昨日周累計：獲取通過 dt 獲取昨日所在的 dt_abs_week 或 dt_rel_week，從而可以當(dāng)前周的 dt 范圍，根據(jù) dt 關(guān)聯(lián)業(yè)務(wù)表即可
2. 周環(huán)比：獲取通過 dt 獲取昨日所在的 dt_abs_week - 1 即可獲取環(huán)比的所在周，再結(jié)合 dt_week_num 可以靈活控制環(huán)比整周或環(huán)比上周的對應(yīng)星期

二、填充數(shù)據(jù)

這里使用 python 生成 csv 并 load 進去即可（這種方式最簡單，對比過使用 sql 來實現(xiàn)），因為生產(chǎn)環(huán)境 hive 表的存儲格式往往不是 textfile，例如博主所在公司所用的存儲格式就是 parquet，遵循一切從簡的原則，創(chuàng)建同 schema 的 textfile 表（一切從簡，注釋都不寫）

create table dim_xxx.dim_dateformat_load
(dt          string,dt_format1  string,dt_format2  string,dt_year     string,dt_month    string,dt_day      string,dt_week_str string,dt_week_num string,dt_abs_week string,dt_rel_week string
)row format delimited fields terminated by ','stored as textfile;

下面的重點是 python 如何實現(xiàn)，直接上代碼

import datetime
import csv# 定義日期范圍
start_date = datetime.date(1970, 1, 1)
end_date = datetime.date(2500, 12, 31)with open(file='dim_dateformat.csv', mode='w', encoding='utf8', newline='') as f:writer = csv.writer(f)# 循環(huán)遍歷current_date = start_date# 初始絕對周abs_week_num = 1# 初始相對周rel_week_num = 1rel_year = 1970display_year_of_week = '1970-1'while current_date <= end_date:# 各種時間格式format1 = current_date.strftime("%Y%m%d")format2 = current_date.strftime("%Y-%m-%d")format3 = current_date.strftime("%Y/%m/%d")# 年、月、日、星期year = current_date.yearmonth = current_date.strftime("%m")day = current_date.strftime("%d")day_of_week1 = current_date.strftime("%A")day_of_week2 = current_date.strftime("%w")day_of_week2 = day_of_week2 if day_of_week2 != '0' else '7'if day_of_week2 == '1':abs_week_num += 1# 計算相對周rel_week_num += 1if rel_year != year:rel_year = yearrel_week_num = 1display_year_of_week = str(rel_year) + '-' + str(rel_week_num)# 寫入 csvwriter.writerow([format1, format2, format3, year, month, day, day_of_week1, day_of_week2, abs_week_num,display_year_of_week])# ++current_date += datetime.timedelta(days=1)

解釋一下相對周和絕對周的計算方式即可

1. 初始化 abs_week_num、rel_week_num 為 1，rel_year 為 1970
2. 如果是周一，abs_week_num 加 1；rel_week_num 加 1 轉(zhuǎn)第 3 步。否則轉(zhuǎn)第 4 步
3. 如果年份不等于 rel_year 則將當(dāng)前年份賦值給 rel_year 并重置 rel_week_num 為 1
4. 寫入文件

對于絕對周初始為 1 后逢周一進一即可，對于相對周，對于周的部分也是逢周一進一，若跨年則年份加一后重置周的計數(shù)

之后將得到的 dim_dateformat.csv 文件 load 進 dim_dateformat_load 并執(zhí)行下面 sql

insert overwrite table dim_dateformat
select * from dim_dateformat_load

結(jié)果如下

接下來就可以拿著這張維表盡情玩耍吧