增量/存量数据按时间维度分组

数据统计分析常用的统计方式：

1、存量：系统在某一时间点下数据在某个条件下的总数；

例：

需求：假设房源状态流转为：上架->下架->上架  （形成闭环） ，按日的维度统计时间范围内每天处于上架状态的存量房源数量

        设计中间表（time 时间 , nums 上架房源数量）用来给用户统计用。因为是按日分组，所以每一天都会对应有处于上架状态的存量房源的数量。因此这里每天都会生成一行记录，且该行的数据继承于昨天的数据，如 2号的上架状态的房源数据=1号上架的房源数据+2号新增上架的房源数-下架数。因此：新增上架的房源数时 数量加1，新增下架房源数时减1

 

这里选取1月1号为初始节点，这时候处于上架状态房源总数为 10 套

时间	上架房源数量
2012-01-01	10

 

1月2号，此时新上架了 20套

时间	上架房源数量
2012-01-02	30

 

1月3号，此时新上架了 10套 下架了15套

 

时间	上架房源数量
2012-01-03	25

说明：

当按日分组统计每天处于状态的房源总数时：select 上架房源数量  from t_housing group by 时间

　　　数据获取

　　　　　　在第二天统计前一天处于上架状态的房源数，如1月1号的数据则在1月2号凌晨通过定时任务，执行统计数据的sql将1月1号的数据查出来之后，再放入中间表里。每次需要统计的时候，再从中间表中执行SQL 获得统计好的数据（类似SQL为：insert(time,nums) value(now(),select count(*) from t_housing where status = '上架')）

优点

• 该方法将统计的数量与具体的房源隔离开，不需要关心系统的房源总数，只需要一年生成365条记录即可
• 可以兼容 按月统计、按年统计（统计最后一天）

 

缺点

 

• 如果需要同时查询房源的其他属性时，则一年的数据量与该属性的总数成正比。如：同时需要增加查询条件—小区，假设有1000个小区，那么数量级将增加1000倍（上架时某房源时，需要找到房源所在的小区的行，再把该房源数量加1）
  
  如：

  时间	小区名称	上架房源数量
  2012-01-03	小区A	5
  2012-01-03	小区B	7
  2012-01-03	.....	9
  2012-01-03	小区Z	10

  
  
  

• 扩展性不好，如果需要临时增加查询条件，无法兼容之前的数据
• 闭环的状态增加了对同一行数据的写（如：房源中的上架与下架，下架时，需要将数量减 1）

 

2、流量：是指在某一段时间内流入/出系统的数量

3、增量：则是指在某一段时间内系统中增加的数量

4、增量=流入量 - 流出量

5、本期期末存量=上期期末存量+本期内增量

6、拉链表：记录数据变化的完整状态

例：

假设订单状态流转为   创建订单→支付完成→已出库

 

6月20号有3条记录：

订单创建日期	订单编号	订单状态
2012-06-20	001	创建订单
2012-06-20	002	创建订单
2012-06-20	003	支付完成

到6月21日，表中有5条记录：

订单创建日期	订单编号	订单状态
2012-06-20	001	支付完成（从创建到支付）
2012-06-20	002	创建订单
2012-06-20	003	支付完成
2012-06-21	004	创建订单
2012-06-21	005	创建订单

 

到6月22日，表中有6条记录：

 

订单创建日期	订单编号	订单状态
2012-06-20	001	支付完成（从创建到支付）
2012-06-20	002	创建订单
2012-06-20	003	已发货（从支付到发货）
2012-06-21	004	创建订单
2012-06-21	005	支付完成（从创建到支付）
2012-06-22	006	创建订单

 

数据仓库中对该表的保留方法：

 

1. 只保留一份全量，则数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足；
2. 每天都保留一份全量，则数据仓库中的该表共有14条记录，但好多记录都是重复保存，没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费；

 

如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表：

订单创建日期	订单编号	订单状态	dw_begin_date	dw_end_date
2012-06-20	001	创建订单	2012-06-20	2012-06-20
2012-06-20	001	支付完成	2012-06-21	9999-12-31
2012-06-20	002	创建订单	2012-06-20	9999-12-31
2012-06-20	003	支付完成	2012-06-20	2012-06-21
2012-06-20	003	已发货	2012-06-22	9999-12-31
2012-06-21	004	创建订单	2012-06-21	9999-12-31
2012-06-21	005	创建订单	2012-06-21	2012-06-21
2012-06-21	005	支付完成	2012-06-22	9999-12-31
2012-06-22	006	创建订单	2012-06-22	9999-12-3

说明：

1. dw_begin_date表示该条记录的生命周期开始时间，dw_end_date表示该条记录的生命周期结束时间；
2. dw_end_date = ‘9999-12-31’表示该条记录目前处于有效状态；
3. 如果查询当前所有有效的记录，则select * from order_his where dw_end_date = ‘9999-12-31′
4. 如果查询2012-06-21的历史快照，则select * from order_his where dw_begin_date <= ‘2012-06-21′ and end_date >= ‘2012-06-21’，这条语句会查询到以下记录：

订单创建日期	订单编号	订单状态	dw_begin_date	dw_end_date
2012-06-20	001	支付完成	2012-06-21	9999-12-31
2012-06-20	002	创建订单	2012-06-20	9999-12-31
2012-06-20	003	支付完成	2012-06-20	2012-06-21
2012-06-21	004	创建订单	2012-06-21	9999-12-31
2012-06-21	005	创建订单	2012-06-21	2012-06-21

缺点：不支持按时间进行分组，分别统计每个时间点的存量数据总和

 

转载于:https://www.cnblogs.com/coding400/p/10865092.html