使用 ClickHouse 做去重加和

发布于 17 天前 作者 iammapping 205 次浏览 来自 实战经验

ClickHouse

原文地址:https://iammapping.com/use-clickhouse-to-sum-distinct/

前情提要

什么是去重加和

在做数据统计查询时,「去重计数」可能大家并不陌生,也就是 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 函数,请读者自行辨别。

回到顶部