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

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

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

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

統合言語クエリ

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

Q&A

解決済

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

infra_se_124
infra_se_124

総合スコア2

SQL Server

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

統合言語クエリ

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

2回答

0グッド

1クリップ

187閲覧

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

試したこと

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

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

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

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

下記のような質問は推奨されていません。

  • 質問になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

適切な質問に修正を依頼しましょう。

回答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

総合スコア1802

下記のような回答は推奨されていません。

  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

infra_se_124

2023/02/10 03:44

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

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

総合スコア1474

下記のような回答は推奨されていません。

  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

infra_se_124

2023/02/01 01:18

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

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

ただいまの回答率
86.02%

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

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

質問する

関連した質問

同じタグがついた質問を見る

SQL Server

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

統合言語クエリ

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