億級資料量近即時統計UV - Hologres

Hologres針對巨量資料量（億級）、高QPS的UV計算情境，提供近即時預彙總UV計算方案，支援您通過RoaringBitmap，結合周期性調度的方式對資料進行預彙總，實現任意長周期的UV靈活計算。

方案介紹

對於巨量資料量且對QPS、延遲有一定需求的業務，可以通過Hologres RoaringBitmap，結合周期性調度的方式對資料進行預彙總，實現任意長周期的UV靈活計算。該方案的優缺點及適用情境如下：

優缺點
- 優點：計算效能好，可以實現高QPS低延遲的UV計算（基數精確去重計算），且可以支援任意周期範圍，滿足業務多種靈活查詢需求。
- 缺點：需要做一次預計算，並周期性更新彙總表資料，會增加維護任務。
適用情境：巨量資料量（億級）的任意長周期高QPS的UV計算。

根據業務的實際情境以及資料類型等，RoaringBitmap方案有以下三種實現方法，您可根據業務情況選擇合適的方法。

方法1：INT欄位類型的長周期UV計算：適用於對INT類型欄位進行UV精確去重計算情境，適用於對單標籤過濾篩選後的基數（即不同值的數量）進行計算。
方法2：TEXT欄位類型的長周期UV計算：適用於對TEXT類型欄位進行UV精確去重計算情境（需要結合Mapping表使用），適用於對單標籤過濾篩選後的基數進行計算。
方法3（進階方法）：分Bucket的長周期UV計算：多用於對畫像類情境中，多個標籤或屬性欄位進行交並差計算，以確定人群基數，通過Bucket分桶平行處理查詢，實現人群的高度壓縮，減少對資料的IO操作，提升計算效率。

說明

本文中的UV僅是RoaringBitmap（RB）情境的一種，RB方案也適用於其他基數精確去重的情境，例如直播電商大屏情境，計算商品數、品牌數等。

更多RoaringBitmap函數資訊請參見RoaringBitmap函數。

方法1：INT欄位類型的RB長周期UV計算

適用情境

適用於巨量資料量（億級）的任意長周期高QPS的UV計算，且計算UV的欄位類型（UID，即使用者ID）是INT類型的情境。

方案流程

該方案的主要流程如下：

步驟一：建立一張使用者明細表，用於存放業務所有維度詳細資料。

步驟二：根據商務邏輯，將明細表按照基礎維度進行GROUP BY彙總，再將彙總出的UID結果轉換為RoaringBitmap形式，存放在彙總結果表中。

步驟三：按照查詢維度查詢彙總結果表，對關鍵的RoaringBitmap欄位進行OR運算，去重並統計基數，即可得出對應使用者數UV，統計彙總結果表中的記錄數即可得出PV，達到亞秒級查詢。

步驟一：準備基礎資料

建立RoaringBitmap Extension。
使用RoaringBitmap前需要建立RoaringBitmap Extension，Extension是DB層級的函數，一個DB只需執行一次即可。文法如下：
說明
RoaringBitmap Extension只能載入在public Schema下。
```
CREATE EXTENSION IF NOT EXISTS roaringbitmap;
```

準備使用者明細表。

準備一張使用者明細表，記錄使用者完整的詳細資料。通常來說，明細表會儲存所有詳細資料，資料量大，建議使用分區表，按天分區，按天更新，使用更方便。

BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
     uid int,
     country text,
     prov text,
     city text,
     channel text,
     operator text,
     brand text,
     ip text,
     click_time text,
     year text,
     month text,
     day text,
     ymd text NOT NULL
)
PARTITION BY LIST (ymd) ;--通常來說明細表資料量大，按天分區更方便。
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
--distribution_key根據需求設定，根據該表的即時查詢需求，從什麼維度做分區能夠取得較好效果即可
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
--用於做where過濾條件，包含完整年月日時間欄位推薦設為clustering_key和event_time_column
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

