Skip to main content
 首页 » 数据库

PostgreSQL时间序列分析入门

2022年07月19日148leader

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

示例数据

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

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 表示决定不相关,第二个数字的结果是否由第一个数字引起,这里并不确定那个字段为因或果。

总结

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


本文参考链接:https://blog.csdn.net/neweastsun/article/details/116139313
阅读延展