金管局计算机岗操作系统与网络25题精讲：从进程调度到RESTful API全链路解析（9000+字深度指南）

在翻阅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)TipThe 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,

先来看bitstring_agg的输出memory Dselectbitstring_agg(A,1,

from(select11aunionallselect13unionallselect

;┌─────────────────────────┐ │ bitstring_agg(A,1,

│ │bit│ ├─────────────────────────┤ │0000000000101000000000│ └─────────────────────────┘ memory Dselectbitstring(bitstring_agg(A,1,

,

from(select11aunionallselect13unionallselect

;┌────────────────────────────────────────┐ │ bitstring(bitstring_agg(A,1,

,

│ │bit│ ├────────────────────────────────────────┤ │000000000000000000101000000000│ └────────────────────────────────────────┘它返回从左到右第11位和第13位为1其他位为0的二进制字符串。

如果用bitstring(长度)扩充字符串的长度则在左侧补零。

下面用随机100万个整数来测试bitstring_agg函数配合bit_count求不同值的计数并与count(DISTINCT …)比较用时。

memory Dcreatetabletas(select(i*random())::intifromrange(1,

t(i));memory Dselectcount(distincti)fromt;┌───────────────────┐ │count(DISTINCTi)│ │ int64 │ ├───────────────────┤ │499996│ └───────────────────┘ RunTime(s):real

023user

136000sys

016000memory Dselectbit_count(bitstring_agg(i,0,

)fromt;┌─────────────────────────────────────────┐ │ bit_count(bitstring_agg(i,0,

)│ │ int64 │ ├─────────────────────────────────────────┤ │499996│ └─────────────────────────────────────────┘ RunTime(s):real

008user

052000sys

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

擼擼社免费看黄-擼擼社免费看黄应用