時系列データのうち、同じステータスが続いている期間を1レコードに集約したいです。

前提・実現したいこと

・使用するDBは、PostgeeSQL11です。
・下記のような「加工前データ」をSQLのみを使用して、「加工後データ」のように、ユーザID毎に時系列上同じステータス状態が続いている期間を1レコードに集約したいです。
・「加工後データ」を実現するようなSQLをご教示ください。
・Window関数の利用も可です。

加工前データ(テーブルに格納されているものとします。)

ユーザID	日付	ステータスA	ステータスB
user1	2020/1/1 00:00:00	0	0
user1	2020/1/1 00:01:00	0	0
user1	2020/1/1 00:02:00	0	1
user1	2020/1/1 00:03:00	0	0
user1	2020/1/1 00:04:00	0	0
user1	2020/1/1 00:05:00	0	1
user1	2020/1/1 00:06:00	0	1
user2	2020/1/1 00:00:00	0	0
user2	2020/1/1 00:01:00	0	0
user2	2020/1/1 00:02:00	0	0
user2	2020/1/1 00:03:00	1	0
user2	2020/1/1 00:04:00	1	0
user2	2020/1/1 00:05:00	0	0
user2	2020/1/1 00:06:00	0	0

加工後データ(このデータを実現するSQLが知りたいです)

ユーザID	開始時間	終了時間	ステータスA	ステータスB
user1	2020/1/1 00:00:00	2020/1/1 00:01:00	0	0
user1	2020/1/1 00:02:00	2020/1/1 00:02:00	0	1
user1	2020/1/1 00:03:00	2020/1/1 00:04:00	0	0
user1	2020/1/1 00:05:00	2020/1/1 00:06:00	0	1
user2	2020/1/1 00:00:00	2020/1/1 00:02:00	0	0
user2	2020/1/1 00:03:00	2020/1/1 00:04:00	1	0
user2	2020/1/1 00:05:00	2020/1/1 00:06:00	0	0

調べたこと

ネットで調べた結果、下記のサイトに記載してある方法が参考になると思ったのですが、理解が進みませんでした。
リンク

Orlofsky

2020/08/13 12:04

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先の説明](http...) に修正してください。

odysseia

2020/08/13 12:24

ありがとうございます。修正しました。

行動規範の内容に同意します

回答1件

ベストアンサー

sql
1SELECT userid,
2       min(record_date) AS "start",
3       max(record_date) AS "end",
4       a,
5       b,
6       c
7FROM
8  (SELECT *,
9          sum(CHANGE) OVER (
10                            ORDER BY userid,
11                                     record_date) AS sumchange
12   FROM
13     (SELECT userid,
14             record_date,
15             a,
16             b,
17             c,
18             CASE
19                 WHEN (a <> lag_a)
20                      OR (b <> lag_b)
21                      OR (c <> lag_c) THEN 1
22                 ELSE 0
23             END AS CHANGE
24      FROM
25        (SELECT *,
26                lag(a) over(
27                            ORDER BY userid, record_date) AS lag_a,
28                lag(b) over(
29                            ORDER BY userid, record_date) AS lag_b,
30                lag(c) over(
31                            ORDER BY userid, record_date) AS lag_c
32         FROM sample) AS withlag) AS withchange) AS withsumchange
33GROUP BY userid,
34         sumchange,
35         a,
36         b,
37         c
38ORDER BY userid,
39         sumchange;

text
1 userid |        start        |         end         | a | b | c 
2--------+---------------------+---------------------+---+---+---
3 user1  | 2020-01-01 00:00:00 | 2020-01-01 00:01:00 | 0 | 0 | 0
4 user1  | 2020-01-01 00:02:00 | 2020-01-01 00:02:00 | 0 | 1 | 0
5 user1  | 2020-01-01 00:03:00 | 2020-01-01 00:04:00 | 0 | 0 | 0
6 user1  | 2020-01-01 00:05:00 | 2020-01-01 00:06:00 | 0 | 1 | 0
7 user2  | 2020-01-01 00:00:00 | 2020-01-01 00:02:00 | 0 | 0 | 0
8 user2  | 2020-01-01 00:03:00 | 2020-01-01 00:04:00 | 1 | 0 | 0
9 user2  | 2020-01-01 00:05:00 | 2020-01-01 00:06:00 | 0 | 0 | 0
10(7 rows)

おまけ

DDL

sql
1create table sample(
2    userid varchar(10),
3    record_date timestamp,
4    a numeric(1),
5    b numeric(1),
6    c numeric(1),
7    primary key(userid, record_date)
8);
9

データ投入

sql
1insert into sample values ('user1','2020/1/1 00:00:00',0,0,0);
2insert into sample values ('user1','2020/1/1 00:01:00',0,0,0);
3insert into sample values ('user1','2020/1/1 00:02:00',0,1,0);
4insert into sample values ('user1','2020/1/1 00:03:00',0,0,0);
5insert into sample values ('user1','2020/1/1 00:04:00',0,0,0);
6insert into sample values ('user1','2020/1/1 00:05:00',0,1,0);
7insert into sample values ('user1','2020/1/1 00:06:00',0,1,0);
8insert into sample values ('user2','2020/1/1 00:00:00',0,0,0);
9insert into sample values ('user2','2020/1/1 00:01:00',0,0,0);
10insert into sample values ('user2','2020/1/1 00:02:00',0,0,0);
11insert into sample values ('user2','2020/1/1 00:03:00',1,0,0);
12insert into sample values ('user2','2020/1/1 00:04:00',1,0,0);
13insert into sample values ('user2','2020/1/1 00:05:00',0,0,0);
14insert into sample values ('user2','2020/1/1 00:06:00',0,0,0);
15commit;