準備RB彙總結果表。
建立RB彙總結果表，用於存放RoaringBitmap彙總後的結果資料。
- 彙總後的資料量通常不多（彙總後單天資料量僅幾百萬條），建議彙總結果表使用非分區表，資料回刷時更方便。或者使用按月/季度分區（若按天分區，資料量小，分區多可能會產生較多小檔案，影響記憶體水位）。
- 以country、prov、city為維度構建基礎維表，並設定為Distribution Key，查詢時可以根據維度查詢。如果存在多個GROUP BY欄位（超過3個），建議將使用最頻繁的欄位設定為Distribution Key。
- 將查詢維度欄位和日期分區鍵作為主鍵，防止重複插入資料。
- 將日期過濾欄位作為Clustering Key和Event Time Column，加快過濾查詢。
代碼如下：
```
BEGIN;
CREATE TABLE dws_app_rb(
  rb_uid roaringbitmap, -- UV計算
  country text,
  prov text,
  city text,
  ymd text NOT NULL,  --日期欄位
  pv integer, -- PV計算
  PRIMARY key(country, prov, city, ymd)--查詢維度和時間作為主鍵，防止重複插入資料
);
CALL set_table_property('dws_app_rb', 'orientation', 'column');
--clustering_key和event_time_column設為日期欄位，便於過濾
CALL set_table_property('dws_app_rb', 'clustering_key', 'ymd');
CALL set_table_property('dws_app_rb', 'event_time_column', 'ymd');
--distribution_key設為group by欄位
CALL set_table_property('dws_app_rb', 'distribution_key', 'country,prov,city');
END;
```

步驟二：構建RB彙總結果表

準備好明細表之後，即可進行RB構建，並將結果資料寫入RB彙總表。樣本如下：

--樣本：查詢半年的資料，構建rb並寫入彙總結果表
INSERT INTO dws_app_rb
SELECT  
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1)
FROM    ods_app_detail
WHERE ymd >= '20231201' AND ymd <='20240502'
GROUP BY country,prov,city,ymd;

當明細表更新後，根據明細表的更新粒度，彙總表的更新可以分為累加式更新或者全量更新。二者的區別如下：

彙總表更新方式

說明

程式碼範例

累加式更新彙總表

明細表的資料有規律地更新或新增，例如只更新昨天的分區，即可通過INSERT的方式將增量資料寫入彙總表。

只需寫入新增資料：

INSERT INTO dws_app_rb
SELECT  
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1)
FROM    ods_app_detail
WHERE ymd = '20240503'
GROUP BY country,prov,city,ymd;

全量更新彙總表

明細表的資料無規律地更新，無法快速計算出增量資料，導致無法用新增資料更新彙總表，因此使用全量回刷方式寫入彙總表。

使用INSERT OVERWRITE的方式將資料全量回刷至彙總表：

-- 使用insert overwrite回刷全量的彙總表資料
CALL hg_insert_overwrite('public.dws_app_rb' , $$SELECT
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1) FROM ods_app_detail 
 WHERE ymd >= '20231201' AND ymd <='20240503'
 GROUP BY country,prov,city,ymd$$);

步驟三：任意長周期UV查詢

查詢時，從彙總結果表中按照查詢維度進行彙總計算，可以通過彙總表查詢基礎維度任意組合任意時間段的UV、PV，查詢效率相比傳統的SQL更高，通常為毫秒級的查詢響應，也能支援較高的QPS查詢。樣本如下：

查詢某天的UV、PV。

SELECT  
RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  SUM(pv) AS pv
FROM    dws_app_rb
WHERE   ymd = '20240329'
GROUP BY country,prov,city;

查詢某個月的UV、PV。

SELECT  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  SUM(pv) AS pv
FROM dws_app_rb
WHERE   ymd >= '20240301' AND ymd <= '20240331'
GROUP BY country,prov,city;

方法2：TEXT欄位類型結合Mapping表的RB長周期UV計算

在實際業務情境中，大多數表的ID欄位會使用TEXT類型，但是RB不支援TEXT類型，因此需要使用Serial類型構建一張Mapping表，來實現基於RB的UV高效計算。

適用情境

適用於巨量資料量（億級）的任意長周期高QPS的UV計算，且計算UV的欄位是TEXT類型（需要精確去重的欄位是TEXT類型）的情境。

方案流程

該方案的主要流程如下：

步驟一：建立一張使用者明細表，用於存放業務所有維度詳細資料。建立一張歷史使用者映射Mapping表，用於存放歷史中每個訪問過的使用者ID（UID）和對應的INT32數值。

步驟二：明細表和使用者映射Mapping表進行JOIN，並按照最細粒度基礎維度進行GROUP BY，將前一天的所有資料根據最大的查詢維度彙總出的UID結果轉換為RoaringBitmap形式，寫入彙總結果表（每天百萬條）。

