【SQL】ウィンドウ関数について

ウィンドウ関数について学習したことをまとめています。

ウィンドウ関数の概要

ウィンドウ関数を使えば今までは相関サブクエリを使用して求めていた移動平均などがSELECT句に記載するだけで簡単に求めることができます。

ウィンドウ関数でできること

• 移動平均
• 異なる行を現在行に持ってこれる（行間比較）

ウィンドウ関数の動作

1. PARTITION BY 句によるテーブルのカット
2. ORDER BY 句によるレコードの順序付け
3. フレーム句によるカレントレコードを中心としたサブセットの定義

ウィンドウ関数の型

ウィンドウ関数構文

OLAP関数(引数) OVER (
    [ PARTITION BY 句 ]
    [ ORDER BY 句 ]
    [ フレーム句 ]
)

OLAP関数

分析関数とも言われる分析のための関数で専用の関数もあるが、集約関数も使用することができます。
以下よく使用されるOLAP関数を記載します。

関数名	機能
AVG	平均を求める
COUNT	件数を求める
MAX	最大値を求める
MIN	最小値を求める
SUM	全合計を求める
ROW_NUMBER	1から数えたパーティション内の現在行の数を返す　（例：　1,2,3,4,5）
RANK	現在行の順位を返す(同じ値の場合に重複あり、値は重複分飛ぶ)　（例： 1,2,2,4,5）
DENSE_RANK	現在行の順位を返す(同じ値の場合に重複あり、値は飛ばない)　（例： 1,2,2,3,4）
NTILE	行をバケットに分割する
FIRST_VALUE	最初の行の値を返す
LAST_VALUE	最後の行の値を返す
LAG	前の行の値を返す
LEAD	次の行の値を返す

フレーム句

フレーム句で使用できるオプションは以下になります。

キーワード	機能名
ROWS	移動単位を行で設定する
RANGE	移動単位を列の値で設定する。基準となる列はORDER BY句で指定された列
n PRECEDING	nだけ前へ移動する。nは正の整数
n FOLLOWING	nだけ後ろへ移動する。nは正の整数
UNBOUNDED PRECEDING	無制限に遡る方へ移動する
UNBOUNDED FOLLOWING	無制限に下る方へ移動する
CURRENT ROW	現在行

ウィンドウ関数の構文

無名構文は簡潔に書けるがOVER以降の括弧（ウィンドウ）に名前をつける名前付き構文を使用すればウィンドウを使いますことができます。
どちらにも利点があるため、使用する場面によって使い分ける必要があります。

無名構文

無名構文

SELECT
	product_id,
	product_name,
	sale_price,
	AVG(sale_price) OVER (
		ORDER BY product_id 
		ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
	) AS moving_avg
FROM
	Products;

名前付き構文

名前付き構文

SELECT
	product_id,
	product_name,
	sale_price,
	AVG(sale_price) OVER W AS moving_avg
FROM
	Products
WINDOW W AS (
		ORDER BY product_id 
		ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
	);