时间维度表的制作
1 需求背景
在大数据分析模块中，我们需要从不同的维度分析主题表，包括常用的公用维度：时间维，地区维度，教育信息维…以及各种各样的业务维度：员工维度，部门维度…，业务维度就是我们从哪些角度去分析业务过程，本文就是做一张常用时间维度表。
时间维表由于是可预见的，因此可以一次性导入未来几十年的，当然对于一些节假日的设置可能只能获取未来一年的，因此可以每年全量更新一次。
2 维表设计
给出时间维度表的建表语句
CREATEDATABASE dim_db DEFAULTCHARACTERSET utf8 DEFAULTCOLLATE utf8_general_ci;droptable dim_db.dim_date;createtableifnotexists dim_db.dim_date
(
udate varchar(20)comment'日期',
uyear varchar(20)comment'年',
uquarter varchar(20)comment'季度',
useason varchar(20)comment'季节',
umonth varchar(20)comment'月',
uday varchar(20)comment'日',
uweek varchar(20)comment'第几周',
uweekday varchar(20)comment'周几：1-周一、2-周二、3-周三、4-周四、5-周五、6-周六、7-周日',
is_workday varchar(20)comment'是否是工作日:1,0',
udatetype varchar(20)comment'节假日类型:工作日，法定上班[还班]，周末，节假日',
updatedate varchar(20)comment'数据更新日期');
这里额外解释三个字段
uquarter
: 季度，按照阳历的日期分的，[1,2,3]第一季度,[4,5,6]为第二季度,[7,8,9]为第三季度,[10,11,12]为第四季度。
useason
:季节，季节的划分有三种方式，本采用的是节气划分法。
- 天文划分法，以春分、夏至、秋分、冬至为四季的开始；
- 气象划分法，以3月至5月为春季，6月至8月为夏季，9月至11月为秋季，12月至2月为冬季；
- 节气划分法，以立春、立夏、立秋、立冬为四季之始。
udatetype
:法定上班指的是因为放假调休周末也要上班的日期，节假日会直接显示日期名称，实现的逻辑大家可以自行更改。
3 实现方式
3.1 安装库
本文实现的方式是基于python的chinese_calendar库和pymysql库将数据导入到mysql，接着生成csv文件导入导hive数据库里面。
- MySQL：5.6.36 本地部署
- chinese_calendar：1.8.0 这是一个基于阿里云开发的中国日历库，每年会更新
- pymysql：1.0.2 连接mysql用的
- python版本：3.7
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple chinesecalendar
pip install pymysql
3.2 实现代码
import chinese_calendar
import pymysql.cursors
import datetime
import pytz
defmain():# (1)设置生成表中数据的开始和截至日期
start_date = datetime.date(2010,1,1)
end_date = datetime.date(2023,12,31)# (2)获取该时间段内所有的日期
dates = chinese_calendar.get_dates(start_date, end_date)# (3)遍历日期,构造sql字符串
strsql ='''INSERT INTO `dim_date`(`udate`,`uyear`,`uquarter`,`useason`,`umonth`,`uday`,`uweek`,`uweekday`,`is_workday`,`udateType`,`updatedate`)VALUES'''
is_first_line =True# (4)设置生成节气的起始时间和截至日期# 因为需要 立春，立夏，立秋，立冬四个字段的名称，所以 [数据项的开始日期和截至日期区间] 应在 [节气的起始时间和截至日期的区间]内
solar_term_start_date = datetime.date(2009,11,7)
solar_term_end_date = datetime.date(2030,12,31)
season_start_date_list = get_season_start_date_list(solar_term_start_date, solar_term_end_date)# get_current_season_index
index = get_current_season_index(season_start_date_list, start_date)# (5)遍历日期集合构造sqlfor date in dates:# [1]udate: 日期
udate = date.__str__()# [2]uyear: 年份
uyear =str(date.year)# [3]umonth: 月份
umonth =str(date.month)# [4]uquarter: 季度
uquarter = get_quarter(date.month)# [5]useason: 季节
useason =''if season_start_date_list[index][0]<= date < season_start_date_list[index +1][0]:
useason = season_start_date_list[index][1]elif date >= season_start_date_list[index +1][0]:
index +=1
useason = season_start_date_list[index][1]# [6]uday: 日
uday =str(date.day)# [7]uweek: 第几周# 构造一个指定日期时间，时区[必选]的datetime 对象
timezone = pytz.timezone('Asia/Shanghai')
dt = datetime.datetime(date.year, date.month, date.day, tzinfo=timezone)
uweek =str(int(dt.strftime("%U"))+1)# [7]uweekday: 周几
uweekday =str(date.isoweekday())# is_holiday, holidays = chinese_calendar.get_holiday_detail(date)
is_workday ='1'if chinese_calendar.is_workday(date)else'0'# [9]udatetype: 日期类型
udatetype =''if is_workday =='1':if date.isoweekday()in[6,7]:# udatetype = '法定上班'
is_holiday, holidays = chinese_calendar.get_holiday_detail(date)
udatetype ='法定上班-'+ get_chinese_name(holidays)else:
udatetype ='工作日'else:
is_holiday, holidays = chinese_calendar.get_holiday_detail(date)if holidays isNone:
udatetype ='周末'else:
udatetype = get_chinese_name(str(holidays))# [10]updatedate: 更新时间，默认是当天
updatedate = datetime.date.today().__str__()# updatedate = datetime.date(2023, 5, 12).__str__() 手动指定更新日期# 判断是否首行if is_first_line:
is_first_line =Falseelse:
strsql +=','# 构建单行数据库文本记录
linerecord ='(\''+ udate +'\',\'' \
+ uyear +'\',\'' \
+ uquarter +'\',\'' \
+ useason +'\',\'' \
+ umonth +'\',\'' \
+ uday +'\',\'' \
+ uweek +'\',\'' \
+ uweekday +'\',\'' \
+ is_workday +'\',\'' \
+ udatetype +'\',\'' \
+ updatedate +'\')'print(linerecord)# 连接sql
strsql += linerecord
strsql +=';'# (4)将生成的数据插入到数据库# 连接配置信息
config ={'host':'127.0.0.1','port':3306,'user':'root','password':'你的数据库密码','db':'dim_dib','charset':'utf8','cursorclass': pymysql.cursors.DictCursor,}# 创建连接
connection = pymysql.connect(**config)# 使用cursor创建游标对象
cursor = connection.cursor()# 执行sql语句
cursor.execute(strsql)
connection.commit()# 关闭连接
cursor.close()
connection.close()defget_quarter(month):'''
获取月份对应的季度
:param month: 月份
:return: 季度
'''if month in[1,2,3]:return'1'elif month in[4,5,6]:return'2'elif month in[7,8,9]:return'3'elif month in[10,11,12]:return'4'else:returnNonedefget_chinese_name(english_name):'''
将英文节日名称映射成中文名称
:param english_name: str
:return: str
'''if english_name =="New Year's Day":return"元旦"elif english_name =="Spring Festival":return"春节"elif english_name =="Tomb-sweeping Day":return"清明"elif english_name =="Dragon Boat Festival":return"端午"elif english_name =="Labour Day":return"劳动节"elif english_name =="National Day":return"国庆节"elif english_name =="Mid-autumn Festival":return"中秋"else:return"无效节日"defget_season_start_date_list(start_date, end_date):'''
返回[每个季节开始的日期和名称]的集合
目前可求的范围是：[1900, 2100]
:param start_date: 起始时间
:param end_date: 结束时间
:return: list，元素内容：(date,'春季')，或者(date,'夏季')，(date,'秋季')，(date,'冬季')
'''
solar_terms = chinese_calendar.get_solar_terms(start_date, end_date)
date_season_list =[]for term in solar_terms:
term_name = term[1]
udate = term[0]if term_name =='立春':
date_season_list.append((udate,'春季'))elif term_name =='立夏':
date_season_list.append((udate,'夏季'))elif term_name =='立秋':
date_season_list.append((udate,'秋季'))elif term_name =='立冬':
date_season_list.append((udate,'冬季'))return date_season_list
defget_current_season_index(season_start_date_list, start_date):'''
获取起始时间对应季节的起始时间
:param season_start_date_list: 每个季节起始时间的日期集合以及相应的季节名称
:param start_date: 起始时间
:return: 起始时间对应季节的起始时间 | 集合的下标
'''for index inrange(0,12000):if start_date >= season_start_date_list[index][0]:return index
if __name__ =='__main__':
main()
3.3 生成数据和更新
使用程序的时候只需要设置程序最开始的start_date和end_date，更新数据同理，不过后续更新数据的时候应该选择更高版本的chinese_calendar库。
4 生成数据预览
这里贴出来了2010年一整年的数据，datagrip导出来的，已经核对过数据是没有问题的，大家可以在http://www.gov.cn/zwgk/2009-12/08/content_1482691.htm国务院办公厅发布的节假日安排核对假日信息，手机日历上的节假日信息个别有偏差。
udateuyearuquarteruseasonumonthudayuweekuweekdayis_workdayudatetypeupdatedate2010-01-0120101冬季11150元旦2023-05-122010-01-0220101冬季12160元旦2023-05-122010-01-0320101冬季13270元旦2023-05-122010-01-0420101冬季14211工作日2023-05-122010-01-0520101冬季15221工作日2023-05-122010-01-0620101冬季16231工作日2023-05-122010-01-0720101冬季17241工作日2023-05-122010-01-0820101冬季18251工作日2023-05-122010-01-0920101冬季19260周末2023-05-122010-01-1020101冬季110370周末2023-05-122010-01-1120101冬季111311工作日2023-05-122010-01-1220101冬季112321工作日2023-05-122010-01-1320101冬季113331工作日2023-05-122010-01-1420101冬季114341工作日2023-05-122010-01-1520101冬季115351工作日2023-05-122010-01-1620101冬季116360周末2023-05-122010-01-1720101冬季117470周末2023-05-122010-01-1820101冬季118411工作日2023-05-122010-01-1920101冬季119421工作日2023-05-122010-01-2020101冬季120431工作日2023-05-122010-01-2120101冬季121441工作日2023-05-122010-01-2220101冬季122451工作日2023-05-122010-01-2320101冬季123460周末2023-05-122010-01-2420101冬季124570周末2023-05-122010-01-2520101冬季125511工作日2023-05-122010-01-2620101冬季126521工作日2023-05-122010-01-2720101冬季127531工作日2023-05-122010-01-2820101冬季128541工作日2023-05-122010-01-2920101冬季129551工作日2023-05-122010-01-3020101冬季130560周末2023-05-122010-01-3120101冬季131670周末2023-05-122010-02-0120101冬季21611工作日2023-05-122010-02-0220101冬季22621工作日2023-05-122010-02-0320101冬季23631工作日2023-05-122010-02-0420101春季24641工作日2023-05-122010-02-0520101春季25651工作日2023-05-122010-02-0620101春季26660周末2023-05-122010-02-0720101春季27770周末2023-05-122010-02-0820101春季28711工作日2023-05-122010-02-0920101春季29721工作日2023-05-122010-02-1020101春季210731工作日2023-05-122010-02-1120101春季211741工作日2023-05-122010-02-1220101春季212751工作日2023-05-122010-02-1320101春季213760春节2023-05-122010-02-1420101春季214870春节2023-05-122010-02-1520101春季215810春节2023-05-122010-02-1620101春季216820春节2023-05-122010-02-1720101春季217830春节2023-05-122010-02-1820101春季218840春节2023-05-122010-02-1920101春季219850春节2023-05-122010-02-2020101春季220861法定上班2023-05-122010-02-2120101春季221971法定上班2023-05-122010-02-2220101春季222911工作日2023-05-122010-02-2320101春季223921工作日2023-05-122010-02-2420101春季224931工作日2023-05-122010-02-2520101春季225941工作日2023-05-122010-02-2620101春季226951工作日2023-05-122010-02-2720101春季227960周末2023-05-122010-02-2820101春季2281070周末2023-05-122010-03-0120101春季311011工作日2023-05-122010-03-0220101春季321021工作日2023-05-122010-03-0320101春季331031工作日2023-05-122010-03-0420101春季341041工作日2023-05-122010-03-0520101春季351051工作日2023-05-122010-03-0620101春季361060周末2023-05-122010-03-0720101春季371170周末2023-05-122010-03-0820101春季381111工作日2023-05-122010-03-0920101春季391121工作日2023-05-122010-03-1020101春季3101131工作日2023-05-122010-03-1120101春季3111141工作日2023-05-122010-03-1220101春季3121151工作日2023-05-122010-03-1320101春季3131160周末2023-05-122010-03-1420101春季3141270周末2023-05-122010-03-1520101春季3151211工作日2023-05-122010-03-1620101春季3161221工作日2023-05-122010-03-1720101春季3171231工作日2023-05-122010-03-1820101春季3181241工作日2023-05-122010-03-1920101春季3191251工作日2023-05-122010-03-2020101春季3201260周末2023-05-122010-03-2120101春季3211370周末2023-05-122010-03-2220101春季3221311工作日2023-05-122010-03-2320101春季3231321工作日2023-05-122010-03-2420101春季3241331工作日2023-05-122010-03-2520101春季3251341工作日2023-05-122010-03-2620101春季3261351工作日2023-05-122010-03-2720101春季3271360周末2023-05-122010-03-2820101春季3281470周末2023-05-122010-03-2920101春季3291411工作日2023-05-122010-03-3020101春季3301421工作日2023-05-122010-03-3120101春季3311431工作日2023-05-122010-04-0120102春季411441工作日2023-05-122010-04-0220102春季421451工作日2023-05-122010-04-0320102春季431460清明2023-05-122010-04-0420102春季441570清明2023-05-122010-04-0520102春季451510清明2023-05-122010-04-0620102春季461521工作日2023-05-122010-04-0720102春季471531工作日2023-05-122010-04-0820102春季481541工作日2023-05-122010-04-0920102春季491551工作日2023-05-122010-04-1020102春季4101560周末2023-05-122010-04-1120102春季4111670周末2023-05-122010-04-1220102春季4121611工作日2023-05-122010-04-1320102春季4131621工作日2023-05-122010-04-1420102春季4141631工作日2023-05-122010-04-1520102春季4151641工作日2023-05-122010-04-1620102春季4161651工作日2023-05-122010-04-1720102春季4171660周末2023-05-122010-04-1820102春季4181770周末2023-05-122010-04-1920102春季4191711工作日2023-05-122010-04-2020102春季4201721工作日2023-05-122010-04-2120102春季4211731工作日2023-05-122010-04-2220102春季4221741工作日2023-05-122010-04-2320102春季4231751工作日2023-05-122010-04-2420102春季4241760周末2023-05-122010-04-2520102春季4251870周末2023-05-122010-04-2620102春季4261811工作日2023-05-122010-04-2720102春季4271821工作日2023-05-122010-04-2820102春季4281831工作日2023-05-122010-04-2920102春季4291841工作日2023-05-122010-04-3020102春季4301851工作日2023-05-122010-05-0120102春季511860劳动节2023-05-122010-05-0220102春季521970劳动节2023-05-122010-05-0320102春季531910劳动节2023-05-122010-05-0420102春季541921工作日2023-05-122010-05-0520102夏季551931工作日2023-05-122010-05-0620102夏季561941工作日2023-05-122010-05-0720102夏季571951工作日2023-05-122010-05-0820102夏季581960周末2023-05-122010-05-0920102夏季592070周末2023-05-122010-05-1020102夏季5102011工作日2023-05-122010-05-1120102夏季5112021工作日2023-05-122010-05-1220102夏季5122031工作日2023-05-122010-05-1320102夏季5132041工作日2023-05-122010-05-1420102夏季5142051工作日2023-05-122010-05-1520102夏季5152060周末2023-05-122010-05-1620102夏季5162170周末2023-05-122010-05-1720102夏季5172111工作日2023-05-122010-05-1820102夏季5182121工作日2023-05-122010-05-1920102夏季5192131工作日2023-05-122010-05-2020102夏季5202141工作日2023-05-122010-05-2120102夏季5212151工作日2023-05-122010-05-2220102夏季5222160周末2023-05-122010-05-2320102夏季5232270周末2023-05-122010-05-2420102夏季5242211工作日2023-05-122010-05-2520102夏季5252221工作日2023-05-122010-05-2620102夏季5262231工作日2023-05-122010-05-2720102夏季5272241工作日2023-05-122010-05-2820102夏季5282251工作日2023-05-122010-05-2920102夏季5292260周末2023-05-122010-05-3020102夏季5302370周末2023-05-122010-05-3120102夏季5312311工作日2023-05-122010-06-0120102夏季612321工作日2023-05-122010-06-0220102夏季622331工作日2023-05-122010-06-0320102夏季632341工作日2023-05-122010-06-0420102夏季642351工作日2023-05-122010-06-0520102夏季652360周末2023-05-122010-06-0620102夏季662470周末2023-05-122010-06-0720102夏季672411工作日2023-05-122010-06-0820102夏季682421工作日2023-05-122010-06-0920102夏季692431工作日2023-05-122010-06-1020102夏季6102441工作日2023-05-122010-06-1120102夏季6112451工作日2023-05-122010-06-1220102夏季6122461法定上班2023-05-122010-06-1320102夏季6132571法定上班2023-05-122010-06-1420102夏季6142510端午2023-05-122010-06-1520102夏季6152520端午2023-05-122010-06-1620102夏季6162530端午2023-05-122010-06-1720102夏季6172541工作日2023-05-122010-06-1820102夏季6182551工作日2023-05-122010-06-1920102夏季6192560周末2023-05-122010-06-2020102夏季6202670周末2023-05-122010-06-2120102夏季6212611工作日2023-05-122010-06-2220102夏季6222621工作日2023-05-122010-06-2320102夏季6232631工作日2023-05-122010-06-2420102夏季6242641工作日2023-05-122010-06-2520102夏季6252651工作日2023-05-122010-06-2620102夏季6262660周末2023-05-122010-06-2720102夏季6272770周末2023-05-122010-06-2820102夏季6282711工作日2023-05-122010-06-2920102夏季6292721工作日2023-05-122010-06-3020102夏季6302731工作日2023-05-12
版权归原作者 yongfeicao 所有， 如有侵权，请联系我们删除。