SQLウィンドウ関数Explained

27 of 27 rows

関数 / 句↕	返す値↕	例↕
ランク付け
⤢ROW_NUMBER()	ウィンドウ内の各行に対する一意の連続整数。	ROW_NUMBER() OVER (ORDER BY total DESC)
⤢RANK()	すき間のあるランク。同順位は同じランクを共有し、次の番号を飛ばします。	RANK() OVER (ORDER BY total DESC)
⤢DENSE_RANK()	すき間のないランク。同順位は同じランクを共有し、何も飛ばされません。	DENSE_RANK() OVER (ORDER BY total DESC)
⤢NTILE(n)	順序付きウィンドウをほぼ等しいn個のバケットに分割します。	NTILE(4) OVER (ORDER BY total DESC)
隣接行を覗く
⤢LAG(expr, n, default)	n行前の値。該当する行がない場合はデフォルト値で置き換えられます。	LAG(total, 1, 0) OVER (ORDER BY created_at)
⤢LEAD(expr, n, default)	n行先の値。該当する行がない場合はデフォルト値で置き換えられます。	LEAD(total, 1, 0) OVER (ORDER BY created_at)
ウィンドウ上の集計
⤢SUM() OVER	行を潰さない、累計またはパーティションごとの合計。	SUM(total) OVER (ORDER BY created_at)
⤢AVG() OVER	累計またはパーティションごとの平均。	AVG(total) OVER (PARTITION BY user_id)
⤢COUNT() OVER	累計またはパーティションごとの行数。	COUNT(*) OVER (PARTITION BY user_id)
⤢MIN() OVER	ウィンドウまたはパーティション全体での最小値。	MIN(total) OVER (PARTITION BY user_id)
⤢MAX() OVER	ウィンドウまたはパーティション全体での最大値。	MAX(total) OVER (PARTITION BY user_id)
⤢Running total	ORDER BYと先頭から開いたフレームによるSUM。各行は自分までのすべてを合計します。	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
⤢Moving average	現在の行の前後n行のフレームに対するAVG。	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
最初・最後・パーセンタイル
⤢FIRST_VALUE()	ウィンドウの順序付けにおける最初の値。	FIRST_VALUE(total) OVER (ORDER BY total DESC)
⤢LAST_VALUE()	フレーム内の最後の値。UNBOUNDED FOLLOWINGがないと、フレームは現在の行で止まります。	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
⤢NTH_VALUE(expr, n)	ウィンドウの順序付けにおけるn番目の行の値。	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
⤢PERCENT_RANK()	行の相対ランク。0から1まで。	PERCENT_RANK() OVER (ORDER BY total)
⤢CUME_DIST()	現在の行までを含む行の割合。1/nから1まで。	CUME_DIST() OVER (ORDER BY total)
OVER句
⤢OVER (...)	すべての関数が読むウィンドウを定義します。	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
⤢PARTITION BY	行のグループごとにウィンドウを再開します。	PARTITION BY user_id
⤢ORDER BY	各パーティション内の行を順序付けします。	ORDER BY created_at
⤢ROWS BETWEEN	スライドするフレームの境界を定めます。例：前の行から後の行まで。	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
⤢RANGE BETWEEN	行の位置ではなく値でフレームの境界を定めます。CURRENT ROWは同順位の行を含み、ORDER BYがある場合の既定フレームです。	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
⤢GROUPS BETWEEN	ORDER BYの同順位グループ単位でフレームの境界を定めます。	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
⤢EXCLUDE CURRENT ROW	現在の行をフレームから除外します。EXCLUDE TIESは代わりに同順位の行を除外します。	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
⤢WINDOW	クエリごとに一度だけウィンドウに名前を付け、複数のOVER句で再利用できるようにします。	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
⤢FILTER (WHERE ...)	ウィンドウが適用される前に、ウィンドウ集計が読む行を絞り込みます。	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)