使用 ClickHouse 做去重加和

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 函数，请读者自行辨别。