Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
2
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

@YumaInaura

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

注意

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

データ

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

image.png

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

image.png

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

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

image.png

ORDER

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

まとめ

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

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

PARTITION

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

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

FRAME

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

image.png

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

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

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

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
2
Help us understand the problem. What are the problem?