SQL Serverでひと塊のデータの最後だけを並べたい

Question

お世話になります。
SQLのレコードで

Date(年月日時分秒) Code
2018/01/01 00:00:00 0
2018/01/01 00:00:10 A
2018/01/01 00:00:20 0
2018/01/01 00:00:40 0
2018/01/01 00:00:50 A
2018/01/01 00:01:00 3
2018/01/01 00:01:10 4
2018/01/01 00:01:20 5
2018/01/01 00:01:30 6
2018/01/01 00:01:40 7
2018/01/01 00:01:50 8
2018/01/01 00:02:00 9
2018/01/01 00:02:10 1
2018/01/01 00:02:20 2
～～～～～～～～～～～～
2018/01/02 05:05:00 0
2018/01/02 05:05:30 A
2018/01/02 05:05:55 0
2018/01/02 05:06:00 1

というようなデータで、ひと塊になったCode 0の最後のレコード(この場合2018/01/01 00:00:40 0と2018/01/02 05:05:55 0の2件)を抽出したいと思っています。
ルールとして、Code 0が出てくるタイミングは不明、ただし初めのCode 0が出て来た時刻のから次の00秒までに最後のCode 0が登場するがCode 0が連続するとは限らない、Dateの開始と終了のみを指定して上記レコードを取得するというものです。
無能非才な自分にはどう頭を捻ってもSQLが出てきません。そもそもSQLでそのような記述ができるものかも分かりません。
その際のクエリ、そもそもそのような記述ができるものなのか、御教示ください。
よろしく御願いします。

Comments

[sousuke]

sql serverのバージョンを教えてください。

[sollalice]

SQL server2014です。よろしく御願いします。

[yambejp]

「ひと塊になったCode 0の最後のレコード」の意図がいまいちわからないのですが、たんに日付ごとにcode0の中で最大の時間を得たいのでしょうか？

[sollalice]

日付で、というよりどのタイミングで来るかが分からない=一日に2回Code 0の塊が来る可能性もあり、各々の最後のCode 0を取得したいです。よろしく御願いします。

[yambejp]

すみません、私が思い違いをしているような気もしますが、そうなると2018/01/01 00:00:00や2018/01/02 05:05:00 はなぜ対象になっていないのでしょうか？

[sollalice]

質問の意図として、Code 0は機械の校正で、予備校正の後、本校正を行います。本校正というのがひと塊の中の最後のCode 0なので、ここがデータ計算の始点となる部分という事で捕まえたいと考えています。よろしく御願いします。

Answer 1

起点となるのは正分（ss=00)のcode='0'で、一分以内のcode='0'のもので最終
という条件として以下

create table tbl(date_time datetime, code varchar(1));
insert into tbl values
 ('2018/01/01 00:00:00',  '0')
,('2018/01/01 00:00:10',  'A')
,('2018/01/01 00:00:20',  '0')
,('2018/01/01 00:00:40',  '0')
,('2018/01/01 00:00:50',  'A')
,('2018/01/01 00:01:00',  '3')
,('2018/01/01 00:01:10',  '4')
,('2018/01/01 00:01:20',  '5')
,('2018/01/01 00:01:30',  '6')
,('2018/01/01 00:01:40',  '7')
,('2018/01/01 00:01:50',  '8')
,('2018/01/01 00:02:00',  '9')
,('2018/01/01 00:02:10',  '1')
,('2018/01/01 00:02:20',  '2')
,('2018/01/02 05:05:00',  '0')
,('2018/01/02 05:05:30',  'A')
,('2018/01/02 05:05:55',  '0')
,('2018/01/02 05:06:00',  '1')
;

select * from tbl
where   code='0'
   and  date_time in (
          select max(t1.date_time)
          from tbl t1 inner join (
              select date_time start_range, dateadd(mi,1, date_time) end_range 
              from tbl
              where format(date_time,'ss')='00' and code='0'
            ) as t2
            on t1.date_time>t2.start_range and t1.date_time<t2.end_range
          where t1.code='0'
          group by t2.start_range, t2.end_range
        )

Answer 2

「ひと塊になったCode 0の最後のレコード」の定義が難解に見えます。
一応「前のcode0から1分未満で再度でたcode0はそのグループに属する」とします。

