多维分析中实现预聚合的有效方法

多维分析（OLAP）通常对响应效率要求极高，当涉及的数据量很大时，直接从明细数据进行聚合，性能会非常低下。为解决此问题，通常会考虑使用预聚合（Pre-aggregation）的方法来加速查询，即提前计算好查询结果，使用时直接读取预聚合的结果。这样就能获得实时的响应，从而满足交互式分析的需求。

然而，如果将所有可能的维度组合都进行预聚合，那将是不现实的。例如，对50个维度做全量预聚合，即便中间CUBE大小只有1KB，所需的存储空间也将高达1MT，相当于一万亿块1T的硬盘。即便只对这50个维度中的20个进行预聚合，也依然需要47万T的空间，这是不可接受的。因此，通常采用部分预聚合的方法，即聚合部分维度，以权衡存储空间和性能的要求。

预聚合方案的困境

事实上，即便忽略存储容量，预聚合也只能满足占多维分析中极小一部分的、相对固定的查询需求，一旦遇到实际业务中大量存在的、稍显复杂灵活的场景，预聚合就无能为力了。

1. 非常规聚合：除了常见的求和、计数等操作，诸如唯一值计数、中位数、方差等非常规的聚合操作，可能无法被预先计算，也无法由其他聚合值推导出来。理论上，聚合操作的种类是无穷多的，不可能预聚合所有这些操作。

2. 聚合组合：聚合操作可能是组合的。例如，我们可能想知道月平均销量，这需要将一个月的每日销量加总，然后取平均值。这个操作不是简单的加总和求平均，而是跨不同维度级别聚合操作的组合。这类操作也不可能提前预聚合。

3. 对指标的条件：指标在统计过程中也可能带有条件。例如，我们想知道交易金额大于100美元的订单的总销售额。这个信息无法在预聚合时处理，因为100这个值是一个临时输入的参数。

4. 时间段统计：时间是一个特殊的维度，可以进行枚举或连续区间式的切片。查询区间的起始和结束点可能是细粒度的（例如，精确到某一天），这种情况下，必须使用细粒度的数据进行重新计数，而不是直接使用更高层级的预聚合数据。

可见，预聚合确实能在一定程度上提升多维分析的性能，但它只能应对多维分析中极少的一部分场景，即便如此，也仅能支持部分预聚合。如此一来，其使用场景就更为有限。即便如此，它仍然面临着巨大的存储空间问题。因此，在追求多维分析效果时，将希望寄托于预聚合方案是不可靠的。要使多维分析有效，硬遍历（Hard Traversal）才是基本功，即便有预聚合数据，也需要在优秀的硬遍历能力辅助下，才能发挥更大的作用。

SPL中的预聚合

开源的esProc SPL不仅提供了多维分析中常规的预聚合方式，还提供了特殊的时间段预聚合机制。更重要的是，借助SPL强大的数据遍历能力，可以满足更广泛的多维分析场景需求。

我们先来看看SPL的预聚合能力。

部分预聚合

既然全量预聚合不现实，我们只能将目光转向部分预聚合。虽然不能实现O(1)的响应速度，但性能提升几十倍也具有现实意义。SPL可以根据需要创建多个预聚合的cuboid。例如，数据表T有A、B、C、D、E五个维度，根据业务经验，我们可以预先计算出几个最常用的cuboid

上图展示了cuboid存储空间的占用情况，cube1占用空间最大，cube2占用空间最小。当前端应用发出一个需要按照B和C进行聚合的请求时，SPL会从多个cuboid中自动选择，过程大致如下

步骤i，SPL会找到可用的cuboid，这里是cube1和cube3。步骤ii，SPL在找到cube1相对较大的情况下，会自动选择较小的cube3，并基于cube3进行B和C的分组聚合。

SPL代码示例

	A
1	=file("T.ctx").open()
2	=A1.cuboid(cube1,A,B,C;sum(…),avg(…),…)
3	=A1.cuboid(cube2,A,C,D;sum(…),avg(…),…)
4	=A1.cgroups(B,C;sum(…), avg(…))

使用cuboid函数可以创建预聚合数据（A2和A3），需要给定名称（如cube1），其余参数为维度和聚合指标；在A4使用时，cgroups函数会自动使用中间cuboid，并按照上述规则选择数据量最小的那个。

时间段预聚合

