利用DuckDB的bitstring_agg函数配合bit_count快速求不同值的计数

在翻阅DuckDB的文档时看到bitstring_agg这么个函数, 还提到能代替count(DISTINCT …)获得更高的性能。但文档没有给出输出的例子。

bitstring_agg(arg)
Description The bitstring_agg function takes any integer type as input and returns a bitstring with bits set for each distinct value. The left-most bit represents the smallest value in the column and the right-most bit the maximum value. If possible, the min and max are retrieved from the column statistics. Otherwise, it is also possible to provide the min and max values.
Example bitstring_agg(A)
Tip
The combination of bit_count and bitstring_agg can be used as an alternative to count(DISTINCT …), with possible performance improvements in cases of low cardinality and dense values.

bitstring_agg(arg, min, max)
Description Returns a bitstring with bits set for each distinct position defined in arg. All positions must be within the range [min, max] or an Out of Range Error will be thrown.
Example bitstring_agg(A, 1, 42)

先来看bitstring_agg的输出

memory D select bitstring_agg(A, 1, 22) from(select 11 a union all select 13 union all select 11);
┌─────────────────────────┐
│ bitstring_agg(A, 1, 22) │
│           bit           │
├─────────────────────────┤
│ 0000000000101000000000  │
└─────────────────────────┘

memory D select bitstring(bitstring_agg(A, 1, 22),30) from(select 11 a union all select 13 union all select 11);
┌────────────────────────────────────────┐
│ bitstring(bitstring_agg(A, 1, 22), 30) │
│                  bit                   │
├────────────────────────────────────────┤
│ 000000000000000000101000000000         │
└────────────────────────────────────────┘

它返回从左到右第11位和第13位为1，其他位为0的二进制字符串。如果用bitstring(长度)扩充字符串的长度，则在左侧补零。
下面用随机100万个整数来测试bitstring_agg函数配合bit_count求不同值的计数，并与count(DISTINCT …)比较用时。

memory D create table t as (select (i*random())::int i from range(1,1000000)t(i));

memory D select count(distinct i) from t;
┌───────────────────┐
│ count(DISTINCT i) │
│       int64       │
├───────────────────┤
│            499996 │
└───────────────────┘
Run Time (s): real 0.023 user 0.136000 sys 0.016000
memory D select bit_count(bitstring_agg(i,0,1000000)) from t;
┌─────────────────────────────────────────┐
│ bit_count(bitstring_agg(i, 0, 1000000)) │
│                  int64                  │
├─────────────────────────────────────────┤
│                                  499996 │
└─────────────────────────────────────────┘
Run Time (s): real 0.008 user 0.052000 sys 0.000000

可见，虽然数据不很稠密，大概有一半重复，bit_count(bitstring_agg())还是比count(DISTINCT …)更快。