实时数据报表需求技术设计

​ 目前普通的T + 1报表已经无法满足很多商家和用户的实际需求了，如果自身的产品上能实现实时数据报表将会是自身产品的一大优势，本文注重讲解实时数据报表的技术实现。

1 背景

随着技术的不断发展，用户或者商家对于实时数据报表的需求日益提升，但是对于绝大多数的实现方案来讲，依然是传统的离线报表方案，如何高效、低成本的实现实时数据报表无疑是一次强有力的个人能力展示。

要达到的目标：三年累积1E数据，平均日增十万（写QPS峰值满足1000）情况下，实时聚合查询QPS至少达到200

2 简单实时数据报表

什么是简单的实时报表，以下举个例子

image-20220609205303735

可以简单认为就是没有搜索条件的报表，没有搜索条件意味着用户永远只能看到全部的汇总数据。

这种需求就算要求实时，做起来也是很简单的，可以参考以下标准流程

image-20220609205647101

总体思路就是冷热分离思想，通过离线任务将99%的数据聚合，来减少数据的储存量。

而实时且变动快的数据（一般来讲为当天数据），通过 Kafka -> Flink 处理后输出到Redis中给业务应用进行读取（因为Redis本身的读写能力足以满足我们的目标）。当然没有搭建Flink平台的，可以起个Java应用消费消息再输出到Redis中，也是一种方案，只不过是缺失了Flink窗口处理和幂等性的能力。

在我们需要查询报表的时候，把数据库的历史聚合数据 + Redis的当天数据 汇总起来就可以得到最终的数据报表了。整个过程简单且高效，排查问题也方便，十分建议大家使用。

3 复杂维度实时数据报表

但是很多情况下，只是简单维度的报表是无法满足用户需求的，比如说用户需要根据时间维度查看数据趋势、根据部门维度查看各个部门的整体状态。

当然有很多系统的做法是采用冷热分离，实时数据只能查看今天的，而趋势数据是不包含今天的来处理。

比如说想淘宝联盟的数据报表中，近七天、近三十天是不包含当日数据的。理论上对于报表需求来讲，做成这样就已经足够了。

image-20220613192721058

那么如果要支持该类需求，只需要在简单实时报表的基础上，把维度信息扩展出来即可。

比方说按照年、月、日查看数据，三年时间内，按照上述周期维度一共有 3 + 36 + 365 * 3 = 1,134个维度，换做部门维度同理（有多少部门就有多少个维度，其中部门维度下的数据模型可以参考附录1）。

也就是说我们要存储的数据从1条会分裂到1,134条，扩大一千倍的情况下，如果做好分表，业务库还是没有任何压力的。

我现在的公司是做SaaS的，假设我定个小目标，做他个十万家，则数据总量为113,400,000，差不多1E条，但是这是可以有效分表的，按照商家维度分个表，每张表压到百万级别轻轻松松。这就为我们带来了能在业务库中进行聚合查询的可能。

其中部门维度的表模型设计可以参考 附录1。

4 支持自定义时间筛选的实时数据报表

但是我们的需求更加变态，要支持筛选指定时间范围内的数据总和（我当时就是无限问号？？？）。直到今天我都没有体会到这个需求能给用户带来多大的价值，**所以奉劝各位，自定义时间报表直接砍掉，不要留情面**。

因为要做指定时间范围筛选，一些需要去重的数据（例如uv、下单用户数）就没有办法再进行优化了，只能将所有的明细存下来去distinct。你说像下单用户数，你去在Mysql-订单表中时间筛选distinct不是等着毕业吗？

那如果真的要做，该如何做呢？

首先不需要去重的数据可以参考《复杂维度实时数据报表》的做法，按日维度储存、汇总数据，不至于让数据过度膨胀。

需要去重的报表数据，可以通过ClickHouse储存明细数据，强行distinct查询（这里要注意，这些OLAP数据库的dinstinct是有误差存在的，因为其大多使用的是近似算法而不是真正的去重）

5 附录

附录1：部门组织结构情况下的数据统计模型设计

假设当前店铺部门层级如下

img

E部门下导购有订单3笔、退款0笔、销售额8000、业绩总额2000、拉新会员0

G部门下导购有订单1笔、退款0笔、销售额5000、业绩总额1000、拉新会员0

H部门下导购有订单2笔、退款2笔、销售额3000、业绩总额1600、拉新会员2

则数据模型如下

img

常见部门统计需求如下

  1. 查询指定部门C部门下的所有数据： 
1
select sum() from table where depart1 = A and depart2 = C and level > 2
  1. 查询指定部门F的数据，不包括下级部门：
1
select sum() from table where depart1 = A and depart2 = C and depart3 = F and level = 3
  1. 查询指定部门F的所有下级部门数据：
1
select sum() from table where depart1 = A and depart2 = C and depart3 = F and level = 4 group by depart1, depart2, depart3, depart4

附录2 大数据领域主要两个优化方向

以下两种方式是优化大数据下的聚合查询解决思路。

  1. MMP，通过堆机器来解决超大数据规模的计算、查询 （缺陷：机器成本高、对于某些查询和join可能支持不完善）

    image-20220609201934980

  2. 预计算方式，通过维度对明细数据不断的进行聚合操作，来减少所处理的数据规模（缺陷：需要事先声明维度、只支持聚合查询）

image-20220609201946357

  1. 预计算方式，通过维度对明细数据不断的进行聚合操作，来减少所处理的数据规模（缺陷：需要事先声明维度、只支持聚合查询）

image-20220609201946357