SQL_状態変化時に記録されるテーブルの時間集計を行う方法

前提

SQL Serverのデータベースで、テーブルに以下のように
状態変化の記録が格納されています。

■テーブル内容の例

ID Time_Stamp 状態
1 2023-01-26 15:30:00 A
2 2023-01-26 16:30:00 B
3 2023-01-27 08:00:00 C
4 2023-01-27 10:00:00 A
5 2023-01-28 10:00:00 A
6 2023-01-30 08:00:00 C

※まれにID4→ID5のように状態が変わらなくても
記録されることがあります。
※例ではキリのいい分単位で書いていますが
実際には秒単位で記録されます。

実現したいこと

上記を状態ごとに時間を積算させた結果を
HH:MM:SSで出力するSQLクエリを作りたいのですが
どこから始めればいいのかわかりません。

■実現したい集計結果
A 47:00:00
B 15:30:00
C 02:00:00

Cの状態はID3とID6がありますが
ID6の後は次の状態がわからないので
ID4→ID5の2時間だけとなります。

試したこと

単位時間で必ず記録されるのであれば
単純に状態ごとに合計を集計すればよいのですが
とっかかりだけでもアドバイスいただけませんでしょうか。。

回答2件

ベストアンサー

以下のようなSQLで実現できるかと思います。

SQL
1SELECT 
2  状態, 
3  CAST(SUM(sec)/3600 AS VARCHAR) + ':' + FORMAT((SUM(sec)%3600)/60, '00') + ':' + FORMAT((SUM(sec)%3600)%60, '00')
4FROM (
5  SELECT 状態, DATEDIFF(SECOND, Time_Stamp, LEAD(Time_Stamp) OVER (ORDER BY Time_Stamp)) AS sec
6  FROM T
7) AS X
8GROUP BY 状態

投稿2023/02/01 15:08

neko_the_shadow

総合スコア1802

回答へのコメント

infra_se_124

2023/02/10 03:44

理屈はわかりませんが、うまくいきました！ありがとうございます。意味についてはこれから勉強します。。

lead(time_stamp) over (order by id)で次行のtime_stampを参照できるから、
leadからtime_stampを差し引いてできる滞在時間列を持つビュー(CTEでも同じ)に対して
group by 状態でsum(滞在時間)を取って、
あとはsumをhhmmssに変換する。

投稿2023/01/31 08:30