質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.45%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

統合言語クエリ

統合言語クエリ(LINQ)は、様々な種類のデータ集合に対して標準化された方法でデータを問い合わせることを可能にする、Microsoft .NETの要素です。

Q&A

解決済

2回答

919閲覧

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

infra_se_124

総合スコア2

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

統合言語クエリ

統合言語クエリ(LINQ)は、様々な種類のデータ集合に対して標準化された方法でデータを問い合わせることを可能にする、Microsoft .NETの要素です。

0グッド

1クリップ

投稿2023/01/31 07:28

前提

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

試したこと

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

以下のような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

総合スコア2273

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

infra_se_124

2023/02/10 03:44

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

0

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

投稿2023/01/31 08:30

matukeso

総合スコア1608

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

infra_se_124

2023/02/01 01:18

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.45%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問