1007
技術社區[雲棲]
PostgreSQL\GPDB 多維數據透視典型案例分享
標簽
PostgreSQL , 數據透視 , 實時 , 物化 , 預計算 , 多維分析 , 流計算 , 增量合並 , 調度 , HLL
背景
典型的電商類數據透視業務，透視的語料可能會包含一些用戶的標簽數據：例如包含品牌的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?字段索引。 從而實現快速的檢索。
得到這份結果後，分析師的查詢簡化如下（前三個條件通過分區過濾數據，最後根據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混合需求》
參考
https://github.com/aggregateknowledge/postgresql-hll
最後更新：2017-06-30 11:02:53