步驟一：準備基礎資料

建立RoaringBitmap Extension。
使用RoaringBitmap前需要建立RoaringBitmap Extension，Extension是DB層級的函數，一個DB只需執行一次即可。文法如下：
說明
RoaringBitmap Extension只能載入在public Schema下。
```
CREATE EXTENSION IF NOT EXISTS roaringbitmap;
```

準備使用者明細表。

BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
     uid text,
     country text,
     prov text,
     city text,
     channel text,
     operator text,
     brand text,
     ip text,
     click_time text,
     year text,
     month text,
     day text,
     ymd text NOT NULL
)
PARTITION BY LIST (ymd) ;--通常來說明細表資料量大，按天分區更方便。
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
--distribution_key根據需求設定，根據該表的即時查詢需求，從什麼維度做分區能夠取得較好效果即可
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
--用於做where過濾條件，包含完整年月日時間欄位推薦設為clustering_key和event_time_column
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

準備RB彙總結果表。
建立RB彙總結果表，用於存放RoaringBitmap彙總後的結果資料。
- 彙總後的資料量通常不多（彙總後單天資料量僅幾百萬條），建議彙總結果表使用非分區表，資料回刷時更方便。或者使用按月/季度分區（若按天分區，資料量小，分區多可能會產生較多小檔案，影響記憶體水位）。
- 以country、prov、city為維度構建基礎維表，並設定為Distribution Key，查詢時可以根據維度查詢。如果存在多個GROUP BY欄位（超過3個），建議將使用最頻繁的欄位設定為Distribution Key。
- 將查詢維度欄位和日期分區鍵作為主鍵，防止重複插入資料。
- 將日期過濾欄位作為Clustering Key和Event Time Column，加快過濾查詢。
代碼如下：
```
BEGIN;
CREATE TABLE dws_app_rb(
  rb_uid roaringbitmap, -- UV計算
  country text,
  prov text,
  city text,
  ymd text NOT NULL,  --日期欄位
  pv integer, -- PV計算
  PRIMARY key(country, prov, city, ymd)--查詢維度和時間作為主鍵，防止重複插入資料
);
CALL set_table_property('dws_app_rb', 'orientation', 'column');
--clustering_key和event_time_column設為日期欄位，便於過濾
CALL set_table_property('dws_app_rb', 'clustering_key', 'ymd');
CALL set_table_property('dws_app_rb', 'event_time_column', 'ymd');
--distribution_key設為group by欄位
CALL set_table_property('dws_app_rb', 'distribution_key', 'country,prov,city');
END;
```

準備使用者Mapping表。

RoaringBitmap計算的使用者ID欄位類型必須為32位INT類型，且越稠密越好，而常見的業務系統或者埋點中的使用者ID很多是字串類型，因此使用Serial類型（自增的32位INT）構建一張使用者映射表，實現使用者映射的自動管理和穩定映射。

BEGIN;
 CREATE TABLE uid_mapping (
     uid text NOT NULL,
     uid_int32 serial,
    PRIMARY KEY (uid)
 );
 --將uid設為clustering_key和distribution_key便於快速尋找其對應的int32值
CALL set_table_property('uid_mapping', 'clustering_key', 'uid');
CALL set_table_property('uid_mapping', 'distribution_key', 'uid');
CALL set_table_property('uid_mapping', 'orientation', 'row');
COMMIT;

步驟二：構建RB匯入至彙總表

匯入與更新使用者映射表。
1. 使用者映射表全量初始化。
  首次將全量UID資料匯入映射表進行資料初始化。您也可以根據業務的查詢時間範圍，選擇時間按欄位過濾，匯入部分周期的UID資料至Mapping表。此處以匯入半年的UID資料為例。
```
--根據業務的查詢時間範圍，可以選擇時間欄位過濾，匯入部分周期的uid資料至mapping表，樣本匯入半年的資料。
INSERT INTO uid_mapping (uid) B
SELECT distinct (uid) FROM ods_app_detail 
WHERE B.ymd >= '20231201' AND B.ymd <='20240502';
```
2. 驗證Mapping表的資料正確性。
  Mapping表資料匯入後，需要驗證其資料是否與明細表的資料一致。
```
--驗證資料正確性
SELECT COUNT(*) FROM uid_mapping;
```
  同時也需要驗證Serial的連續性。在某些情境中，會使用TRUNCATE+INSERT的方式多次全量初始化Mapping表，而執行TRUNCATE時，Serial類型的Sequence不會被重設，導致Serial值浪費，在多次匯入之後會超過Serial的INT32位上限。