时间是多维分析中一个特别重要的维度，可以进行枚举或连续区间式的切片。例如，我们需要查询2018年5月8日至6月12日期间的总销售额，这两个时间点也是在查询时作为参数传入的，具有高度的任意性。时间段的计数还可能涉及多个关联组合，例如查询在2018年5月8日至6月12日期间售出的商品，并且是在2018年1月9日至2月17日期间生产的总销售额。这类时间段计数具有很强的业务意义，但常规预聚合方案无法处理。

针对特殊的时间段计数，SPL提供了**时间段预聚合**方式。例如，对于订单表，已经有一个按订单日期预聚合的cuboid（cube1），那么可以再添加一个按月份预聚合的cuboid（cube2）：这样，当需要计算2018年1月22日至9月8日期间的总金额时，大致过程如下

首先，将时间段分为三个部分，然后基于月度聚合数据cube2计算2月到8月整个月份的数据的聚合值，最后使用cube1计算1月22日至31日以及9月1日至8日的聚合值。这样，涉及的计算量是7（2月-8月）+10（1月22日-31日）+8（9月1日-8日）=25；而如果使用cube1的数据进行聚合，计算量将是223（1月22日至9月8日的天数）。由此，计算量减少了近10倍。

SPL代码示例

	A
1	=file("orders.ctx").open()
2	=A1.cuboid(cube1,odate,dept;sum(amt))
3	=A1.cuboid(cube2,month@y(odate),dept;sum(amt))
4	=A1.cgroups(dept;sum(amt);odate>=date(2018,1,22)&&dt<=date(2018,9,8))

cgroups函数增加了条件参数，当SPL发现存在时间段条件且存在更高层级的预聚合数据时，它就会启用此机制来减少计算量。在本例中，SPL会在聚合之前分别从cube1和cube2读取相应的数据。

SPL中的硬遍历

预聚合能应对的场景仍然非常有限，要实现灵活的多维分析，我们仍然需要依赖强大的遍历能力。多维分析操作本身并不复杂，遍历计算主要在于过滤维度。传统数据库只能使用WHERE进行硬计算，将与维度相关的过滤视为常规操作，因此无法获得更好的性能。SPL提供了多种维度过滤机制，足以满足各种多维分析场景的性能需求。

布尔维度序列

多维分析中最常见的切片（dicing）操作，通常是针对枚举类型维度进行的。除了时间维度，几乎所有的维度都是枚举维度，如产品、地区、类型等。如果采用传统处理方式，SQL中的表达式大致如下

SELECT D1,…,SUM(M1),COUNT(ID)… FROM T GROUP BY D1,…

WHERE Di in (di1,di2…) …

其中，Di in (di1, di2) 表示过滤枚举范围内的字段值。在实际应用中，“按客户性别、员工部门、产品类型等进行切片”就是对枚举维度的切片。对于传统的IN方式，需要进行多次比较判断才能滤出符合条件的数据（切片），因此性能非常低。IN中的值越多，性能越差。

SPL通过将查找操作转化为值访问操作来提升性能。首先，SPL将枚举维度转化为整数。如下图所示，事实表中维度D5的值被转换成维度表中序列号（位置）

然后，将切片条件转化为一个对齐的、由布尔值组成的序列。这样，在进行比较时，可以从序列的指定位置直接取出值（true/false）的判断结果，从而快速执行切片操作。

SPL数据预处理代码示例

	A
