OLAP関数の基本構文

それではOLAP関数の使い方を見ていこうと思います。最初にどんな関数があるのか、という点を整理しておくと、OLAP関数は大きく次の2種類に分類されます。

［2］のパターンは、関数を見ればOLAP用途だと一目でわかります。他方、［1］はどういうことかというと、SUMやAVGは構文によって普通の集約関数になったりOLAP関数になったりする、ということです。最初は紛らわしいと思うかもしれませんが、集約関数をOLAP関数として使うときはかなり特徴的な構文になるので、こちらも慣れれば一目で判断できるようになります。

ではまず［1］の例として、普通のAVG関数をOLAP関数として使う場合のサンプルを見ながら、構文を学習しましょう。

テーブルは図1のものを使います。ある銀行の入出金の処理金額を記録するテーブルです。正数は入金を、負数は出金を意味します。A～Cの3人の口座が管理されています。

さて、まずはこのテーブルから、口座IDごとの処理金額の平均を「過去からその当日まで」のデータを使って出力してみます（リスト1、図2⁠）⁠。

--My SQL以外（ただしSQL ServerではORDER BY句を除外すること）
SELECT account_id,
       record_date,
       AVG(prc_amt) OVER (PARTITION BY account_id
                          ORDER BY record_date) AS cumulative_avg
  FROM Accounts;

--My SQL
SELECT account_id,
       record_date,
       prc_amt,
       (SELECT AVG(prc_amt)
          FROM Accounts A2
         WHERE A1.account_id = A2.account_id
           AND A1.record_date >= A2.record_date ) AS cumulative_avg
  FROM Accounts A1;

account_id record_date   amount    cumulative_avg
---------- -----------   ------    --------------
A          2010-01-16     1,000             1,000
A          2010-01-17    20,000            10,500
A          2010-01-18    -3,000             6,000
A          2010-01-19  -500,000          -120,500
A          2010-01-20    23,000           -91,800
B          2010-01-16    48,000            48,000
B          2010-01-17    98,000            73,000
B          2010-01-18    -5,000            47,000
B          2010-01-19    30,000            42,750
B          2010-01-20  -100,000            14,200
C          2010-01-16     5,000             5,000
C          2010-01-17    12,000             8,500
C          2010-01-18    30,000            15,667
C          2010-01-19     1,000            12,000
C          2010-01-20       500             9,700

AVG関数を使っていますが、この結果は普通の平均の計算ではありません。日をおって平均の計算に使われる値が1つずつ増えていっています。ためしに口座Aの結果を見てみると、リスト2のように新しい日付の処理金額が平均の計算式に追加されていくのがわかります。計算式自体が動的に変化しているわけです。

1/16： 1000 = 1000
1/17： 10500 = (1000 + 20000) / 2
1/18： 6000 = (1000 + 20000 + (-3000)) / 3
1/19：-120500 = (1000 + 20000 + (-3000) + (-500000)) / 4
1/20： -91800 = (1000 + 20000 + (-3000) + (-500000) + 23000) / 5

通常の集約関数との違い

このクエリには、通常AVG関数を使うときとは大きく異なる点がいくつかあります。まず、結果から見ると、一般的にAVG関数を使った場合、複数のレコードが集約されるため、結果の行数がもとのテーブルの行数より少なくなります。このクエリのように、GROUP BY句がない場合、全行をまとめて1行に集約するはずなのですが、実際の結果は、元のテーブルの件数と同じ15行が出力されています。

このことからもわかるとおり、OLAP関数は、通常の集約関数が持っているようなレコードに対する集約機能を一切持っていません。したがってテーブルのレコードはまとめられることなく、そのまま出てきます[2]⁠。

OLAP関数のルール 1

OLAP関数は見かけ上集約関数を使っているように見えても、実は集約は一切行っていない

構文上の特徴

さて、次に構文上の特徴を見ましょう。コードを見てまず目につくのはOVER句でしょう。これがOLAP関数のキモです。OVER句を集約関数の後ろにくっつけることで、普通のAVG関数が一瞬にしてOLAP関数として機能するようになります。集約関数の後ろにOVER句を見つけたら、「⁠あ、これは集約関数じゃなくてOLAP関数として使われているんだな」と思ってください。

PARTITION BYとORDER BY

OVER句で指定する必要があるのは、以下の2つです。

PARTITION BY

テーブルをどのようなキーでカットするかを指定します。構文はGROUP BY句と同じです。ここで指定したキーでカットされた部分集合を、パーティションまたはウィンドウ[3]と呼びます。普通に「グループ」と呼んでもよいのですが、それだとGROUP BY句でカットした場合の部分集合（前述のように集約されることが前提となる）と混同するので、違う用語が使われているのでしょう。

ORDER BY

ここでは、パーティション内部のレコードをどういう順序で走査するかを指定します。構文は、SELECT文の最後につける普通のORDER BY句とまったく同じで、昇順／降順の指定もASC／DESCで行います（デフォルトはASCで、省略するとASC扱いになります⁠）⁠。

いわば、PARTITION BYとORDER BYは、対象のテーブルに対し図3のように、横（PARTITION BY）と縦（ORDER BY）の方向に作用するようになっているのです。まずはPARTITION BYがテーブルを横方向にカットし、次にORDER BYで縦に順序付けを行います。

PARTITION BY、ORDER BYを省略した場合

なお、この2つの指定は必須というわけではありません。PARTITION BYを省略すれば、テーブル全体を1つのパーティションとみなすことになります。これはちょうど、GROUP BYなしで集約関数を使えばテーブル全体を1つのグループとして集約するのと同じです。

一方、ORDER BYも省略することが構文上できます[4]⁠。ただし省略するということは、どんな順序でレコードをスキャンするか、ユーザ側がまったく指定しないことを意味するので、DBMSが適当な順序を決めることになります。実際には、ORDER BY句を省略するような要件は少ないため、基本的にORDER BY句は常に指定すると考えてください[5]⁠。

OLAP関数のルール 2

PARTITION BYは省略することもあるが、ORDER BY句は通常はいつも指定する

OLAP関数を使いこなすコツは、この縦横2つの軸で考えることです。自分はどういう基準でテーブルをカットしたいのか。どういう順序でレコードを走査したいのか。それを明確にできれば、あとは構文の中に機械的にキーとなる列をあてはめていくだけです。

なお、冒頭でも述べたとおり、My SQLはまだこのOLAP関数を実装していません。そのため、同じことを実現するには、相関サブクエリを使わねばなりません。コードを比較するとわかるように、こちらの書き方はかなり難しく、またパフォーマンスもOLAP関数に比べてよくありません。この相関サブクエリの使い方についての解説は、本稿で行うスペースはないため、「⁠SQLアタマ養成講座」を読んでいただけると幸いです。