SQL Server2014 集計方法について

Question

現在、bit型フィールドとDatetime型フィールドを持ったテーブルの集計方法は

どのような方法が有るでしょうか？？

-----------------------------テーブル-----------------------------------

行フィールド１フィールド２フィールド３フィールド４

1　true｜false｜true｜2000/01/01 10:00:00.000
2　true｜true｜false｜2000/01/01 10:00:01.000
3　false｜false｜ｆalse｜2000/01/01 10:00:01.500
4　true｜false｜false｜2000/01/01 10:00:03.000
5　false｜true｜true｜2000/01/01 10:00:0４.200
6　true｜false｜false｜2000/01/01 10:00:0５.000

---------------------------------------------------------------------------------------------

※見づらくてすみません。。

①フィールド１のtrueからfalseまでの期間を集計したい

例：行１〜行３の期間　１．５sec  ＋　行4〜行５の期間 1.0sec =　2.5secを得たいです。

②フィールド１のtrueからfalseの間にフィールド２がtrueになっていたら、その期間を集計したい

例：フィールド１の行１からフィールド２の行２の期間 1.0sec　＋

　　フィールド１の行４からフィールド２の行５の期間 1.２sec　＝　２．２ｓｅｃを得たいです。

上記の様な集計方法は可能でしょうか？？

以上、よろしくお願いします。　

Answer 1

＞例：行１〜行３の期間　１．５sec  ＋　行4〜行５の期間 1.0sec =　2.5secを得たいです。

は、以下の誤りですよね？

「例：行１〜行３の期間　１．５sec  ＋　行4〜行５の期間 1.2sec =　2.7secを得たいです。」

とりあえず、私が実現すべき仕様を思い違いしていない限り、①は以下で良いと思います。

select datediff
		(ms,
		 t.フィールド4,
		 (select top(1) t2.フィールド4
		  from 集計テスト t2
		  where t2.フィールド4 > t.フィールド4 and t2.フィールド1 = 0
		  order by t2.フィールド4)			
		)
 from 集計テスト t
 where t.フィールド1 = 1
	and isnull((select top(1) t2.フィールド1
	            from 集計テスト t2
				where t2.フィールド4 < t.フィールド4
				order by t2.フィールド4 desc)
				, 0) = 0
	and exists(select * from 集計テスト t2
			   where t2.フィールド4 > t.フィールド4 and t2.フィールド1 = 0)
 order by t.フィールド4

②については、もうちょっと仕様を教えて下さい。フィールド１がtrueからfalseの間に、フィールド２で複数のtrueが発生する可能性があるのでしょうか？　もし、そうであれば、その場合どのように集計すれば良いのでしょうか？　フィールド１のtrueからfalseの期間における最初のtrueから、フィールド２の最後のtrueまでの差のみを対象にすれば良いのでしょうか？

（追記）
合計を出すなら以下のSQLになります。

select sum(差) from (
select datediff
		(ms,
		 t.フィールド4,
		 (select top(1) t2.フィールド4
		  from 集計テスト t2
		  where t2.フィールド4 > t.フィールド4 and t2.フィールド1 = 0
		  order by t2.フィールド4)			
		) 差
 from 集計テスト t
 where t.フィールド1 = 1
	and isnull((select top(1) t2.フィールド1
	            from 集計テスト t2
				where t2.フィールド4 < t.フィールド4
				order by t2.フィールド4 desc)
				, 0) = 0
	and exists(select * from 集計テスト t2
			   where t2.フィールド4 > t.フィールド4 and t2.フィールド1 = 0)
) tt



---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 2

trapemiya様

返信遅くなりすみません。 画像投稿に手間取りました。。。。

回答ありがとうございます。

>「例：行１〜行３の期間　１．５sec  ＋　行4〜行５の期間 1.2sec =　2.7secを得たいです。」

⇒指摘ありがとうございます。私の間違えです。

>②については、もうちょっと仕様を教えて下さい。フィールド１がtrueからfalseの間に、フィールド２で複数のtrueが発生する可能>性があるのでしょうか？　もし、そうであれば、その場合どのように集計すれば良いのでしょうか？　フィールド１のtrueからfalseの期>間における最初のtrueから、フィールド２の最後のtrueまでの差のみを対象にすれば良いのでしょうか？

⇒フィールド１がtrueからfalseの間に、フィールド２で複数のtrueが発生する可能性があります。

その際は、フィールド２の最初のTRUEの立ち上がりまでの時間を、集計したいと考えています。

イメージのタイムチャートを書いてみました。

最終的には、下記の様に、赤矢印部と青矢印部を集計したいと考えています。

一度、ご提示頂いたSQL分を、私自身でも内容を確認しながらトライしてみます！

Answer 3

わかりやすい図をありがとうございます。②に関して理解できました。
ただ、この図であれば、TRUEとFALSEは必ずテレコになるはずですが、最初のご質問文における例では、フィールド１でTRUEが２度連続して現れているところがあります。もしこれが誤りであれば、私が回答したSQL文はもう少し単純化できるように思いますが、いかがでしょうか？

---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 4

現在のレコード追加の条件だと、連続で現れております。

下図の様なイメージです。

この様にbit型のフィールドのどれかが変化したタイミングでインサートされております。

PS.SQL Server Management StudioでSQLを確認させて頂きましたところ、

　　思っていた通りの集計をしてくれていました。ありがとうございます。

Answer 5

>この様にbit型のフィールドのどれかが変化したタイミングでインサートされております。

