报表自动化: 抓住时间流逝的瞬间

前面《报表自动化: 没有压力的维度建模》以超市的一个订单为例简单讲述了维度建模中事实表与维度表的概念，这一篇主要讲一讲维度里面的时间维度这个特殊的数据内容。

为什么说时间维度特殊呢？比如说商品的分类：蔬菜、水果、饮品、小家电……很多种分类，但这个分类的数量有限且不是很多，但是对于时间呢，每一时间点都是一个值。

有人说既然时间这么多那不当做维度表处理不就行了么？但是我们的各种统计往往会根据时间作为判断依据，比如当日营业额，同比增长率……那么我们该如何处理呢？

简单的时间维度

首先我们要确定我们需要的最小粒度的时间，这里我们期待找到业务记录的精确到秒、毫秒级别以上的一个时间，这一步我们需要根据我们未来的报表需要进行处理。

比如我们的业务只需要分析每日信息，那么我们只需要以日期作为最小粒度的纬度值。这样我们的日期作为最小粒度的维度表中，每天都有一行数据，存储着一天的维度类型。

如果我们需要区分上午来访人数与下午来访人数的比较，那么我们的维度可能需要精确到上午下午，这时候也许还需要个晚上？这样一天就需要拆分为三个类型，每天都有三行数据。

那么我们的维度表存储什么呢？让我们来看一个以日期为最小粒度的简易维度表 dim_date_time_utc8

UTC+8日期	起始时间	终止时间
2010.10.10	2019.10.09 16:00:00	2019.10.10 16:00:00
2010.10.11	2019.10.10 16:00:00	2019.10.11 16:00:00
2010.10.12	2019.10.11 16:00:00	2019.10.12 16:00:00
2010.10.13	2019.10.12 16:00:00	2019.10.13 16:00:00

通过上面的维度表，我们可以快速的将数据库里的 UTC 时间转换为 UTC+8 时区的日期，快速生成可供后端直接读取显示的报表数据，同样的我们也可以创建多个不同时区的维度，以快速的算的多时区的日期。

注意一下，这个维度表可以存储过程一次生成的，但是我们不要生成从宇宙创立之初到一千年以后的维度，可以根据数据的需要进行生成，比如报表只需要显示最近两年的情况，那么我们只需要生成最近两年的纬度值，这样在进行数据库处理分析的时候能降低数据量并通过这个时间维度表来过滤掉时间范围以外的数据。

挖掘零碎时间的价值

让我们进阶一下，上午下午晚上这种粒度的时间维度该怎么做呢？让我们那创建以时间段为最小粒度的时间维度表 dim_date_time_segment_utc8：

UTC+8日期	时间段	起始时间	终止时间
2010.10.10	1	2019.10.09 16:00:00	2019.10.09 23:00:00
2010.10.10	2	2019.10.09 23:00:00	2019.10.10 08:00:00
2010.10.10	3	2019.10.10 08:00:00	2019.10.10 16:00:00

起止时间我就乱改了，这时候还需要第二个维度表了，把“时间段”的维度写出来 dim_time_segment_status_utc8：

UTC+8日期	时间段	英文
1	上午	Forenoon
2	下午	Afternoon
3	晚上	Night

面面俱到的分析时间

前面提到我们要找到最小粒度的时间，那么我们还需要大力度的时间该怎么办呢？比如还需要月度的统计、季度的统计呢？

我们有两种方式，第一种方式将耦合度提高，降低运算过程连表的数量，不用过多的介绍直接上表结构示意：

UTC+8日期	起始时间	终止时间	年份	月度	季度
2010.10.10	2019.10.09 16:00:00	2019.10.10 16:00:00	2010	10	1
2010.10.11	2019.10.10 16:00:00	2019.10.11 16:00:00	2010	10	2
2010.10.12	2019.10.11 16:00:00	2019.10.12 16:00:00	2010	10	3
2010.10.13	2019.10.12 16:00:00	2019.10.13 16:00:00	2010	10	4

