PostgreSQL\HybridDB for PG 毫秒級多元資料透視 案例分享

postgresql , 資料透視 , 實時 , 物化 , 預計算 , 多元分析 , 流計算 , 增量合并 , 排程 , hll

典型的電商類資料透視業務，透視的語料可能會包含一些使用者的标簽資料：例如包含品牌的id，銷售區域的id，品牌對應使用者的id，以及若幹使用者标簽字段，時間字段等。

标簽可能會按不同的次元進行歸類，例如tag1 性别，tag2 年齡段, tag3 興趣愛好, ...。

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

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

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

使用者标簽，維護增量

對某品牌、某銷售區域，某标簽、某日進行透視。

例如

這類查詢的運算量較大，而且分析師可能對不同的次元進行比對分析，是以建議采用預計算的方法進行優化。

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

對于gpdb，可以使用列存儲，表分區則按day範圍一級分區，按pinpai, groupid哈希進行二級分區，資料分布政策選擇随機分布，最後針對每個tag?字段建立單獨索引。 進而實作快速的檢索（甭管資料量多大，單次透視請求的速度應該可以控制在100毫秒以内）。

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

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

産生統計結果的sql如下

解釋：

1、将uid聚合為數組

2、将uid轉換為hll hash val，并聚合為hll類型

3、為了按每個标簽次元進行統計，可以使用多元分析文法grouping sets，不必寫多條sql來實作，資料也隻會掃一遍，将按每個标簽次元進行統計

多元分析的文法詳見

<a href="https://github.com/digoal/blog/blob/master/201505/20150526_02.md">《postgresql 9.5 new feature - support grouping sets, cube and rollup.》</a>

<a href="https://github.com/digoal/blog/blob/master/201212/20121218_03.md">《greenplum 最佳實踐 - 多元分析的使用(cube, rollup, grouping sets in greenplum and oracle)》</a>

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

一、數組邏輯運算

1、在數組1但不在數組2的值

2、數組1和數組2的交集

3、數組1和數組2的并集

例如在促銷活動前（2017-06-24）的使用者集合為uid1[]，促銷活動後（2017-06-25）的使用者集合為uid2[]，想知道促銷活動得到了哪些新增使用者。

arr_miner(uid2[], uid1[]) 即可得到。

二、我們使用了hll類型，hll本身支援資料的邏輯計算

1、計算唯一值個數

2、計算兩個hll的并集，得到一個hll

例如在促銷活動前（2017-06-24）的使用者集合hll為uid1_hll，促銷活動後（2017-06-25）的使用者集合hll為uid2_hll，想知道促銷活動得到了多少新增使用者。

業務以前通過即時join得到透視結果，而現在我們使用事先統計的方法得到透視結果，事先統計本身是需要排程的。

排程方法取決于資料的來源，以及資料合并的方法，流式增量或批量增量。

一、資料按天統計，曆史統計資料無更新，隻有增量。

定時将統計結果寫入、合并至t_result結果表。

二、合并統計次元資料

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

當然，業務也能選擇異步彙聚，最終使用者查詢彙聚後的結果。

array聚合需要自定義一個聚合函數

按月彙聚sql如下

按年彙聚以此類推。

三、流式排程

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

<a href="https://github.com/digoal/blog/blob/master/201612/20161220_01.md">《流計算風雲再起 - postgresql攜pipelinedb力挺iot》</a>

<a href="https://github.com/digoal/blog/blob/master/201510/20151015_01.md">《基于postgresql的流式pipelinedb, 1000萬/s實時統計不是夢》</a>

<a href="https://github.com/digoal/blog/blob/master/201512/20151215_01.md">《"物聯網"流式處理應用 - 用postgresql實時處理(萬億每天)》</a>

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

<a href="https://github.com/digoal/blog/blob/master/201701/20170101_02.md">《apsaradb的左右互搏(pgsql+hybriddb+oss) - 解決oltp+olap混合需求》</a>

1、對于透視分析需求，使用倒轉的方法，将資料按查詢需求進行預計算，得到統計結果，進而在透視時僅需查詢計算結果，任意次元透視，都可以做到100毫秒以内的響應速度。

2、使用grouping sets，對多個标簽次元進行一次性統計，降低資料重複掃描和重複運算，大幅提升處理效率。

3、使用數組，記錄每個透視次元的uid，進而不僅能支援透視，還能支援圈人的需求。同時支援未來更加複雜的透視需求。

4、使用hll類型，存儲估算值，在進行複雜透視時，可以使用hll，例如多個hll的值可以union，可以求唯一值個數，通常用于評估uv，新增uv等。

5、使用流計算，如果資料需要實時的統計，那麼可以使用pipelinedb進行流式分析，實時計算統計結果。（pipelinedb正在插件化，将來使用會更加友善）

6、與阿裡雲雲端元件結合，使用oss對象存儲過渡資料（原始資料），使用oss_fdw外部表對接oss，是以過渡資料可以不入庫，僅僅用于預計算。大幅降低資料庫的寫入需求、空間需求。

7、使用greenplum的一級、二級分區，将透視資料的通路需求打散到更小的機關，然後使用标簽索引，再次降低資料搜尋的範圍，進而做到任意資料量，任意次元透視請求100毫秒以内響應。

8、使用列存儲，提升壓縮比，節省統計資料的空間占用。

<a href="https://github.com/aggregateknowledge/postgresql-hll">https://github.com/aggregateknowledge/postgresql-hll</a>