不定期に連続するログの開始と終了を SQL クエリーで集計する

センサーが物体を検出したときのみ記録される、そういった不定期に連続するログの開始と終了を SQL クエリーで集計する方法です。

# データサンプルと欲しい集計データ

例えばこのようなデータがあったとして．．．

| Id | TimeStamp |
|----|:--------------|
| A001 | 20XX/1/20 7:55 |
| A001 | 20XX/1/20 7:56 |
| A001 | 20XX/1/20 7:57 |
| A001 | 20XX/1/20 13:41 |
| A001 | 20XX/1/20 13:42 |
| A001 | 20XX/1/20 16:27 |
| A001 | 20XX/1/20 23:58 |
| A001 | 20XX/1/20 23:59 |
| A001 | 20XX/1/21 0:00 |
| A001 | 20XX/1/21 0:01 |

このように検出の始まり(StartDateTime)と終わり(EndDateTime)を集計したい、といったケースです。

| Id | StartDateTime | EndDateTime |
|----|:--------------|:--------------|
| A001 | 20XX/1/20 7:55 | 20XX/1/20 7:57 |
| A001 | 20XX/1/20 13:41 | 20XX/1/20 13:42 |
| A001 | 20XX/1/20 16:27 | 20XX/1/20 16:27 |
| A001 | 20XX/1/20 23:58 | 20XX/1/21 0:01 |

> もちろん Id が一意ではない (複数の値が存在する)、他にも列の要素がある、といったケースが普通だと思いますが、その辺りの操作は単純な SQL クエリーで操作できると思いますので、今回は省略します。

# 方法

## Step1. 連続するデータの終わりを判別する (EndDateTime)

TimeStamp に -1 (今回のデータは 1 分刻みなので -1 分) したデータを作成します。

| Id | TimeStamp | TimeStamp_minus1 |
|----|:--------------|:--------------|
| A001 | 20XX/1/20 7:55 | 20XX/1/20 7:54 |
| A001 | 20XX/1/20 7:56 | 20XX/1/20 7:55 |
| A001 | 20XX/1/20 7:57 | 20XX/1/20 7:56 |
| A001 | 20XX/1/20 13:41 | 20XX/1/20 13:40 |
| A001 | 20XX/1/20 13:42 | 20XX/1/20 13:41 |
| A001 | 20XX/1/20 16:27 | 20XX/1/20 16:26 |
| A001 | 20XX/1/20 23:58 | 20XX/1/20 23:57 |
| A001 | 20XX/1/20 23:59 | 20XX/1/21 23:58 |
| A001 | 20XX/1/21 0:00 | 20XX/1/21 23:59 |
| A001 | 20XX/1/21 0:01 | 20XX/1/21 0:00 |

ある行のログの後ろに連続データがあるのなら、TimeStamp の列のデータは -1 したデータに存在するはずです。

| Id | TimeStamp | -1 に存在? |
|----|:--------------|:---|
| A001 | 20XX/1/20 7:55 | 〇 |
| A001 | 20XX/1/20 7:56 | 〇 |
| A001 | 20XX/1/20 7:57 | × |
| A001 | 20XX/1/20 13:41 | 〇 |
| A001 | 20XX/1/20 13:42 | × |
| A001 | 20XX/1/20 16:27 | × |
| A001 | 20XX/1/20 23:58 | 〇 |
| A001 | 20XX/1/20 23:59 | 〇 |
| A001 | 20XX/1/21 0:00 | 〇 |
| A001 | 20XX/1/21 0:01 | × |

-1 に存在しなかったデータ(×) が連続したデータの終わりということになります(=EndDateTime)。Timestamp の順に並べて番号を振っておきます。

| # | Id | TimeStamp(EndDateTime) | -1 に存在? |
|---|----|:--------------|:---|
| 1 | A001 | 20XX/1/20 7:57 | × |
| 2 | A001 | 20XX/1/20 13:42 | × |
| 3 | A001 | 20XX/1/20 16:27 | × |
| 4 | A001 | 20XX/1/21 0:01 | × |


## Step2. 連続するデータの開始を判別する (StartDateTime)

今度は TimeStamp に +1 (今回のデータは 1 分刻みなので +1 分) したデータを作成します。

