PostgreSQL时间序列分析入门

本文通过简单示例介绍时间序列分析，如求增量与相关性。

示例数据

为了演示，创建简单表，加载一些示例数据。可参考上文内容。

test=# CREATE TABLE t_oil 
(
    region      text, 
    country     text, 
    year        int, 
    production  int, 
    consumption     int
);

加载数据命令：

COPY t_oil FROM
    PROGRAM 'curl https://www.cybertec-postgresql.com/secret/oil_ext.txt';

如果是数据库管理员，可以很容易采用这种方式加载数据。COPY FROM PROGRAM 可以在服务器上直接执行代码并直接传入给PostgreSQL。但你必须是超级用户（安全考虑）。

lag实现时间序列分析

处理时间序列时，通常要计算与前一周期的差，即增长率。SQL提供这种能力，非常简单：

test=# SELECT year, production,
test-#         lag(production, 1) OVER (ORDER BY year)
test-#     FROM    t_oil
test-#     WHERE   country = 'USA'
test-#     LIMIT 5;
 year | production |  lag
------+------------+-------
 1965 |       9014 |
 1966 |       9579 |  9014
 1967 |      10219 |  9579
 1968 |      10600 | 10219
 1969 |      10828 | 10600
(5 行记录)

lag函数有两个参数：第一个定义列，第二个参数可选，缺省即表示lag(production, 1)。上面示例中lag列将访问后面一行，但能使用任何整数，即访问前后给定行记录。正数向后、负数向前。

我们已经获取到后面一行记录了，下面计算差：

test=# SELECT year, production,
test-#         production - lag(production, 1) OVER (ORDER BY year) AS diff
test-#     FROM    t_oil
test-#     WHERE   country = 'USA'
test-#     LIMIT 5;
 year | production | diff
------+------------+------
 1965 |       9014 |
 1966 |       9579 |  565
 1967 |      10219 |  640
 1968 |      10600 |  381
 1969 |      10828 |  228
(5 行记录)

我们使用当前行减去前一行。我们还可以计算二级增长：

test=# select year, production ,diff, diff - lag(diff,1) OVER (ORDER BY year) AS diff2
test-# from (
test(# SELECT year, production,
test(#         production - lag(production, 1) OVER (ORDER BY year) AS diff
test(#     FROM    t_oil
test(#     WHERE   country = 'USA'
test(#     LIMIT 5
test(# )x;
 year | production | diff | diff2
------+------------+------+-------
 1965 |       9014 |      |
 1966 |       9579 |  565 |
 1967 |      10219 |  640 |    75
 1968 |      10600 |  381 |  -259
 1969 |      10828 |  228 |  -153
(5 行记录)

窗口函数非常强大，这个示例仅仅是开始。

PostgreSQL计算字段之间相关性

有时需要计算字段之间的相关性。PostgreSQL提供了corr函数，情况示例：

test=#     SELECT country, corr(production, consumption)
test-#     FROM    t_oil
test-#     GROUP BY 1
test-#     ORDER BY 2 DESC NULLS LAST;
       country        |        corr
----------------------+--------------------
 Mexico               |  0.962790640608018
 Canada               |  0.932931452462893
 Qatar                |  0.925552359601189
 United Arab Emirates |  0.882953285119214
 Saudi Arabien        |  0.642815458284221
 Other Middle East    |  0.480795636900294
 Kuwait               |  0.228437819296843
 Iran                 |  0.128329279839342
 USA                  | -0.724570663230018

结果显示Mexico和Canada相关性最强。正数表示正相关，反之表示负相关。0 表示决定不相关，第二个数字的结果是否由第一个数字引起，这里并不确定那个字段为因或果。

总结

本文通过实例展示了简单的时间序列分析，包括增量分析和相关性分析。