通過 HyperLoglog 實現高效能多維資料透視 - AnalyticDB for PostgreSQL

本文通過電商類資料透視樣本，介紹了使用AnalyticDB PostgreSQL通過HLL預計算，實現毫秒級多維資料透視的方法。關於HyperLogLog的用法，請參考使用HLL。

實踐總結

本文介紹的操作方法，涉及以下最佳實務。如您已瞭解操作方法，可以直接參考實踐總結來應用。

對於透視分析需求，使用倒轉的方法，將資料按查詢需求進行預計算，得到統計結果；從而在透視時僅需查詢計算結果，可以做到任意維度透視，都可以在100毫秒以內響應。
使用GROUPING SETS，對多個標籤維度進行一次性統計，降低資料重複掃描和重複運算，大幅提升處理效率。
使用數組，記錄每個透視維度UID，不僅支援透視，也可以滿足圈人的需求，同時支援未來更加複雜的透視需求。
使用HLL類型來儲存估算值，在進行複雜透視時，可以使用HLL。例如，多個HLL的值可以UNION，可以求唯一值個數，通常用於評估UV，新增UV等。
使用Realtime Compute。如果資料需要即時的統計，那麼可以使用pipelineDB進行流式分析，Realtime Compute統計結果。
與阿里雲雲端組件結合，使用OSSObject Storage Service過渡資料（未經處理資料）。使用OSS_FDW外部表格對接OSS，因此過渡資料可以不入庫，僅僅用於預計算。大幅降低資料庫的寫入需求、空間需求。
使用Greenplum的一級、二級分區，將透視資料的訪問需求打散到更小的單位，然後使用標籤索引，再次降低資料搜尋的範圍，從而做到任意資料量，任意維度透視請求100毫秒以內響應。
使用列儲存，提升壓縮比，節省統計資料的空間佔用。

背景

典型的電商類資料透視業務會使用一些使用者的標籤資料作為透視的語料。例如，包含品牌的ID，銷售領域的ID，品牌對應使用者的ID，以及若干使用者標籤欄位，時間欄位等。在作分析時，標籤可能會按不同的維度進行歸類。例如，tag1 性別，tag2 年齡段, tag3 興趣愛好等等。

業務方較多的需求往往是對自有品牌的使用者進行透視。例如，一個非常典型的資料透視需求就是統計不同的銷售領域（渠道）、時間段、標籤維度下的使用者數。

準備

作為樣本，定義以下資料結構。

t1：每天所在地區、銷售渠道的活躍使用者ID。

t1 (
uid,       -- 使用者識別碼
groupid,   -- 銷售渠道、地區 ID
day        -- 日期
)

t2：每個品牌的自有使用者（維護增量）。
```
t2 (
uid,    -- 使用者識別碼
brand   -- 品牌
)
```

t3：使用者標籤（維護增量）。

t3 (
uid,    -- 使用者識別碼
tag1,   -- 標籤1，如興趣
tag2,   -- 標籤2，如性別
tag3,   -- 標籤3，如年齡段
... ,
)

基於已定義的資料結構，可以按照品牌、銷售領域、標籤、日期進行透視。例如，

select
  '興趣' as tag,
  t3.tag1 as tag_value,
  count(1) as cnt
from
  t1,
  t2,
  t3
where
  t1.uid = t3.uid
  and t1.uid = t2.uid
  and t2.brand = ?
  and t1.groupid = ?
  AND t1.day = '2017-06-25'
group by t3.tag1

可以看出，這類查詢的運算量較大。而且，分析師可能需要對不同的維度進行比對分析。因此，建議採用預計算的方法進行最佳化。

使用預計算最佳化檢索

為實現快速檢索，您可以使用以下最佳化方法：

對於Greenplum，使用列儲存。
表分區按照day範圍一級分區，按brand, groupid雜湊進行二級分區。
資料分布策略選擇隨機分布。
針對每個tag?欄位建立單獨索引。

結合以上最佳化，不管資料量多大，單次透視請求的響應速度都可以控制在100毫秒以內。

通過預計算最佳化，希望得到以下結果：

t_result (
  day,      -- 日期
  brand,    -- 品牌 ID
  groupid,  -- 渠道、地區、門店 ID
  tag1,     -- 標籤類型1
  tag2,     -- 標籤類型2
  tag3,     -- 標籤類型3
  ...       -- 標籤類型n
  cnt,      -- 使用者數
  uids,     -- 使用者識別碼 數組，這個為可選欄位，如果不需要知道 ID 明細，則不需要儲存
  hll_uids  -- 使用者 HLL 估值
)

得到這份結果後，分析師的查詢過程可以簡化為以下內容：

select
  day, brand, groupid, 'tag?' as tag, cnt, uids, hll_uids
from t_result
where
  day =
  and brand =
  and groupid =
  and tag? = ?

其中，前三個條件（day, brand, groupid）通過分區過濾資料，最後根據tag?的索引快速得到結果。

可以看出，預計算後能夠以少量的運算，實現更加複雜的維度分析。例如，可以分析出某兩天的差異使用者，多個TAG疊加的使用者等。

使用預計算的方法

使用如下SQL來產生統計結果。

select
  t1.day,
  t2.brand,
  t1.groupid,
  t3.tag1,
  t3.tag2,
  t3.tag3,
  ...
  count(1) as cnt,
  array_agg(uid) as uids,
  ## 將 uid 彙總為數組。
  hll_add_agg(hll_hash_integer(uid)) as hll_uids
  ## 將 UID 轉換為 hll hash val，並彙總為 HLL 類型。