| Id | TimeStamp | TimeStamp_plus1 |
|----|:--------------|:--------------|
| A001 | 20XX/1/20 7:55 | 20XX/1/20 7:56 |
| A001 | 20XX/1/20 7:56 | 20XX/1/20 7:57 |
| A001 | 20XX/1/20 7:57 | 20XX/1/20 7:58 |
| A001 | 20XX/1/20 13:41 | 20XX/1/20 13:42 |
| A001 | 20XX/1/20 13:42 | 20XX/1/20 13:43 |
| A001 | 20XX/1/20 16:27 | 20XX/1/20 16:28 |
| A001 | 20XX/1/20 23:58 | 20XX/1/20 23:59 |
| A001 | 20XX/1/20 23:59 | 20XX/1/21 0:00 |
| A001 | 20XX/1/21 0:00 | 20XX/1/21 0:01 |
| A001 | 20XX/1/21 0:01 | 20XX/1/21 0:02 |

ある行のログの前に連続データがあるのなら、TimeStamp の列のデータは +1 したデータに存在するはずです。

| Id | TimeStamp | +1 に存在? |
|----|:--------------|:---|
| A001 | 20XX/1/20 7:55 | × |
| A001 | 20XX/1/20 7:56 | 〇 |
| A001 | 20XX/1/20 7:57 | 〇 |
| A001 | 20XX/1/20 13:41 | × |
| A001 | 20XX/1/20 13:42 | 〇 |
| A001 | 20XX/1/20 16:27 | × |
| A001 | 20XX/1/20 23:58 | × |
| A001 | 20XX/1/20 23:59 | 〇 |
| A001 | 20XX/1/21 0:00 | 〇 |
| A001 | 20XX/1/21 0:01 | 〇 |

+1 に存在しなかったデータ(×) が連続したデータの始まりということになります(=StartDateTime)。Timestamp の順に並べて番号を振っておきます。

| # | Id | TimeStamp(StartDateTime) | +1 に存在? |
|---|----|:--------------|:---|
| 1 | A001 | 20XX/1/20 7:55 | × |
| 2 | A001 | 20XX/1/20 13:41 | × |
| 3 | A001 | 20XX/1/20 16:27 | × |
| 4 | A001 | 20XX/1/20 23:58 | × |

## Step3. StartDateTime と EndDateTime を JOIN

振った番号で JOIN して StartDateTime と EndDateTime のデータをマージします。

| # | Id | StartDateTime | EndDateTime |
|---|----|:--------------|:--------------|
| 1 | A001 | 20XX/1/20 7:55 | 20XX/1/20 7:57 |
| 2 | A001 | 20XX/1/20 13:41 | 20XX/1/20 13:42 |
| 3 | A001 | 20XX/1/20 16:27 | 20XX/1/20 16:27 |
| 4 | A001 | 20XX/1/20 23:58 | 20XX/1/21 0:01 |

# SQL クエリーで実践

## データを確認

```sql
Select Id, TimeStamp FROM DataTable
```

| Id   | TimeStamp      |
|------|:---------------|
| A001 | 20XX-01-20 07:55:00 |
| A001 | 20XX-01-20 07:56:00 |
| A001 | 20XX-01-20 07:57:00 |
| A001 | 20XX-01-20 13:41:00 |
| A001 | 20XX-01-20 13:42:00 |
| A001 | 20XX-01-20 16:27:00 |
| A001 | 20XX-01-20 23:58:00 |
| A001 | 20XX-01-20 23:59:00 |
| A001 | 20XX-01-21 00:00:00 |
| A001 | 20XX-01-21 00:01:00 |

## StartDateTime の検出

### Timestamp +1 のデータを作成 

```sql
SELECT Id, TimeStamp, DATEADD(MINUTE,1,Timestamp) AS TimeStamp_plus1 FROM DataTable
```

| Id | TimeStamp | TimeStamp_plus1 |
|----|:--------------|:--------------|
| A001 | 20XX-01-20 07:55:00 | 20XX-01-20 07:56:00 |
| A001 | 20XX-01-20 07:56:00 | 20XX-01-20 07:57:00 |
| A001 | 20XX-01-20 07:57:00 | 20XX-01-20 07:58:00 |
| A001 | 20XX-01-20 13:41:00 | 20XX-01-20 13:42:00 |
| A001 | 20XX-01-20 13:42:00 | 20XX-01-20 13:43:00 |
| A001 | 20XX-01-20 16:27:00 | 20XX-01-20 16:28:00 |
| A001 | 20XX-01-20 23:58:00 | 20XX-01-20 23:59:00 |
| A001 | 20XX-01-20 23:59:00 | 20XX-01-21 00:00:00 |
| A001 | 20XX-01-21 00:00:00 | 20XX-01-21 00:01:00 |
| A001 | 20XX-01-21 00:01:00 | 20XX-01-21 00:02:00 |

