postgresql , 遞歸查詢 , 大屏播報 , 最擁堵的路口 , 最旺的商鋪 , 某個區域最擁堵的廁所 , 資料透視 , 流式計算 , 時序資料
随着物聯網的發展，資料的産生越來越快。比如馬路的汽車通過資料，使用者的駐留資料，水紋傳感器的資料，電商的feed資料，網約車的軌迹資料 等等。
這麼多的資料，用途也不一樣，有需要流式實時統計的，也有時序處理相關需求的，還有全量分析需求的。
這些都有對應的解決方案。
<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/201704/20170409_05.md">《時序資料庫有哪些特點? timescaledb時序資料庫介紹》</a>
<a href="https://github.com/digoal/blog/blob/master/201701/20170101_02.md">《apsaradb的左右互搏(pgsql+hybriddb+oss) - 解決oltp+olap混合需求》</a>
以指揮中心的大屏為例，有一些需求就很有意思，比如
1. 展示最新的傳感器top value資料。
2. 選擇時間軸，按區域，展示人流量。
這個需要和我之前寫的這個例子很相似。
<a href="https://github.com/digoal/blog/blob/master/201611/20161128_01.md">《時序資料合并場景加速分析和實作 - 複合索引，視窗分組查詢加速，變态遞歸加速》</a>
設計表結構，gid表示傳感器id，val是上傳的值，crt_time是時間。
假設有1萬個傳感器，插入1000萬條記錄。
寫入1000萬傳感器測試資料
建立索引
使用遞歸調用的方法，擷取所有傳感器的最新值(以每個傳感器的最大的自增id為最新上報标記)
1000萬條記錄，篩選1萬條最新記錄，耗費時間：129毫秒。
為什麼能這麼快？因為用了遞歸，減少了掃描量和運算量。
使用遊标則更快，一次擷取10條，僅花費0.36毫秒。
相比第一個例子，做了一次收斂，按value排序，輸出最大的。
假設每個路口有傳感器不斷上報路口通過的車流數量。大屏展示通過量最大的10個路口。
為了測試友善，我這裡依舊使用第一個例子的資料，末尾加上。
1000萬條記錄，篩選1萬條最新記錄，輸出top 10，耗費時間：126毫秒。
相比前兩個例子，多了兩個次元：
一個是時間次元，使用者可以勾選時間段進行分析。另一個是區域次元，使用者要勾選地區，輸出地區内的資料。
思考：
空間索引不像b-tree索引是有序存儲的，空間索引是gist索引，使用了類似聚類分區的結構，是以在進行多列複合時，gist的空間查詢結合索引排序輸出第一條，是行不通的，會引入顯示的sort。
原理參考
<a href="https://github.com/digoal/blog/blob/master/201612/20161231_01.md">《從難纏的模糊查詢聊開 - postgresql獨門絕招之一 gin , gist , sp-gist , rum 索引原理與技術背景》</a>
同時查詢條件包含了時間區間作為條件，索引非驅動列（子段gid+val）的排序也是行不通的。
什麼時候能使用複合索引的查詢+排序？
僅僅當排序列前面的所有列都是等值查詢時，才能使用隐式排序，并且索引的順序要和排序的順序一緻。例如index(a,b,c)支援where a=? and b=? order by c，但是不支援where a> ? and b=? order by c等等。
重新規劃測試資料，為了測試友善, 以point取代經緯度，真實業務可以使用geometry類型。
插入1000萬測試資料，1萬個店鋪id，1億的點陣範圍中的随機point。
時間+空間 的快速傳感器最大值篩選怎麼加速呢？
分兩種情況優化
1. 總的傳感器（店鋪）不多（例如1萬個店鋪）
利用索引快速搜尋每個gid的最大val，使用partial index，規避時間問題；使用cpu完成點面判斷。
例子，
例如我們允許使用者勾選的最小時間範圍是2小時，可以每2小時建一個partial index。（使用這麼多partial index很變态，也不優雅。建議10.0的分區表優化後，每2小時切一個分區。）
這個區間的總資料量, 約350萬。
使用這個partial index，以及遞歸調用，取出該區間的所有店鋪的最大值。然後根據點面判斷，得到某個區域的資料，再排序輸出top 10。
135毫秒傳回
執行計劃如下
2. 店鋪很多，但是時間+空間收斂後，記錄數不多(比如幾百萬)
這種情況，可以考慮使用時間分區表。然後建構空間索引。
通過時間條件，定位到指定的分區，通過空間索引，篩選資料。對篩選後的資料，通過少量cpu計算得到top店鋪。
例子
2.1 将表按時間分區（例如每2小時一個分區，前面有介紹為什麼這麼做）
2.2 建立空間索引
2.3 透視
sql中輸入時間條件時，postgresql會自動鎖定到分區表，我這裡為了簡便，直接寫test表。
使用視窗查詢，得到top sql
效率
執行計劃
讓postgresql支援空間grid分區(實際上你現在就可以使用繼承來實作，觸發器中使用grid+mod判斷應該插入哪個分區)。
參考如下
<a href="https://github.com/digoal/blog/blob/master/201701/20170124_01.md">《蜂巢的藝術與技術價值 - postgresql postgis's hex-grid》</a>
對于時間+空間次元的資料透視，可以建立空間grid分區 + 時間分區 二級分區。
檢索時，通過分區表直接過濾到目标子分區表。再通過btree索引，遞歸調用，篩選出每個店鋪在候選區間的峰值資料，最後加上少量cpu運算，得到top店鋪。
使用這種方法，時間+空間的四維資料透視，查詢效率可以進入100毫秒以内。
1. 對于例子1和2，由于業務層面取的都是最近的資料，曆史資料并不關心。除了使用遞歸優化，還有2種方法。
方法1，不記錄曆史，将插入換成插入或更新。使用這種方法，查詢sort_test得到的始終是最新的值。
方法2，記錄曆史，同時記錄最新狀态。使用觸發器完成這項工作。
分解：
資料插入時，自動更新最後一條記錄。(寫入量和更新量同等)
建立一個狀态表記錄最新狀态，建立一個觸發器，寫入曆史資料時，自動更新最新狀态表。
插入資料，自動更新到最新狀态
查詢時，直接查詢最新狀态表，連遞歸調用都省了。
2. 對于例子3，由于分析的是曆史資料，而且分析次元是時間和空間兩個次元。
是以可以将其中一個次元作為分區，将資料打散，打散之後，對分區建立另一個次元的索引。
這樣的話，在查詢時，可以将資料盡量的收斂到更小的範圍。
空間和時間都支援分區。（空間分區建議使用網格化的表述，便于查找和定位分區）。