SQLアタマアカデミー

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

初出：WEB+DB PRESS Vol.55（2010年2月24日発売

SQL, データベース, 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句に外出しされている

SQL, データベース, OLAP関数, 統計, 移動平均

著者プロフィール

ミック

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

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

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