T-SQL:日時を比較して最新のデータを取得したい

Question

お世話になります。

テーブルにint型でUNIX時間を表すデータが大量にあり、
その中から、ある一定期間のデータを引っこ抜きたいと思っています。
しかし、中には年月日時分までは同じで秒だけ違うデータが混入しており、
そういったデータに関しては最新１件（時間が新しい方）のデータを抽出したいです。

テーブルの構成は時間がint、列Aはnumeric(6,2)、列Bはnumeric(6,2)です
下記のサンプルテーブルではデータ数は少ないですが、このような形で大量のデータが
テーブルに保存されています。

--test_tbl
|時間|列A|列B|
|1520905810|5.5|6.6|
|1521003000|1.1|2.2|
|1521003010|3.3|4.4|

現状のSQLは

SELECT 
	   (CONVERT(datetime,FORMAT(DATEADD(S,[時間],'19700101 09:00:00'),'yyyy/MM/dd hh:mm:ss'))), 
　　　　列A,
       列B 

FROM test_tbl
WHERE  CONVERT(datetime,FORMAT(DATEADD(S,[時間],'19700101 09:00:00'),'yyyy/MM/dd hh:mm:ss')) BETWEEN '2018-03-01 00:00:00' AND '2018-03-20 11:59:00'

実行結果
--test_tbl
|時間|列A|列B|
|2018-03-13 10:50:10|5.5|6.6|
|2018-03-14 13:50:00|1.1|2.2|
|2018-03-14 13:50:10|3.3|4.4|

こんな感じで、UNIX時間をdatetime型に変換して一定の期間に絞り込むところまでは
できました。ただ、依然として秒のみが違うデータも混入しているので、そこをどうすればいいかがわからない状態です。

最終的にはテーブルが下記のような形になってほしいです。
--test_tbl
|時間|列A|列B|
|2018-03-13 10:50:10|5.5|6.6|
|2018-03-14 13:50:10|3.3|4.4|

拙い説明ではありますが、皆様の知恵をお貸しください。

Answer 1

あんまり効率いいクエリじゃないけどrow_number
秒の切り捨てはfloor(時間/100)で。
↑これだめやねちゃんとformatしましたm(_ _;)m→floor(時間/60)ならいいんかな？

with test as (
	select 1520905810 as 時間,'5.5' as 列A,'6.6' as 列B
	union all
	select 1521003000,'1.1','2.2'
	union all
	select 1521003010,'3.3','4.4'
)
select *
from (
	select *,FORMAT(DATEADD(S,[時間],'19700101 09:00:00'),'yyyy/MM/dd hh:mm:ss') as 日時
		-- 秒切り捨ての時間降順
		,row_number() over(partition by floor(時間/60) order by 時間 desc) as 順番
	from test
) as X
where X.順番=1

Comments

[sazi]

>sousuke さん
floor(時間/60)なら大丈夫じゃないでしょうか。
確証が持てなかったので、私はformat()にしましたが。
尚、format()での時間に関する書式指定はhhだと12時間表記になるので、HHの指定である必要があります。

[sousuke]

ホントですね。コピペして使ってました…ご指摘どうも～ｂ

Answer 2

同じ時分内での最大とする方法もありますが、Window関数の方がコストが低いのでLead()を利用しています。

select *
from (
	select *, lead(日時) over (partition by format(日時, 'yyyy/MM/dd HH:mm') order by 日時) as 次秒
	from (
		SELECT *,DATEADD(S,[時間],'19700101 09:00:00') as 日時
		FROM test_tbl
	) step1
    WHERE  日時 >= '2018-03-01' AND 日時 < '2018-03-21'
) step2
where 次秒 is null

Comments

[dera]

ありがとうございます。やりたいことができました。
私が知らない関数ばかりなのですが、lead関数で現在の行から次の行へアクセスしてるのは調べてわかったのですが、そのあとのover句とpartition by で何をしているのかがイマイチわかりません。
後、UNIX時間からdatetime型には変換できてなかったのは何故でしょうか・・。

[sazi]

partition byで指定した項目が区切りになります。
同じ時分で区切って、その中でソートするってことです。

[dera]

なるほど。そういうことだったのですね。とても勉強になりました。ありがとうございます！

[sazi]

>後、UNIX時間からdatetime型には変換できてなかったのは何故でしょうか・・。
とは、どこの記述について言われてます？

[dera]

日時をformatしているので、てっきりこのSQLを実行した後は、UNIX時間の形からdatetime型に変換された形で表示されるものとばかり思っていました。

[sazi]

ああ、失礼しました。
質問でのSQLの記述上、日付型に直しているのが冗長に見えたので変更していましたが、
CAST()を忘れていました。
以下に変更下さい。
>DATEADD(S,[時間],'19700101 09:00:00' )
DATEADD(S,[時間],CAST('19700101 09:00:00' AS DATETIME))