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

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

図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句に外出しされている

移動平均：対象行数を制限したOLAP関数

さて、最初の例で見た、カレントの日付までの累積的な平均は、特にレコード数を指定せず、過去いくらでもさかのぼって平均の計算式に追加していました。そのため今のサンプルデータでは、最大5日分の平均が計算されることになります。

今度はこれを少し改変して、常に直近3日間の平均を算出してみましょう。こういう算出対象のレコード数を一定に保って、期間をずらしていく平均値を移動平均（moving average）といって、統計でここ最近の実績を見たい場合によく使います。

OLAP関数には、このために対応するオプションがちゃんと備わっています。OVER句に次のように1行追加するだけです（リスト4、図5⁠）⁠。

リスト4　直近3日間の平均を算出（MySQLの場合［OLAP関数を使わない場合］は後述の演習問題参照）

--移動平均（3レコードごと）：My SQL以外
SELECT account_id,
       record_date,
       prc_amt,
       AVG(prc_amt) OVER (PARTITION BY account_id
                              ORDER BY record_date
                               ROWS 2 PRECEDING) AS cumulative_avg_3
  FROM Accounts;

図5　リスト4の実行結果

account_id record_date  prc_amt  cumulative_avg_3
---------- -----------  -------  ----------------
A          2010-01-16     1,000              1000
A          2010-01-17    20,000             10500
A          2010-01-18    -3,000              6000
A          2010-01-19  -500,000           -161000
A          2010-01-20    23,000           -160000
B          2010-01-16    48,000             48000
B          2010-01-17    98,000             73000
B          2010-01-18    -5,000             47000
B          2010-01-19    30,000             41000
B          2010-01-20  -100,000            -25000
C          2010-01-16     5,000              5000
C          2010-01-17    12,000              8500
C          2010-01-18    30,000             15667
C          2010-01-19     1,000             14333
C          2010-01-20       500             10500

ROWS 2 PRECEDINGは、「⁠計算対象を過去2 行までに制限する」という意味です（precedingは「過去の、先行する」という意味です⁠）⁠。これと反対に、カレント行より未来の日付を計算に含めたい場合は、PRECEDINGの代わりにFOLLOWING（後続の）というキーワードを使ってROWS 2 FOLLOWINGとすることもできます。このように、ウィンドウの中でさらに制限された計算対象行の集合を「フレーム」と呼びます。

OLAP関数のルール 4: 計算対象行の制限は、ウィンドウ（パーティション）の中でさらにフレームで行うことができる

さて、図5で口座Aについての結果を見てみると、リスト5のようにちゃんと直近3日分の平均が出ていることがわかります（最初の2日間は、3行に満たないためデータ数が不足していますが⁠）⁠。

リスト5　口座Aの直近3日間の平均計算式

1/16：   1,000 = 1,000 / 1
1/17：  10,500 = (1,000 + 20,000) / 2
1/18：   6,000 = (1,000 + 20,000 + (-3,000)) / 3
1/19：-161,000 = (20,000 + (-3,000) + (-500,000)) / 3
1/20：-160,000 = ((-3,000) + (-500,000) + 23,000) / 3

この移動平均をOLAP関数を使わずに求めるのは、かなり難しい問題です。これは今回の演習問題としましょう。最終回ということで、難易度はA+です[6]⁠。