了解しました。
考えてみればわかりそうなことですね。失礼いたしました。
私は今、SQL Serverが扱える環境にいませんので、明日、②に関して私も考えてみようと思います。

---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 6

考えてみました。おそらく以下でいいんじゃないでしょうか？

select
		datediff
		(ms,

		 t.フィールド4,
		
		 -- フィールド1の時刻より大きい最初のフィールド1がfalseとなる時刻
		 (select top(1) t2.フィールド4
		  from 集計テスト t2
		  where t2.フィールド4 > t.フィールド4 and t2.フィールド1 = 0
		  order by t2.フィールド4)			
		
		) as '①',

		 datediff
		(ms,
		
		 t.フィールド4,

		 -- フィールド1の時刻より大きい最初のフィールド2がtrueとなる時刻
		 (select top(1) t2.フィールド4
		  from 集計テスト t2
		  where t2.フィールド4 > t.フィールド4 and t2.フィールド2 = 1
		  order by t2.フィールド4)			
		
		) as '②',

		 datediff
		(ms,

		 -- フィールド1の時刻より大きい最初のフィールド2がtrueとなる時刻。②と同じ。
		 (select top(1) t2.フィールド4
		  from 集計テスト t2
		  where t2.フィールド4 > t.フィールド4 and t2.フィールド2 = 1
		  order by t2.フィールド4),	

		 -- フィールド1の時刻より大きい最初のフィールド1がfalseとなる時刻。①と同じ。
		 (select top(1) t2.フィールド4
		  from 集計テスト t2
		  where t2.フィールド4 > t.フィールド4 and t2.フィールド1 = 0
		  order by t2.フィールド4)	
		  			
		) as '③'

 from 集計テスト t

 where 
	-- フィールド1がtrue
	t.フィールド1 = 1

 	-- フィールド1がtrueでかつその一つ前のフィールド1がfalse
	and isnull((select top(1) t2.フィールド1
	            from 集計テスト t2
				where t2.フィールド4 < t.フィールド4
				order by t2.フィールド4 desc)
				, 0) = 0
	-- フィールド1がtrueでかつ、それ以降にフィールド1がfalseのデータが存在する。
	and exists(select * from 集計テスト t2
			   where t2.フィールド4 > t.フィールド4 and t2.フィールド1 = 0)

 order by t.フィールド4

（追記）
SQL Serverのtop句はこういう時にかなり便利に使えます。他のデータベースだと無かったりするので、SQLがもっと複雑化してしまいます。

---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 7

色々と考えて頂きありがとうございます。

これからは、このSQL文を参考に自分自身で考えて行きたいと思います。

しかし、また分からない時はご指導下さい。

Answer 8

SQLServerの分析関数の使用例の
「SQLServer2012のTransact-SQLの新機能」
www.geocities.jp/oraclesqlpuzzle/sqlserver2008-sql1-olap.html#3
を見ながら、Oracle11gR2で作ってみました :-)

SQLServer2014なら、多分動きます。

with t(Field1,Field2,Field4) as(
select 'T','F',TimeStamp '2000-01-01 10:00:00.000' from dual union
select 'T','T',TimeStamp '2000-01-01 10:00:01.000' from dual union
select 'F','F',TimeStamp '2000-01-01 10:00:01.500' from dual union
select 'T','F',TimeStamp '2000-01-01 10:00:03.000' from dual union
select 'F','T',TimeStamp '2000-01-01 10:00:04.200' from dual union
select 'T','F',TimeStamp '2000-01-01 10:00:05.000' from dual),
tmp1 as(
select Field1,Field2,Field4,
min(case Field1 when 'F' then Field4 end)
over(order by Field4 Rows BetWeen 1 Following and UnBounded Following) as "集計1のTo",
min(case Field2 when 'T' then Field4 end)
over(order by Field4 Rows BetWeen 1 Following and UnBounded Following) as "集計2のTo",
Lag(Field1) over(order by Field4) as LagField1
from t)
select Field1,Field2,Field4 as "集計1と2のFrom","集計1のTo","集計2のTo"
  from tmp1
 where Field1 = 'T' and (LagField1 is null or LagField1 = 'F')
   and "集計1のTo" is not null
order by Field4;

Field1  Field2  集計1と2のFrom  集計1のTo    集計2のTo
------  ------  --------------  -----------  -----------
T       F       10:00:00.00     10:00:01.50  10:00:01.00
T       F       10:00:03.00     10:00:04.20  10:00:04.20

Answer 9

さらに、
標準SQLで規定されてる、Lead関数でのIgnore Nullsが
SQLServerの将来のバージョンでサポートされるのを楽しみにしつつ、
Oracle11gR2で作ってみました :-)

with tmp1 as(
select Field1,Field2,Field4,
Lead(case Field1 when 'F' then Field4 end Ignore Nulls) over(order by Field4) as "集計1のTo",
Lead(case Field2 when 'T' then Field4 end Ignore Nulls) over(order by Field4) as "集計2のTo",
Lag(Field1) over(order by Field4) as LagField1
from t)
select Field1,Field2,Field4 as "集計1と2のFrom","集計1のTo","集計2のTo"
  from tmp1
 where Field1 = 'T' and (LagField1 is null or LagField1 = 'F')
   and "集計1のTo" is not null
order by Field4;

結果は、前の投稿と同じなので省略

Answer 10

AketiJyuuzou様

回答ありがとうございます。

とても参考になります。