明細圈人函數 - 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函數，只需要通過一輪過濾篩選和Function Compute即可得到結果人群資料，降低明細圈人情境開發複雜度，快速得到結果。

使用限制

在Hologres中使用明細圈人函數，具體限制如下：

該函數僅Hologres V0.10及以上版本支援，請在Hologres管控台查看當前執行個體版本，如果您的執行個體是V0.10以下版本，請您使用自助升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。
明細圈人函數在使用之前，需要執行以下語句開啟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名。	無