センサーが物体を検出したときのみ記録される、そういった不定期に連続するログの開始と終了を 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 クエリーで実践

データを確認

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 のデータを作成

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 を付与

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 のデータを作成

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 を付与

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

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

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