```
--驗證serial的連續性是否正確
SELECT MAX(uid_int32),MIN(uid_int32) FROM uid_mapping;
```
3. 更新使用者映射表。
  隨著明細表的UID資料更新，也需要及時更新使用者映射表的資料。若明細表中僅涉及某天的UID資料更新，可使用INSERT ON CONFLICT更新使用者映射表，如果無法確定UID的修改範圍，可以進行全量資料回刷，但資料量較大可能導致資料回刷時間變長。
  樣本：使用INSERT ON CONFLICT DO NOTHING更新前一天的使用者映射表，DO NOTHING會保證只更新資料，不會重複寫入。
```
--更新前一天的使用者映射表資料
INSERT INTO uid_mapping (uid)
SELECT distinct (uid) FROM ods_app_detail 
WHERE ymd = '20240503' 
ON conflict do nothing;
```
匯入與更新彙總結果表。
更新完使用者映射表後，將資料做彙總運算後插入彙總結果表，主要步驟如下：
1. 通過明細表JOIN使用者映射表，得到彙總條件和對應的uid_int32。
2. 按照彙總條件做彙總運算後，將資料插入RoaringBitmap彙總結果表，可以根據業務情況選擇彙總周期，如天、月等。
3. 只需進行一次彙總，存放一份資料，資料條數等於UV數量。
4. 插入資料至彙總結果表，命令如下。此處以彙總半年的資料至結果表為例。
```
WITH aggregation_src AS (
    SELECT
       B.uid_int32,
        A.country,
        A.prov,
        A.city,
        A.ymd
    FROM
        ods_app_detail A
        INNER JOIN uid_mapping B ON A.uid = B.uid
    WHERE
        A.ymd >= '20231201' AND A.ymd <='20240502')
INSERT INTO dws_app_rb
SELECT
    RB_BUILD_AGG (uid_int32),
    country,
    prov,
    city,
    ymd,
    COUNT(1)
FROM
    aggregation_src
GROUP BY
    country,
    prov,
    city,
    ymd;
```
5. 更新彙總結果表。
  明細表和Mapping表更新後，也需要更新彙總結果表。可以根據實際業務中明細表的更新情況選擇適當的方式更新彙總結果表。
  - 累加式更新：明細表周期性寫入和更新，如按天寫入，按天回刷，可以使用INSERT的方式直接寫入最新分區資料。
  - 全量回刷：明細表無固定時間範圍的寫入與更新，即無法識別明細表的增量資料，則無法使用新增資料更新彙總結果表，建議使用INSERT OVERWRITE的方式全量回刷彙總結果表。

步驟三：任意長周期UV查詢

查詢時，從彙總結果表中按照查詢維度做彙總計算，可以通過彙總表查詢基礎維度任意組合和任意時間段的UV、PV，查詢效率相比傳統的SQL更高，通常為毫秒級的查詢響應，並且能支援較高的QPS查詢。樣本如下：

查詢某天的UV、PV。

SELECT  country,
        prov,
        city,
        RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
        SUM(pv) AS pv
FROM    dws_app_rb
WHERE   ymd = '20240329'
GROUP BY country,prov,city;

查詢某一個月的UV、PV。

--查一個月的pv、uv
SELECT  country
        ,prov
        ,RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv
        ,SUM(pv) AS pv
FROM    public.dws_app_rb
WHERE   ymd >= '20240301' AND ymd <= '20240331'
GROUP BY country,prov,city;

方法3（進階方法）：分Bucket的RB長周期UV計算

在實際情境中，會存在多個大表關聯計算基數的需求，例如標籤表和屬性工作表關聯計算人群交並差的基數、行為表和屬性工作表關聯計算行為基數等的Portrait analysis情境，通過Hologres RoaringBitmap結合Bucket分桶的方案來實現高效的基數計算。通過分桶，可以將Bitmap拆分成多段打散儲存，充分利用並發計算的能力，實現人群的高度壓縮，減少對資料的IO操作，提升計算效率。

適用情境

適用於大規模資料量（億級以上）的任意長周期UV查詢，通常為精確去重的欄位位元長（可能會超過INT32位）或欄位基數低（資料的重複度高，例如性別）的情境，更多是多標籤關聯計算人群基數的情境。

