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>