背景
大部分旧版本数据存储平台没有提供生成序列的函数,导致有时需要用很长的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()
}
}