OLAP専用関数
では次に、[2]OLAP専用関数の使い方を見ていきましょう。OLAP専用の関数は、DBMSによって使える種類に差がありますが、次の3つが標準SQLで決められている代表選手ですので、必ず覚えてください。
ROW_NUMBER | レコードに一意な連番を振る |
RANK | レコードのランキングを算出する。同位のレコードが複数存在する場合は、順位が飛ぶ |
DENSE_RANK | 基本的にRANKと同じだが、同位のレコードが存在しても順位が飛ばない |
これらOLAP専用の関数は、集約関数としての用途はありません。使うときは自動的にOLAP関数になります(だから必ずOVER句が必要)。
構文は先ほど説明したものとまったく同じですが、1点だけ注意があります。それを、コードを見ながら説明しましょう。口座ごとに入金の多い日の順番で、ランキングを出してみます。
では、先ほど言ったように2つの軸に沿って考えましょう。
- 第一の軸(カット):どんなキーでこの結果をカットするべきか?
- 口座ごとにカットするので、account_id
- 第二の軸(走査順序):どんなキーで走査するべきか?
- 入金の多い順なので、処理金額の降順。つまりprc_amtの降順
従って、リスト3、図4のようになります。
注意点は、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)。
ROWS 2 PRECEDING
は、「計算対象を過去2 行までに制限する」という意味です(precedingは「過去の、先行する」という意味です)。これと反対に、カレント行より未来の日付を計算に含めたい場合は、PRECEDINGの代わりにFOLLOWING(後続の)というキーワードを使ってROWS 2 FOLLOWING
とすることもできます。このように、ウィンドウの中でさらに制限された計算対象行の集合を「フレーム」と呼びます。
- OLAP関数のルール 4
- 計算対象行の制限は、ウィンドウ(パーティション)の中でさらにフレームで行うことができる
さて、図5で口座Aについての結果を見てみると、リスト5のようにちゃんと直近3日分の平均が出ていることがわかります(最初の2日間は、3行に満たないためデータ数が不足していますが)。
この移動平均をOLAP関数を使わずに求めるのは、かなり難しい問題です。これは今回の演習問題としましょう。最終回ということで、難易度はA+です[6]。