### Timestamp +1 に 含まれない Timestamp の行を抽出、GroupId を付与

```sql
SELECT Id, TimeStamp as StartTime, ROW_NUMBER() OVER(ORDER BY TimeStamp) AS GroupId 
FROM DataTable
WHERE Timestamp NOT IN (
	SELECT DATEADD(MINUTE,1,TimeStamp)
	FROM DataTable
	)
```

| Id | StartTime | GroupId |
|----|:--------------|:-------|
| A001 | 20XX-01-20 07:55:00 | 1 |
| A001 | 20XX-01-20 13:41:00 | 2 |
| A001 | 20XX-01-20 16:27:00 | 3 |
| A001 | 20XX-01-20 23:58:00 | 4 |


## EndDateTime の検出

### Timestamp -1 のデータを作成 

```sql
SELECT Id, TimeStamp, DATEADD(MINUTE,-1,Timestamp) AS TimeStamp_minus1 FROM DataTable
```

| Id | TimeStamp | TimeStamp_minus1 |
|----|:--------------|:--------------|
| A001 | 20XX-01-20 07:55:00 | 20XX-01-20 07:54:00 |
| A001 | 20XX-01-20 07:56:00 | 20XX-01-20 07:55:00 |
| A001 | 20XX-01-20 07:57:00 | 20XX-01-20 07:56:00 |
| A001 | 20XX-01-20 13:41:00 | 20XX-01-20 13:40:00 |
| A001 | 20XX-01-20 13:42:00 | 20XX-01-20 13:41:00 |
| A001 | 20XX-01-20 16:27:00 | 20XX-01-20 16:26:00 |
| A001 | 20XX-01-20 23:58:00 | 20XX-01-20 23:57:00 |
| A001 | 20XX-01-20 23:59:00 | 20XX-01-20 23:58:00 |
| A001 | 20XX-01-21 00:00:00 | 20XX-01-20 23:59:00 |
| A001 | 20XX-01-21 00:01:00 | 20XX-01-21 00:00:00 |

### Timestamp -1 に 含まれない Timestamp の行を抽出、GroupId を付与

```sql
SELECT Id, TimeStamp as EndTime, ROW_NUMBER() OVER(ORDER BY TimeStamp) AS GroupId 
FROM DataTable
WHERE Timestamp NOT IN (
	SELECT DATEADD(MINUTE,-1,TimeStamp)
	FROM DataTable
	)
```

| Id | StartTime | GroupId |
|----|:--------------|:-------|
| A001 | 20XX-01-20 07:57:00 | 1 |
| A001 | 20XX-01-20 13:42:00 | 2 |
| A001 | 20XX-01-20 16:27:00 | 3 |
| A001 | 20XX-01-21 00:01:00 | 4 |

## StartDateTime と EndDateTime の JOIN

```sql
SELECT T1.Id,StartTime,EndTime

FROM(
	SELECT Id, TimeStamp as StartTime, ROW_NUMBER() OVER(ORDER BY TimeStamp) AS GroupId 
	FROM DataTable
	WHERE Timestamp NOT IN (
		SELECT DATEADD(MINUTE,1,TimeStamp)
		FROM DataTable
		)
	)
	AS T1

JOIN(
	SELECT Id, TimeStamp as EndTime, ROW_NUMBER() OVER(ORDER BY TimeStamp) AS GroupId 
	FROM DataTable
	WHERE Timestamp NOT IN (
		SELECT DATEADD(MINUTE,-1,TimeStamp)
		FROM DataTable
		)
	)
	AS T2

ON T1.GroupId=T2.GroupId
```

| Id | StartTime | EndTime |
|----|:--------------|:--------------|
| A001 | 20XX-01-20 07:55:00 | 20XX-01-20 07:57:00 |
| A001 | 20XX-01-20 13:41:00 | 20XX-01-20 13:42:00 |
| A001 | 20XX-01-20 16:27:00 | 20XX-01-20 16:27:00 |
| A001 | 20XX-01-20 23:58:00 | 20XX-01-21 00:01:00 |