PostgreSQL 時序資料案例 - 時間流逝, 自動壓縮, 同比\環比

PostgreSQL , 時序資料 , rrd , rrdtool , round robin database , 自動壓縮 , CTE , dml returning , 環比 , 同比 , KNN

時序資料庫一個重要的特性是時間流逝壓縮，例如1天前壓縮為5分鐘一個點，7天前壓縮為30分鐘一個點。

PostgreSQL 壓縮算法可定制。例如簡單的平均值、最大值、最小值壓縮，或者基于旋轉門壓縮算法的壓縮。

<a href="https://github.com/digoal/blog/blob/master/201712/20171205_01.md">《[未完待續] SQL流式案例 - 旋轉門壓縮(前後計算相關滑窗處理例子)》</a>

<a href="https://github.com/digoal/blog/blob/master/201608/20160813_01.md">《旋轉門資料壓縮算法在PostgreSQL中的實作 - 流式壓縮在物聯網、監控、傳感器等場景的應用》</a>

本文介紹一種簡單壓縮的場景，類似RRD資料庫，按時間次元，壓縮為 平均值、最大值、最小值、總和、記錄數 等次元。

本文還介紹了視窗查詢、同比、環比UDF(含KNN計算)、按時間分組均勻寫入、等進階SQL用法。

<a href="https://github.com/digoal/blog/blob/master/201712/20171225_01_pic_001.jpg" target="_blank"></a>

1、5分鐘級壓縮表

2、30分鐘級壓縮表

3、5分鐘級壓縮語句

4、30分鐘級壓縮語句

1、寫入明細測試資料，1億條，分布到10天。

2、5分鐘壓縮排程，1天前的資料，每隔1小時排程一次以下SQL。

3、30分鐘壓縮排程，7天前的資料，每隔1天排程一次以下SQL。

1、根據interval取時間分組，用整型除法+乘法。

例子：

5分鐘：

30分鐘：

2、生成均勻分布的時序資料，使用PG的interval和generate_series，可以将寫入時間均勻配置設定到對應區間。

3、時序資料庫一個重要的特性是時間流逝壓縮，例如1天前壓縮為5分鐘一個點，7天前壓縮為30分鐘一個點。

本文介紹了一種簡單壓縮的場景，類似RRD資料庫，按時間次元，壓縮為 平均值、最大值、最小值、總和、記錄數 等次元。

加上排程即可：

<a href="https://github.com/digoal/blog/blob/master/201305/20130531_02.md">《PostgreSQL 定時任務方法2》</a>

<a href="https://github.com/digoal/blog/blob/master/201305/20130531_01.md">《PostgreSQL Oracle 相容性之 - DBMS_JOBS - Daily Maintenance - Timing Tasks(pgagent)》</a>

4、壓縮後包含區間、最大值、最小值、平均值、點數等值，可以用于繪制圖形。

5、結合PG的視窗函數，很容易繪制同比、環比的圖形，SQL例句：

索引，加速

複合類型，傳回環比值

擷取環比值函數，傳回指定SID,HID在某個時間點附近的一條記錄，含KNN算法

同比、周環比、月環比(這些值也可以自動生成，避免每次查詢時計算)：

6、結合PG的線性回歸，可以繪制預測名額。以下為詳細介紹的例子：

<a href="https://github.com/digoal/blog/blob/master/201512/20151214_01.md">《PostgreSQL 多元線性回歸 - 2 股票預測》</a>

<a href="https://github.com/digoal/blog/blob/master/201503/20150305_01.md">《在PostgreSQL中用線性回歸分析linear regression做預測 - 例子2, 預測未來數日某股收盤價》</a>

<a href="https://github.com/digoal/blog/blob/master/201503/20150304_01.md">《PostgreSQL 線性回歸 - 股價預測 1》</a>

<a href="https://github.com/digoal/blog/blob/master/201503/20150303_01.md">《在PostgreSQL中用線性回歸分析(linear regression) - 實作資料預測》</a>

7、将壓縮表繼承到明細表，友善開發的使用，不需要再寫UNION的SQL，直接查明細表，即可得到所有資料(包括壓縮資料)。

<a href="https://github.com/digoal/blog/blob/master/201712/20171222_02.md">《逾時流式處理 - 沒有消息流入的資料異常監控》</a>

<a href="https://github.com/digoal/blog/blob/master/201712/20171212_01.md">《阿裡雲RDS PostgreSQL varbitx實踐 - 流式标簽 (閱後即焚流式批量計算) - 萬億級，任意标簽圈人，毫秒響應》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171123_02.md">《PostgreSQL 流式統計 - insert on conflict 實作 流式 UV(distinct), min, max, avg, sum, count ...》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171107_33.md">《HTAP資料庫 PostgreSQL 場景與性能測試之 32 - (OLTP) 高吞吐資料進出(堆存、行掃、無需索引) - 閱後即焚(JSON + 函數流式計算)》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171107_32.md">《HTAP資料庫 PostgreSQL 場景與性能測試之 31 - (OLTP) 高吞吐資料進出(堆存、行掃、無需索引) - 閱後即焚(讀寫大吞吐并測)》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171107_28.md">《HTAP資料庫 PostgreSQL 場景與性能測試之 27 - (OLTP) 物聯網 - FEED日志, 流式處理 與 閱後即焚 (CTE)》</a>

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

RRD is round robin database.

RRDtool is the OpenSource industry standard, high performance data logging and graphing system for time series data.

RRDtool can be easily integrated in shell scripts, perl, python, ruby, lua or tcl applications.

<a href="https://www.postgresql.org/docs/10/static/functions-window.html">https://www.postgresql.org/docs/10/static/functions-window.html</a>