SQLアタマアカデミー

最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (2)OLAP関数の基本構文

この記事を読むのに必要な時間:およそ 3 分

OLAP関数の基本構文

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

  • [1]通常の集約関数(SUM/AVG/COUNT/MAX/MIN)をOLAP関数として使う
  • [2]RANK,ROW_NUMBERなどOLAP専用の関数

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

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

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

図1 解説に利用するテーブル

口座テーブル(Accounts)

口座ID
(account_id)
記録日
(record_date)
処理金額
(prc_amount)
A2010-01-16
1,000
A2010-01-17
20,000
A2010-01-18
-3,000
A2010-01-19
-500,000
A2010-01-20
23,000
B2010-01-16
48,000
B2010-01-17
98,000
B2010-01-18
-5,000
B2010-01-19
30,000
B2010-01-20
-100,000
C2010-01-16
5,000
C2010-01-17
12,000
C2010-01-18
30,000
C2010-01-19
1,000
C2010-01-20
500

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

リスト1 口座IDごとの処理金額の平均を出力

--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;

図2 リスト1の実行結果

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のように新しい日付の処理金額が平均の計算式に追加されていくのがわかります。計算式自体が動的に変化しているわけです。

リスト2 口座Aの日ごとの計算式

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

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

  • リスト1の結果について

    ウインドウを指定せずに累計になりますか?
    rows between unbounded preceding and current row を指定しないとグループ全体のavgになる気がします

    Commented : #1  柴田 (2015/02/27, 11:08)

コメントの記入