在线咨询
内容搜索

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

发布时间：2025-12-09 16:10:31 浏览次数：7

时间维度表的制作

1 需求背景

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

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

2 维表设计

给出时间维度表的建表语句

CREATE DATABASE dim_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;drop table dim_db.dim_date;create table if not exists 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_calendarimport pymysql.cursorsimport datetimeimport pytzdef main():# (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_indexindex = 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 += 1useason = 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 is None: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)# 连接sqlstrsql += linerecordstrsql += ';'# (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()def get_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:return Nonedef get_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 "无效节日"def get_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_listdef get_current_season_index(season_start_date_list, start_date):'''获取起始时间对应季节的起始时间:param season_start_date_list: 每个季节起始时间的日期集合以及相应的季节名称:param start_date: 起始时间:return: 起始时间对应季节的起始时间 | 集合的下标'''for index in range(0,12000):if start_date >= season_start_date_list[index][0]:return indexif __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_workdayudatetypeupdatedate
2010-01-0120101冬季11150元旦2023-05-12
2010-01-0220101冬季12160元旦2023-05-12
2010-01-0320101冬季13270元旦2023-05-12
2010-01-0420101冬季14211工作日2023-05-12
2010-01-0520101冬季15221工作日2023-05-12
2010-01-0620101冬季16231工作日2023-05-12
2010-01-0720101冬季17241工作日2023-05-12
2010-01-0820101冬季18251工作日2023-05-12
2010-01-0920101冬季19260周末2023-05-12
2010-01-1020101冬季110370周末2023-05-12
2010-01-1120101冬季111311工作日2023-05-12
2010-01-1220101冬季112321工作日2023-05-12
2010-01-1320101冬季113331工作日2023-05-12
2010-01-1420101冬季114341工作日2023-05-12
2010-01-1520101冬季115351工作日2023-05-12
2010-01-1620101冬季116360周末2023-05-12
2010-01-1720101冬季117470周末2023-05-12
2010-01-1820101冬季118411工作日2023-05-12
2010-01-1920101冬季119421工作日2023-05-12
2010-01-2020101冬季120431工作日2023-05-12
2010-01-2120101冬季121441工作日2023-05-12
2010-01-2220101冬季122451工作日2023-05-12
2010-01-2320101冬季123460周末2023-05-12
2010-01-2420101冬季124570周末2023-05-12
2010-01-2520101冬季125511工作日2023-05-12
2010-01-2620101冬季126521工作日2023-05-12
2010-01-2720101冬季127531工作日2023-05-12
2010-01-2820101冬季128541工作日2023-05-12
2010-01-2920101冬季129551工作日2023-05-12
2010-01-3020101冬季130560周末2023-05-12
2010-01-3120101冬季131670周末2023-05-12
2010-02-0120101冬季21611工作日2023-05-12
2010-02-0220101冬季22621工作日2023-05-12
2010-02-0320101冬季23631工作日2023-05-12
2010-02-0420101春季24641工作日2023-05-12
2010-02-0520101春季25651工作日2023-05-12
2010-02-0620101春季26660周末2023-05-12
2010-02-0720101春季27770周末2023-05-12
2010-02-0820101春季28711工作日2023-05-12
2010-02-0920101春季29721工作日2023-05-12
2010-02-1020101春季210731工作日2023-05-12
2010-02-1120101春季211741工作日2023-05-12
2010-02-1220101春季212751工作日2023-05-12
2010-02-1320101春季213760春节2023-05-12
2010-02-1420101春季214870春节2023-05-12
2010-02-1520101春季215810春节2023-05-12
2010-02-1620101春季216820春节2023-05-12
2010-02-1720101春季217830春节2023-05-12
2010-02-1820101春季218840春节2023-05-12
2010-02-1920101春季219850春节2023-05-12
2010-02-2020101春季220861法定上班2023-05-12
2010-02-2120101春季221971法定上班2023-05-12
2010-02-2220101春季222911工作日2023-05-12
2010-02-2320101春季223921工作日2023-05-12
2010-02-2420101春季224931工作日2023-05-12
2010-02-2520101春季225941工作日2023-05-12
2010-02-2620101春季226951工作日2023-05-12
2010-02-2720101春季227960周末2023-05-12
2010-02-2820101春季2281070周末2023-05-12
2010-03-0120101春季311011工作日2023-05-12
2010-03-0220101春季321021工作日2023-05-12
2010-03-0320101春季331031工作日2023-05-12
2010-03-0420101春季341041工作日2023-05-12
2010-03-0520101春季351051工作日2023-05-12
2010-03-0620101春季361060周末2023-05-12
2010-03-0720101春季371170周末2023-05-12
2010-03-0820101春季381111工作日2023-05-12
2010-03-0920101春季391121工作日2023-05-12
2010-03-1020101春季3101131工作日2023-05-12
2010-03-1120101春季3111141工作日2023-05-12
2010-03-1220101春季3121151工作日2023-05-12
2010-03-1320101春季3131160周末2023-05-12
2010-03-1420101春季3141270周末2023-05-12
2010-03-1520101春季3151211工作日2023-05-12
2010-03-1620101春季3161221工作日2023-05-12
2010-03-1720101春季3171231工作日2023-05-12
2010-03-1820101春季3181241工作日2023-05-12
2010-03-1920101春季3191251工作日2023-05-12
2010-03-2020101春季3201260周末2023-05-12
2010-03-2120101春季3211370周末2023-05-12
2010-03-2220101春季3221311工作日2023-05-12
2010-03-2320101春季3231321工作日2023-05-12
2010-03-2420101春季3241331工作日2023-05-12
2010-03-2520101春季3251341工作日2023-05-12
2010-03-2620101春季3261351工作日2023-05-12
2010-03-2720101春季3271360周末2023-05-12
2010-03-2820101春季3281470周末2023-05-12
2010-03-2920101春季3291411工作日2023-05-12
2010-03-3020101春季3301421工作日2023-05-12
2010-03-3120101春季3311431工作日2023-05-12
2010-04-0120102春季411441工作日2023-05-12
2010-04-0220102春季421451工作日2023-05-12
2010-04-0320102春季431460清明2023-05-12
2010-04-0420102春季441570清明2023-05-12
2010-04-0520102春季451510清明2023-05-12
2010-04-0620102春季461521工作日2023-05-12
2010-04-0720102春季471531工作日2023-05-12
2010-04-0820102春季481541工作日2023-05-12
2010-04-0920102春季491551工作日2023-05-12
2010-04-1020102春季4101560周末2023-05-12
2010-04-1120102春季4111670周末2023-05-12
2010-04-1220102春季4121611工作日2023-05-12
2010-04-1320102春季4131621工作日2023-05-12
2010-04-1420102春季4141631工作日2023-05-12
2010-04-1520102春季4151641工作日2023-05-12
2010-04-1620102春季4161651工作日2023-05-12
2010-04-1720102春季4171660周末2023-05-12
2010-04-1820102春季4181770周末2023-05-12
2010-04-1920102春季4191711工作日2023-05-12
2010-04-2020102春季4201721工作日2023-05-12
2010-04-2120102春季4211731工作日2023-05-12
2010-04-2220102春季4221741工作日2023-05-12
2010-04-2320102春季4231751工作日2023-05-12
2010-04-2420102春季4241760周末2023-05-12
2010-04-2520102春季4251870周末2023-05-12
2010-04-2620102春季4261811工作日2023-05-12
2010-04-2720102春季4271821工作日2023-05-12
2010-04-2820102春季4281831工作日2023-05-12
2010-04-2920102春季4291841工作日2023-05-12
2010-04-3020102春季4301851工作日2023-05-12
2010-05-0120102春季511860劳动节2023-05-12
2010-05-0220102春季521970劳动节2023-05-12
2010-05-0320102春季531910劳动节2023-05-12
2010-05-0420102春季541921工作日2023-05-12
2010-05-0520102夏季551931工作日2023-05-12
2010-05-0620102夏季561941工作日2023-05-12
2010-05-0720102夏季571951工作日2023-05-12
2010-05-0820102夏季581960周末2023-05-12
2010-05-0920102夏季592070周末2023-05-12
2010-05-1020102夏季5102011工作日2023-05-12
2010-05-1120102夏季5112021工作日2023-05-12
2010-05-1220102夏季5122031工作日2023-05-12
2010-05-1320102夏季5132041工作日2023-05-12
2010-05-1420102夏季5142051工作日2023-05-12
2010-05-1520102夏季5152060周末2023-05-12
2010-05-1620102夏季5162170周末2023-05-12
2010-05-1720102夏季5172111工作日2023-05-12
2010-05-1820102夏季5182121工作日2023-05-12
2010-05-1920102夏季5192131工作日2023-05-12
2010-05-2020102夏季5202141工作日2023-05-12
2010-05-2120102夏季5212151工作日2023-05-12
2010-05-2220102夏季5222160周末2023-05-12
2010-05-2320102夏季5232270周末2023-05-12
2010-05-2420102夏季5242211工作日2023-05-12
2010-05-2520102夏季5252221工作日2023-05-12
2010-05-2620102夏季5262231工作日2023-05-12
2010-05-2720102夏季5272241工作日2023-05-12
2010-05-2820102夏季5282251工作日2023-05-12
2010-05-2920102夏季5292260周末2023-05-12
2010-05-3020102夏季5302370周末2023-05-12
2010-05-3120102夏季5312311工作日2023-05-12
2010-06-0120102夏季612321工作日2023-05-12
2010-06-0220102夏季622331工作日2023-05-12
2010-06-0320102夏季632341工作日2023-05-12
2010-06-0420102夏季642351工作日2023-05-12
2010-06-0520102夏季652360周末2023-05-12
2010-06-0620102夏季662470周末2023-05-12
2010-06-0720102夏季672411工作日2023-05-12
2010-06-0820102夏季682421工作日2023-05-12
2010-06-0920102夏季692431工作日2023-05-12
2010-06-1020102夏季6102441工作日2023-05-12
2010-06-1120102夏季6112451工作日2023-05-12
2010-06-1220102夏季6122461法定上班2023-05-12
2010-06-1320102夏季6132571法定上班2023-05-12
2010-06-1420102夏季6142510端午2023-05-12
2010-06-1520102夏季6152520端午2023-05-12
2010-06-1620102夏季6162530端午2023-05-12
2010-06-1720102夏季6172541工作日2023-05-12
2010-06-1820102夏季6182551工作日2023-05-12
2010-06-1920102夏季6192560周末2023-05-12
2010-06-2020102夏季6202670周末2023-05-12
2010-06-2120102夏季6212611工作日2023-05-12
2010-06-2220102夏季6222621工作日2023-05-12
2010-06-2320102夏季6232631工作日2023-05-12
2010-06-2420102夏季6242641工作日2023-05-12
2010-06-2520102夏季6252651工作日2023-05-12
2010-06-2620102夏季6262660周末2023-05-12
2010-06-2720102夏季6272770周末2023-05-12
2010-06-2820102夏季6282711工作日2023-05-12
2010-06-2920102夏季6292721工作日2023-05-12
2010-06-3020102夏季6302731工作日2023-05-12
上一篇：管理科学与工程 国内核心期刊 国外a刊及SCI 下一篇：(精心整理）WindowsXP终极优化设置
需要做网站？需要网络推广？欢迎咨询客户经理 13272073477

免责声明：本站内容（文字信息+图片素材）来源于互联网公开数据整理或转载，仅用于学习参考，如有侵权问题，请及时联系本站删除，我们将在5个工作日内处理。联系邮箱：303555158#qq.com（把#换成@）

Copyright © 格特瑞商务咨询-贵州做网站公司 版权所有 | 黔ICP备19002813号-1