from
  t1,
  t2,
  t3
where
  t1.uid = t3.uid
  and t1.uid = t2.uid
group by
  t1.day,
  t2.brand,
  t1.groupid,
  grouping sets (
  ## 為了按每個標籤維度進行統計，使用多維分析文法 grouping sets，這樣可以不必通過多條 SQL 來實現。結果是資料只掃描一遍，且按每個標籤維度進行統計。
    (t3.tag1),
    (t3.tag2),
    (t3.tag3),
    (...),
    (t3.tagn)
  )

預計算結果透視查詢

如果進行複雜透視，可以對分析結果的不同記錄進行數組邏輯運算，得到UID集合結果。

使用數組邏輯運算

您可以使用以下數組邏輯運算。

統計在數組1但不在數組2的值。

create or replace function arr_miner(anyarray, anyarray) returns anyarray as $$
select array(select * from (select unnest($1) except select unnest($2)) t group by 1);
$$ language sql strict;

統計數組1和數組2的交集。

create or replace function arr_overlap(anyarray, anyarray) returns anyarray as $$
select array(select * from (select unnest($1) intersect select unnest($2)) t group by 1);
$$ language sql strict;

統計數組1和數組2的並集。

create or replace function arr_merge(anyarray, anyarray) returns anyarray as $$
select array(select unnest(array_cat($1,$2)) group by 1);
$$ language sql strict;

應用樣本

例如，假設促銷活動前（2017-06-24）的使用者集合為UID1[]，促銷活動後（2017-06-25）的使用者集合為UID2[]，可以使用以下命令得出促銷活動中有哪些新增使用者。

arr_miner(uid2[], uid1[])

使用HLL做資料邏輯計算

您可以使用HLL進行以下邏輯計算。

計算唯一值個數。
```
hll_cardinality(users)
```
計算兩個HLL的並集，得到一個HLL。
```
hll_union()
```

應用樣本

例如，假設在促銷活動前（2017-06-24）的使用者集合HLL為uid1_hll，促銷活動後（2017-06-25）的使用者集合HLL為uid2_hll，可以使用以下命令得出促銷活動中有哪些新增使用者。

hll_cardinality(uid2_hll) - hll_cardinality(uid1_hll)

預計算調度

在最佳化前，業務通過即時JOIN得到透視結果，而最佳化後使用事先統計的方法來獲得透視結果，而事先統計本身需要調度。調度方法取決於資料的來源以及資料合併的方法（流式增量或批量增量）。

按天統計資料

歷史統計資料無更新，只有增量。需要定時將統計結果寫入併合並至t_result結果表中。

insert into t_result
select
  t1.day,
  t2.brand,
  t1.groupid,
  t3.tag1,
  t3.tag2,
  t3.tag3,
  ...
  count(1) as cnt,
  array_agg(uid) as uids,
  hll_add_agg(hll_hash_integer(uid)) as hll_uids
from
  t1,
  t2,
  t3
where
  t1.uid = t3.uid
  and t1.uid = t2.uid
group by
  t1.day,
  t2.brand,
  t1.groupid,
  grouping sets (
    (t3.tag1),
    (t3.tag2),
    (t3.tag3),
    (...),
    (t3.tagn)
  )

合并統計維度資料

資料結果按天進行統計，但如果要查詢按月，或者按年的統計，則需要對按天統計的資料查詢並匯聚。業務也能選擇非同步匯聚，終端使用者查詢到的是匯聚後的結果。

t_result_month (
  month,    -- yyyy-mm
  brand,    -- 品牌 ID
  groupid,  -- 渠道、地區、門店 ID
  tag1,     -- 標籤類型1
  tag2,     -- 標籤類型2
  tag3,     -- 標籤類型3
  ...       -- 標籤類型n
  cnt,      -- 使用者數
  uids,     -- 使用者識別碼 數組，這個為可選欄位，如果不需要知道 ID 明細，則不需要儲存
  hll_uids  -- 使用者 HLL 估值
)

array彙總需要自訂以下彙總函式：

postgres=# create aggregate arragg (anyarray) ( sfunc=arr_merge, stype=anyarray);
CREATE AGGREGATE
postgres=# select arragg(c1) from (values (array[1,2,3]),(array[2,5,6])) t (c1);
   arragg
-------------
 {6,3,2,1,5}
(1 row)

例如，您可以使用以下SQL，來按月匯聚資料：

select
  to_char(day, 'yyyy-mm'),
  brand,
  groupid,
  tag1,
  tag2,
  tag3,
  ...
  array_length(arragg(uid),1) as cnt,
  arragg(uid) as uids,
  hll_union_agg() as hll_uids
from t_result
group by
  to_char(day, 'yyyy-mm'),
  brand,
  groupid,
  tag1,
  tag2,
  tag3,
  ...

以此類推，可以得出按年匯聚的結果。

流式調度

如果業務方有即時統計的需求，那麼可以使用流式計算的方法，即時進行以上彙總統計。如果資料量非常龐大，可以根據分區鍵，對資料進行分流，不同的資料落到不同的Realtime Compute節點，最後匯總Realtime Compute的結果到AnalyticDB PostgreSQL（base on GPDB）中。