SQL学習シリーズ⑦：ウィンドウ関数

はじめに

前回あえて相関サブクエリについて詳しく解説しませんでした。
というのも、相関サブクエリは大抵の場合はウィンドウ関数に置き換えることができかつ、基本的にはウィンドウ関数のほうがパフォーマンスが優れていることが上げられるためです。

もちろん相関サブクエリとウィンドウ関数は同じ処理をするわけでは無いので、相関サブクエリが必要な場面はあるはずです。

なのでウィンドウ関数を今回紐解きながら相関サブクエリとの違いや使いどころを考えます

相関サブクエリとは

その前に相関サブクエリから説明します。

相関サブクエリはその名前の通りにサブクエリの一種です。その上で外側のクエリの値をサブクエリ内で使用する仕組みです。
動作イメージはサブクエリの中身関係なく外のクエリが動く、その次にサブクエリが動く、1行を取得、外のクエリが動く……と繰り返すのが相関サブクエリです。

こうすることによって全体比較から、自分が所属するクラスの平均みたいなものを求めることができます。

ただし基本的に行分のループするためパフォーマンス的には重くなることが予測されるためFROMを絞るとかの対策は必要かもしれませんね。

形としてはこうなります

SELECT name, class, age FROM users AS u1
WHERE u1.age < (
  SELECT AVG(age)
  FROM users AS u2
  WHERE u1.class = u2.class
);

usersテーブルから、自分が所属するクラスの平均年齢より若いユーザーを全て取得する、みたいなSQLになります。
サブクエリ内のWHEREはそのclass同士を紐づけているというより、外側のクエリを1行ずつみて今回の場合はclassの定数を入れているだけです。
つまり1組なら1になるし、2組なら2になる。その都度切り替わるのでグループ化ができるというわけですね。
構文としては私も苦手なので解説はここまでとします。

ウィンドウ関数とは

分析関数ともいうこの関数では、集合関数と同じ集計動作をそれぞれの行に実行するみたいな動作をします。
一見すれば相関サブクエリと似たように感じますが、利点はサブクエリ的な書き方はせず効率的に書けることと、動作が相関サブクエリよりも軽量になりやすいというのが特徴らしいです。

ウィンドウ関数を捉える上で用途としては、GROUP BY句や集計関数のように行を減らすのではなく、行を残して処理をすることができる、という風に覚えると結構良いかも知れません。

先ほどの構文をウィンドウ関数に入れるとこうなります。

SELECT name, class, age FROM (
  SELECT name, class, age,
    AVG(age) OVER (PARTITION BY class) AS class_avg_age
  FROM users
) AS t
WHERE age < class_avg_age;

あえて今回は相関サブクエリでの処理と同じにするためにFROM内にサブクエリとして置いていますが、ウィンドウ関数の処理は色んなところで活用出来ます。

未定

現時点でウィンドウ関数や相関サブクエリについてはチラッとしか勉強していないので深堀りが十分できていません。
そのため達人DBの書籍が終わり次第もう少しSQL群について深堀りできたらなと思っています。ここまでsql1~7までシリーズ化しましたが、もう少し深堀りしてから記事の追記修正を行いたいと思います。

次回からはSQLではなくDBの話に入るかと思います。よろしくお願いします。