本記事は『達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ』の「2 必ずわかるウィンドウ関数」を抜粋し、掲載にあたり編集したものです。
順序を使ったプログラミングの復活
本記事ではウィンドウ関数というSQLの道具を取り上げます。1990年代の後半にアイデアが登場し、2000年代にOracle、Db2、SQL Server などのDBMSでサポートされるようになり、2017年にMySQLがサポートを表明したことで、現在では主要なすべてのDBMSで利用することが可能になりました。ウィンドウ関数を使いこなすことで、ある意味で手続き型言語を使う感覚でデータを操作することが可能になります。SQLプログラミングの可能性を大きく広げる重要な道具について、理解を深めましょう。
ウィンドウ関数は、SQLに導入された道具の中では新しいほうですが、その重要性は抜きん出て高いと言えます。ウィンドウ関数なしでは、モダンなSQLプログラミングは不可能だと断言してよいくらいです。本書初版の執筆時2008年では、まだサポートしていないDBMSもあったため、あまりウィンドウ関数について言及しませんでしたが、この第2版では主役級のフォーカスを当てます。
ウィンドウ関数の応用方法はたくさんありますが、特に、これまで行間比較において相関サブクエリに頼らなければならなかったケースにおいて、ウィンドウ関数を使うことで相関サブクエリを消去し、SQL文をエレガントに記述することが可能になります（このポイントについては「7 ウィンドウ関数で行間比較を行なう」で詳しく説明します）。本章では、そのように便利なウィンドウ関数を使いこなしてもらうために、その動作の基本的理解に焦点を当てて解説します。というのも、ウィンドウ関数は、ぱっと見たときには動作が少しわかりにくく、それがしばしばSQLユーザーにとってとまどいの種になることもあるからです。伝統的な集合指向のSQLコーディングに慣れ親しんできたDBエンジニアにとっては、RDBとSQLが遠い昔に決別した手続き型の概念――行の順序――を遠慮なく使っているように見えますし、一方で初めてSQLに触れる初心者にとっては、1つの関数の中に多くの機能を詰め込んだことで、動作のイメージを持ちにくい難物に見えます。
しかし、そうした理由でウィンドウ関数を敬遠することは、もったいない話です。本章では、ウィンドウ関数を理解するポイントを、いくつかのサンプルを通して見ていきましょう。ウィンドウ関数の基本的な構文についてはおおよそ把握していることを前提に話を進めるので、もしまったくウィンドウ関数を見たこと／使ったことがないという方は、巻末参考文献に挙げた初心者向け書籍でウィンドウ関数の説明を読んでからのほうが理解がスムーズでしょう（逆に、細かいオプションの構文まで覚えていなくても、本章の理解には支障ありません）。
ウィンドウとは何か？
まず、ウィンドウ関数を初めて見た人が不思議に思うのが、この名前だと思います。何かウィンドウというものを使った関数なのだろうという想像はつくものの、いざ構文のサンプルを見てみると、どこにも「これがウィンドウです」とは書かれておらず、いきなりPARTITION BY句やORDER BY句を使ったクエリの応用例の紹介が始まります。
たとえば、ウィンドウ関数の典型的な利用ケースである、移動平均を求める構文の一例を見てみましょう。具体的なデータに意味はないので、テーブル定義は省略します。
SELECT shohin_id, shohin_mei, hanbai_tanka,
AVG (hanbai_tanka) OVER (ORDER BY shohin_id
ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW) AS moving_avg
FROM Shohin;
これは商品テーブルを商品IDの昇順にソートして、各商品についてIDの2つ前までの商品を含む価格の移動平均を求めていますが、AVG、OVER、ROWS BETWEEN、CURRENT ROWといったウィンドウ関数を構成するキーワードがいくつか登場しているものの、ウィンドウそのものの定義があるようには見えません。
しかしこれは、このクエリがウィンドウという概念を使っていないわけではなく、この構文でも一応、ウィンドウは定義されています。ただ、それが暗黙に行なわれているため、一見するとウィンドウが登場していないような錯覚を受けるのです。ウィンドウの定義まで明示した構文は次のようになります。
SELECT shohin_id, shohin_mei, hanbai_tanka,
AVG(hanbai_tanka) OVER W AS moving_avg
FROM Shohin
WINDOW W AS (ORDER BY shohin_id
ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW);
こちらは、ウィンドウを明示的に定義したうえで、それに対してAVG関数を適用するという構文になっています。ここでのウィンドウとはすなわち、FROM句から選択されたレコードの集合に対して、ORDER BYによる順序付けやROWS BETWEENによるフレーム定義が行なわれたうえでのデータセットということになります。レコード集合に対して、こうしたさまざまなオプションによるデータ加工を行なえるところが、ただのレコード集合とウィンドウの異なるところです。
この2つの構文を比較するとわかるように、私たちが一般によく使うウィンドウ関数の構文は、（無名プロシージャや無名ファンクションと同じく）暗黙的に「無名ウィンドウ」を利用する簡略形なのです。こちらのほうが、文字通り簡略に書けるのが利点ですが、名前付きウィンドウ構文のほうも、ウィンドウの使いまわしが可能で編集時のエラーを抑止できる利点があります。ちょうど、共通表式（CTE）によるビューの使いまわしや、ストアドプロシージャにおける名前付きプロシージャの定義と同じ効果があるわけです。
-- 名前付きウィンドウ構文では、ウィンドウの使いまわしが可能
SELECT shohin_id, shohin_mei, hanbai_tanka,
AVG(hanbai_tanka) OVER W AS moving_avg,
SUM(hanbai_tanka) OVER W AS moving_sum,
COUNT(hanbai_tanka) OVER W AS moving_count,
MAX(hanbai_tanka) OVER W AS moving_max
FROM Shohin
WINDOW W AS (ORDER BY shohin_id
ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW);
このように、無名構文と名前付き構文にはそれぞれ利点があるので、場合によって使い分けるのが基本路線ですが、一点注意しなければならないのは、後者の名前付き構文がエラーになり、受け付けないDBMSもあることです。本来は名前付きのほうが「正式」な構文という気もしますが、無名構文のほうが普及してしまった結果として起きた逆転現象のように思われます。
こうした構文の互換性のなさはDBMS間のマイグレーションでリスクになるため、（名前付き構文でウィンドウの定義を理解した後は）原則として無名構文を利用する、というスタンスが無難かもしれません。「上りきったら梯子は捨てろ」の精神です。
１枚でわかるウィンドウ関数
さて、ウィンドウの定義を理解したところで、ウィンドウ関数の機能を俯瞰してみましょう（図2.1）。
ウィンドウ関数は複数の操作を1つの関数に詰め込んでいるのが、理解をわかりにくくさせている理由の1つですが、図2.1のように全体を整理して見れば、実は、以下の3つの機能しか持っていないのです（3つも詰め込んでいたら十分複雑だ、という意見もあるかもしれませんが、まあそれはそれとして）。
- PARTITION BY 句によるレコード集合のカット
- ORDER BY 句によるレコードの順序付け
- フレーム句によるカレントレコードを中心としたサブセットの定義
しかもこのうち1.と2.の機能は、従来のGROUP BYとORDER BYの機能とほぼ同じなので、SQLの基本構文を理解している人なら、すんなり理解できます。ウィンドウ関数の本当に独自の機能という意味では、3.のフレーム句だけです。「カレントレコード」の概念をプログラミングの中で明示的に利用するというのは、伝統的なSQLになかった発想です。しかし一方で、これがRDBから手続き型言語へデータを受け渡すときに昔から利用されている「カーソル（cursor）」をSQLの構文に取り込んだものであることは、RDBを使ってシステムを構築した経験のある人ならすぐに気づくでしょう（図2.2）。
カーソルが必要だった理由は、原則としてテーブルのレコードが順序を持たず、レコードの集合を操作の基本単位とする「set at a time」の考え方に基づくRDBと、レコードが順序を持ち、1行のレコードを操作の基本単位とする「record at a time」の考え方に基づく手続き型言語のギャップを埋める仲介が必要とされたからです。
手続き型言語においては、レコード集合が何らかのキーで順序付けされたうえで、それをfor文やwhile文のループで回してカレントレコードを1行ずつずらしながら処理するというのが、昔から今に至るまで変わらない基本的な操作方法です。これはアドレスの隠蔽やオブジェクト指向が導入された後でも変化しませんでした。その点で、ウィンドウ関数は手続き型言語の考え方をSQLに輸入した機能ということができます。
フレーム句を使って違う行を自分の行に持ってくる
フレーム句は、上述のような移動平均などカレントレコードを基準に計算する統計指標を、SQLで簡単に算出するために導入されました。しかしそれだけにとどまらず、広い応用があります。感覚的な表現をすれば、フレーム句を使うことで「異なる行を自分の行に持ってくる」ことができるようになり、従来SQLで難しかった行間比較を自在に行なうことができるようになるのです。
直近を求める
まずは基本的な時系列分析から考えてみましょう。時系列にデータを比較する場合、基本となるのは、時系列に従って、1行ずつ過去へさかのぼる、または未来へ進むSQLです。サンプルに、LoadSampleというサーバの時間ごとの負荷量を記録したテーブルを使います（負荷量は適当な数値なので、意味は気にしないでください）。サンプリングは思いついたときに不定期に行なわれるため、不連続で間隔もランダムな日付が格納されているとします。
| sample_date（計測日） | load_val（負荷量） |
| 2018-02-01 | 1024 |
| 2018-02-02 | 2366 |
| 2018-02-05 | 2366 |
| 2018-02-07 | 985 |
| 2018-02-08 | 780 |
| 2018-02-12 | 1000 |
まずは、各行について「過去の直近の日付」を求めてみます。すなわち「1行前」の日付を求めるということです。
SELECT sample_date AS cur_date,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_date
FROM LoadSample;
cur_date latest_date ---------- ------------ 2018-02-01 2018-02-02 2018-02-01 2018-02-05 2018-02-02 2018-02-07 2018-02-05 2018-02-08 2018-02-07 2018-02-12 2018-02-08
2月1日より前のデータはこのテーブルには登録されていないので、2月1日の行については直前の日付はNULLになります。これは直観的に納得のいく仕様でしょう。2月2日以降についてはそれぞれ直前の日付がテーブルに存在するので、これがlatest列に入ることになります。このクエリのポイントは、「ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING」によって、フレーム句の範囲をあくまでsample_dateでソートした場合の直前の1行に限定していることです。普通、「BETWEEN」は、複数行の範囲を指定するために使う場合が多いですが、ここはあえて範囲を1行に限定するために利用していますし、別に1行しか範囲がなかったからエラーになるというわけでもありません。
いわば、ここでのフレーム句は、カーソルがカレント行に当たった状態を前提として、「1行前」の範囲に限定したレコード集合を作ったわけです。日付だけでなく、当該の日付の負荷量を求めることも簡単です。カレントレコードの負荷量はそのままload列で求められますし、1行前の負荷量も、同じウィンドウ定義でload列に変えるだけです。
SELECT sample_date AS cur_date,
load_val AS cur_load,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_date,
MIN(load_val)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_load
FROM LoadSample;
cur_date cur_load latest_date latest_load ---------- --------- ----------- ----------- 2018-02-01 1024 2018-02-02 2366 2018-02-01 1024 2018-02-05 2366 2018-02-02 2366 2018-02-07 985 2018-02-05 2366 2018-02-08 780 2018-02-07 985 2018-02-12 1000 2018-02-08 780
ところで、このコードで、同じウィンドウ定義が二度登場していることに気づいたでしょうか。上述の名前付きウィンドウ構文を使えば、次のように1つにまとめて記述することも可能です（結果は同じ）。
SELECT sample_date AS cur_date,
load_val AS cur_load,
MIN(sample_date) OVER W AS latest_date,
MIN(load_val) OVER W AS latest_load
FROM LoadSample
WINDOW W AS (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING);
ウィンドウ関数の内部動作
「1枚でわかるウィンドウ関数」の節でも述べたように、ウィンドウ関数は、以下の3 つの機能を持っています。
- PARTITION BY 句によるレコード集合のカット
- ORDER BY 句によるレコードの順序付け
- フレーム句によるカレントレコードを中心としたサブセットの定義
込み入った機能を1つの関数に詰め込んでいるように見えるわけですが、実際のところ、どのような内部動作で実現されているのでしょうか。本節では、この疑問を明らかにします。
SQL文の内部動作を調べる手段として、一般的に「実行計画（execution plan）」を調べるという方法があります。実行計画とは、DBMSがSQL文を実行する際に、どのようなアクセス経路でデータを取得し、どのような計算を行なうことが最も効率的かを判断するために作る、文字通り計画書です。いわば登山のルートを決めるようなものです。
実行計画は、DBMSによってフォーマットに違いはあるものの、ある程度訓練すると人間も読み解くことができます。したがって、SQL文が遅かったときに、その原因を突き止めるために実行計画を出力して解読するということを、チューニングのプロセスとして行なうことがあります（SQL文が複雑になるほど実行計画も複雑になり、それなりに解読も大変になりますが）。
本書の目的は実行計画を読めるようになることではないので、実行計画についての解説は省略しますが、ウィンドウ関数の実行計画は初見でも意味がわかるくらい簡単です。試しに、本章の最初で見た移動平均のクエリについて実行計画を見てみましょう。
SELECT shohin_id, shohin_mei, hanbai_tanka,
AVG (hanbai_tanka) OVER (ORDER BY shohin_id
ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW) AS moving_avg
FROM Shohin;
QUERY PLAN
-----------------------------------------------------------------------
WindowAgg (cost=20.76..24.61 rows=220 width=274)
-> Sort (cost=20.76..21.31 rows=220 width=242)
Sort Key: shohin_id
-> Seq Scan on shohin (cost=0.00..12.20 rows=220 width=242)
| id | select_type | table |（省略）| rows | filtered | Extra | +--+-------------+--------+ +-----+---------+------------------+ | 1 | SIMPLE | Shohin |（省略）| 8 | 100.00 | Using filesort |
サンプルに、PostgreSQLとMySQLの実行計画を掲載しています。MySQLの実行計画は横長のレイアウトで紙幅に収まりきらないため、重要な箇所だけ抜粋しています。
この実行計画は両方とも「Shohin」というテーブルのデータをスキャン（読み取り）して、読み取ったデータに対してソートを行なう、ということを意味しています。PostgreSQLでは「SORT」、MySQLでは「Using filesort」と、どちらもソートを意味するキーワードが現われていることが、それを示しています。
ウィンドウ関数の正体はソート
このことからもわかるように、ウィンドウ関数は、内部でレコード集合に対してソートを行なっています。2018年現在、これはDBMSの種類を問わず共通しています。なぜウィンドウ関数においてソートが必要になるかといえば、PARTITION BY句によるグループへの分類やORDER BY句によるレコードのソートで必要になるからです。RDBにおいてテーブルのレコードは物理的に順序付けられている保証はないため、一般的に、レコードをあるキーの値に基づいて順序付けようとするときは、ソートが必要になります。
そして、ソートが行なわれるということは、すなわちfor文やwhile文を使ったループが行なわれているということも意味しています。実行計画からは、どのようなソートアルゴリズムが利用されているかまでは判別できませんが、クイックソートであれマージソートであれ、手続き型言語においてはループを使って実装するのが一般的でしょう。実際、もし皆さんが、SQLではなく手続き型言語を使って、CSVやテキストファイルのような適当な形式のデータに対してウィンドウ関数と同等の計算を行なおうとする場合も、ループによるソートを行なうことで問題を解こうとするのではないでしょうか。
ハッシュとソート
一方で、ウィンドウ関数の実装方法として、ソートが本当に性能面で最適なのか、という点については、異なる意見もあります。下記の論文では、原理的にはPARTITION BY句をハッシュによって計算するほうが性能が良好になるケースがあることが、実測結果とともに示されています。
というのも、入力行数nに対してパーティション数がO(n)だとすれば、ハッシュはO(n)、ソートは最善でもO(n log n)になる。
――p.1062, 4.2 Determining the Window Frame Bounds
ハッシュ関数は、入力値が異なると出力値も基本的には異なる（値が重ならない）という特性を持っています。この出力値を「ハッシュ値」と呼びます。たとえば、「30」→「cdae7jh02」のような変換を行なうわけです（図2.3）。入力値とハッシュ値のペアを「ハッシュテーブル」と呼び、これを使ってグルーピングを行なうことで、ソートなしで集約できるのです（ハッシュ値に変換しなくてもグルーピングは可能ですが、ハッシュ値のほうが列数やデータ型を気にする必要がなく、ハッシュ値を入力にとる様々な関数も利用できるというメリットがあります）。
実際、PARTITION BY 句とほぼ同じ機能を持つGROUP BY 句は、OracleやPostgreSQLでは、ソートだけでなくハッシュで計算されることがあります。ただ、上記論文でも指摘されていますが、ハッシュが有利になるのはいくつかの前提が必要になり、常に有利であるとは言い切れないようですが、いずれGROUP BY 句のように、ウィンドウ関数の計算がハッシュで行なわれるようになる日も来るかもしれません。
まとめ
それでは、本章の要点を振り返りましょう。
- ウィンドウ関数の「ウィンドウ」とは、（原則として順序を持つ）「範囲」という意味。
- ウィンドウ関数の構文上では、PARTITION BY句とORDER BY句で特徴づけられたレコードの集合を意味するが、一般的に簡略形の構文が使われるため、かえってウィンドウの存在を意識しにくい。
- PARTITION BY句はGROUP BY句から集約の機能を引いて、カットの機能だけを残し、ORDER BY句はレコードの順序を付ける。
- フレーム句はカーソルの機能をSQLの構文に持ち込むことで、「カレントレコード」を中心にしたレコード集合の範囲を定義することができる。
- フレーム句を使うことで、異なる行のデータを1つの行に持ってくることができるようになり、行間比較が簡単に行なえるようになった。
- ウィンドウ関数の内部動作としては、現在のところ、レコードのソートが行なわれている。将来的にハッシュが採用される可能性もゼロではない。
続きは本書で
本書『達人に学ぶSQL徹底指南書 第2版』では、このほかにも脱初級者を目指す方のためにより便利にSQLを使うための手法について解説しています。
