Q&A
URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先の説明](http...) に修正してください。
前提・実現したいこと
・使用するDBは、PostgeeSQL11です。
・下記のような「加工前データ」をSQLのみを使用して、「加工後データ」のように、ユーザID毎に時系列上同じステータス状態が続いている期間を1レコードに集約したいです。
・「加工後データ」を実現するようなSQLをご教示ください。
・Window関数の利用も可です。
加工前データ(テーブルに格納されているものとします。)
| ユーザID | 日付 | ステータスA | ステータスB | ステータスC |
|---|---|---|---|---|
| user1 | 2020/1/1 00:00:00 | 0 | 0 | 0 |
| user1 | 2020/1/1 00:01:00 | 0 | 0 | 0 |
| user1 | 2020/1/1 00:02:00 | 0 | 1 | 0 |
| user1 | 2020/1/1 00:03:00 | 0 | 0 | 0 |
| user1 | 2020/1/1 00:04:00 | 0 | 0 | 0 |
| user1 | 2020/1/1 00:05:00 | 0 | 1 | 0 |
| user1 | 2020/1/1 00:06:00 | 0 | 1 | 0 |
| user2 | 2020/1/1 00:00:00 | 0 | 0 | 0 |
| user2 | 2020/1/1 00:01:00 | 0 | 0 | 0 |
| user2 | 2020/1/1 00:02:00 | 0 | 0 | 0 |
| user2 | 2020/1/1 00:03:00 | 1 | 0 | 0 |
| user2 | 2020/1/1 00:04:00 | 1 | 0 | 0 |
| user2 | 2020/1/1 00:05:00 | 0 | 0 | 0 |
| user2 | 2020/1/1 00:06:00 | 0 | 0 | 0 |
加工後データ(このデータを実現するSQLが知りたいです)
| ユーザID | 開始時間 | 終了時間 | ステータスA | ステータスB | ステータスC |
|---|---|---|---|---|---|
| user1 | 2020/1/1 00:00:00 | 2020/1/1 00:01:00 | 0 | 0 | 0 |
| user1 | 2020/1/1 00:02:00 | 2020/1/1 00:02:00 | 0 | 1 | 0 |
| user1 | 2020/1/1 00:03:00 | 2020/1/1 00:04:00 | 0 | 0 | 0 |
| user1 | 2020/1/1 00:05:00 | 2020/1/1 00:06:00 | 0 | 1 | 0 |
| user2 | 2020/1/1 00:00:00 | 2020/1/1 00:02:00 | 0 | 0 | 0 |
| user2 | 2020/1/1 00:03:00 | 2020/1/1 00:04:00 | 1 | 0 | 0 |
| user2 | 2020/1/1 00:05:00 | 2020/1/1 00:06:00 | 0 | 0 | 0 |
調べたこと
ネットで調べた結果、下記のサイトに記載してある方法が参考になると思ったのですが、理解が進みませんでした。
リンク
回答1件
0
ベストアンサー
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;
投稿2020/08/14 10:47
編集2020/08/14 10:55
退会済みユーザー
総合スコア0
あなたの回答
tips
プレビュー
