1. この記事は何？
PostgreSQLでWindow関数の便利さに感動したので、後々でも思い出せるように整理したものです。
2. 実行環境
SQL Fiddleでテスト出来ます。
3. Window関数って？
3.5. ウィンドウ関数より引用
ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、通常の集約関数とは異なり、ウィンドウ関数の使用は単一出力行に行をグループ化しません。 行はそれぞれ個別の身元を維持します。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることができます。
この関数のポイントとなる部分は以下かと思います。
- 特定カラムのグループとみなして集約関数が利用できる
- 集約関数の対象となった行はそのまま残る
- Select内でのみ利用可能
ざっくり「Selectの結果を維持したまま、集約関数の結果を後付けできる関数」と理解しています。
3-1. どうやって使うの？
まずはクエリをどうぞ。
SELECT
shohin_mei
, shohin_bunrui
, avg(hanbai_tanka) OVER (PARTITION BY shohin_bunrui)
FROM
Shohin;
結果は以下の通りです。
| shohin_mei | shohin_bunrui | avg |
|---|---|---|
| ボールペン | 事務用品 | 300 |
| 穴あけパンチ | 事務用品 | 300 |
| 圧力鍋 | キッチン用品 | 2795 |
| 包丁 | キッチン用品 | 2795 |
| フォーク | キッチン用品 | 2795 |
| おろしがね | キッチン用品 | 2795 |
| Tシャツ | 衣服 | 2500 |
| カッターシャツ | 衣服 | 2500 |
ウィンドウ関数の呼び出しは常に集約関数とその直後のOVER句を含んで行います。
-> OVER句の中で明確に条件を指定して、どのカラムをグループの単位とするのか、グループ内のソート順などを設定します。（※OVER句の引数として何も指定しなければ、Selectした結果すべてを1グループとしてみなします）
avg(hanbai_tanka) OVER (PARTITION BY shohin_bunrui)
----------------- ----------------------------
--> 2.集約関数 -> 1.shoshin_bunruiをグループの単位とする
処理のイメージ↓
Window関数で定義したグループはウィンドウフレームと呼ばれ、その単位でOVER句の前に定義した関数が実行されます。
3-1-1. ウィンドウフレームの細かな指定
ウィンドウフレームはPARTITION BYの中でオプションを付けて、明示的に範囲を指定することが出来ます。
3-1-1-1. 処理対象となる行を基準とした範囲の指定
ROWSを指定することで、処理する対象の行を基準とした範囲をウィンドウフレームとして指定することが出来ます。
SELECT
shohin_mei
, shohin_bunrui
, hanbai_tanka
, sum(hanbai_tanka) OVER (PARTITION BY shohin_bunrui ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM
Shohin;
PARTITION BYの部分にROWで追加の絞り込み条件を追加しています。
sum(hanbai_tanka) OVER (PARTITION BY shohin_bunrui ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- ---- ----------- -----------
-- | | |
-- └─ ウィンドウフレームの行数を指定
-- | |
-- └─ 処理する行の2行手前から
-- |
-- └─ 処理をする行まで
結果は以下の通りです。
自身の2行前～自身の行までの集計を行い表示しています。
処理のイメージ↓
3-1-1-1-1. 範囲を指定するときに利用する句
-
BETWEEN- 例：
BETWEEN x AND y -
xとyの範囲の行を取得します
- 例：
-
PRECEDING- 例：
x PRECEDING - 処理している行の手前
x行を取得します
- 例：
-
FOLLOWING- 例：
x FOLLOWING - 処理している行の後
x行を取得します
- 例：
-
CURRENT ROW- 自身の行を取得します
3-1-1-2. 範囲を指定するときに気を付けるポイント
ROWSで範囲を指定する際には、BETWEENを省略した記述をすることも可能です。
-- 範囲の指定には↓のどちらかの記述をすればOK
{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end
以下のSQLは3-1-1-1. 処理対象となる行を基準とした範囲の指定のSQLと同義です。
SELECT
shohin_mei
, shohin_bunrui
, hanbai_tanka
, sum(hanbai_tanka) OVER (PARTITION BY shohin_bunrui ROWS 2 PRECEDING)
FROM
Shohin;
省略した場合には自動的にframe_endにCURRENT ROWが指定されます。
-> BETWEEN frame_start AND CURRENT ROW が範囲条件として指定される
上記の通りのBETWEENで条件が指定されるため、前後関係を正しく保つ必要上、省略時にはPRECEDINGしか指定できません。
-> FOLLOWINGを指定した場合は、ERROR: frame starting from following row cannot end with current row Positionが発生します`。
3-2. ウィンドウ関数で利用する関数
3-2-1. 順位付け
順位付けを行う場合は、ORDER BYで順位を付けたいカラムを指定しないと全ての値が1で固定されます。
3-2-1-1. ROW_NUMBER()
フレーム内での行番号を表示することが出来る関数です。
同値判断などもなく、機械的に上の行から行番号を付与している重複がある数だけ次の順位がずれます。
-> 例：1、2、3、4...
SELECT
shohin_mei
, shohin_bunrui
, hanbai_tanka
, row_number() OVER (ORDER BY hanbai_tanka)
FROM
Shohin;
3-2-1-2. RANK()
フレーム内での順位を表示することが出来る関数です。
同値の値がある場合は同位とされ、重複がある数だけ次の順位がずれます。
-> 例：1位、2位、2位、4位...
SELECT
shohin_mei
, shohin_bunrui
, hanbai_tanka
, rank() OVER (ORDER BY hanbai_tanka)
FROM
Shohin;
3-2-1-3. DENSE_RANK()
フレーム内での順位を表示することが出来る関数です。
同値の値がある場合は同位とされ、次の順位は連番で取得されます
-> 例：1位、2位、2位、3位...
SELECT
shohin_mei
, shohin_bunrui
, hanbai_tanka
, dense_rank() OVER (ORDER BY hanbai_tanka)
FROM
Shohin;
3-3. 集約関数の利用
この記事の例でも記載しましたが、集約関数を利用することが出来ます。
-> 例：SUM, AVG, MAX, MIN
フレームを指定して処理を行うことで移動合計や移動平均等、数字の推移を取得することが出来るため、クエリ一発で数値の分析まで出来るのでものすごく便利です。
↓「キッチン用品」のデータを上から処理し、処理行+処理行の前2行を合計範囲とした移動合計の例↓