お客さんへの説明でいろいろやったんでついでに自分の資料用に作ってしまった。search?q=%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E4%B8%AD%E3%81%AE%E5%90%84%E3%82%B0%E3%83%AB%E3%83%BC%E3%83%97%E3%81%A7%E6%9C%80%E5%A4%A7%E5%80%A4%E3%82%92%E6%8C%81%E3%81%A4%E3%83%AC%E3%82%B3%E3%83%BC%E3%83%89%E3%81%AB%E5%8D%B0%E3%82%92%E4%BB%98%E3%81%91%E3%82%8B&amp;btnI=lucky各グループ中で最大値を持つレコードに印を付けたい大きな組織では、複数の活動拠点から日毎のデータが送られてきて、それを元に経営や営業の判断に利用する、という事が普通に行われています。こんなテーブルがあるとしましょう。SQL>テーブルには日々レコードが追加され続け、ある程度の期間が過ぎると、該当レコードはテーブルから削除されます。このテーブルは以降の話の為に、年月での集約集計が行われた結果が格納されている、という事にしましょう。この例は2014年1月の情報ですね。後輩:「先輩、上司から作業指示がありまして。先輩:「面倒なのはいやだよ?後輩:「面倒なんですよねー。こんな風に、エリアで売り上げ最大の拠点にマークを付けたリストが欲しいそうです。190たぶん、データベースにかかわるお仕事をしていると1回はやる事になるお題です。ある後輩氏は項目TOPSALESを含むテーブルXを作り、テーブルTRANTBLからXへ必要なレコードをINSERTして、更にテーブルXにUPDATE文でマークを付ける処理実行、という2段階の処理を行いました。まとめてやろうとすると面倒な事も分割すると楽になるので、そういうのもありなんですが、上記はサブクエリやウインドウ関数を知っていると楽が出来ます。先輩:「とりあえずサブクエリの使い方覚えてみよう。後輩:「え!?FROM句以外にも使えるんですか!?先輩:「少ないデータ量ならこういうやり方がある。先輩:「もう少しお勉強しようよ...CASE構文でサブクエリを使っています。『現在のレコードのを持つ項目SALESと最大値が同一かどうか』を判定しています。現在のレコードの項目SALESと最大値が等しいならそのレコードは最大値を持つレコードとなりますので、印である‘Y'を、そうでないならNULLを、項目TOPSALESに当てはめる事になります。構文的には1レコード毎にサブクエリが実行されます。項目DATE_YM='201401'、項目AREACODE=10、が3レコードありますが、同じ条件でサブクエリが3回実行されることになります。...データベースの内部処理的には、最適化処理が行われ同条件のサブクエリは1回だけの実行になります。先輩:「今度はウインドウ関数を使う例。どっちかというとこっちを覚えたほうがいい。先輩:「最初のはサブクエリの復習がてら例示しただけだもの。今度はウインドウ関数の使用例。SQL>CASE構文は一緒なんですが、今度は直接集計関数MAXを使っています。『現在のレコード中の項目DATE_YMと項目AREACODEと同じ値を持っているレコード』である事を指定します。集計関数MAXの集計範囲を指定している訳で、サブクエリで集計する範囲を指定してるのとそれほど変わらないように見えます。データベースシステム的には結構な違いがあるんですよ。サブクエリ版とウインドウ関数版の違い後輩:「どっちも結果が一緒なら二つ覚えなくても良いんじゃないですかね。先輩:「正確には結果は一緒じゃないんだよね。データが少量ならサブクエリの方がやってる事がわかり易い。データ量が大きくなるとウインドウ関数版じゃなきゃ駄目かもしれない。後輩:「納得いく説明が無いと許しませんよ?では、先のサブクエリ版の実行計画を覗いてみましょう。3をマージしてるんですね。先輩:「そういう理解でいいと思うよ。FULL」が2回現れている事がわかります。OracleさんがテーブルTRANTBL全体読み込みを2回繰り返しますよ、と言ってるわけです。filter(..)」を見ると、項目DATE_YMと項目AREACODEで集計条件を指定しているようです。結果を使った、印付与判定処理のため、と考えられます。次はウインドウ関数版の実行計画です。FULL」が1回しかありません。項目DATE_YMと項目AREACODEをキーに集計を行っている様子もありません。テーブル読み込みを行いながら同時に必要な集計を行っているためです。1でキー毎の集計を取っています。その為1回のテーブル読み込みで必要な処理が終わっています。先輩:「1回の読み込みで必要な集計処理も同時に済ませている。2回読み込む必要がない。後輩:「言われてみれば、わざわざ別けて読み直ししなくても処理できますもんね。DATE_YM='201401'』が1時間かかる場合、サブクエリ版は2時間かかる可能性があるね。実際はキャッシュ等が使われてそこまで行かないと思うけど。FULLが発生するから、知っていないと大変な結果を招く場合があるって事ですね。後輩:「でもウインドウ関数版はレビュー時の説明で苦労しそうだなぁ......先輩:「ウインドウ関数は知らない人への説明が面倒だけど、ウインドウ関数を使うとデータベースが1回で読み込みを終えてくれるようになる、と言えばきっと分かってくれると思うよ。印を付けるレコードが一意に決まらない場合の対処例そして、やっぱり一度はやる失敗。後輩:「項目SALESに重複が発生してしまいました!なので複数の印がついちゃいます!先輩:「そらそうだ、合計値が一緒になる事を想定出来ないやつがおかしい。後輩:「orz今回の処理対象はこれになります。AREACODE=10、BRANCHCODE=10102、の2レコードに印がついてしまいます。SQL>先輩:「一意に決めるコードが無いならこうなるしかないよね。後輩:「何とかインチキできませんかね?先輩:「水曜どうでしょう再開したんだってねぇ。...ソートを行って、常にレコードの並びを一意に出来るなら手はある。句で指定する項目SALES(降順)、項目AREACODE、項目BRANCHCODE、項目ITEMCODE、でソート順を指定のレコードセットを適用対象範囲とした、ウインドウ関数LAGで判定し印を付ける例です。SQL>ウインドウ関数LAGは、現在のレコードセットの直前のレコードにあった項目の値を取得します。LAG(SALES,1)は、1レコード前の項目SALESとなります。上記の例で、項目DATE_YM='201404'、項目AREACODE=20、の範囲に注目すると、レコードセット2番目のレコードでLAG(SALES,1)の結果はレコードセット1番目のレコードの項目SALESの値である840になります。レコードセット1番目のレコードでLAG(SALES,1)の結果は、...ひとつ前のレコードがありませんから、NULLになります。BY句で最初に項目SALESを降順で指定したのであれば、最大値を持つレコードが先頭に集まります。最大値の同じレコードが並んでいたとしてもウインドウ関数LAGを使う事で、1番目なのか、それ以外なのかを判定できるので、先頭にあるレコードだけに印を付けてしまえばいいのです。後輩:「並びがちょっと変わっちゃいましたね。先輩:「完全には無理だけど、こんな感じでソートしなおせばいいと思う。句がないので、この出力結果は環境依存となります。並びがそうなった、と理解してください。“完全には無理”というのはそういうことです。句なんですが、項目SALESだけでも良いんじゃないでしょうか。それだと実行の度にLAG(SALES,1)がNULLになるレコードが変わってしまう。後輩:「kwsk先輩:「項目SALESだけの指定だと、ソートの結果が不定になってしまうから。句の指定でソート結果を一意にしている。SELECT文実行時そのレコードセットの出力順序は不定です。句を指定して出力順序を決めるのですが、指定が足りなかったりすると、その足りない部分での出力順が不定となります。これは環境に依存する部分です。BRANCHCODEだと、次のような2通りの結果が発生する可能性があります。句も同様で、ソート順を決める項目の指定に不足があればソート結果が異なる可能性がありますので、以下の様に印が付くレコードが一意に決まらず不安定に見える場合があります。ITEMCODE必要な項目を記述して明示的に結果を一意にさせます。300先輩:「楽そうだからターゲットを先頭のレコードとしてみただけなので、好きな手段で決めればいいと思うよ。後輩:「考えるの面倒なので、一意に決めるキーを付ける様にお願いしてみます。先輩:「じゃあ最初からそうして。後輩:「上司が、印が付くレコードだけ欲しいといってきたんですけど。先輩:「まぁそうだよね。トップだけ見たいって言われるのも想像できるよね。900としたい訳ですね。難しく考えなくてもサブクエリで解決すればいいんです。どのレコードが対象か分かる印をつける事ができているんですから。SQL>先輩:「気になるならSQLの実行計画を調べてみればいいよ。後輩:「なんかだまされた気分。サブクエリ使うなって言われたその直後に使ってるし......先輩:「確定した情報を利用している、実に分かり易いサンプルだと思うけどなぁ。