LoginSignup
26
16

More than 3 years have passed since last update.

@niwasawa

SQL のウィンドウ関数を使うサンプル: OVER, PARTITION BY, ORDER BY, ROW_NUMBER, RANK, DENSE_RANK

Posted at

概要

  • SQL のウィンドウ関数を使うサンプルを示す
  • OVER 句, PARTITION BY 句, ORDER BY 句, ROW_NUMBER 関数, RANK 関数, DENSE_RANK 関数を使う
  • 環境: MySQL Ver 8.0.21 for osx10.15 on x86_64 (Homebrew)

ウィンドウ関数の機能

ウィンドウ関数とは

ウィンドウ関数はテーブルから部分集合を切り出して処理をかける機能。
OVER 句にて、 PARTITION BY 句で部分集合を切り出し、部分集合内を ORDER BY 句でソートし、部分集合を順序関数または集約関数で処理する。

窓関数 (SQL) - Wikipedia

SQL において、窓関数（まどかんすう）もしくはウィンドウ関数 (ウィンドウかんすう、英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されている。分析関数やOLAP機能と呼ばれる場合もある。

PARTITION BY 句

指定したカラムの値ごとに部分集合を作る。

ORDER BY 句

部分集合内を指定した条件でソートする。

ROW_NUMBER 関数

部分集合内のレコードに連番を割り当てる関数。

MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions

Returns the number of the current row within its partition. Rows numbers range from 1 to the number of partition rows.

ORDER BY affects the order in which rows are numbered. Without ORDER BY, row numbering is nondeterministic.

RANK 関数

部分集合内のレコードに順位の数値を割り当てる関数。同じ順位の場合は同じ数値を割り当てて、その後に続く順位は +1 とはならず数値をスキップする。

MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions

Returns the rank of the current row within its partition, with gaps. Peers are considered ties and receive the same rank. This function does not assign consecutive ranks to peer groups if groups of size greater than one exist; the result is noncontiguous rank numbers.

This function should be used with ORDER BY to sort partition rows into the desired order. Without ORDER BY, all rows are peers.

DENSE_RANK 関数

部分集合内のレコードに順位の数値を割り当てる関数。同じ順位の場合は同じ数値を割り当てて、その後に続く順位は +1 した数値になる。

MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions

Returns the rank of the current row within its partition, without gaps. Peers are considered ties and receive the same rank. This function assigns consecutive ranks to peer groups; the result is that groups of size greater than one do not produce noncontiguous rank numbers. For an example, see the RANK() function description.

This function should be used with ORDER BY to sort partition rows into the desired order. Without ORDER BY, all rows are peers.

サンプル

サンプルデータを準備

買ったお菓子テーブルを作成。

mysql> CREATE TABLE katta_okashi (
    ->   number INTEGER,
    ->   name   VARCHAR(128),
    ->   okashi VARCHAR(128),
    ->   price  INTEGER
    -> );
Query OK, 0 rows affected (0.02 sec)

買ったお菓子データを追加。

mysql> INSERT INTO katta_okashi VALUES
    ->   (1, 'Alice', 'candy',     100),
    ->   (2, 'Alice', 'candy',      50),
    ->   (3, 'Alice', 'cookie',     30),
    ->   (4, 'Bob',   'chocolate', 200),
    ->   (5, 'Bob',   'chocolate',  20),
    ->   (6, 'Bob',   'cookie',     20);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

買ったお菓子データ一覧を出力。

mysql> select * from katta_okashi;
+--------+-------+-----------+-------+
| number | name  | okashi    | price |
+--------+-------+-----------+-------+
|      1 | Alice | candy     |   100 |
|      2 | Alice | candy     |    50 |
|      3 | Alice | cookie    |    30 |
|      4 | Bob   | chocolate |   200 |
|      5 | Bob   | chocolate |    20 |
|      6 | Bob   | cookie    |    20 |
+--------+-------+-----------+-------+
6 rows in set (0.00 sec)

OVER + ROW_NUMBER

PARTITION BY を使わない場合は部分集合を作らない。

1つの集合として扱い、ナンバリングする。

mysql> SELECT
    ->   name,
    ->   okashi,
    ->   price,
    ->   ROW_NUMBER()
    ->     OVER () AS my_number
    -> FROM
    ->   katta_okashi;
+-------+-----------+-------+-----------+
| name  | okashi    | price | my_number |
+-------+-----------+-------+-----------+
| Alice | candy     |   100 |         1 |
| Alice | candy     |    50 |         2 |
| Alice | cookie    |    30 |         3 |
| Bob   | chocolate |   200 |         4 |
| Bob   | chocolate |    20 |         5 |
| Bob   | cookie    |    20 |         6 |
+-------+-----------+-------+-----------+
6 rows in set (0.00 sec)

OVER + PARTITION BY + ROW_NUMBER

name カラムの値ごとに部分集合を作り、それぞれの部分集合内でナンバリングする。

mysql> SELECT
    ->   name,
    ->   okashi,
    ->   price,
    ->   ROW_NUMBER()
    ->     OVER (
    ->       PARTITION BY name
    ->      ) AS my_number
    -> FROM
    ->   katta_okashi;
+-------+-----------+-------+-----------+
| name  | okashi    | price | my_number |
+-------+-----------+-------+-----------+
| Alice | candy     |   100 |         1 |
| Alice | candy     |    50 |         2 |
| Alice | cookie    |    30 |         3 |
| Bob   | chocolate |   200 |         1 |
| Bob   | chocolate |    20 |         2 |
| Bob   | cookie    |    20 |         3 |
+-------+-----------+-------+-----------+
6 rows in set (0.00 sec)

OVER + PARTITION BY 複数カラム指定 + ROW_NUMBER

name カラムと okashi カラムの値の組み合わせごとに部分集合を作り、それぞれの部分集合内でナンバリングする。

mysql> SELECT
    ->   name,
    ->   okashi,
    ->   price,
    ->   ROW_NUMBER()
    ->     OVER (
    ->       PARTITION BY name, okashi
    ->      ) AS my_number
    -> FROM
    ->   katta_okashi;
+-------+-----------+-------+-----------+
| name  | okashi    | price | my_number |
+-------+-----------+-------+-----------+
| Alice | candy     |   100 |         1 |
| Alice | candy     |    50 |         2 |
| Alice | cookie    |    30 |         1 |
| Bob   | chocolate |   200 |         1 |
| Bob   | chocolate |    20 |         2 |
| Bob   | cookie    |    20 |         1 |
+-------+-----------+-------+-----------+
6 rows in set (0.02 sec)

OVER + PARTITION BY + ORDER BY + ROW_NUMBER

name カラムの値ごとに部分集合を作り、それぞれの部分集合内にて price カラムでソートしてナンバリングする。

mysql> SELECT
    ->   name,
    ->   okashi,
    ->   price,
    ->   ROW_NUMBER()
    ->     OVER (
    ->       PARTITION BY name
    ->       ORDER BY price
    ->      ) AS my_number
    -> FROM
    ->   katta_okashi;
+-------+-----------+-------+-----------+
| name  | okashi    | price | my_number |
+-------+-----------+-------+-----------+
| Alice | cookie    |    30 |         1 |
| Alice | candy     |    50 |         2 |
| Alice | candy     |   100 |         3 |
| Bob   | chocolate |    20 |         1 |
| Bob   | cookie    |    20 |         2 |
| Bob   | chocolate |   200 |         3 |
+-------+-----------+-------+-----------+
6 rows in set (0.00 sec)

OVER + PARTITION BY + ORDER BY + RANK

name カラムの値ごとに部分集合を作り、それぞれの部分集合内にて price カラムでソートしてランキングする。
同順位が存在する場合は次のランク数との間に隙間ができる。
例えば name ='Bob' の部分集合では price = 20 が2つあるため同順1位になり、次の price = 200 は2位ではなく3位になる。

mysql> SELECT
    ->   name,
    ->   okashi,
    ->   price,
    ->   RANK()
    ->     OVER (
    ->       PARTITION BY name
    ->       ORDER BY price
    ->      ) AS my_number
    -> FROM
    ->   katta_okashi;
+-------+-----------+-------+-----------+
| name  | okashi    | price | my_number |
+-------+-----------+-------+-----------+
| Alice | cookie    |    30 |         1 |
| Alice | candy     |    50 |         2 |
| Alice | candy     |   100 |         3 |
| Bob   | chocolate |    20 |         1 |
| Bob   | cookie    |    20 |         1 |
| Bob   | chocolate |   200 |         3 |
+-------+-----------+-------+-----------+
6 rows in set (0.01 sec)

OVER + PARTITION BY + ORDER BY + DENSE_RANK

name カラムの値ごとに部分集合を作り、それぞれの部分集合内にて price カラムでソートしてランキングする。
同順位が存在する場合は次のランク数との間に隙間ができない。
例えば name ='Bob' の部分集合では price = 20 が2つあるため同順1位になり、次の price = 200 は3位ではなく2位になる。

mysql> SELECT
    ->   name,
    ->   okashi,
    ->   price,
    ->   DENSE_RANK()
    ->     OVER (
    ->       PARTITION BY name
    ->       ORDER BY price
    ->      ) AS my_number
    -> FROM
    ->   katta_okashi;
+-------+-----------+-------+-----------+
| name  | okashi    | price | my_number |
+-------+-----------+-------+-----------+
| Alice | cookie    |    30 |         1 |
| Alice | candy     |    50 |         2 |
| Alice | candy     |   100 |         3 |
| Bob   | chocolate |    20 |         1 |
| Bob   | cookie    |    20 |         1 |
| Bob   | chocolate |   200 |         2 |
+-------+-----------+-------+-----------+
6 rows in set (0.01 sec)

OVER + PARTITION BY + RANK

MySQL の RANK 関数では ORDER BY を付けないとすべて1位になる。

mysql> SELECT
    ->   name,
    ->   okashi,
    ->   price,
    ->   RANK()
    ->     OVER (
    ->       PARTITION BY name
    ->      ) AS my_number
    -> FROM
    ->   katta_okashi;
+-------+-----------+-------+-----------+
| name  | okashi    | price | my_number |
+-------+-----------+-------+-----------+
| Alice | candy     |   100 |         1 |
| Alice | candy     |    50 |         1 |
| Alice | cookie    |    30 |         1 |
| Bob   | chocolate |   200 |         1 |
| Bob   | chocolate |    20 |         1 |
| Bob   | cookie    |    20 |         1 |
+-------+-----------+-------+-----------+
6 rows in set (0.01 sec)

参考資料

26
16
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
26
16