SQLで同じ値が連続している部分がいくつあるか集計するスニペット
あれ？これSQLでどうやってやるんだ？となった集計のメモです。
やりたいこと
以下のようなテーブルがあったとします。
時系列に沿ったデータで、「フラグが1の状態がある程度続いて0に戻り、また1になっては0に戻る」みたいなデータがあった場合に連続する1は一つのグループとしてカウントし「1になった区間が3回あった」みたいな集計をしたくなりました。
| 日時 | フラグ | グルーピングしたい |
|---|---|---|
| 2019-04-24 10:00:00 | 1 | ←区間① |
| 2019-04-24 11:00:00 | 1 | ←区間① |
| 2019-04-24 12:00:00 | 1 | ←区間① |
| 2019-04-24 13:00:00 | 0 | |
| 2019-04-24 14:00:00 | 0 | |
| 2019-04-24 15:00:00 | 0 | |
| 2019-04-24 16:00:00 | 1 | ←区間② |
| 2019-04-24 17:00:00 | 0 | |
| 2019-04-24 18:00:00 | 0 | |
| 2019-04-24 19:00:00 | 1 | ←区間③ |
| 2019-04-24 20:00:00 | 1 | ←区間③ |
| 2019-04-24 21:00:00 | 0 |
（ネット上に解法や解説もありますが自分の理解用メモとして。。。）
カウントアップを使った解法
データ作る
テスト用にデータ作ります。動作はSQLite3で確認しています。
# テーブル作る
CREATE TABLE test(
id INTEGER PRIMARY KEY,
date_time DEFAULT CURRENT_TIMESTAMP,
flag INTEGER
);
# サンプルデータ
INSERT INTO test VALUES
(null, '2019-04-24 10:00:00', 1),
(null, '2019-04-24 11:00:00', 1),
(null, '2019-04-24 12:00:00', 1),
(null, '2019-04-24 13:00:00', 0),
(null, '2019-04-24 14:00:00', 0),
(null, '2019-04-24 15:00:00', 0),
(null, '2019-04-24 16:00:00', 1),
(null, '2019-04-24 17:00:00', 0),
(null, '2019-04-24 18:00:00', 0),
(null, '2019-04-24 19:00:00', 1),
(null, '2019-04-24 20:00:00', 1),
(null, '2019-04-24 21:00:00', 0);
時間の連続とROW_COUNTによる連番を比較
今回は1時間ごとのデータなので、連続に並んでいるという状態は日時の"時間"の部分が1ずつ増えているという状態です。そして判定対象はフラグが1となっている部分の"時間"が連続になっているかどうかということになります。
そこでまず、基準となる「日時の"時間"に時系列順に番号をふり」集計対象である「フラグが１の部分にも時系列順にも番号をふり」ます。
そうするとフラグが1の場合は基準と集計対象が同じようにカウントアップされますが、フラグ0が間に入るごとにカウントアップに差が生まれるという状態になります。
これを利用することで区間のカウントアップができます。
多分見たほうが速いので↓
select
date_time,
flag,
row_number() over (partition by date(date_time) order by date_time asc) as 時間の連番,
row_number() over (partition by date(date_time),flag order by date_time asc) as フラグ連番,
row_number() over (partition by date(date_time) order by date_time asc) - row_number() over (partition by date(date_time),flag order by date_time asc) as 差分
from
test
order by
date_time;
| 日時 | フラグ | 時間の連番 | フラグ連番 | 差分 |
|---|---|---|---|---|
| 2019-04-24 10:00:00 | 1 | 1 | 1 | 0 |
| 2019-04-24 11:00:00 | 1 | 2 | 2 | 0 |
| 2019-04-24 12:00:00 | 1 | 3 | 3 | 0 |
| 2019-04-24 13:00:00 | 0 | 4 | 1 | 3 |
| 2019-04-24 14:00:00 | 0 | 5 | 2 | 3 |
| 2019-04-24 15:00:00 | 0 | 6 | 3 | 3 |
| 2019-04-24 16:00:00 | 1 | 7 | 4 | 3 |
| 2019-04-24 17:00:00 | 0 | 8 | 4 | 4 |
| 2019-04-24 18:00:00 | 0 | 9 | 5 | 4 |
| 2019-04-24 19:00:00 | 1 | 10 | 5 | 5 |
| 2019-04-24 20:00:00 | 1 | 11 | 6 | 5 |
| 2019-04-24 21:00:00 | 0 | 12 | 6 | 6 |
↓
見やすくするためにフラグ１のみに絞る
↓
| 日時 | フラグ | 時間の連番 | フラグ連番 | 差分 |
|---|---|---|---|---|
| 2019-04-24 10:00:00 | 1 | 1 | 1 | 0 |
| 2019-04-24 11:00:00 | 1 | 2 | 2 | 0 |
| 2019-04-24 12:00:00 | 1 | 3 | 3 | 0 |
| 2019-04-24 16:00:00 | 1 | 7 | 4 | 3 |
| 2019-04-24 19:00:00 | 1 | 10 | 5 | 5 |
| 2019-04-24 20:00:00 | 1 | 11 | 6 | 5 |
このように差分列（時間連番-フラグ連番）が区間を示す番号となるので、この列でgroup byなりdistinctなりすることで区間の集計が可能になりました。
なお今回の例の場合は時間の連番を"時間"をそのままstrftime('%H', date_time)みたいな感じで使っても大丈夫。
わかれば簡単なのですが、なるほどなと思ったのでメモでした。
注意：SQLiteのバージョン
SQLiteでテストしようとしたらバージョンがちょっと古い場合、window関数に対応しておらずハマったのでご注意を。
sqlite3 3.25以上をご利用ください。
参考
Author And Source
この問題について(SQLで同じ値が連続している部分がいくつあるか集計するスニペット), 我々は、より多くの情報をここで見つけました https://qiita.com/stkdev/items/29e38941312dc0905821著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .