LAG関数に似た関数があればご教示ください。

質問

いろいろ試行錯誤したのですが、ご質問させて頂きたいと思います。

以下のようなテーブルがあったとします。トランザクション形式です。
すべてのデータは数値型です。

ID seq time
A 1 10
A 2 5
B 1 25
B 2 30
C 3 20
C 3 10
C 3 20

以下のようにしたいと思います。
同一ＩＤの中で一つ前の行のTimeをみて差分を出したいと思います。
以下のテーブルをみますと、IDがAのSEQ2番のtimeは5、
IDがAのSEQ1番のtimeは10です。なので5から10を引くと-5となります。
それをtime_diffにいれたいと思います。

ID seq time　time_diff
A 1 10 null
A 2 5 -5
B 1 25 null
B 2 30 5
C 3 20 null
C 3 10 -10
C 3 20 -10

OracleではLAG関数があり、一つ前の行を参照することができ、今までそうしてましたが、SQL2005には同じような関数を探してもありませんでした。

仕方ないのでテーブルを別名保存し、timeフィールドをtime_lagと変更、そのテーブルに１行空行を追加し、オリジナルと結合させて
time同士を引き算してました。

しかし、この場合、あまりに負荷がかかり、ディスク領域も取ることから何か別の方法があればどうかご教示ください。

回答No.3

jamshid6 · ベストアンサー

#2です。

WITHはCTE(共通テーブル式)と言って、SQL2005から使えるようになりました。同じ記述が複数登場するようなケースで先頭に宣言します。

このCTEは一時テーブルではなく、tempdbに実体が作られるわけではありません。
（もちろん分析関数を使った時点で処理中にtempdbを使うのは避けられないわけですが）

＞1000万件ではなく７億万件という笑えない大きさです。
これは後出しじゃんけんみたいなものですね。

結局LAGはなくても、LAGと同じ機能のものお望みということですよね。
であれば、SQL Serverにはありません。
たぶん、一時テーブルを使わない方法では先に書いた方法以外はないと思います。

正直知恵を絞る場所はLAGの代わりを探すことではなくて、７億件を一気に処理させないで済む方法を考えることだと思わなくはないです。

お礼

ご解説ありがとうございます。tempdbに作られないわけですね！
それだけでもありがたいです。本当にありがとうございます。

回答No.2

jamshid6

＞あまりに負荷がかかり、ディスク領域も取ることから
1000万件くらいあるんですかね？

分析関数の実装状況はDBMSにより違いますから、一時テーブルにインデックスを張るなど、うまく活用すれば今のやり方でも決してだめではないと思います（SQL2000でやれと言われれば分析関数がないからできないとは言えないですし、そういう方法も知っていて然るべきでしょう）。
LAGやRATIO_TO_REPORTなどは結局すべてROW_NUMBER() OVERやSUM() OVERの延長ですから、書き換えられます。

WITH data AS
(SELECT ID,seq,time,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SEQ) rseq
FROM table1)

SELECT
d1.*,
d1.time-d2.time time_diff
FROM data d1
LEFT OUTER JOIN data d2 ON d2.ID=d1.ID AND d2.rseq=d1.rseq-1

補足

jamshid6様、ご丁寧な解説ありがとうございます。
1000万件ではなく７億万件という笑えない大きさです。
頂いた回答を拝見させていただきましたが、

（１）以下のスクリプトはSQL2005でもいけるのでしょうか？
WITHは使えないような気がいたします。私の勉強不足なら
すいません。Withより前がないので、どうなってますでしょうか？

WITH data AS
(SELECT ID,seq,time,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SEQ) rseq
FROM table1)

（２）以下のスクリプトを見ますとやはり一発でやるのは無理なのでしょうか？７億でやるのはかなり辛く、かといってSSDの1TBを買う予算も
ございません。なるべくtempディスクを取らず、データ領域も取らない方法はないものでしょうか？

SELECT
d1.*,
d1.time-d2.time time_diff
FROM data d1
LEFT OUTER JOIN data d2 ON d2.ID=d1.ID AND d2.rseq=d1.rseq-1

回答No.1

chukenkenkou

SQL Server 2005とのことなので、Oracleの分析関数( over() )と同等の機能が実装されています。
これを活用し、row_numberで自己結合すれば、比較的容易に実装できると思います。