Skip to main content
 首页 » 数据库

PostgreSQL检测时间序列活动周期

2022年07月19日191wayfarer

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

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. 总结

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


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