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算法计算时间序列围成的面积，与我们的方法类似。如果你愿意创新，可能还会又很多方法进行实现。