Skip to main content
 首页 » 数据库

PostgreSQL时间序列分析示例——模式匹配

2022年07月19日152傻小

本文通过实例讲解简单时间序列模式分析,如查找连续三个月增长的记录。

示例数据

为了演示,创建简单示例表,包括少部分示例数据:

CREATE TABLE t_timeseries 
( 
        id              serial, 
        data            numeric 
); 
 
COPY t_timeseries FROM stdin DELIMITER ','; 
1,11 
2,14 
3,16 
4,9 
5,12 
6,13 
7,14 
8,9 
9,15 
10,9 
\. 

现在的问题是:我们能否从数据中找到某种趋势?在这个例子中,我们能否找到一个值在一段时期内不断增长(如连续3次)。然后根据其表现的特征模式再进行更复杂的分析。

一种相对简单的方法是将时间序列变化编码为字符串。这样做的优点是充分利用标准字符串处理方法。首先利用PostgreSQL(以及一般的SQL)提供了一种简单的方法计算增量:

test=# SELECT  *, data - lag(data, 1) 
        OVER (ORDER BY id)  AS diff     
     FROM    t_timeseries; 
 id | data | diff 
----+------+------ 
  1 |   11 |      
  2 |   14 |    3 
  3 |   16 |    2 
  4 |    9 |   -7 
  5 |   12 |    3 
  6 |   13 |    1 
  7 |   14 |    1 
  8 |    9 |   -5 
  9 |   15 |    6 
 10 |    9 |   -6 
(10 rows) 

lag函数按照OVER子句定义的顺序一行一行移动并计算相对于前一行的增量。

模式编码

利用前节SQL的输出分析行之间的差值。如果大于0编码为“u”(表示up),反之用“d”(down)。这有什么用呢?优势是每移动一个时间序列表示单个字符,因此很容易继续向后处理。完成编码之后,我们使用滑动窗口:从5个时期(前2个,本期和后2个)获取相应数据并连接成字符串。

test=# SELECT  *, 
    string_agg(CASE WHEN diff > 0 
            THEN 'u'::text 
            ELSE 'd'::text END, '') 
                OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING 
            AND 2 FOLLOWING) AS encoded 
FROM    (    
                SELECT  *, data - lag(data, 1) 
            OVER (ORDER BY id)  AS diff  
                FROM    
t_timeseries   
                ) AS x; 
 id | data | diff | encoded 
----+------+------+--------- 
  1 |   11 |      | duu 
  2 |   14 |    3 | duud 
  3 |   16 |    2 | duudu 
  4 |    9 |   -7 | uuduu 
  5 |   12 |    3 | uduuu 
  6 |   13 |    1 | duuud 
  7 |   14 |    1 | uuudu 
  8 |    9 |   -5 | uudud 
  9 |   15 |    6 | udud 
 10 |    9 |   -6 | dud 
(10 rows) 

我们看到一开始编码字符串仅3个字符,因为没有前面记录,所有只能看到未来的趋势。这个查询结果反应了一些数据及趋势编码。

为了简化后续分析代码,我们可以创建视图:

CREATE VIEW v AS 
SELECT  *, string_agg(CASE WHEN diff > 0 
                THEN 'u'::text 
                ELSE 'd'::text END, '') 
                OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING 
                AND 2 FOLLOWING) AS encoded 
FROM    ( 
        SELECT  *, data - lag(data, 1) OVER (ORDER BY id)  AS diff 
        FROM    t_timeseries 
) AS x; 

这个编码非常简单,当然不能足够反应真实世界场景。但这能够利用PostgreSQL标准功能展示实现思路,给读者一定启发。

分析编码字符串

现在数据已经完成编码,下面我们进行分析。假设需要查找连续增长至少三次的所有相关的记录。
下面是简单的查询语句:

test=# SELECT  *  
FROM    v  
WHERE   encoded LIKE '%uuu%'; 
 id | data | diff | encoded 
----+------+------+--------- 
  5 |   12 |    3 | uduuu 
  6 |   13 |    1 | duuud 
  7 |   14 |    1 | uuudu 
(3 rows) 

总结

你可能会想利用更复杂的搜索算法匹配复杂模式,正则表达式可能是较合适的工具。另外创建距离函数并使用KNN算法计算时间序列围成的面积,与我们的方法类似。如果你愿意创新,可能还会又很多方法进行实现。


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