SQL — ウィンドウ関数の基本 ( ORDER / PARTITION / FRAME を図で理解する )

# 注意

- この記事は「自分にとって理解しやすい図」を描いたもので、正式なものではない。
 
# データ

Alice / Bob / Carol というデータがあるとする。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/63590323-53b6-cd5c-85ac-81fd3d7d17f4.png)

それぞれに得点を持っているとする。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/01204f79-f461-044f-04ce-d3565f9b0a7a.png)

このデータ本体とは別に「ウィンドウ」というものがあると考える。
「ウィンドウ」という名前の通り、架空の場所に存在する枠のようなもの。

ここにはデータ本体のようでデータ本体でない少しデータ本体のようなデータが入る。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/dc306745-014b-56dc-4aaa-93df31cf02f6.png)


## ORDER

ウィンドウは必ず、 ORDER (順序)を持っている。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/d760a97f-88fa-6ee9-2cff-422b3ccb89c0.png)

たとえば「データを得点順に並べたウィンドウ」では、得点の一番高いBobが一番上に来ることになる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/a7915acd-a550-ad4b-7ba1-b65b39993dc1.png)


データの行はそれぞれ、関数を使ってウィンドウに問い合わせをおこなう。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/cf26d499-bd0e-31bf-206f-d57fcfcc0d4d.png)


たとえば Alice が `RANK()` を問い合わせると、「2位です」という答えが返ってくる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/b53e11e6-1dd2-4d00-7a54-34080e46d8bc.png)


Bob が 問い合わせると 1位が返ってくる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/698f111d-f14c-1b23-d933-709d5ae9b052.png)


Carol 問い合わせると3位が。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/e22f5edd-7322-b57c-217c-94b7e4788abe.png)

### まとめ

- ウィンドウは順序を持っている
- 「行」が「関数」を使って「ウィンドウ」に問い合わせる。

これがウィンドウ関数の基本である。

## PARTITION


ウィンドウは PARTITION (区切り) を持つことも出来る。

たとえばデータの各行が「性別」も持っている場合。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/b8f2caaa-7ccb-aaf7-8c5a-02f9e223443a.png)

性別ごとの PARTITION を作ってみる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/2d7d76db-818a-f223-a420-aa18a147925e.png)


この場合、 PARTITIONE ごとに ORDER を持つ。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/129f4a43-1524-c0ff-041d-c06c81e611dc.png)

性別ごとにデータを分けるとこんな感じ。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/5e547097-7375-07f4-a9a3-9d3be6ce2954.png)


それぞれの行は、ウィンドウ内の PARTITION に対して問い合わせをおこなう。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/58503ada-a7e5-84b2-cf20-3ff2f10c5495.png)

たとえば Alice が PARTITION に問い合わせると「(女性の中で)1位」と返ってくる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/69f53b2c-a2fb-9abc-a243-22e97ab126c3.png)

Bob が問い合わせると「(男性の中で)1位」が。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/7a8bee3d-a371-be3f-e2ca-bcf90e33141e.png)

Carol が問い合わせると「(女性の中で)2位」が。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/d60f43f3-24af-d573-df11-53bc1d4e5066.png)

# FRAME

ここでは PARTITION されていないウィンドウに話を戻す。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/fd054593-7d60-0712-99e9-c5deadc24e96.png)

ウィンドウはフレームを持つことが出来る。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/faab14db-19c2-f090-9da4-2fa891e23487.png)

たとえば「自分と、ひとつ前のデータ」というフレームを作った場合、Aliceにとってのフレームは「AliceとBob」の二個になる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/36537a0b-1b34-808e-607a-6620fca8dd29.png)

ここでは AVG 関数を使って、得点の平均を出してみる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/30077452-d428-8117-3705-aa2a23b14d6b.png)

Aliceが問い合わせると、AliceとBobの平均点「95」が返ってくる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/6b427543-43df-8365-0faa-fcc89b147f11.png)

Bobが問い合わせると、Bobひとりの平均点「100」が返ってくる。
(Bobの「ひとつ前のデータ」は存在しないので、この場合フレームは一個だけになる)

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/b754322d-c653-9d15-3ca5-2f49252ce16d.png)

Carolが問い合わせると、CarolとAliceの平均点「80」が返ってくる。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/89618/5de24606-2aab-9397-c2fc-11ac8d58cc2c.png)

# クエリで再現

( mysql  8.0.3 を利用 )

## データの用意

```
CREATE TABLE scores (name varchar(255), sex varchar(255), score int);
```

```
INSERT INTO scores (name, sex, score) VALUES ('Alice', 'female', 90); 
INSERT INTO scores (name, sex, score) VALUES ('Bob',   'male',   100);
INSERT INTO scores (name, sex, score) VALUES ('Carol', 'female', 70);
```

## ORDER

```sql
SELECT
  name,
  score,
  RANK() OVER (
    ORDER BY score DESC
  ) AS ranking
FROM
  scores
ORDER BY
  name ASC;
```

```
+-------+-------+---------+
| name  | score | ranking |
+-------+-------+---------+
| Alice |    90 |       2 |
| Bob   |   100 |       1 |
| Carol |    70 |       3 |
+-------+-------+---------+
```

## PARTITION

```sql
SELECT
  name,
  score,
  sex,
  RANK() OVER (
    PARTITION BY sex
    ORDER BY score DESC
  ) AS ranking
FROM
  scores
ORDER BY
  name ASC;
```

```
+-------+-------+--------+---------+
| name  | score | sex    | ranking |
+-------+-------+--------+---------+
| Alice |    90 | female |       1 |
| Bob   |   100 | male   |       1 |
| Carol |    70 | female |       2 |
+-------+-------+--------+---------+
```

## FRAME

```sql

SELECT
  name,
  score,
  AVG(score) OVER (
    ORDER BY score DESC
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS ranking
FROM
  scores
ORDER BY
  name ASC;
```

```
+-------+-------+----------+
| name  | score | ranking  |
+-------+-------+----------+
| Alice |    90 |  95.0000 |
| Bob   |   100 | 100.0000 |
| Carol |    70 |  80.0000 |
+-------+-------+----------+
```

# 環境

- mysql 8.0.3 ( mysql では 8.0.2 からウィンドウ関数がサポートされている )
- Docker for Mac

# 参考

- [クエリ リファレンス  |  BigQuery  |  Google Cloud Platform](https://cloud.google.com/bigquery/query-reference?hl=ja#windowfunctions)
- [Docker | Mac | mysql コマンドで外部=ホストからアクセスする ( mysql 公式イメージを利用 ) - Qiita](https://qiita.com/YumaInaura/items/88f396f06e91131a4415)








<!-- Update From Qiita API -->

# チャットメンバー募集


何か質問、悩み事、相談などあればLINEオープンチャットもご利用ください。

https://line.me/ti/g2/eEPltQ6Tzh3pYAZV8JXKZqc7PJ6L0rpm573dcQ





# Twitter


https://twitter.com/YumaInaura


<!-- Update From Qiita API -->