数据仓库-日期维度表的设计与实现

时间维度表的制作

1 需求背景

在大数据分析模块中，我们需要从不同的维度分析主题表，包括常用的公用维度：时间维，地区维度，教育信息维…以及各种各样的业务维度：员工维度，部门维度…，业务维度就是我们从哪些角SEO靠我度去分析业务过程，本文就是做一张常用时间维度表。

时间维表由于是可预见的，因此可以一次性导入未来几十年的，当然对于一些节假日的设置可能只能获取未来一年的，因此可以每年全量更新一次。

2 维表设计

给出时间维SEO靠我度表的建表语句

CREATE DATABASE dim_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;drop table SEO靠我dim_db.dim_date; create table if not exists dim_db.dim_date (udate varchar(20) commeSEO靠我nt 日期,uyear varchar(20) comment 年,uquarter varchar(20) comment 季度,useason varchar(20) comment 季节,umoSEO靠我nth varchar(20) comment 月,uday varchar(20) comment 日,uweek varchar(20) comment 第几周,uweekday varchar(SEO靠我20) comment 周几：1-周一、2-周二、3-周三、4-周四、5-周五、6-周六、7-周日,is_workday varchar(20) comment 是否是工作日:1,0,udatetypSEO靠我e varchar(20) comment 节假日类型:工作日，法定上班[还班]，周末，节假日,updatedate varchar(20) comment 数据更新日期 );

这里额外SEO靠我解释三个字段

uquarter: 季度，按照阳历的日期分的，[1,2,3]第一季度,[4,5,6]为第二季度,[7,8,9]为第三季度,[10,11,12]为第四季度。

useason:季节，季节的划分有SEO靠我三种方式，本采用的是节气划分法。

天文划分法，以春分、夏至、秋分、冬至为四季的开始；

气象划分法，以3月至5月为春季，6月至8月为夏季，9月至11月为秋季，12月至2月为冬季；

节气划分法，以立春、立夏、立SEO靠我秋、立冬为四季之始。

udatetype:法定上班指的是因为放假调休周末也要上班的日期，节假日会直接显示日期名称，实现的逻辑大家可以自行更改。

3 实现方式

3.1 安装库

本文实现的方式是基于python的SEO靠我chinese_calendar库和pymysql库将数据导入到mysql，接着生成csv文件导入导hive数据库里面。

MySQL：5.6.36 本地部署chinese_calendar：1.8.0 SEO靠我这是一个基于阿里云开发的中国日历库，每年会更新pymysql：1.0.2 连接mysql用的python版本：3.7 pip install -i https://pypi.tuna.SEO靠我tsinghua.edu.cn/simple chinesecalendar pip install pymysql

3.2 实现代码

