SQLアタマアカデミー

最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (3)OLAP専用関数

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

OLAP専用関数

では次に,[2]OLAP専用関数の使い方を見ていきましょう。OLAP専用の関数は,DBMSによって使える種類に差がありますが,次の3つが標準SQLで決められている代表選手ですので,必ず覚えてください。

ROW_NUMBERレコードに一意な連番を振る
RANKレコードのランキングを算出する。同位のレコードが複数存在する場合は,順位が飛ぶ
DENSE_RANK基本的にRANKと同じだが,同位のレコードが存在しても順位が飛ばない

これらOLAP専用の関数は,集約関数としての用途はありません。使うときは自動的にOLAP関数になります(だから必ずOVER句が必要)⁠

構文は先ほど説明したものとまったく同じですが,1点だけ注意があります。それを,コードを見ながら説明しましょう。口座ごとに入金の多い日の順番で,ランキングを出してみます。

では,先ほど言ったように2つの軸に沿って考えましょう。

第一の軸(カット):どんなキーでこの結果をカットするべきか?
口座ごとにカットするので,account_id
第二の軸(走査順序):どんなキーで走査するべきか?
入金の多い順なので,処理金額の降順。つまりprc_amtの降順

従って,リスト3,図4のようになります。

リスト3 入金の多い日の順番で,ランキングを表示(OLAP専用関数は引数を持たないことに注意)

-- My SQL以外
SELECT account_id,
       record_date,
       prc_amt,
       RANK() OVER (PARTITION BY account_id
                       ORDER BY prc_amt DESC) AS rank_amt
  FROM Accounts;

-- My SQL
SELECT account_id,
       record_date,
       prc_amt,
       (SELECT COUNT(*)
          FROM Accounts A2
         WHERE A1.account_id = A2.account_id
          AND A1.prc_amt <= A2.prc_amt ) AS rank_amt
  FROM Accounts A1;

図4 リスト3の実行結果

account_id record_date  prc_amt rank_amt
---------- -----------  ------- --------
A          2010-01-20    23,000        1
A          2010-01-17    20,000        2
A          2010-01-16     1,000        3
A          2010-01-18    -3,000        4
A          2010-01-19  -500,000        5
B          2010-01-17    98,000        1
B          2010-01-16    48,000        2
B          2010-01-19    30,000        3
B          2010-01-18    -5,000        4
B          2010-01-20  -100,000        5
C          2010-01-18    30,000        1
C          2010-01-17    12,000        2
C          2010-01-16     5,000        3
C          2010-01-19     1,000        4
C          2010-01-20       500        5

注意点は,RANK関数をRANK ()のように引数を持たない関数として使うことです。これはほかのROW_NUMBERなども同様です。OLAP専用関数は,集約関数をOLAP関数として使うときとは違い,引数を取りません。したがって括弧の中は常に空です。

正確には,これらの関数が引数を取っていないわけではないのです。このコードで言えば,account_idやprc_amtはこの関数の引数です。ただ,それがOVER句のほうへ外出しされているのです。

OLAP関数のルール 3
OLAP専用関数の引数は,見た目上は空っぽ。引数はOVER句に外出しされている

著者プロフィール

ミック

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

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

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

コメント

コメントの記入