SQLアタマアカデミー

最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (4)OLAP関数と集約関数を組み合わせる

この記事を読むのに必要な時間:およそ 2 分

OLAP関数と集約関数を組み合わせる

これでOLAP関数の基本は押さえられました。今度はもう少し踏み込んだ使い方を勉強しましょう。もう一度同じAccountsテーブル図1を使い,記録日ごとに入金額の大きかった順のランキングを求めます。今度は口座の違いは気にせず,あくまで日付だけでまとめます。先に結果を紹介すると,図6のようになります。一番入金が多かったのは1月17日です図7)⁠

図6 記録日ごとに入金額の大きかった順のランキング

record_date sum_prc_amt rank_sum
----------- ----------- --------
2010-01-17      130,000        1
2010-01-16       54,000        2
2010-01-18       22,000        3
2010-01-20      -76,500        4
2010-01-19     -469,000        5

図7 1月17日の入金額の内訳

図7 1月17日の入金額の内訳

ランキングをつける関数は,先ほど紹介したRANK関数を使えばOKです。rank_sum列はこれで求めます。また,処理日ごとの処理金額の合計を出すのも,処理日をGROUP BYのキーに使うだけですので非常に簡単です。問題は,この2つの処理を同時にSQLの中で組み合わせられるかという点です。

結論から言うと,何の問題もなくできます。順を追って考えてみましょう。まずは,処理日ごとに処理金額の合計を算出します。これは別に難しくありませんリスト6,図8)⁠

リスト6 処理日ごとに処理金額の合計を算出

SELECT record_date,
       SUM(prc_amt) sum_prc_amt
  FROM Accounts
 GROUP BY record_date

図8 リスト6を実行して作られる中間テーブル

record_datesum_prc_amt
2010-01-16
54,000
2010-01-17
130,000
2010-01-18
22,000
2010-01-19
-469,000
2010-01-20
-76,500

この結果にランキングをつければよいわけです。それでは,2つの軸に沿って考えましょう。

第一の軸(カット):どんなキーでこの結果をカットするべきか?
結果全体を一つのウィンドウとみなすため,カットは不要。したがってPARTITION BY句も不要
第二の軸(走査順序):どんなキーで走査するべきか?
もちろん,処理金額の合計の降順。つまりSUM(prc_amt)の降順

ここまでわかれば,問題は解けたも同然です。最終的な答えはリスト7のようになります。

ORDER BY句にSUM(prc_amt)という集約関数を指定しているのが見た目に違和感があるかもしれませんが,論理的にこれで正しいことは,もう読者のみなさんもおわかりでしょう。SELECT句に指定可能な項目ならば,OLAP関数のPARTITION BY句とORDER BY句のキーとして利用できるのです。

リスト7 処理日ごとの処理金額の合計でランキング

-- My SQL以外
SELECT record_date,
       SUM(prc_amt) sum_prc_amt,
       RANK() OVER (ORDER BY SUM (prc_amt) DESC) AS rank_sum
  FROM Accounts
 GROUP BY record_date;

-- My SQL
CREATE VIEW SumByDate (record_date, sum_prc_amt) AS
SELECT record_date,
       SUM(prc_amt) sum_prc_amt
  FROM Accounts
 GROUP BY record_date;

なお,ここでORDER BY sum_prc_amtのように,SELECT句で指定した列の別名を指定したいと思うかもしれませんが,残念ながらこれは構文エラーとなって動きません。

OLAP関数のルール 5
PARTITION BYとORDER BYのキーには,SELECT句で指定可能な項目を使用できる

OLAP 関数はいつ計算されるのか

OLAP関数を記述できる場所は,基本的にSELECT句と考えてください。クエリの最後のORDER BY句にも構文上は書くことができますが,実際にはほとんどそういう使い方はしません。一方,通常の関数と違いWHERE句に書くことはできません。

この使える場所の制限は,OLAP関数がDBMS上で実行されるタイミングに関係しています。SQL というのは,一般的に次のような順序で実行されます。

  • ① FROM:走査対象テーブルの絞り込みとJOINを行う
  • ② WHERE:レコードの制限を行う(restriction)
  • ③ GROUP BY:集約を行う
  • ④ HAVING:集約結果のレコードの制限を行う
  • ⑤ SELECT:選択対象列の制限を行う(projection)
  • ⑥ ORDER BY:結果のソートを行う

この一連の処理ステップの中で,OLAP関数が実行されるのは,⑤です(⑥も可能性としてはありますが,前述のように実際はまれです)⁠つまりOLAP関数は,もうWHERE句による制限やGROUP BY句による集約が全部終わったあとに,その結果に対して適用される関数なのです。けっしてFROM句で指定したナマのテーブルに対して適用されるものではないのです。GROUP BY句による集約をしなければ,たまたまテーブルそのものに適用されているように見えるだけです。実際は,⁠OLAP関数と集約関数を組み合わせる」でも見たように,GROUP BY句の集約結果に適用することもできます。

それが可能な理由は,この関数があくまでSELECT句で―つまりSQL全体の処理ステップの中では終わりのほうで―実行されるからです。たとえ見た目上SUMやAVG を使っているように見えても,GROUP BYの集約プロセスとはまったく独立に動く(したがってGROUP BYと組み合わせると,原理上ソートも2回走る)ことを忘れないでください。

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入