PostgreSQL 海量時序資料(任意滑動視窗實時統計分析) - 傳感器、人群、物體等對象跟蹤

postgresql , 物聯網 , feed , 網遊 , 熱力 , 商場駐留 , 人群分析 , 實時熱力圖 , 實時線上圖 , 實時分段最大最小區間圖 , 任意滑動視窗實時最高、最低線上數

在現實生活中，經常有聚集分析的需求。

例如：

某個商場，每個時間點，商場的每個商鋪位置的人群駐留數量。（有技術手段可以感覺人的駐留位置，當走進某個區域時，将寫入一條記錄，表示你進入了這個區域，離開時記錄一條離開的記錄，如果長時間不動，則定時寫心跳記錄）。

某個網遊，每個時間點，線上人數。（上線時寫一條上線記錄，下線時寫一條下線記錄。）

某個共享單車公司，每個時間點，線上和不線上的車輛數量。（借車時寫一條上線記錄，還車時寫一條下線記錄。同時每隔一段時間詢問車輛狀态。）

某個物聯網企業，每個分鐘機關内，最小、最大線上傳感器的數量。（傳感器上線時寫一條上線記錄，下線時寫一條下線記錄，同時每隔一段時間詢問傳感器狀态。）

這種屬于非常典型的feed應用，要求輸出每個時間點這個世界（系統）的線上數。（如果按時間段輸出，則輸出每個時間段内的最大，最小線上數，實際上就是取range的邊界）。

場景：

某個物聯網企業，有一些傳感器，傳感器上線時寫一條上線記錄，下線時寫一條下線記錄，同時每隔小時詢問傳感器狀态，也就是說1小時内沒有記錄的傳感器視為不線上。

企業需要統計每個分鐘機關内，最小、最大線上傳感器的數量。

1、表結構

2、索引

寫入1.101億測試資料（我們假設這是1小時的資料寫入量，全天寫入26.424億記錄），1001個傳感器id。

3、資料ttl，確定表比較瘦，隻包含心跳時間範圍内的資料。

由于每小時接收心跳，是以1小時内，必有資料，沒有資料的傳感器不計狀态。是以我們保留1小時内的狀态即可。

一種保留方法是pipelinedb，用法如下。

<a href="https://github.com/digoal/blog/blob/master/201706/20170612_03.md">《資料保留時間視窗的使用》</a>

另一種保留方法，使用兩張表，輪詢使用即可。

類似的用法如下

<a href="https://github.com/digoal/blog/blob/master/201703/20170321_02.md">《postgresql 資料rotate用法介紹 - 按時間覆寫曆史資料》</a>

4、使用遞歸查詢，高效查詢傳感器的最終狀态

執行計劃如下

樣例

效率很高，1.101億資料，11毫秒擷取最終線上狀态。

線上的裝置為state=t的。

5、統計任意時間點的傳感器線上數量，如果每個裝置上線的時間精确到秒(crt_time精确到秒)，那麼不管有多少條記錄，一天最多需要統計86400個時間點的傳感器線上數量。

例如統計 <code>2017-07-05 10:29:09</code> 時間點的傳感器線上數量，加一個時間限制即可。

新增這個時間限制，會帶來一定的性能影響，特别是如果這個時間是過去很久以前的時間，過濾會越多，性能下降越嚴重。

是以，建議實時，每秒發起一次查詢請求，就不要加這個時間限制了。

6、一次性生成過去每一秒的線上數。

使用視窗查詢的幀查詢技術。（幀表示按時間排序，截止到目前記錄的區間。）

7、統計每分鐘内，最高線上數、最低線上數。

每秒查詢一次，将資料寫入結果表。

由于每次查詢僅需12毫秒，每秒調用一次沒有問題。

統計某一分鐘内，最高線上數、最低線上數。

當傳感器id達到10萬級别時，查詢性能會下降到250毫秒。

如果傳感器id特别多，例如有百萬以上，那麼會下降到2.5秒。就不适合每秒查詢一次了。

是以傳感器數量特别多時，如何優化？

有一個比較好的方法是資料按傳感器id進行哈希分布，例如每張分區表負責1萬個傳感器id。在查詢線上數時，并發的查詢所有的分區表，進而降低rt。

使用本文提到的方法(遞歸查詢)，我們可以實作非常細粒度的，大量被跟蹤物的狀态實時統計。

用于繪制被跟蹤物的實時狀态圖，例如：

1、實時熱力圖

2、實時傳感器(或使用者)線上、離線數，任意滑動視窗的最大最小線上、離線值。