原理介紹

Bucket是將欄位拆分成不同的桶，讓資料可以打散分布到各個Shard上。如果要實現較好的查詢效能，分桶方式及分桶數量是關鍵因素。結合大多數的實際業務使用方式：

分桶方式：常見的比較高效的分桶方式是通過位元運算計算出欄位的高位元和低位元，然後將低位存入Bitmap，高位設定為Bucket。
分桶數量：通常執行個體中的單個Table Group的Shard數不會超過256，對於INT32位的資料來說，int_value>>24（即高8位）存為桶號，低24位存成Bitmap，即可將資料平分到多個Shard上，並且每個Shard上的資料都會聚集好，利於並發。
推薦的分桶計算公式：
- 方式1：Table Group有n個Shard，則桶的資料量即為n，桶所需要的位元就是log2(n)向上取整。推薦Shard數小於或等於256時，都用256個桶。Shard數大於256時，可以按照公式適當計算。
- 方式2：如果UID都是比較小的數字，方式1的計算公式可能會導致高位都是0，資料都會在一個Bucket中。此時建議使用(i>>16)%256作為Bucket，桶數量不變，同時會使得65536個值在一個桶裡，不影響儲存效率。
- 分桶的策略實踐也可以參考淘寶業務實踐。

方案流程

分Bucket的RB周期UV計算的方案流程如下：

說明

為了降低難度，本樣本中的UID欄位使用INT類型，如果UID欄位是TEXT類型，可參考方法2建立Mapping表來構建RB，分桶的方式不變。

步驟一：準備基礎資料

建立RoaringBitmap Extension。
使用RoaringBitmap前需要建立RoaringBitmap Extension，Extension是DB層級的函數，一個DB只需執行一次即可。文法如下：
說明
RoaringBitmap Extension只能載入在public Schema下。
```
CREATE EXTENSION IF NOT EXISTS roaringbitmap;
```

準備使用者的行為表和屬性工作表。

鑒於Bucket的方案通常用在多表關聯計算畫像的情境，因此此處準備兩張表，一張為使用者行為表，記錄完整的明細行為，另一張為屬性工作表，記錄使用者的屬性，例如性別、年齡等。通過行為和屬性的關聯分析，可以計算任意維度人群基數。

使用者行為表ods_user_behaviour_detail。

--行為詳細資料
BEGIN;
CREATE TABLE IF NOT EXISTS ods_user_behaviour_detail (
     uid int,
     operator text,
     channel text,
     shop_id text,
     time text,
     ymd text NOT NULL
);
CALL set_table_property('ods_user_behaviour_detail', 'orientation', 'column');
--distribution_key根據需求設定，根據該表的即時查詢需求，從什麼維度做分區能夠取得較好效果即可
CALL set_table_property('ods_user_behaviour_detail', 'distribution_key', 'uid');
--用於做where過濾條件，包含完整年月日時間欄位推薦設為clustering_key和event_time_column
CALL set_table_property('ods_user_behaviour_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_user_behaviour_detail', 'event_time_column', 'ymd');
COMMIT;

使用者屬性工作表dim_userbase。

--使用者屬性資料
BEGIN;
CREATE TABLE IF NOT EXISTS dim_userbase (
     uid int, 
     age text,
     gender text,
     country text,
     prov text,
     city text
     );
CALL set_table_property('dim_userbase', 'orientation', 'column');
CALL set_table_property('dim_userbase', 'distribution_key', 'uid');
COMMIT;

準備RB彙總結果表。

建立RB彙總結果表，用於存放RoaringBitmap彙總後的結果資料。

彙總後的資料量通常不多（彙總後單天資料量僅幾百萬條），建議彙總結果表使用非分區表，資料回刷時更方便。或者使用按月/季度分區（若按天分區，資料量小，分區多可能會產生較多小檔案，影響記憶體水位）。
以country、prov、city為維度構建基礎維表，並設定為Distribution Key，查詢時可以根據維度查詢。如果存在多個GROUP BY欄位（超過3個），建議將使用最頻繁的欄位設定為Distribution Key。
將查詢維度欄位和日期分區鍵作為主鍵，防止重複插入資料。
將日期過濾欄位作為Clustering Key和Event Time Column，加快過濾查詢。

行為表彙總結果表dws_user_behaviour_rb。

