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関数は見かけ上集約関数を使っているように見えても，実は集約は一切行っていない