LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@annie(annie)

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

Last updated at Posted at 2021-01-21

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?