如何使用插件hll - 云原生数据库 PolarDB

hll插件支持的数据类型HyperLogLog（hll）可以帮助您快速预估PV、UV等业务指标。

前提条件

支持的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（内核小版本14.5.1.0及以上）
PostgreSQL 11（内核小版本1.1.28及以上）

说明

您可通过如下语句查看PolarDB PostgreSQL版的内核小版本的版本号：

PostgreSQL 14
```
SELECT version();
```
PostgreSQL 11
```
SHOW polar_version;
```

背景信息

hll插件支持一种可变长、类似集合的数据类型HyperLogLog（hll），常用于在指定精度下返回近似的distinct值，例如，1280字节的hll数据类型可以高精度地估计出近百亿的distinct值。hll适合互联网广告分析或其他有类似预估分析计算需求的行业，可以快速预估PV、UV等业务指标。

安装hll插件

CREATE EXTENSION hll;

基础操作

创建一个含有hll字段的表，命令如下：

CREATE TABLE agg (id INT PRIMARY KEY, userids hll);

将int数据类型转换为hll_hashval类型，命令如下：
```
SELECT 1::hll_hashval;
```
返回结果如下：
```
 hll_hashval 
-------------
 1
(1 row)
```

基本操作符

hll类型支持如下操作符：

示例如下：

SELECT hll_add_agg(1::hll_hashval) = hll_add_agg(2::hll_hashval);

返回结果如下：

 ?column? 
----------
 f
(1 row)

SELECT hll_add_agg(1::hll_hashval) || hll_add_agg(2::hll_hashval);

返回结果如下：

                 ?column?                 
------------------------------------------
 \x128b7f00000000000000010000000000000002
(1 row)

SELECT #hll_add_agg(1::hll_hashval);

返回结果如下：

 ?column? 
----------
        1
(1 row)

hll_hashval类型支持如下操作符：

示例如下：

SELECT 1::hll_hashval = 2::hll_hashval;

返回结果如下：

 ?column? 
----------
 f
(1 row)

SELECT 1::hll_hashval <> 2::hll_hashval;

返回结果如下：

 ?column? 
----------
 t
(1 row)

基本函数

支持hll_hash_boolean、hll_hash_smallint和hll_hash_bigint等hash函数，示例如下：

SELECT hll_hash_boolean(true);

返回结果如下：

  hll_hash_boolean   
---------------------
 8849112093580131862
(1 row)

SELECT hll_hash_integer(1);

返回结果如下：

   hll_hash_integer   
----------------------
 -8604791237420463362
(1 row)

支持hll_add_agg函数，可以将int转换为hll格式，示例如下：

SELECT hll_add_agg(1::hll_hashval);

返回结果如下：

       hll_add_agg        
--------------------------
 \x128b7f0000000000000001
(1 row)

支持hll_union函数，可以将hll并集，示例如下：

SELECT hll_union(hll_add_agg(1::hll_hashval), hll_add_agg(2::hll_hashval));

返回结果如下：

                hll_union                 
------------------------------------------
 \x128b7f00000000000000010000000000000002
(1 row)

支持hll_set_defaults函数，可以设置精度，示例如下：

SELECT hll_set_defaults(15,5,-1,1);

返回结果如下：

 hll_set_defaults 
------------------
 (11,5,-1,1)
(1 row)

支持hll_print函数，用于打印debug信息，示例如下：

SELECT hll_print(hll_add_agg(1::hll_hashval));

返回结果如下：

                                  hll_print                                  
-----------------------------------------------------------------------------
 EXPLICIT, 1 elements, nregs=32768, nbits=5, expthresh=-1(2560), sparseon=1:+
 0:                    1 
(1 row)

卸载hll插件

DROP EXTENSION hll;

示例

准备基础数据。

CREATE TABLE access_date (acc_date DATE UNIQUE, userids hll);
INSERT INTO access_date
     SELECT current_date, hll_add_agg(hll_hash_integer(user_id))
     FROM generate_series(1,10000) t(user_id);
INSERT INTO access_date
     SELECT current_date-1, hll_add_agg(hll_hash_integer(user_id))
     FROM generate_series(5000,20000) t(user_id);
INSERT INTO access_date
     SELECT current_date-2, hll_add_agg(hll_hash_integer(user_id))
     FROM generate_series(9000,40000) t(user_id);

执行如下查询。

SELECT #userids FROM access_date WHERE acc_date=current_date;

返回结果如下：

     ?column?
------------------
 9725.852733707077
(1 row)

SELECT #userids FROM access_date WHERE acc_date=current_date-1;

返回结果如下：

     ?column?
------------------
 14968.65968832792
(1 row)

SELECT #userids FROM access_date WHERE acc_date=current_date-2;

返回结果如下：

     ?column?
------------------
 29361.520914991113
(1 row)