正直合っているのかどうかわからない上に効率の悪いクエリにならざるを得ません。
ほかにデータがあれば利用したいところです…

select *
from (
	select *
		,case when lag(日時) over(order by 日時)>dateadd(MINUTE,-1,日時) then 1 else 0 end as 前
		,case when isnull(lead(日時) over(order by 日時),日時+1)>dateadd(MINUTE,1,日時) then 1 else 0 end as 後
	from test
	where コード='0'
) as A
where 前=1 and 後=1
order by 日時

多分これでいけると思いますが…仕組みはcode=0抽出後
・日時順で見た場合、前の日時が現在の日時の１分未満ならば1を立てる
・日時順で見た場合、次の日時が現在の日時の１分以上ならば1を立てる
・両方１のもののみ表示

Comments

[sousuke]

いつの間にか１日制限がありそうです…１日制限があれば１分制限より大分楽なんですが。

[sollalice]

やりたいことが、校正完了時のデータを基準にして、以降のデータと演算するというもので、設備の関係上、一日に何回校正がかかるか分からない、校正1回目～測定、校正2回目～測定、校正x回目～測定というデータの並びなので、校正を捕まえると、どのデータからどのデータまでが計算対象かが決まる、という意図です。

[sousuke]

校正完了のデータは列にないのですか？「１日に何回校正がくるかわからない」とありますが
１日の中で１分以上の離れたデータが２箇所以上存在した場合いくつ取ればいいのですか？１日に複数回取りたいデータがあるのかどうかが示された方がよろしいかと。また必ず連続するのかどうかが気になります。
連続しないデータで必要なものがあるならば自分のロジックは破綻します。

[sollalice]

校正完了というのがCode 0とCode Aの交互(測定器1と測定器2の校正)で、その交互に出たCode 0(とCode A、簡単に考える為に今回Code Aの求め方は同じロジックと考えているので質問にあげていませんでした)の最後が校正完了です(校正完了の特別コード等はありません)。何度か測定をしているうちに誤差が溜まるのでそのために校正をする関係で一日に複数回校正をする可能性があります。校正1回目～測定、校正2回目～校正2回目以降の測定、校正x回目～校正x回目以降の測定という流れなので、校正1回目～測定で1日終わることもあれば、一日x回の校正が掛かる可能性もあります。校正、測定、2回目校正、2回目校正以降の測定、3回目校正、3回目校正以降の測定データが連続してレコードに入ってくるので、それを校正から2回目の校正がかかるまでのデータひとまとめ、2回目校正から3回目校正がかかるまでのデータひとまとめ、x回目校正からｘ+1回目校正がかかるまでのデータひとまとめと分離したいのです。
校正が約1分弱かかり(1分以内に連続するCode 0が校正完了で測定基準)、その測定基準から次の測定機順までのデータを収集、演算したいと考えています。

[sousuke]

つまり1日に複数の「ひと塊」はあり得て、それでその「ひと塊」の最後を取得したいということですね？
一回で完了することはあるかどうかに関してはどうなのですか？

[sollalice]

必ず連続するものとして扱って頂いて大丈夫です。

[sousuke]

仮に「2018/01/01 00:01:10 0」というデータがある場合これは「2018/01/01 00:00:00 0」からの連続とみなせますか？
それと『1日に複数の「ひと塊」はあり得る』に関しては明言いただかないと私含め他の回答者が先に進めません。

[sollalice]

一日に複数のひと塊はありえます。また、ひと塊が連続して発生することはありません。よろしく御願いします。

Answer 3

やりたいことは、
コードが0のレコードで日付毎に最後の時間のレコードを取得したい
ということであっていますか。

あまり複雑なSQLでないのであれば、
疑似的な日付（yymmdd）の列を作って、
日付（yymmdd）でグループ化して最大の日時（yyyy/mm/dd hh:mm:ss）を取得するのが、
一般的で分かりやすいかと思います。

SELECT MAX(Date) FROM (
  SELECT Date, CONVERT(NVARCHAR, Date, 12) AS yymmdd FROM table WHERE Code = 0
)
GROUP BY yymmdd;

※未実行です