SQLに「順序」の概念を付加したウィンドウ関数｜Chopesu Blog - 文系・未経験でエンジニアに転身した元営業マンのエンジニアライフ

Contents

ウィンドウ関数

ウィンドウ関数

SQLにはウィンドウ関数と呼ばれる関数があり、行間比較をしたりするときにとても便利。本来SQLは「順序（データがどこにあるのか？（アドレス））」という概念を持たないが、ウィンドウ関数はSQLに「順序」の概念を取り入れることで、データの行間比較等を簡単にできるようにしているらしいが、処理のイメージが難しいので、自分なりに図解化しながらアウトプットしてみた。
概念の理解は下記記事がとても分かりやすかった。
https://qiita.com/YumaInaura/items/5ed5e8d63f325a1837fb

ウィンドウ関数の3つの機能

ウィンドウ関数は1つの関数のなかに、3つの機能を持っている。
これらの機能を組み合わせて、関数を作っている。「OVER」はウィンドウ関数を使用するトリガーのようなもの。

FRAMEで使用できるオプション

オプション	役割
ROWS	移動単位を行で設定
RANGE	移動単位を列の値で設定。基準列はORDER BYで指定した列
n PRECEDING	nだけ前へ(小さい方)へ移動。
n FOLLOWING	nだけ後へ(大きい方)へ移動。
UNBOUNDED PRECEDING	無制限にさかのぼるほうへ移動
UNBOUNDED　FOLLOWING	無制限に下るほうへ移動
CURRENT ROW	現在行

例えば、「ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING」とすると、BETWEEN前後で指定している行が同一のものになるので、1つ前の行が結果として返ってくる。

サンプル①(ORDER BYとFRAMEを使う)

SELECT month, name, price,
 　AVG(price) OVER (ORDER BY month
 　　　　　　　　　　　ROWS BETWEEN 1 PRECEDING
  　　　　　　　　　　　　　　　　　AND CURRENT ROW) AS avg FROM shohin;

SELECT month, name, price,

　AVG(price) OVER (ORDER BY month

　　　　　　　　　　　ROWS BETWEEN 1 PRECEDING

　　　　　　　　　　　　　　　　　AND CURRENT ROW) AS avg FROM shohin;

サンプル①のようにウィンドウ関数では集計関数(サンプル①でAVG)を使える。ただ、ウィンドウ関数のFRAMEが結果を1行しか返さない場合は、SUM・MIN・MAX・AVGを使っても結果は変わらないのでどれを使ってもOK。（ただ、FRAMEに日付が入る場合はデータ型の制約によりMIN、MAXしか使えない）

サンプル②(ORDER BYとPARTITION BYを使う)

SELECT name, class, score,
 　RANK() OVER (PARTITION BY class ORDER BY score) AS rank 
FROM Score
ORDER BY class;

SELECT name, class, score,

　RANK() OVER (PARTITION BY class ORDER BY score) AS rank

FROM Score

ORDER BY class;

集合をカット、つまりグルーピングという側面ではPARTITION BYとは別にGROUP BYがあるが、
GROUP BYの場合はグルーピングをした後に必ず「集計」というプロセスがあり、結果として返ってくる行が「一行」に集約されてしまうので、上記のようなランキングを作ることはできない。PARTITON BY(ウィンドウ関数)の良いところは、元のテーブルにあるデータ情報を保持しながらも、集計した結果を新たな列として取得することができる点にある。

サンプル③行間比較

例えば、ある企業の年度ごとの売上を比較して、対前年比で売上が成長しているのか、衰退しているのか、それとも現状維持なのかを知ることができる。

SELECT year,sales,
　　CASE WHEN current_sales > pre_sales THEN 'UP'　　　-- CASE式で列(var)の定義域を決める。
　　CASE WHEN current_sales < pre_sales THEN 'DOWN'
　　CASE WHEN current_sales = pre_sales THEN 'STAY'
　　ELSE '--' END AS var
　FROM (SELECT year,　　　　　　　　　　　　　　　　　　-- 副問い合わせを使ったFROM句で外側のクエリで使われる集合を取得
　　　　　　　　sales AS current_sales,
　　　　　　　　SUM(sales) OVER (ORDER BY year
　　　　　　　　　　　　　　　　　　RANGE BETWEEN 1 PRECEDING
　　　　　　　　　　　　　　　　　　AND 1 PRECEDING) AS pre_sales
　　　　FROM Sales) TMP
ORDER BY year;