いろいろ試行錯誤したのですが、ご質問させて頂きたいと思います。
以下のようなテーブルがあったとします。トランザクション形式です。
すべてのデータは数値型です。
ID seq time
A 1 10
A 2 5
B 1 25
B 2 30
C 3 20
C 3 10
C 3 20
以下のようにしたいと思います。
同一IDの中で一つ前の行の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と変更、そのテーブルに1行空行を追加し、オリジナルと結合させて
time同士を引き算してました。
しかし、この場合、あまりに負荷がかかり、ディスク領域も取ることから何か別の方法があればどうかご教示ください。
No.3ベストアンサー
- 回答日時:
#2です。
WITHはCTE(共通テーブル式)と言って、SQL2005から使えるようになりました。同じ記述が複数登場するようなケースで先頭に宣言します。
このCTEは一時テーブルではなく、tempdbに実体が作られるわけではありません。
(もちろん分析関数を使った時点で処理中にtempdbを使うのは避けられないわけですが)
>1000万件ではなく7億万件という笑えない大きさです。
これは後出しじゃんけんみたいなものですね。
結局LAGはなくても、LAGと同じ機能のものお望みということですよね。
であれば、SQL Serverにはありません。
たぶん、一時テーブルを使わない方法では先に書いた方法以外はないと思います。
正直知恵を絞る場所はLAGの代わりを探すことではなくて、7億件を一気に処理させないで済む方法を考えることだと思わなくはないです。
No.2
- 回答日時:
>あまりに負荷がかかり、ディスク領域も取ることから
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万件ではなく7億万件という笑えない大きさです。
頂いた回答を拝見させていただきましたが、
(1)以下のスクリプトはSQL2005でもいけるのでしょうか?
WITHは使えないような気がいたします。私の勉強不足なら
すいません。Withより前がないので、どうなってますでしょうか?
WITH data AS
(SELECT ID,seq,time,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SEQ) rseq
FROM table1)
(2)以下のスクリプトを見ますとやはり一発でやるのは無理なのでしょうか?7億でやるのはかなり辛く、かといって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
- 回答日時:
SQL Server 2005とのことなので、Oracleの分析関数( over() )と同等の機能が実装されています。
これを活用し、row_numberで自己結合すれば、比較的容易に実装できると思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- CGI perlで書いたcgiでsqliteの使い方を教えてください 2 2023/05/08 21:29
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- Visual Basic(VBA) ExcelからAccessのテーブルに書き込む時に時間がかかる 1 2022/10/14 20:38
- 英語 「this is the/my first time~」の文法上の制約について 1 2023/04/06 09:48
- 英語 Think back to the last time you gave a little pres 3 2023/01/29 23:50
- その他(Microsoft Office) Excelで時間計算(負) 8 2023/02/26 05:47
- 英語 提示した名言について(並列表現の文法規則) 4 2023/06/02 09:41
- SQL Server ACCESSで表が作りたく、そのためのSQL文や設定方法を教えてください。 1 2022/08/15 12:28
- 英語 提示した名言の文の構造について(waste one hour of time) 5 2023/05/20 15:22
- Visual Basic(VBA) エクセルマクロでアニメを作る方法を教えてください。 1 2023/02/07 14:27
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
面接のときテーブルが正面に。...
-
テーブルリンク リンク元を知...
-
取数計算
-
L2SWはARPテーブルを持っている?
-
下の画像はSQLの4大命令の性質...
-
一致するデータのみ削除したい
-
1対1のリレーション(主キー同...
-
まるいテーブル 円い 丸い 漢字...
-
SQL 現在のレコードより古い番...
-
「テーブルに座って……」という...
-
飲み会で、座敷orテーブルどち...
-
テーブルの白く剥がれてるところに
-
アクセスのリンクテーブル一覧...
-
お金持ちのテーブル
-
Accessのリンクテーブルマネー...
-
MACアドレス見えない
-
立体の重心の求め方について
-
会社の飲み会の幹事になり、座...
-
UTF8のテーブルをODBCドライバ...
-
複雑なSQL文について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
会社の飲み会の幹事になり、座...
-
テーブルリンク リンク元を知...
-
L2SWはARPテーブルを持っている?
-
テーブルの白く剥がれてるところに
-
飲み会で、座敷orテーブルどち...
-
まるいテーブル 円い 丸い 漢字...
-
1つのテーブルに同じデータを参...
-
このテーブルで
-
置き配された食べ物を袋からど...
-
外部キーだけのテーブル(主キ...
-
【PHP】SQL文のSUM関数で出力し...
-
「テーブルに座って……」という...
-
男性と2人で飲食店に行きテーブ...
-
アクセスのリンクテーブル一覧...
-
一致するデータのみ削除したい
-
論理名とコメント構文(?)について
-
ACCESSで3ファイルを結合して、...
-
MySQLで複数テーブルを作成する
-
複数テーブルにわたるCOUNT
-
SQL 外部結合
おすすめ情報