PostgreSQL检测时间序列活动周期

本文通过示例学习如何发现时间序列的活动周期，如：用户什么时间段处于活动状态或有活动数据。本文给一些思路，希望对你有帮助。

1. 准备数据

为了简化，这里简单生成一些示例数据，代码如下：

CREATE TABLE t_series (t date, data int);
 
COPY t_series FROM stdin DELIMITER ';';
2018-03-01;12
2018-03-02;43
2018-03-03;9
2018-03-04;13
2018-03-09;23
2018-03-10;26
2018-03-11;28
2018-03-14;21
2018-03-15;15
\.

为了简单，数据仅包括两列数据。我们注意到数据不是连续的，中间有周期中断，共有三个时间段有信息。我们的目标是对每个连续段数据进行分析。使用R展示图示如下：

2. 活动周期分析

时间序列分析，其中最重要的学习如何向前和向后；大多数场景是简单比较当前行和前一行。我们首先利用lag函数获取前一行；然后计算当前行与前一行的差值；接着利用sum函数计算周期从而区分出活动周期，最后对每个周期进行分析。

2.1 获取前一行数据

test=# SELECT *, lag(t, 1) OVER (ORDER BY t) FROM t_series;
          t | data | lag
------------+------+----------
 2018-03-01 |   12 | 
 2018-03-02 |   43 | 2018-03-01
 2018-03-03 |    9 | 2018-03-02
 2018-03-04 |   13 | 2018-03-03
 2018-03-09 |   23 | 2018-03-04
 2018-03-10 |   26 | 2018-03-09
 2018-03-11 |   28 | 2018-03-10
 2018-03-14 |   21 | 2018-03-11
 2018-03-15 |   15 | 2018-03-14
(9 rows)

最后一列包括前一行的数据。PostgreSQL 根据order by知道前一行数据。下面计算当前行与前一行差值。

2.2 计算差值

利用前面的查询很容易计算差值：

test=# SELECT *, t - lag(t, 1) OVER (ORDER BY t) AS diff FROM t_series;
          t | data | diff 
------------+------+------
 2018-03-01 |   12 | 
 2018-03-02 |   43 | 1
 2018-03-03 |    9 | 1
 2018-03-04 |   13 | 1
 2018-03-09 |   23 | 5
 2018-03-10 |   26 | 1
 2018-03-11 |   28 | 1
 2018-03-14 |   21 | 3
 2018-03-15 |   15 | 1
(9 rows)

现在看到了不同时期的差异。这非常有用，因为我们可以创建我们的规则。即一个环节什么时候结束，它是下一个环节/阶段之前，我们允许有多长时间的间隔?

2.3 利用sum函数区分活动周期

在我们的示例中，间隔超过2天则触发创建新的周期。下面要解决给每个周期赋一个用于检测的值，之后就很容易计算结果。我们决定使用sum函数。

test=#  SELECT *, sum(CASE WHEN diff IS null then 1
test(#                  WHEN diff < 2 THEN 0
test(#                  when diff >=2 then 1 END) OVER (ORDER BY t) AS period
test-#    FROM (
test(#    SELECT *, t - lag(t, 1) OVER (ORDER BY t) AS diff
test(#          FROM   t_series
test(#    ) AS x;
    
     t      | data | diff | period
------------+------+------+--------
 2018-03-01 |   12 |      |      1
 2018-03-02 |   43 |    1 |      1
 2018-03-03 |    9 |    1 |      1
 2018-03-04 |   13 |    1 |      1
 2018-03-09 |   23 |    5 |      2
 2018-03-10 |   26 |    1 |      2
 2018-03-11 |   28 |    1 |      2
 2018-03-14 |   21 |    3 |      3
 2018-03-15 |   15 |    1 |      3
(9 行记录)

第一个行diff为null，我们赋初始值1，小于2表示连续周期赋值0，否则赋值1用于区分活动周期。从结果来看非常清晰地分为三个活动周期。

2.4 分析活动周期数据

有了上面的结果，分析结果就简单了。根据period分组计算data的和：

test=#  select period ,sum(data)
test-#  from
test-#  (
test(#  SELECT *, sum(CASE WHEN diff IS null then 1
test(#                  WHEN diff < 2 THEN 0
test(#                  when diff >=2 then 1 END) OVER (ORDER BY t) AS period
test(#    FROM (
test(#    SELECT *, t - lag(t, 1) OVER (ORDER BY t) AS diff
test(#          FROM   t_series
test(#    ) AS x
test(#  ) as y
test-# GROUP BY period
test-# ORDER BY period;
 period | sum
--------+-----
      1 |  77
      2 |  77
      3 |  36
(3 行记录)

结果显示每个周期的数据值。

3. 总结

本文通过示例展示了如何检测时间序列的活动周期，希望在遇到类似问题时能给你一些启发。