【PostgreSQL】ウィンドウ関数使い方メモ

# 1. この記事は何？
PostgreSQLでWindow関数の便利さに感動したので、後々でも思い出せるように整理したものです。

# 2. 実行環境
[SQL Fiddle](http://sqlfiddle.com/#!17/e1277/1)でテスト出来ます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/ff7fb531-4dc8-ae6f-692a-5104cf7d76d3.png)


# 3. Window関数って？
[3.5. ウィンドウ関数](https://www.postgresql.jp/document/9.6/html/tutorial-window.html)より引用
> ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、通常の集約関数とは異なり、ウィンドウ関数の使用は単一出力行に行をグループ化しません。 行はそれぞれ個別の身元を維持します。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることができます。

この関数のポイントとなる部分は以下かと思います。

* 特定カラムのグループとみなして集約関数が利用できる
* **集約関数の対象となった行はそのまま残る**
* Select内でのみ利用可能

ざっくり「Selectの結果を維持したまま、集約関数の結果を後付けできる関数」と理解しています。

## 3-1. どうやって使うの？
まずはクエリをどうぞ。

```sql
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グループとしてみなします**）

```sql
avg(hanbai_tanka) OVER (PARTITION BY shohin_bunrui)
-----------------      ----------------------------
--> 2.集約関数          -> 1.shoshin_bunruiをグループの単位とする
```
処理のイメージ↓
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/d54bc8e2-bca5-f4b7-daa8-63a98de5446b.png)![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/48669a65-24c2-c958-e5e7-9e644451e7b1.png)

Window関数で定義したグループはウィンドウフレームと呼ばれ、その単位で`OVER`句の前に定義した関数が実行されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/53d77f17-33ed-28fa-65f2-0dd4cdbfb075.png)

### 3-1-1. ウィンドウフレームの細かな指定
ウィンドウフレームは`PARTITION BY`の中でオプションを付けて、明示的に範囲を指定することが出来ます。

#### 3-1-1-1. 処理対象となる行を基準とした範囲の指定
`ROWS`を指定することで、処理する対象の行を基準とした範囲をウィンドウフレームとして指定することが出来ます。

```sql
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`で追加の絞り込み条件を追加しています。

```sql
sum(hanbai_tanka) OVER (PARTITION BY shohin_bunrui ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
--                                                 ----         -----------     -----------
--                                                 |            |               |
--                                                 └─ ウィンドウフレームの行数を指定
--                                                              |               |
--                                                              └─ 処理する行の2行手前から
--                                                                              |
--                                                                              └─ 処理をする行まで
```
結果は以下の通りです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/24a60bb5-ae79-f0d5-aacc-1614d248bb9a.png)
自身の2行前～自身の行までの集計を行い表示しています。
処理のイメージ↓
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/40096492-e46c-e885-1881-4f4b3a6f83a7.png)

##### 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と同義です。

```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...

```sql
SELECT
  shohin_mei
  , shohin_bunrui
  , hanbai_tanka
  , row_number() OVER (ORDER BY hanbai_tanka)
FROM 
  Shohin;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/d904d9f1-0789-e447-77fb-cfdac0f6c99d.png)


#### 3-2-1-2. RANK()
フレーム内での順位を表示することが出来る関数です。
同値の値がある場合は同位とされ、重複がある数だけ次の順位がずれます。
-> 例：1位、2位、2位、4位...

```sql
SELECT
  shohin_mei
  , shohin_bunrui
  , hanbai_tanka
  , rank() OVER (ORDER BY hanbai_tanka)
FROM 
  Shohin;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/e86a139b-2749-1e93-1c91-61aab3783f64.png)

#### 3-2-1-3. DENSE_RANK()
フレーム内での順位を表示することが出来る関数です。
同値の値がある場合は同位とされ、次の順位は連番で取得されます
-> 例：1位、2位、2位、3位...

```sql
SELECT
  shohin_mei
  , shohin_bunrui
  , hanbai_tanka
  , dense_rank() OVER (ORDER BY hanbai_tanka)
FROM 
  Shohin;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/ac26e6e0-fb83-98db-7a44-3bdd4f327aed.png)

### 3-3. 集約関数の利用
この記事の例でも記載しましたが、集約関数を利用することが出来ます。
-> 例：`SUM`, `AVG`, `MAX`, `MIN`

フレームを指定して処理を行うことで移動合計や移動平均等、数字の推移を取得することが出来るため、クエリ一発で数値の分析まで出来るので**ものすごく便利**です。
↓「キッチン用品」のデータを上から処理し、処理行+処理行の前2行を合計範囲とした移動合計の例↓
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146452/40096492-e46c-e885-1881-4f4b3a6f83a7.png)


# 参考サイト
* [【SQL】ブラウザ上でちょっとしたクエリを実行して結果を確認したいときに便利なサイト](https://zeema.hatenablog.com/entry/2018/04/30/122453)
* [3.5. ウィンドウ関数
](https://www.postgresql.jp/document/9.6/html/tutorial-window.html)
* [4.2. 評価式](https://www.postgresql.jp/document/9.6/html/sql-expressions.html#syntax-window-functions)
* [PostgreSQLウィンドウ関数のTips
](https://postd.cc/window_functions_postgresql/)

# 参考書籍
* [SQL 第2版 ゼロからはじめるデータベース操作
](https://www.shoeisha.co.jp/book/detail/9784798144450)