使用 ClickHouse 做去重加和
前情提要
什么是去重加和
在做数据统计查询时，「去重计数」可能大家并不陌生，也就是 COUNT(DISTINCT x)，访客数、买家数、订单数等都要这么来统计。那「去重加和」是什么？顾名思义就是 SUM(DISTINCT x)，对 x 去重后然后做加和运算，得到加和之后的值。比如一串数字 1, 2, 1, 3, 1，去重计数为 3，去重加和为 6。
为什么要去重加和
了解了「去重加和」是怎么算的，那这个「去重加和」有什么应用场景呢？对事实度量数据进行聚合查询时，指标类型分为可加、半可加、不可加三种，以下拿订单事实表为例。
可加
指的是该度量可以按照和事实表关联的任一维度进行汇总。比如：销售额、售出数，按订单、商品、买家维度统计都可以直接加和。
半可加
指的就是该度量在某些维度下不可进行汇总，或者说汇总起来没有意义。比如：商品库存，只能在商品维度下做加和才有意义，否则库存数会虚高。
不可加
指的是该度量在所有与该事实表关联的维度下都不可进行汇总。比如：客单价，这类比值字段都不可直接累加，需要把分子、分母分别汇总再做计算。
了解了以上几个概念，我们「去重加和」主要就是针对半可加指标的计算。但在实际应用中直接对值做去重然后加和的场景比较少，更多是像上面提到的商品库存，需要按某个维度去重，然后再加和。那这么说直接用 SUM(DISTINCT x) 的方式不行了吗？是又不是，这里先按住不表。
如何做去重加和
假设一个销售场景
- 键鼠套装组合销售，组合了一个键盘两个鼠标，商品 ID 为 123，键盘鼠标分别对应库存系统里的两个 SKU JP1 和 SB1
- 键盘 JP1 也单独销售，商品 ID 为 456，
- 同样鼠标 SB1 也单独销售，商品 ID 为 789
对应的销售记录如下：
- 订单 o1 买了 1 个 123 键鼠套装
- 订单 o2 买了 2 个 456 键盘
- 订单 o3 买了 1 个 789 鼠标
订单事实表如下：
CREATE TABLE f_order
(
`orderID` String,
`productID` UInt32,
`sku` String,
`orgQuantity` UInt16,
`realQuantity` UInt16
)
ENGINE = MergeTree
ORDER BY orderID
INSERT INTO f_order VALUES ('o1', 123, 'JP1', 1, 1), ('o1', 123, 'SB1', 1, 2), ('o2', 456, 'JP1', 2, 2), ('o3', 789, 'SB1', 1, 1)
SELECT *
FROM f_order
┌─orderID─┬─productID─┬─sku─┬─orgQuantity─┬─realQuantity─┐
│ o1 │ 123 │ JP1 │ 1 │ 1 │
│ o1 │ 123 │ SB1 │ 1 │ 2 │
│ o2 │ 456 │ JP1 │ 2 │ 2 │
│ o3 │ 789 │ SB1 │ 1 │ 1 │
└─────────┴───────────┴─────┴─────────────┴──────────────┘
orgQurantity 表示原始销售数量
realQuantity 表示库存实际扣减的数量
需求方期望得到这样的结果
- 统计原始销量汇总为 4（123：1，456：2，789：1）
- 统计商品（productID）维度的原始销量为 123：1，456：2，789：1
- 统计 sku 维度的实际销量为 JP1：3，SB1：3
- 统计 sku 维度的原始销量为 JP1：3，SB1：2
- ...
看上去好像有些绕，其实这里的关键点就是统计原始销量时，需要按订单和商品维度去重后再加和。
常规做法
使用两层聚合的方式，内层按订单和商品维度去重，外层再对去重后的原始销售数量加和。
统计原始销量汇总
SELECT sum(orgQuantity) AS totalOrgQuantity
FROM
(
SELECT any(orgQuantity) AS orgQuantity
FROM f_order
GROUP BY orderID, productID
)
┌─totalOrgQuantity─┐
│ 4 │
└──────────────────┘
这种写法优点是很清晰，写法符合 SQL 直觉；缺点是需要多做一层聚合，不一定能跟其他指标同时查询。
ClickHouse 做法
做法一
统计原始销量汇总
SELECT arraySum(x -> (x.3), groupUniqArray((orderID, productID, orgQuantity))) AS totalOrgQuantity
FROM f_order
┌─totalOrgQuantity─┐
│ 4 │
└──────────────────┘
将 (orderID, productID, orgQuantity) 作为一个元组 Tuple，然后通过 groupUniqArray 聚合成一个去重后的数组，再用 arraySum 对数组中每一项元组的第 3 项也就是 orgQuantity 进行加和。
这个做法优点是只要一层聚合，跟其他指标一起查询没啥障碍；缺点是如果去重后的数组非常大，将非常影响查询性能。
看下这两条语句的执行耗时就知道了：
SELECT sum(number) AS x
FROM
(
SELECT number
FROM numbers(10000000)
)
┌──────────────x─┐
│ 49999995000000 │
└────────────────┘
1 rows in set. Elapsed: 0.012 sec. Processed 10.02 million rows, 80.18 MB (814.14 million rows/s., 6.51 GB/s.)
SELECT arraySum(x -> x, groupUniqArray(number)) AS x
FROM numbers(10000000)
┌──────────────x─┐
│ 49999995000000 │
└────────────────┘
1 rows in set. Elapsed: 1.264 sec. Processed 10.02 million rows, 80.18 MB (7.93 million rows/s., 63.45 MB/s.)
差的不是一点半点，而是 100 倍，这肯定没法接受。
做法二
既然做法一主要性能问题出在去重后数组太大上，那我们就想办法缩小这个数组。回到需求上，我们不难发现只有组合销售的商品才要去重，普通的商品直接加和就行，而正常的销售场景中组合商品占比很少，刚好可以用这个「是否组合商品」的条件来减少需要去重的数量。
不过在查询之前，我们需要对以上 f_order 表改造下
-- 将 orgQuantity 修改为 Tuple(k Nullable(UInt32), q UInt16) 类型
-- 第一位 k 作去重键，取值 NULL 表示非组合商品，有值时为组合商品当前订单的订单号和商品 ID 的 crc32 值
CREATE TABLE f_order_2
(
`orderID` String,
`productID` UInt32,
`sku` String,
`orgQuantity` Tuple(k Nullable(UInt32), q UInt16),
`realQuantity` UInt16
)
ENGINE = MergeTree
ORDER BY orderID
SETTINGS index_granularity = 8192
-- 从 f_order 中导入数据，仅 productID 为 123 组合商品时才赋值给 orgQuantity.k
INSERT INTO f_order_2 SELECT
orderID,
productID,
sku,
(if(productID = 123, crc32(concat(orderID, toString(productID))), NULL), orgQuantity),
realQuantity
FROM f_order
SELECT *
FROM f_order_2
┌─orderID─┬─productID─┬─sku─┬─orgQuantity────┬─realQuantity─┐
│ o1 │ 123 │ JP1 │ (3806212741,1) │ 1 │
│ o1 │ 123 │ SB1 │ (3806212741,1) │ 2 │
│ o2 │ 456 │ JP1 │ (NULL,2) │ 2 │
│ o3 │ 789 │ SB1 │ (NULL,1) │ 1 │
└─────────┴───────────┴─────┴────────────────┴──────────────┘
查询语句改写
SELECT arraySum(x -> (x.2), groupUniqArrayIf(orgQuantity, isNotNull(orgQuantity.k))) + sumIf(orgQuantity.q, isNull(orgQuantity.k)) AS totalOrgQuantity
FROM f_order_3
┌─totalOrgQuantity─┐
│ 4 │
└──────────────────┘
语句分为两部分相加组成，第一部分跟做法一类似，但加入了条件判断，只有组合商品参与去重加和；第二部分也有条件判断，对非组合商品直接累加数量，这样让绝大多数非组合商品直接累加，就大大减少了去重加和的开销。
行文至此，是不是觉得这个问题已经完美解决了，还记得前文那个「是又不是」的回答吗，为什么这么说呢，我们来看看做法三。
做法三
如果你还对 SUM(DISTINCT x) 耿耿于怀，那告诉你念念不忘，必有回响。经过做法二，使用元组保存了「去重键」及「原始数量」，那是不是可以更进一步，拿 UInt64 类型分两段来分别保存「去重键」和「原始数量」，高位 33 ~ 64 用来保存「去重键」，低位 1 ~ 32 用来保存「原始数量」，高位和低位的加和不冲突，查询加和后取低位 1 ~ 32 的值作为结果（见下图），理论可行，实践开始。
改造表
-- 将 orgQuantity 修改为 UInt64 类型
-- 高位 33 ~ 64 用来保存「去重键」, 为 0 表示非组合商品，有值时为组合商品当前订单的订单号和商品 ID 的 crc32 值
CREATE TABLE f_order_3
(
`orderID` String,
`productID` UInt32,
`sku` String,
`orgQuantity` UInt64,
`realQuantity` UInt16
)
ENGINE = MergeTree
ORDER BY orderID
SETTINGS index_granularity = 8192
-- 从 f_order 中导入数据，仅 productID 为 123 组合商品时才赋值给 orgQuantity.k
INSERT INTO f_order_3 SELECT
orderID,
productID,
sku,
bitShiftLeft(toUInt64(if(productID = 123, crc32(concat(orderID, toString(productID))), 0)), 32) + orgQuantity,
realQuantity
FROM f_order
SELECT *
FROM f_order_3
┌─orderID─┬─productID─┬─sku─┬──────────orgQuantity─┬─realQuantity─┐
│ o1 │ 123 │ JP1 │ 16347559244213518337 │ 1 │
│ o1 │ 123 │ SB1 │ 16347559244213518337 │ 2 │
│ o2 │ 456 │ JP1 │ 2 │ 2 │
│ o3 │ 789 │ SB1 │ 1 │ 1 │
└─────────┴───────────┴─────┴──────────────────────┴──────────────┘
查询
-- 统计原始销量汇总为 4（123：1，456：2，789：1）
SELECT bitAnd(sumDistinctIf(orgQuantity, orgQuantity >= 4294967296), 4294967295) + sumIf(orgQuantity, orgQuantity < 4294967296) AS totalOrgQuantity
FROM f_order_3
┌─totalOrgQuantity─┐
│ 4 │
└──────────────────┘
-- 统计商品（productID）维度的原始销量为 123：1，456：2，789：1
SELECT
productID,
bitAnd(sumDistinctIf(orgQuantity, orgQuantity >= 4294967296), 4294967295) + sumIf(orgQuantity, orgQuantity < 4294967296) AS totalOrgQuantity
FROM f_order_3
GROUP BY productID
┌─productID─┬─totalOrgQuantity─┐
│ 456 │ 2 │
│ 789 │ 1 │
│ 123 │ 1 │
└───────────┴──────────────────┘
-- 统计 sku 维度的实际销量为 JP1：3，SB1：3
SELECT
sku,
sum(realQuantity) AS totalRealQuantity
FROM f_order_3
GROUP BY sku
┌─sku─┬─totalRealQuantity─┐
│ JP1 │ 3 │
│ SB1 │ 3 │
└─────┴───────────────────┘
-- 统计 sku 维度的原始销量为 JP1：3，SB1：2
SELECT
sku,
bitAnd(sumDistinctIf(orgQuantity, orgQuantity >= 4294967296), 4294967295) + sumIf(orgQuantity, orgQuantity < 4294967296) AS totalOrgQuantity
FROM f_order_3
GROUP BY sku
┌─sku─┬─totalOrgQuantity─┐
│ JP1 │ 3 │
│ SB1 │ 2 │
└─────┴──────────────────┘
语句分同样为两部分组成，第一部分条件 orgQuantity >= 4294967296 表示有去重键是组合商品，需要进行去重加和，直接使用 sumDistinct 做加和，对加和后的结果使用 bitAnd 取出低位 1 ~ 32；第二部分条件 orgQuantity < 4294967296 表示没有去重键是非组合商品，直接累加数量；最后两部分相加得出正确结果。
小结
做法二和做法三看上去都能满足需求，到底应该用哪个呢？先不急着下结论，我们再对比下两者的性能：
模拟做法二
CREATE TABLE x_tuple
(
`x` Tuple(Nullable(UInt32), UInt16)
)
ENGINE = MergeTree
ORDER BY x
-- 模拟 9 / 10 的数据为非组合商品（没有去重键）
INSERT INTO x_tuple SELECT x
FROM
(
SELECT
number % 100000 AS y,
(if(y > 90000, NULL, y), (number % 4) + 1) AS x
FROM numbers(10000000)
)
SELECT arraySum(x -> (x.2), groupUniqArrayIf(x, isNotNull(x.1))) + sumIf(x.2, isNull(x.1)) AS x
FROM x_tuple
┌───────x─┐
│ 2724901 │
└─────────┘
1 rows in set. Elapsed: 0.381 sec. Processed 10.00 million rows, 70.00 MB (26.26 million rows/s., 183.81 MB/s.)
模拟做法三
CREATE TABLE x_distinct
(
`x` UInt64
)
ENGINE = MergeTree
ORDER BY x
-- 模拟 9 / 10 的数据为非组合商品（没有去重键）
INSERT INTO x_distinct SELECT x
FROM
(
SELECT
number % 100000 AS y,
(if(y > 90000, 0, bitShiftLeft(toUInt64(y + 1), 32)) + (number % 4)) + 1 AS x
FROM numbers(10000000)
)
SELECT bitAnd(sumDistinctIf(x, x >= 4294967296), 4294967295) + sumIf(x, x < 4294967296) AS x
FROM x_distinct
┌───────x─┐
│ 2724901 │
└─────────┘
1 rows in set. Elapsed: 0.170 sec. Processed 10.00 million rows, 80.00 MB (58.71 million rows/s., 469.69 MB/s.)
从上面结果看出，做法三在性能上还是快了一大截。
那么果断使用做法三吗，其实不然，如果经常要查看原始数据，建议还是使用做法二，毕竟做法三像给数量「加密」了，没法肉眼看出原始数量多少，为了一点性能，损失便利性，有点得不偿失；做法三还有一个天生的短板，低位加和超过 4294967295 后数据就开始错乱了，但这个值也足够销量指标用了。
总结
本文通过「去重加和」这个场景，展现出 ClickHouse 查询语法的灵活性，以及存储格式的多样性，抛砖引玉，希望可以拓宽读者使用 ClickHouse 解决查询问题的思路。若「查询组合商品的原始销量汇总」这一特定案例的模拟，能帮助读者解决类似数据查询的问题，倍感荣幸。
注：本文所有语句均运行在 ClickHouse 21.8.4.51 版本上，过低的版本可能无法支持
sumDistinct函数，请读者自行辨别。