PostgreSQL时间序列分析示例——模式匹配_数据库

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

示例数据

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

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