SQLアタマアカデミー

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

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句で指定可能な項目を使用できる

おすすめ記事

記事・ニュース一覧