想一想漫长的日子，就让我们每天 = 渡过三个月吧

另一种解耦的方法就是时间表不变，我们把 UTC+8日期、年份、月度、季度这三个字段单独作为一个表。

只看到了一堆的 table，让我们来看一下如何使用，上最喜欢的 code

select dim_invoied_date_time.utc_cn_date as invoiced_date,
			dim_invoiced_date_year_month.month as invoiced_month,
			dim_invoiced_date_year_month.quarter as invoiced_quarter
from supermarket_sales_order as sales_order
   join dim_date_time_utc8 as dim_invoied_date_time
   		on sales_order.invoiced_time >= date_time.from_time
   		  and sales_order.invoiced_time < date_time.end_time
   join dim_date_year_month as dim_invoiced_date_year_month
   		on dim_invoied_date_time.utc_cn_date = dim_date_year_month.utc_cn_date

由于表里涉及的是起止时间有重叠，所以代码里一定要注意大于等于、小于

不建议使用 between，一定要明确当前数据库中 between 对边界的处理，如果使用 between，需要注意避免在迁移数据库中对临界值的数据进行考虑

抓住时间流逝的瞬间

我们的报表往往不止需要关注当前业务的各种信息的最终状态，我们还需要回顾以前的种种。

继续拿超市的订单来举例，一个订单有已创建、已付款、已兑换、已退款……有多种状态的变化，如果我们业务数据库中记录下了每一次变化的时间点，比如我们有这样的一个表：

order_id	time	status
1233	2019.10.09 16:00:00	1
1233	2019.10.10 16:00:00	2
1233	2019.10.11 16:00:00	3

这个用户好像很犹豫呀，一天做一步

status 我们假设有一个表存了以下的 mapping 关系：1=已创建，2=已付款，3=已退款

如果业务只记录了这样的表结构，那么我们该如何在报表中应用呢？难道我们每次都要来这里搜索一个订单的在这里存储的最大时间值来获取当前状态，通过找到一个订单紧邻的两个状态条目的时间来计算“付款犹豫期”？

对于这种一个物体会在多个时间点进行状态变化的问题，为了方便快速的做各种分析，包括：当前状态、状态的持续时间、某一时间点的状态，我们引入 缓慢变化维 (Slowly Changing Dimension)来解决这类问题。

对于 SCD 我们有三种处理方式：

• 保留最新：我们只关心最后的状态，那么好吧，每次都直接 update（存储的只有最新的数据）
• 起止时间：我们记录每一个状态的起止时间，最新的状态的终止时间为 null，每次新增数据都先补全上一次记录的终止时间。这种方法是把复杂的逻辑拆分到业务运行时去填写信息（会造成业务数据的 update 操作），或者业务只记录上面的表格里的样子由 DW 层去通过 view 计算得到（运算量大需进行增量计算）。
• 链式存储：我们不关心时间区间，只需要知道上次是什么状态，或者我们的状态是怎么不断切换的，那么只需要在每条数据里存上一个状态的条目的 id（多一次查询但不会改历史数据）

来一个起止时间的例子：

order_id	start_time	end_time	status
1234	2019.10.09 16:00:00	2019.10.10 15:59:59	1
1234	2019.10.10 16:00:00	2019.10.11 15:59:59	2
1234	2019.10.11 16:00:00		3

对于 end_time 也可以是下一次的 start_time，保持风格一致即可，因为会影响到处理逻辑的区间边界问题

最后，欢迎测试 / 对比一下在大数据量时，下面的三种方式的运算性能差异：

1. 通过 join 这种时间维度进行时区转换

2. 通过 sql 的时间计算函数通过 view 或者 ETL 工具读取数据时进行转换

3. 通过 ETL 工具内的时间计算功能的进行时间计算

| 版权声明: 本站文章采用 CC 4.0 BY-SA 协议 进行许可，转载请附上原文出处链接和本声明。
| 本文链接: Cologic Blog - 报表自动化: 抓住时间流逝的瞬间 - https://www.coologic.cn/2020/03/1767/

微信公众号