数据仓库实践：日期维度实践

背景

大部分旧版本数据存储平台没有提供生成序列的函数，导致有时需要用很长的SQL代码生成一段日期的序列，比如UNION.

而且即使有生成序列的函数，在SQL中也很难动态的调整它的长度，比如大月小月，闰年。

同时除了日期序列的生成，仍然有标准化，集中化的要求，比如日期的字符串格式化标准、工作日、节假日排班、星期的起始日是星期日还是星期一等都需要统一。

如此才能在后续的关联、汇总等计算操作中保持一致，从而降低管理开发的复杂度和其他成本。

理想来源

日期序列

日期序列的主体使用编程语言按需生成一段日期，开始日可以是公司注册日期、上市日期，某个系统部署上线日期等一般符合所有计算需求的日期，然后使用定时任务按需周期性地录入。

可以是每半年往后生成两年，以此适应市场部门的需求预测等计划内日期计算需求。

日期维度的粒度

同时挑选覆盖当前汇总过程的粒度，比如日期，星期，月度，季度，年度，DAY_OF_YEAR(所在年的第几天)；

进一步可以录入各种标签，比如农历等历法等；

还有某些标准中需要额外注意的不存在的时刻，这些是历法改革、夏令时等产生的问题，在人员相关数据中存在，比如涉及生日的年龄计算。大部分新版数据平台已经能够兼容；

涉及的部分日期有

1986年5月4日

1987年4月12日

1988年4月10日

1989年4月16日

1990年4月15日

1989年4月14日

工作节假日规划

工作日，节假日等规划需要根据人事行政相关部门的排班管理流程；

日期维度序列生成示例

python 示例

import math

import arrow

# 开始日期
fact_date_start = arrow.get("2019-10-31", "YYYY-MM-DD")

for i in range(10):
    fact_date = fact_date_start.shift(days=i)

    # 日期
    print("ddate", fact_date.format('YYYY-MM-DD'))
    # 年
    print("year", fact_date.format('YYYY'))
    # 月
    print("month", fact_date.format('YYYY-MM'))
    # 月英文简写
    print("month_alias", fact_date.format('MMM'))
    # 所在年第几周
    print("week", fact_date.format('W')[:8])
    # 季度
    print("quarter", "{}".format(math.ceil(float(fact_date.format('M')) / 3)))
    # 所在年第几天
    print("day", fact_date.format('DDD'))

Oracle SQL 示例

Oracle SQL 可以直接使用 ETL 工具比如 Kettle等工具，或者存储过程做生成或者定时任务。

SELECT TO_CHAR(TRUNC(SYSDATE) + LEVEL, 'YYYY-MM-DD')                              DDATE,
       TO_CHAR(TRUNC(SYSDATE) + LEVEL, 'YYYY')                                  YEAR,
       TO_CHAR(TRUNC(SYSDATE) + LEVEL, 'YYYY-MM')                                MONTH,
       SUBSTR(TO_CHAR(TRUNC(SYSDATE) + LEVEL, 'IYYYIW'), 1, 4) || '-WK' ||
       SUBSTR(TO_CHAR(TRUNC(SYSDATE) + LEVEL, 'IYYYIW'), 5, 2)                  WEEK,
       SUBSTR(TO_CHAR(TRUNC(SYSDATE) + LEVEL, 'YYYYQ'), 1, 4) || '-Q' ||
       SUBSTR(TO_CHAR(TRUNC(SYSDATE) + LEVEL, 'YYYYQ'), 5, 1)                   QUATER,
       CAST(TO_NUMBER(TO_CHAR(TRUNC(SYSDATE) + LEVEL, 'DDD')) AS NVARCHAR2(10)) DAY
FROM DUAL
CONNECT BY LEVEL < 730

Spark

object DimTime {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
    .appName("data")
    .master("local[*]")
    .config("spark.debug.maxToStringFields", "200")
    .config("spark.sql.crossJoin.enabled", "true")
    .getOrCreate()

    spark.sql(
      """
        |select '2011-12-31' fact_date_start
        |""".stripMargin).createOrReplaceTempView("tmp_start")

    spark.sql(
      """
        |select cast(to_date(seq_date) as string)                                           ddate,
        |       cast(date_format(seq_date, 'y') as string)                                  year,
        |       cast(date_format(seq_date, 'y-MM') as string)                               month,
        |       cast(date_format(seq_date, 'MMM') as string)                               month_alias,
        |       cast(concat(date_format(trunc(seq_date, 'week'), 'y'), '-WK',
        |                   format_number(weekofyear(seq_date), '00')) as string)           week,
        |       cast(concat(date_format(seq_date, 'y'), '-Q', quarter(seq_date)) as string) quater,
        |       cast(dayofyear(seq_date) as string)                                         day
        |from (select explode(sequence(date_add( fact_date_start, 1), add_months( fact_date_start, 1))) seq_date
        |      from tmp_start ) temp_genr
        |""".stripMargin).show()

  }
}