--行為表彙總結果表
BEGIN;
CREATE TABLE dws_user_behaviour_rb(
  rb_uid roaringbitmap, -- UV計算
  bucket int NOT NULL, -- 分桶欄位
  operator text,
  channel text,
  shop_id text,
  time text,
  ymd text NOT NULL,
  PRIMARY key(operator,channel,shop_id,time, ymd,bucket)--查詢維度和時間作為主鍵，防止重複插入資料
);
CALL set_table_property('dws_user_behaviour_rb', 'orientation', 'column');
--clustering key和event_time_column設為日期欄位，便於過濾
CALL set_table_property('dws_user_behaviour_rb', 'clustering_key', 'ymd');
CALL set_table_property('dws_user_behaviour_rb', 'event_time_column', 'ymd');
--將分桶欄位bucket設定成distribution key
CALL set_table_property('dws_user_behaviour_rb', 'distribution_key', 'bucket');
END;

使用者屬性彙總結果表dim_userbase_rb。

--使用者屬性工作表的彙總結果表
BEGIN;
CREATE TABLE IF NOT EXISTS dim_userbase_rb (
     rb_uid roaringbitmap, -- UV計算
     bucket int NOT NULL, -- 分桶欄位
     age text,
     gender text,
     country text,
     prov text,
     city text,
     PRIMARY key(age,gender,country, prov,city,bucket)--查詢維度作為主鍵，防止重複插入資料
     );
CALL set_table_property('dim_userbase_rb', 'orientation', 'column');
CALL set_table_property('dim_userbase_rb', 'distribution_key', 'bucket');--bucket設定成distribution key，可以利用local join的能力
COMMIT;

步驟二：構建RB匯入至彙總表

準備好明細表後，需要將明細結果表資料寫入彙總結果表，同時也需要將UID進行分桶存入Bucket欄位中。分桶的劃分需要根據業務的資料規模、資料分布特徵綜合考慮，本樣本中將UID劃分為256個桶，這樣可以將UID充分打散到每個Shard，系統也會保證同一個桶的UID資料分布在相同的Shard，查詢時並行計算，實現高效能查詢。最終的匯入命令如下：

行為表資料寫入彙總表：

--明細表資料寫入彙總表
INSERT INTO dws_user_behaviour_rb
SELECT  
RB_BUILD_AGG(uid),
uid >> 24 AS bucket,--右移24位，這樣高8位作為桶，低24為作為bitmap
operator,
channel,
shop_id,
time
ymd
FROM ods_user_behaviour_detail
WHERE 
 ymd >= '20231201' AND ymd <='20240503'

屬性工作表資料寫入彙總結果表：

--屬性工作表資料寫入彙總結果表
INSERT INTO dim_userbase_rb
SELECT 
RB_BUILD_AGG(uid),
uid >> 24 AS bucket,--右移24位，這樣高8位作為桶，低24為作為bitmap
age,
gender,
country,
prov,
city
FROM dim_userbase
GROUP BY age,gender,country,prov,city,bucket;

當明細表更新後，根據明細表的更新粒度，彙總表的更新可以分為累加式更新或者全量更新。二者區別如下：

累加式更新彙總表：明細表資料有規律地更新或新增，例如只更新昨天的分區，可以通過INSERT的方式將增量資料寫入彙總表。
全量更新彙總表：明細表的資料無規律地更新，無法快速計算出增量資料，導致無法用新增資料更新彙總表，因此使用全量回刷方式寫入彙總表。

步驟三：任意長周期UV查詢

查詢時，可以通過彙總表查詢基礎維度任意組合任意時間段的UV、PV，查詢效率相比傳統SQL更高。樣本如下：

--查詢:gender = 'man'&country = '北京'&operator = '購買'&shop_id ='1'的客群數
SELECT
    SUM(RB_CARDINALITY (rb_and (t1.rb_uid, t2.rb_uid)))
FROM (
    SELECT
        rb_or_agg (rb_uid) AS rb_uid,
        bucket
    FROM
        dws_user_behaviour_rb
    WHERE
        OPERATOR = '購買'
        AND shop_id = '1'
        AND ymd = '20240501'
    GROUP BY
        bucket) t1
    JOIN (
        SELECT
            rb_or_agg (rb_uid) AS rb_uid,
            bucket
        FROM
            dim_userbase_rb
        WHERE
            gender = 'man'
            AND country = '北京'
        GROUP BY
            bucket) t2 ON t1.bucket = t2.bucket;