SQL — ウィンドウ関数の基本 ( ORDER / PARTITION / FRAME を図で理解する )
注意
- この記事は「自分にとって理解しやすい図」を描いたもので、正式なものではない。
データ
Alice / Bob / Carol というデータがあるとする。
それぞれに得点を持っているとする。
このデータ本体とは別に「ウィンドウ」というものがあると考える。
「ウィンドウ」という名前の通り、架空の場所に存在する枠のようなもの。
ここにはデータ本体のようでデータ本体でない少しデータ本体のようなデータが入る。
ORDER
ウィンドウは必ず、 ORDER (順序)を持っている。
たとえば「データを得点順に並べたウィンドウ」では、得点の一番高いBobが一番上に来ることになる。
データの行はそれぞれ、関数を使ってウィンドウに問い合わせをおこなう。
たとえば Alice が RANK() を問い合わせると、「2位です」という答えが返ってくる。
Bob が 問い合わせると 1位が返ってくる。
Carol 問い合わせると3位が。
まとめ
- ウィンドウは順序を持っている
- 「行」が「関数」を使って「ウィンドウ」に問い合わせる。
これがウィンドウ関数の基本である。
PARTITION
ウィンドウは PARTITION (区切り) を持つことも出来る。
たとえばデータの各行が「性別」も持っている場合。
性別ごとの PARTITION を作ってみる。
この場合、 PARTITIONE ごとに ORDER を持つ。
性別ごとにデータを分けるとこんな感じ。
それぞれの行は、ウィンドウ内の PARTITION に対して問い合わせをおこなう。
たとえば Alice が PARTITION に問い合わせると「(女性の中で)1位」と返ってくる。
Bob が問い合わせると「(男性の中で)1位」が。
Carol が問い合わせると「(女性の中で)2位」が。
FRAME
ここでは PARTITION されていないウィンドウに話を戻す。
ウィンドウはフレームを持つことが出来る。
たとえば「自分と、ひとつ前のデータ」というフレームを作った場合、Aliceにとってのフレームは「AliceとBob」の二個になる。
ここでは AVG 関数を使って、得点の平均を出してみる。
Aliceが問い合わせると、AliceとBobの平均点「95」が返ってくる。
Bobが問い合わせると、Bobひとりの平均点「100」が返ってくる。
(Bobの「ひとつ前のデータ」は存在しないので、この場合フレームは一個だけになる)
Carolが問い合わせると、CarolとAliceの平均点「80」が返ってくる。
クエリで再現
( 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
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
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
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
- Docker | Mac | mysql コマンドで外部=ホストからアクセスする ( mysql 公式イメージを利用 ) - Qiita
チャットメンバー募集
何か質問、悩み事、相談などあればLINEオープンチャットもご利用ください。
公開日時
2017-12-28
Discussion