SQLアタマアカデミー

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

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

構文上の特徴

さて,次に構文上の特徴を見ましょう。コードを見てまず目につくのは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で縦に順序付けを行います。

図3 PARTITION BYとORDER BYのテーブルへの作用の様子

口座テーブル(Accounts)

画像

注2)
もちろんWHERE句で制限したり,GROUP BY句と併用すれば結果の行数は減るのですが,OLAP関数自体にそういう機能はない,ということです。
注3)
標準SQLの「ウィンドウ関数」という名称はここから来ています。この場合のウィンドウは「範囲」という意味です。
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アタマ養成講座を読んでいただけると幸いです。

注4)
実は,SQL Serve(r2005および2008)は,集約関数とOVER句を組み合わせた場合,ORDER BY句を指定できないという,奇妙な仕様上の制限があります。OracleやDB2にはこうした制限はありません。またSQL Serverでも,RANKやROW_NUMBERなどOLAP専用関数の場合はORDER BY句を使えます。参照:SQL Server 2008 オンライン ブック (2009年7月) OVER句(Transact-SQL)
注5)
例外は注4で述べたSQL Serverの特殊な制限の場合です。

ONではなくOVERが使われている理由

パーティション(ウィンドウ)のカットと走査のルールを決める句を作るキーワードに,⁠OVER」という語が使われている点に注目してください。これはもちろん,⁠ある対象の)上」という意味の単語です。そしてここでの「対象」は,もちろんテーブルです。しかしそれなら,よく似た意味を持つ単語「ON」が使われないのはなぜでしょう。

まあ,結合条件を指定するキーワードとして,SQLはすでに「ON」を使ってしまっているから,という若干身も蓋もない理由もあるかもしれません。ですが筆者は,ここにはそれ以上の意味があると思います。この点についてちょっと勝手な憶測を述べてみます。

学校の英語の授業でも習ったと思いますが,ONとOVERにはニュアンスの違いがあります。それは,ONが対象の上で静止しているイメージであるのに対し,OVERは上を横切るという動作を含意することです図a⁠。

図a ONとOVERの違い

図a ONとOVERの違い

OLAP関数は,複数のレコードの上を順番に走査して計算を行います。OVERという語は,その動作イメージと合致するためにここで使われているのではないか,というのが筆者の考えです。正しいかどうかわかりませんが,個人的にはそんなに外していないと思っています。

著者プロフィール

ミック

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

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

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