PostgreSQLHybridDBforPG毫秒級多維資料透視案例分享

德哥發表於2017-06-28

SQL

背景

典型的電商類資料透視業務，透視的語料可能會包含一些使用者的標籤資料：例如包含品牌的ID，銷售區域的ID，品牌對應使用者的ID，以及若干使用者標籤欄位，時間欄位等。

標籤可能會按不同的維度進行歸類，例如tag1 性別，tag2 年齡段, tag3 興趣愛好, …。

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

例子

資料結構舉例

每天所在區域、銷售渠道的活躍使用者ID

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

每個品牌的自有使用者，維護增量

t2 (    
  uid,    -- 使用者ID    
  pinpai  -- 品牌    
)

使用者標籤，維護增量

t3 (    
  uid,    -- 使用者ID    
  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.pinpai = ?     
  and t1.groupid = ?     
  AND t1.day = `2017-06-25`     
group by t3.tag1

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

預計算優化

預計算需要得到的結果如下：

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

對於GPDB，可以使用列儲存，表分割槽則按day範圍一級分割槽，按pinpai, groupid雜湊進行二級分割槽，資料分佈策略選擇隨機分佈，最後針對每個tag?欄位建立單獨索引。從而實現快速的檢索（甭管資料量多大，單次透視請求的速度應該可以控制在100毫秒以內）。

得到這份結果後，分析師的查詢簡化如下（前三個條件通過分割槽過濾資料，最後根據tag?的索引快速得到結果）：

select     
  day, pinpai, groupid, `tag?` as tag, cnt, uids, hll_uids     
from t_result    
where    
  day =     
  and pinpai =     
  and groupid =     
  and tag? = ?

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

預計算的方法

產生統計結果的SQL如下

select     
  t1.day,    
  t2.pinpai,    
  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.pinpai,    
  t1.groupid,    
  grouping sets (    
    (t3.tag1),     
    (t3.tag2),    
    (t3.tag3),    
    (...),    
    (t3.tagn)    
  )

解釋：

1、將uid聚合為陣列

array_agg(uid)

2、將UID轉換為hll hash val，並聚合為HLL型別

hll_add_agg(hll_hash_integer(uid))

3、為了按每個標籤維度進行統計，可以使用多維分析語法grouping sets，不必寫多條SQL來實現，資料也只會掃一遍，將按每個標籤維度進行統計

  grouping sets (    
    (t3.tag1),     
    (t3.tag2),    
    (t3.tag3),    
    (...),    
    (t3.tagn)    
  )

多維分析的語法詳見

《PostgreSQL 9.5 new feature – Support GROUPING SETS, CUBE and ROLLUP.》

《Greenplum 最佳實踐 – 多維分析的使用(CUBE, ROLLUP, GROUPING SETS in GreenPlum and Oracle)》

預計算結果透視查詢

如果進行復雜透視，可以將分析結果的不同記錄進行陣列的邏輯運算，得到最終UID集合結果。

一、陣列邏輯運算

1、在陣列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;

2、陣列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;

3、陣列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本身支援資料的邏輯計算

1、計算唯一值個數

hll_cardinality(users)

2、計算兩個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.pinpai,    
  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.pinpai,    
  t1.groupid,    
  grouping sets (    
    (t3.tag1),     
    (t3.tag2),    
    (t3.tag3),    
    (...),    
    (t3.tagn)    
  )

二、合併統計維度資料

每天的統計結果只有按天統計的結果，如果要查詢按月，或者按年的統計，需要對天的資料查詢並匯聚。

當然，業務也能選擇非同步匯聚，終端使用者查詢匯聚後的結果。

t_result_month (    
  month,    -- yyyy-mm    
  pinpai,   -- 品牌ID    
  groupid,  -- 渠道、地區、門店ID    
  tag1,     -- 標籤型別1    
  tag2,     -- 標籤型別2    
  tag3,     -- 標籤型別3    
  ...       -- 標籤型別n    
  cnt,      -- 使用者數    
  uids,    -- 使用者ID陣列，這個為可選欄位，如果不需要知道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`),    
  pinpai,    
  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`),    
  pinpai,    
  groupid,    
  tag1,    
  tag2,    
  tag3,    
  ...

按年匯聚以此類推。

三、流式排程

如果業務方有實時統計的需求，那麼可以使用流式計算的方法，實時進行以上聚合統計。方法詳見

《流計算風雲再起 – PostgreSQL攜PipelineDB力挺IoT》

《基於PostgreSQL的流式PipelineDB, 1000萬/s實時統計不是夢》

《”物聯網”流式處理應用 – 用PostgreSQL實時處理(萬億每天)》

如果資料量非常龐大，可以根據分割槽鍵，對資料進行分流，不同的資料落到不同的流計算節點，最後彙總流計算的結果到HybridDB(base on GPDB)中。

《ApsaraDB的左右互搏(PgSQL+HybridDB+OSS) – 解決OLTP+OLAP混合需求》