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

Question

前提

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時間だけとなります。

試したこと

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

Answer 1

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

SELECT 
  状態, 
  CAST(SUM(sec)/3600 AS VARCHAR) + ':' + FORMAT((SUM(sec)%3600)/60, '00') + ':' + FORMAT((SUM(sec)%3600)%60, '00')
FROM (
  SELECT 状態, DATEDIFF(SECOND, Time_Stamp, LEAD(Time_Stamp) OVER (ORDER BY Time_Stamp)) AS sec
  FROM T
) AS X
GROUP BY 状態

Comments

[infra_se_124]

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

Answer 2

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

Comments

[infra_se_124]

ありがとうございます。
試してみます！