【SQL】カウンターの値がリセットされた直前の値を取得

Question

実現したいこと

SQL Server 2019にて記録している生産カウンタのテーブルがある。リセット前の値とその時の日時を全て取得するSQLを考えたい。

前提

・テーブル名は「ProdCounter」
・フィールドは「TimeStamp （Date Time)）」と「Counter （Integer)」
・24時間、365日、毎分「TimeStamp」と「Counter」の値を記録する。
・シフト勤務の入れ替え時にカウンタはリセットされる。一日につき2-3回。
・記録間隔は1分なので、リセット後の次のCounterの記録は「ゼロ」とは限らない。
・リセット直前のTimeStampとCounterの値を全て取得したい。

例）
TimeStamp Counter
2023/06/19 14:50 164
2023/06/19 14:49 136
2023/06/19 14:48 87
2023/06/19 14:47 37
2023/06/19 14:46 15458
2023/06/19 14:45 12033
2023/06/19 14:44 10111
2023/06/19 14:43 8210
2023/06/19 14:42 5232
2023/06/19 14:41 3589
2023/06/19 14:40 1258
2023/06/19 14:39 200
2023/06/19 14:38 25
2023/06/19 14:37 9
2023/06/19 14:36 14958
2023/06/19 14:35 11056
2023/06/19 14:34 9325
2023/06/19 14:33 6899
2023/06/19 14:32 4568
2023/06/19 14:31 2298
2023/06/19 14:30 875
2023/06/19 14:29 268
2023/06/19 14:28 61
2023/06/19 14:27 14553
2023/06/19 14:26 14553

希望結果）
TimeStamp Counter
2023/06/19 14:46 15458
2023/06/19 14:36 14958
2023/06/19 14:27 14553

発生している問題・エラーメッセージ

MAX(Counter)などを使ってみましたが、うまくできませんでした。

該当のソースコード

試したこと

VBAで実現できましたが、10,000レコードくらいになるとかなり処理時間がかかるので、SQLでできないかと思った次第です。よろしくお願いいたします。

補足情報（FW/ツールのバージョンなど）

SQL Server 2019

Comments

[sazi]

そのVBAのコードを質問に追記された方が、質問内容が明確になってが良いかと思います。

[HRNK]

sazi様、コメントありがとうございました。色々バタバタしていて、勘違いの補足を書き込んでおりました。VBAで実現できたのは、エクセルのシートで必要なデータを絞り込んで、SQLサーバからそのデータをシートに取り込むスクリプトでした。リセット前の値を取得するものではありません。このスクリプトが結構遅かったので、SQL側で処理できないかと相談差し上げております。混乱を招き申し訳ありません。

Answer 1

TimeStamp が必ず連続しているのであればずらして自己結合すればよいだけでは？
(以下MySQLのサンプル)

select * from ProdCounter as t1
inner join ProdCounter as t2 on
t1.TimeStamp =t2.TimeStamp -interval 1 minute
and t1.Counter >t2.Counter 

※連続性のあるデータなら時間で差分を取るよりも、auto_incrementでカウントアップしていき
その差でやる方が楽かもしれません

Comments

[HRNK]

yambejp様、なるほどです。考え方はわかりましたが、何故か「-interval 1 minute」がSyntaxエラーになりました。

Answer 2

1分後のCounterよりも値が大きい行を取得したいという認識で正しいですか? であれば、この要件をそのままSQLに変換することができます。

SELECT *
FROM ProdCounter AS P1
WHERE Counter > (
  SELECT Counter
  FROM ProdCounter AS P2
  WHERE DATEADD(minute, 1, P1.TimeStamp) = P2.Timestamp
);

個人的にはWindow関数を利用する方法が好みです。

SELECT TimeStamp, Counter
FROM (
  SELECT *, LEAD(Counter) OVER (ORDER BY TimeStamp) AS NextCounter
  FROM ProdCounter
) AS X
WHERE Counter > NextCounter

Comments

[HRNK]

neko_the_shadow様、ありがとうございました。どちらも問題ありませんでしたが、TimeStampの間隔がが1分以上あるところがあり、NextCounterのほうが本件に向いているようです。

Answer 3

手元にSQLServerがないので、PostgreSQLでの結果です。同じようなことはできる、のではないかと。

1. 時刻順に並べる
2. ひとつ次のcounterを連結する
3. 現在のcounterと次のcounterを比較して...

with 
aa as (
  select * from test order by time
),
bb as (
  select 
	*,
	lead(counter) over (order by time) as nextCounter
  from aa
)
select * from bb
  where
   counter > nextCounter
  or
   nextCounter is null
 ;

time	counter	nextcounter
2023-06-19 14:27:00.000	14553	61
2023-06-19 14:36:00.000	14958	9
2023-06-19 14:46:00.000	15458	37
2023-06-19 14:50:00.000	164	null

create table test(time timestamp, counter int);
insert into test
  values
  ('2023/06/19 14:50', 164),
  ('2023/06/19 14:49', 136),
  ('2023/06/19 14:48', 87),
  ('2023/06/19 14:47', 37),
  ('2023/06/19 14:46', 15458),
  ('2023/06/19 14:45', 12033),
  ('2023/06/19 14:44', 10111),
  ('2023/06/19 14:43', 8210),
  ('2023/06/19 14:42', 5232),
  ('2023/06/19 14:41', 3589),
  ('2023/06/19 14:40', 1258),
  ('2023/06/19 14:39', 200),
  ('2023/06/19 14:38', 25),
  ('2023/06/19 14:37', 9),
  ('2023/06/19 14:36', 14958),
  ('2023/06/19 14:35', 11056),
  ('2023/06/19 14:34', 9325),
  ('2023/06/19 14:33', 6899),
  ('2023/06/19 14:32', 4568),
  ('2023/06/19 14:31', 2298),
  ('2023/06/19 14:30', 875),
  ('2023/06/19 14:29', 268),
  ('2023/06/19 14:28', 61),
  ('2023/06/19 14:27', 14553),
  ('2023/06/19 14:26', 14553)
  ;

Comments

[HRNK]

shiketa様、参考になります。ありがとうございました。