SQLアタマアカデミー

最終回 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]⁠。

おすすめ記事

記事・ニュース一覧