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