1	=file("T.ctx").open()
2	=file("T_new.ctx”).create(…)
3	=DV=T(“DV.btx”)
4	=A1.cursor().run(D=DV.pos@b(D))
5	=A2.append@i(A4)

A3读取维度表，A4使用DV将维度D转换为整数。DV会单独保存，用于查询时使用。

切片聚合

	A
1	=file("T.ctx").open()
2	=DV.(V.pos(~))
3	=A1.cursor(…;A2(D))
4	=A3.groups(…)

A2将参数V转换为一个与DV等长的布尔序列。当DV中的某个成员在V中时，A2中对应的位置成员非空（相当于判断时的true），否则填充为null（即false）。然后，在遍历进行切片时，只需要将已转换为整数的维度D作为序列号，取出该布尔序列的成员。如果非空，则表示原维度D属于切片条件列表V。通过序列号取值的操作复杂度远低于IN比较，从而极大地提升了切片性能。

SPL强大的硬遍历能力在实践中拥有显著的应用效果。借助布尔维度序列、预序过滤等硬遍历技术，计算银行用户画像与客户群的交集效率提升了200多倍。

标签位维度

在多维分析中，还有一种特殊的枚举维度，常用于切片（很少用于分组聚合），它只有两个值，是/否或真/假。这种枚举维度称为标签维度或二元维度，例如，某人是否已婚、是否上过大学、是否有信用卡等。标签维度的切片属于过滤条件中的是/或类型计算，用SQL表达大致是

SELECT D1,…,SUM(M1),COUNT(ID)… FROM T GROUP BY D1,…

WHERE Dj=true and Dk=false …

标签维度非常普遍，对客户或事物打标签是当前数据分析的重要手段。现代多维分析的数据集往往包含数百个标签维度。如果将如此多的维度作为普通字段处理，无论是在存储还是在操作上都会造成极大的浪费，难以获得高性能。

标签维度只有两个值，只需要一个比特（bit）来存储。而一个16位的整数可以存储16个标签，这样原本需要16个字段的存储，现在只需要一个字段即可。这种存储方式称为标签位维度。SPL提供了此机制，可以极大减少存储量，即减少硬盘的读取量。而且，整数的读取速度也不会受到影响。

例如，我们假设总共有8个二元维度，它们被存储在整数字段c1中，存储8位二进制数。要使用位存储方式计算二元维度切片，需要对事实表进行预处理，将其转化为位存储。

在处理后的事实表中，第一行c1的值为AFh，转换为二进制数是10100000，表示D6和D8为true，其他二元维度为false。之后，就可以进行位计算，实现二元维度切片。

当从前端传入的切片条件为“2,3”时，表示过滤出第二位二元维度（D7）和第三位二元维度（D8）值为true，而其他二元维度值为false的数据。

SPL代码示例

	A	B
1	="2,3"	=A1.split@p(",")
2	=to(8).(0)	=B1.(A2(8-~+1)=1)
3	=bits(A2)
4	=file("T.ctx").open().cursor(;and(c1,A3)==A3)
5	=A4.groups(~.D1,~.D2,~.D3,~.D4;sum(~.M1):S,count(ID):C)

对于8个是/或类型的条件过滤，只需要进行一次位运算AND即可实现。这样，原本需要在二元维度上进行的多重比较计算，被转换成了一次位运算AND，性能得到显著提升。将多个是/或类型的值转换为一个整数，也能降低数据占用的存储空间。

冗余排序

冗余排序是一种利用有序特性来加速读取（遍历）速度的优化方法。具体来说，存储一份按维度D1,...,Dn排序后的副本，再存储一份按维度Dn,...,D1排序后的副本。这样，虽然数据量会翻倍，但这是可以接受的。对于任何一个维度D，总会存在一份数据集，使其D排在它排序维度列表的前半部分。如果不是第一个维度，切片后的数据一般不会连成一个整体区域，而是由一些相对较大的连续区域组成。维度在排序维度列表中的位置越靠前，切片后数据的物理有序度就越高。

计算时，只需使用一个维度的切片条件进行过滤，其他维度的条件仍可在遍历过程中计算。在多维分析中，对某个维度的切片操作往往能将涉及的数据量缩小几倍甚至几十倍。重复使用其他维度的切片条件意义不大。当多个维度都有切片条件时，SPL会选择切片后范围占总值范围比例较小的维度，这通常意味着过滤掉的数据量更小。

SPL的cgroups函数实现了这种选择。如果有多个按不同维度排序的预聚合数据，并且存在多个切片条件，它会选择最合适的预聚合数据。

	A
1	=file("T.ctx").open()
2	=A1.cuboid(cube1,D1,D2,…,D10;sum(…))
3	=A1.cuboid(cube2,D10,D9,…,D1;sum(…))
4	=A1.cgroups(D2;sum(…);D6>=230 && D6<=910 && D8>=100 && D8<=10 &&…)

在cuboid创建预聚合数据时，分组维度的顺序是有意义的，因为不同的维度顺序会创建不同的预聚合数据。也可以手动选择一个排序合适的数据集并存储更多排序数据集。

此外，SPL还提供了许多高效的操作机制，不仅适用于多维分析，也适用于其他数据处理场景，如高性能存储、有序计算、并行计算等。结合这些机制，可以获得更高效的数据处理体验。

如上所述，预聚合只能满足多维分析中相对简单、固定的、占比较小的需求。对于大量其他常见需求，我们仍然需要借助计算引擎（如SPL）来实现高效硬遍历来满足。在强大的硬遍历能力基础上，结合SPL提供的部分预聚合和时间段预聚合能力，我们可以更好地满足多维分析中的性能和灵活性需求，同时将存储成本降至最低。

使用SPL应对多维分析场景，具有覆盖面广、查询性能高、成本低廉的优点，是理想的技术解决方案。

历史

• 2022年7月14日：初始版本