import chinese_calendar SEO靠我 import pymysql.cursors import datetime import pytzdef main():# (1)设置生成表中数据的开始SEO靠我和截至日期start_date = datetime.date(2010, 1, 1)end_date = datetime.date(2023, 12, 31)# (2)获取该时间段内所有的日期daSEO靠我tes = chinese_calendar.get_dates(start_date, end_date)# (3)遍历日期,构造sql字符串strsql = INSERT INTO `dim_daSEO靠我te`(`udate`,`uyear`,`uquarter`,`useason`,`umonth`,`uday`,`uweek`,`uweekday`,`is_workday`,`udateType`SEO靠我,`updatedate`)VALUESis_first_line = True# (4)设置生成节气的起始时间和截至日期# 因为需要 立春，立夏，立秋，立冬四个字段的名称，所以 [数据项的开始日期和SEO靠我截至日期区间] 应在 [节气的起始时间和截至日期的区间]内solar_term_start_date = datetime.date(2009, 11, 7)solar_term_end_date =SEO靠我 datetime.date(2030, 12, 31)season_start_date_list = get_season_start_date_list(solar_term_start_datSEO靠我e, solar_term_end_date)# get_current_season_indexindex = get_current_season_index(season_start_date_SEO靠我list, start_date)# (5)遍历日期集合构造sqlfor date in dates:# [1]udate: 日期udate = date.__str__()# [2]uyear: 年SEO靠我份uyear = str(date.year)# [3]umonth: 月份umonth = str(date.month)# [4]uquarter: 季度uquarter = get_quarteSEO靠我r(date.month)# [5]useason: 季节useason = if season_start_date_list[index][0] <= date < season_start_daSEO靠我te_list[index + 1][0]:useason = season_start_date_list[index][1]elif date >= season_start_date_list[SEO靠我index + 1][0]:index += 1useason = season_start_date_list[index][1]# [6]uday: 日uday = str(date.day)# SEO靠我[7]uweek: 第几周# 构造一个指定日期时间，时区[必选]的datetime 对象timezone = pytz.timezone(Asia/Shanghai)dt = datetime.datSEO靠我etime(date.year, date.month, date.day, tzinfo=timezone)uweek = str(int(dt.strftime("%U")) + 1)# [7]uSEO靠我weekday: 周几uweekday = str(date.isoweekday())# is_holiday, holidays = chinese_calendar.get_holiday_deSEO靠我tail(date)is_workday = 1 if chinese_calendar.is_workday(date) else 0# [9]udatetype: 日期类型udatetype = SEO靠我if is_workday == 1:if date.isoweekday() in [6, 7]:# udatetype = 法定上班is_holiday, holidays = chinese_cSEO靠我alendar.get_holiday_detail(date)udatetype = 法定上班- + get_chinese_name(holidays)else:udatetype = 工作日elSEO靠我se:is_holiday, holidays = chinese_calendar.get_holiday_detail(date)if holidays is None:udatetype = 周SEO靠我末else:udatetype = get_chinese_name(str(holidays))# [10]updatedate: 更新时间，默认是当天updatedate = datetime.dSEO靠我ate.today().__str__()# updatedate = datetime.date(2023, 5, 12).__str__() 手动指定更新日期# 判断是否首行if is_firstSEO靠我_line:is_first_line = Falseelse:strsql += ,# 构建单行数据库文本记录linerecord = (\ + udate + \,\ \+ uyear + \,\SEO靠我 \+ uquarter + \,\ \+ useason + \,\ \+ umonth + \,\ \+ uday + \,\ \+ uweek + \,\ \+ uweekday + \,\ \SEO靠我+ is_workday + \,\ \+ udatetype + \,\ \+ updatedate + \)print(linerecord)# 连接sqlstrsql += linerecordSEO靠我strsql += ;# (4)将生成的数据插入到数据库# 连接配置信息config = {host: 127.0.0.1,port: 3306,user: root,password: 你的数据库密SEO靠我码,db: dim_dib,charset: utf8,cursorclass: pymysql.cursors.DictCursor,}# 创建连接connection = pymysql.connSEO靠我ect(**config)# 使用cursor创建游标对象cursor = connection.cursor()# 执行sql语句cursor.execute(strsql)connection.cSEO靠我ommit()# 关闭连接cursor.close()connection.close()def get_quarter(month):获取月份对应的季度:param month: 月份:returnSEO靠我: 季度if month in [1, 2, 3]:return 1elif month in [4, 5, 6]:return 2elif month in [7, 8, 9]:return 3elSEO靠我if month in [10, 11, 12]:return 4else:return Nonedef get_chinese_name(english_name):将英文节日名称映射成中文名称:pSEO靠我aram english_name: str:return: strif english_name == "New Years Day":return "元旦"elif english_name ==SEO靠我 "Spring Festival":return "春节"elif english_name == "Tomb-sweeping Day":return "清明"elif english_name SEO靠我== "Dragon Boat Festival":return "端午"elif english_name == "Labour Day":return "劳动节"elif english_nameSEO靠我 == "National Day":return "国庆节"elif english_name == "Mid-autumn Festival":return "中秋"else:return "无效SEO靠我节日"def get_season_start_date_list(start_date, end_date):返回[每个季节开始的日期和名称]的集合目前可求的范围是：[1900, 2100]:parSEO靠我am start_date: 起始时间:param end_date: 结束时间:return: list，元素内容：(date,春季)，或者(date,夏季)，(date,秋季)，(date,冬季)SEO靠我solar_terms = chinese_calendar.get_solar_terms(start_date, end_date)date_season_list = []for term inSEO靠我 solar_terms:term_name = term[1]udate = term[0]if term_name == 立春:date_season_list.append((udate, 春季SEO靠我))elif term_name == 立夏:date_season_list.append((udate, 夏季))elif term_name == 立秋:date_season_list.appSEO靠我end((udate, 秋季))elif term_name == 立冬:date_season_list.append((udate, 冬季))return date_season_listdef SEO靠我get_current_season_index(season_start_date_list, start_date):获取起始时间对应季节的起始时间:param season_start_dateSEO靠我_list: 每个季节起始时间的日期集合以及相应的季节名称:param start_date: 起始时间:return: 起始时间对应季节的起始时间 | 集合的下标for index in rangeSEO靠我(0,12000):if start_date >= season_start_date_list[index][0]:return indexif __name__ == __main__:mainSEO靠我()

