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

时间维度表的制作

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_calendar
import pymysql.cursors
import datetime
import pytz


def 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_index
    index = get_current_season_index(season_start_date_list, start_date)

    # (5)遍历日期集合构造sql
    for 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 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 = False
        else:
            strsql += ','

        # 构建单行数据库文本记录
        linerecord = '(\'' + udate + '\',\'' \
                     + uyear + '\',\'' \
                     + uquarter + '\',\''