postgresql , 資料透視 , cube , grouping sets , rollup
人群透視是商業與資料結合的案例之一，比如大型商場的選址，可與分析的資料包括車流、人流量等等。
結合資料可以更深入的分析人群的組成結構，消費能力等等，給大型商場的選址帶來更多的參考價值。
那麼如何使用資料庫透視人群資料呢？
1. 人群屬性表
記載了每個人的各個屬性段落，比如收入、車齡、固定資産等等。如下
2. 人群動态軌迹
記錄的是人群的活動位置或軌迹
使用postgresql postgis插件，可以很友善的記錄軌迹資料，并且支援gist索引，可以快速的根據某個區域或範圍搜尋對應的人群。
1. 生成1000萬人群的測試資料, 其中車齡為4, 年齡段為4的不插入，制造一些空洞。
2. 生成1000萬人群軌迹資料
1. 選擇人群
以某個點為中心、或者根據某個閉環區域，圈一部分人群，（采用postgis）
這裡不舉例gis(跟興趣的童鞋可以使用postgis測試一下，性能杠杠的)，我直接以時間為度量直接圈人。
有人可能要問，如果這個時間段，同一個人出現了多條軌迹，怎麼處理呢？
這裡使用了in，postgresql 的優化器很強大，join時資料庫會自動聚合，不必在這裡group by，原理可參考如下文章。
<a href="https://github.com/digoal/blog/blob/master/201612/20161223_01.md">《聊一下postgresql優化器 - in裡面有重複值時postgresql如何處理?》</a>
2. 資料透視
postgresql的sql相容性非常強大，對于資料透視，可以使用grouping sets, cube, rollup等文法。
<a href="https://github.com/digoal/blog/blob/master/201505/20150526_02.md">《grouping sets, cube and rollup》</a>
更多透視用法參考cube, rollup, grouping sets用法。
目前postgresql, hybriddb, greenplum都支援以上文法。
3. 結果轉換
使用with文法，将以上結果進行轉換
perf report
4. left join 補缺(可選)
對于空洞值，如果你要補齊的話，使用left join即可
補缺如下
5. 行列變換(可選)
如果要将以上資料，多行轉換為單行，可以使用tablefunc插件，postgresql玩法巨多哦。
<a href="https://www.postgresql.org/docs/9.6/static/tablefunc.html">https://www.postgresql.org/docs/9.6/static/tablefunc.html</a>
1. 關于索引（brin, gist, btree_gist）
通常我們會限定兩個次元，篩選人群，1時間範圍，2地理位置範圍。
由于軌迹資料通常是時間和堆的線性相關性很好的，是以，在索引方面，可以使用brin索引。
brin索引詳見
<a href="https://github.com/digoal/blog/blob/master/201702/20170219_01.md">《postgresql 聚集存儲 與 brin索引 - 高并發行為、軌迹類大吞吐資料查詢場景解說》</a>
而對于地理位置，如果要進行快速篩選的話，可以建立gist索引
如果要建立兩者的複合索引，可以使用btree_gist插件，那麼時間和地理位置就能放在一個gist索引中了。
2. 遞歸優化
如果軌迹點很多，但是大多數為重複人群，可使用遞歸優化in查詢
參考
<a href="https://github.com/digoal/blog/blob/master/201612/20161201_01.md">《用postgresql找回618秒逝去的青春 - 遞歸收斂優化》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161128_02.md">《distinct xx和count(distinct xx)的變态遞歸優化方法 - 索引收斂(skip scan)掃描》</a>
<a href="https://github.com/digoal/blog/blob/master/201611/20161128_01.md">《時序資料合并場景加速分析和實作 - 複合索引，視窗分組查詢加速，變态遞歸加速》</a>
3. case when 優化，在使用本例的cube,grouping sets,rollup前，或者其他不支援資料透視文法的資料庫中，可以使用case when的方法來聚合，但是每條資料都要經過case when的計算，耗費很大的cpu。
1. 文法cube, grouping sets, rollup給資料透視提供了比較好的便利。
2. 行列變換可以使用tablefunc插件。
3. case when過多時，對cpu的開銷會比較大。
4. 結合postgis可以很友善的基于地理位置和時間次元，分析人群特性。
5. 阿裡雲hybriddb, postgresql都能提供以上功能，其中hybriddb為分布式資料倉庫。