3.3 生成数据和更新

使用程序的时候只需要设置程序最开始的start_date和end_date，更新数据同理，不过后续更新数据的时候应该选择更高版本的chinese_calendar库。

4 生成SEO靠我数据预览

这里贴出来了2010年一整年的数据，datagrip导出来的，已经核对过数据是没有问题的，大家可以在http://www.gov.cn/zwgk/2009-12/08/content_1482SEO靠我691.htm国务院办公厅发布的节假日安排核对假日信息，手机日历上的节假日信息个别有偏差。

udateuyearuquarteruseasonumonthudayuweekuweekdayis_workSEO靠我dayudatetypeupdatedate2010-01-0120101冬季11150元旦2023-05-122010-01-0220101冬季12160元旦2023-05-122010-01-03SEO靠我20101冬季13270元旦2023-05-122010-01-0420101冬季14211工作日2023-05-122010-01-0520101冬季15221工作日2023-05-122010-0SEO靠我1-0620101冬季16231工作日2023-05-122010-01-0720101冬季17241工作日2023-05-122010-01-0820101冬季18251工作日2023-05-122SEO靠我010-01-0920101冬季19260周末2023-05-122010-01-1020101冬季110370周末2023-05-122010-01-1120101冬季111311工作日2023-0SEO靠我5-122010-01-1220101冬季112321工作日2023-05-122010-01-1320101冬季113331工作日2023-05-122010-01-1420101冬季114341工SEO靠我作日2023-05-122010-01-1520101冬季115351工作日2023-05-122010-01-1620101冬季116360周末2023-05-122010-01-1720101冬季SEO靠我117470周末2023-05-122010-01-1820101冬季118411工作日2023-05-122010-01-1920101冬季119421工作日2023-05-122010-01-20SEO靠我20101冬季120431工作日2023-05-122010-01-2120101冬季121441工作日2023-05-122010-01-2220101冬季122451工作日2023-05-1220SEO靠我10-01-2320101冬季123460周末2023-05-122010-01-2420101冬季124570周末2023-05-122010-01-2520101冬季125511工作日2023-0SEO靠我5-122010-01-2620101冬季126521工作日2023-05-122010-01-2720101冬季127531工作日2023-05-122010-01-2820101冬季128541工SEO靠我作日2023-05-122010-01-2920101冬季129551工作日2023-05-122010-01-3020101冬季130560周末2023-05-122010-01-3120101冬季SEO靠我131670周末2023-05-122010-02-0120101冬季21611工作日2023-05-122010-02-0220101冬季22621工作日2023-05-122010-02-0320SEO靠我101冬季23631工作日2023-05-122010-02-0420101春季24641工作日2023-05-122010-02-0520101春季25651工作日2023-05-122010-02SEO靠我-0620101春季26660周末2023-05-122010-02-0720101春季27770周末2023-05-122010-02-0820101春季28711工作日2023-05-122010SEO靠我-02-0920101春季29721工作日2023-05-122010-02-1020101春季210731工作日2023-05-122010-02-1120101春季211741工作日2023-05SEO靠我-122010-02-1220101春季212751工作日2023-05-122010-02-1320101春季213760春节2023-05-122010-02-1420101春季214870春节2SEO靠我023-05-122010-02-1520101春季215810春节2023-05-122010-02-1620101春季216820春节2023-05-122010-02-1720101春季2178SEO靠我30春节2023-05-122010-02-1820101春季218840春节2023-05-122010-02-1920101春季219850春节2023-05-122010-02-2020101春SEO靠我季220861法定上班2023-05-122010-02-2120101春季221971法定上班2023-05-122010-02-2220101春季222911工作日2023-05-122010-0SEO靠我2-2320101春季223921工作日2023-05-122010-02-2420101春季224931工作日2023-05-122010-02-2520101春季225941工作日2023-05-SEO靠我122010-02-2620101春季226951工作日2023-05-122010-02-2720101春季227960周末2023-05-122010-02-2820101春季2281070周末2SEO靠我023-05-122010-03-0120101春季311011工作日2023-05-122010-03-0220101春季321021工作日2023-05-122010-03-0320101春季33SEO靠我1031工作日2023-05-122010-03-0420101春季341041工作日2023-05-122010-03-0520101春季351051工作日2023-05-122010-03-062SEO靠我0101春季361060周末2023-05-122010-03-0720101春季371170周末2023-05-122010-03-0820101春季381111工作日2023-05-122010-SEO靠我03-0920101春季391121工作日2023-05-122010-03-1020101春季3101131工作日2023-05-122010-03-1120101春季3111141工作日2023-SEO靠我05-122010-03-1220101春季3121151工作日2023-05-122010-03-1320101春季3131160周末2023-05-122010-03-1420101春季31412SEO靠我70周末2023-05-122010-03-1520101春季3151211工作日2023-05-122010-03-1620101春季3161221工作日2023-05-122010-03-1720SEO靠我101春季3171231工作日2023-05-122010-03-1820101春季3181241工作日2023-05-122010-03-1920101春季3191251工作日2023-05-122SEO靠我010-03-2020101春季3201260周末2023-05-122010-03-2120101春季3211370周末2023-05-122010-03-2220101春季3221311工作日20SEO靠我23-05-122010-03-2320101春季3231321工作日2023-05-122010-03-2420101春季3241331工作日2023-05-122010-03-2520101春季3SEO靠我251341工作日2023-05-122010-03-2620101春季3261351工作日2023-05-122010-03-2720101春季3271360周末2023-05-122010-03-SEO靠我2820101春季3281470周末2023-05-122010-03-2920101春季3291411工作日2023-05-122010-03-3020101春季3301421工作日2023-05-SEO靠我122010-03-3120101春季3311431工作日2023-05-122010-04-0120102春季411441工作日2023-05-122010-04-0220102春季421451工作SEO靠我日2023-05-122010-04-0320102春季431460清明2023-05-122010-04-0420102春季441570清明2023-05-122010-04-0520102春季45SEO靠我1510清明2023-05-122010-04-0620102春季461521工作日2023-05-122010-04-0720102春季471531工作日2023-05-122010-04-0820SEO靠我102春季481541工作日2023-05-122010-04-0920102春季491551工作日2023-05-122010-04-1020102春季4101560周末2023-05-122010SEO靠我-04-1120102春季4111670周末2023-05-122010-04-1220102春季4121611工作日2023-05-122010-04-1320102春季4131621工作日2023SEO靠我-05-122010-04-1420102春季4141631工作日2023-05-122010-04-1520102春季4151641工作日2023-05-122010-04-1620102春季416SEO靠我1651工作日2023-05-122010-04-1720102春季4171660周末2023-05-122010-04-1820102春季4181770周末2023-05-122010-04-192SEO靠我0102春季4191711工作日2023-05-122010-04-2020102春季4201721工作日2023-05-122010-04-2120102春季4211731工作日2023-05-12SEO靠我2010-04-2220102春季4221741工作日2023-05-122010-04-2320102春季4231751工作日2023-05-122010-04-2420102春季4241760周末SEO靠我2023-05-122010-04-2520102春季4251870周末2023-05-122010-04-2620102春季4261811工作日2023-05-122010-04-2720102春季SEO靠我4271821工作日2023-05-122010-04-2820102春季4281831工作日2023-05-122010-04-2920102春季4291841工作日2023-05-122010-0SEO靠我4-3020102春季4301851工作日2023-05-122010-05-0120102春季511860劳动节2023-05-122010-05-0220102春季521970劳动节2023-05SEO靠我-122010-05-0320102春季531910劳动节2023-05-122010-05-0420102春季541921工作日2023-05-122010-05-0520102夏季551931工作SEO靠我日2023-05-122010-05-0620102夏季561941工作日2023-05-122010-05-0720102夏季571951工作日2023-05-122010-05-0820102夏季SEO靠我581960周末2023-05-122010-05-0920102夏季592070周末2023-05-122010-05-1020102夏季5102011工作日2023-05-122010-05-11SEO靠我20102夏季5112021工作日2023-05-122010-05-1220102夏季5122031工作日2023-05-122010-05-1320102夏季5132041工作日2023-05-1SEO靠我22010-05-1420102夏季5142051工作日2023-05-122010-05-1520102夏季5152060周末2023-05-122010-05-1620102夏季5162170周末SEO靠我2023-05-122010-05-1720102夏季5172111工作日2023-05-122010-05-1820102夏季5182121工作日2023-05-122010-05-1920102夏SEO靠我季5192131工作日2023-05-122010-05-2020102夏季5202141工作日2023-05-122010-05-2120102夏季5212151工作日2023-05-122010-SEO靠我05-2220102夏季5222160周末2023-05-122010-05-2320102夏季5232270周末2023-05-122010-05-2420102夏季5242211工作日2023-0SEO靠我5-122010-05-2520102夏季5252221工作日2023-05-122010-05-2620102夏季5262231工作日2023-05-122010-05-2720102夏季52722SEO靠我41工作日2023-05-122010-05-2820102夏季5282251工作日2023-05-122010-05-2920102夏季5292260周末2023-05-122010-05-3020SEO靠我102夏季5302370周末2023-05-122010-05-3120102夏季5312311工作日2023-05-122010-06-0120102夏季612321工作日2023-05-12201SEO靠我0-06-0220102夏季622331工作日2023-05-122010-06-0320102夏季632341工作日2023-05-122010-06-0420102夏季642351工作日2023-SEO靠我05-122010-06-0520102夏季652360周末2023-05-122010-06-0620102夏季662470周末2023-05-122010-06-0720102夏季672411工作SEO靠我日2023-05-122010-06-0820102夏季682421工作日2023-05-122010-06-0920102夏季692431工作日2023-05-122010-06-1020102夏季SEO靠我6102441工作日2023-05-122010-06-1120102夏季6112451工作日2023-05-122010-06-1220102夏季6122461法定上班2023-05-122010-SEO靠我06-1320102夏季6132571法定上班2023-05-122010-06-1420102夏季6142510端午2023-05-122010-06-1520102夏季6152520端午2023-SEO靠我05-122010-06-1620102夏季6162530端午2023-05-122010-06-1720102夏季6172541工作日2023-05-122010-06-1820102夏季61825SEO靠我51工作日2023-05-122010-06-1920102夏季6192560周末2023-05-122010-06-2020102夏季6202670周末2023-05-122010-06-21201SEO靠我02夏季6212611工作日2023-05-122010-06-2220102夏季6222621工作日2023-05-122010-06-2320102夏季6232631工作日2023-05-1220SEO靠我10-06-2420102夏季6242641工作日2023-05-122010-06-2520102夏季6252651工作日2023-05-122010-06-2620102夏季6262660周末20SEO靠我23-05-122010-06-2720102夏季6272770周末2023-05-122010-06-2820102夏季6282711工作日2023-05-122010-06-2920102夏季62SEO靠我92721工作日2023-05-122010-06-3020102夏季6302731工作日2023-05-12