明细圈人函数 - 实时数仓Hologres

本文将为您介绍明细圈人函数bit_construct和bit_match的具体使用语法。

背景信息

在圈人场景中，通常会有通过明细表圈人的需求，例如一个用户有多条记录，不同的记录满足不同的条件，明细圈人通常需要找出满足某些条件组合的用户列表。

示例明细表如下，需要在明细表中找出有过click购物车和view收藏页行为的user列表（此处满足条件的是user A）。

user	action	page
A	click	购物车
B	click	首页
A	view	收藏页
B	click	购物车
A	click	收藏页

传统的查询方法是采用多轮条件过滤并使用JOIN语句来筛选出符合条件的结果，这种方法需要写复杂的SQL，同时多个JOIN语句也会消耗大量的资源。从Hologres V0.10版本开始，针对明细表圈人场景提供bit_construct和bit_match函数，只需要通过一轮过滤筛选和函数计算即可得到结果人群数据，降低明细圈人场景开发复杂度，快速得到结果。

使用限制

在Hologres中使用明细圈人函数，具体限制如下：

该函数仅Hologres V0.10及以上版本支持，请在Hologres管控台查看当前实例版本，如果您的实例是V0.10以下版本，请您使用自助升级或加入Hologres钉钉交流群反馈，详情请参见如何获取更多的在线支持？。
明细圈人函数在使用之前，需要执行以下语句开启extension。extension是DB级别的函数，一个DB只需执行一次即可，新建DB需要重新执行。
```
--创建extension
CREATE EXTENSION flow_analysis;
```
说明
如需卸载extension请执行如下命令。
```
DROP EXTENSION flow_analysis;
```

bit_construct

Hologres在V0.10及以上版本提供了bit_construct函数用于明细表圈人场景。

函数说明
bit_construct通过设置筛选条件，返回一个最多32位integer类型的bitmap。

语法示例

bit_construct(
  a ,
  b ,
  ....,
  a6
)

参数说明
- 参数a和b等，代表筛选条件，为bool类型，最多支持32个条件，取值范围为a~z,a1~a6。
- 函数返回值类型为int。

bit_match

Hologres在V0.10及以上版本提供了bit_match函数用于明细表圈人场景。

函数说明
bit_match用于对bit_construct筛选的结果进行进一步计算
语法示例
```
bit_match('expression', bitmask)
```
参数说明
参数
说明
示例
expression
bit_construct函数中的条件表达式，支持&（and）、 | （or）、 !（not）和^（XOR ）。
a&b
bitmask
bit_construct构造的bitmap名。
无

使用示例

使用明细圈人函数，完整语法示例如下：

开启extension。
Superuser在DB中执行如下语句开启extension：
```
CREATE EXTENSION flow_analysis;
```

准备表和数据。

如下为一张记录用户购物行为的明细表，该表记录了用户的uid以及购物路径的行为，同时对该表插入部分数据。

create table ods_app_dwd(
event_time timestamptz,
uid bigint,
action text,
page text,
product_code text,
from_days int
);

insert into ods_app_dwd values('2021-04-03 10:01:30', 274649163, 'click', '购物车', 'MDS', 1);
insert into ods_app_dwd values('2021-04-03 10:04:30', 274649163, 'view', '收藏页', 'MDS', 4);
insert into ods_app_dwd values('2021-04-03 10:06:30', 274649165, 'click', '购物车', 'MMS', 8);
insert into ods_app_dwd values('2021-04-03 10:09:30', 274649165, 'view', '购物车', 'MDS', 10);

数据查询。

查询出满足“某个时间范围内将商品既加入购物车又添加至收藏页”条件的精准用户。

您可以使用如下两种方式进行数据查询：

使用WHERE语句查询。
说明
在查询语句中WHERE条件筛选后的数据越少，查询性能越好。
```
WITH tbl as (
SELECT uid, bit_or(bit_construct(
  a := (action='click' and page='购物车'),
  b := (action='view' and page='收藏页'))) as uid_mask
  FROM ods_app_dwd
WHERE event_time > '2021-04-03 10:00:00' AND event_time < '2021-04-04 10:00:00'
GROUP BY uid )
SELECT uid from tbl where bit_match('a&b', uid_mask);
```
其中关于SQL语句的释义如下：
- bit_construct：筛选出加入购物车的用户为条件a，筛选出添加至收藏夹的用户为条件b。
- bit_or：将筛选出的条件a和b取或，在该SQL语句示例中，同一个用户只要有一行符合条件，即认为符合条件。
- bit_match：a&b取满足条件a和b的交集。

使用HAVING子句调用查询。

SELECT uid FROM (
    SELECT uid, bit_or(bit_construct(
      a := (action='click' AND page='购物车'),
      b := (action='view' AND page='收藏页'))) as uid_mask
    FROM ods_app_dwd
    WHERE event_time > '2021-04-03 10:00:00' AND event_time < '2021-04-04 10:00:00'
    GROUP BY uid 
    HAVING bit_match('a&b', bit_or(bit_construct(
      a := (action='click' and page='购物车'),
      b := (action='view' and page='收藏页'))))
) t

参数	说明	示例
expression	bit_construct函数中的条件表达式，支持`&`（and）、 `\|` （or）、 `!`（not）和`^`（XOR ）。	a&b
bitmask	bit_construct构造的bitmap名。	无