Zenn
👣

Window関数の基本について学びました

2023/05/19に公開
MySQL
初心者
tech

記事を書くきっかけ

学習した内容を整理するために本記事を書きました。

ゴール

Window関数について学んだことを整理することで知識を定着させる。

学習した内容

こちらのサイトでWindows関数についての基本を学びました。

Window関数とは

上に示したサイトでは、次のような説明がありました。

Window関数は、一連のクエリー行に対して集計のような操作を実行します。 ただし、集計操作ではクエリー行が単一の結果行にグループ化されますが、ウィンドウ関数ではクエリー行ごとに結果が生成されます。

例を見るとイメージがつかみやすいと思いました。
つぎのようなデータを持ったsalesテーブルがあるとします。

mysql> SELECT * FROM sales ORDER BY country, year, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2001 | Finland | Phone      |     10 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2001 | USA     | Computer   |   1200 |
| 2001 | USA     | TV         |    150 |
| 2001 | USA     | TV         |    100 |
+------+---------+------------+--------+

このテーブルに対して、COUNTやMAXやSUMなど通常の集約関数を利用した場合の例をつぎに示します。
例の上のクエリでは、profitをひとつのグループとして集約した値が出力されています。
例の下のクエリでは、countryごとにprofitをグループとして集約した値が出力されています。

mysql> SELECT SUM(profit) AS total_profit
       FROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+
mysql> SELECT country, SUM(profit) AS country_profit
       FROM sales
       GROUP BY country
       ORDER BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland |           1610 |
| India   |           1350 |
| USA     |           4575 |
+---------+----------------+

Window関数を利用した場合の例をつぎに示します。この場合はレコードごとに結果が出力されます。

mysql> SELECT
         year, country, product, profit,
         SUM(profit) OVER() AS total_profit,
         SUM(profit) OVER(PARTITION BY country) AS country_profit
       FROM sales
       ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

通常の集約関数ではグループごとに単一の結果が出力されますが、Window関数ではレコードごとに結果が出力されます。

Window関数の構文

Window関数の基本的な構文はつぎのようになります。

<Window関数> OVER (PARTITION BY col_name1 ORDER BY col_name2)

col_name1の部分には、順位付けを行う対象の範囲を指定します。
col_name2の部分には、順位付けを行う列を指定します。

Window関数の使用例

つぎに、Window関数の使用例を示します。今回はつぎの３つのWindow関数を使用してみます。
RANK():パーティション内の現在の行のランク (ギャップあり)
DENSE_RANK():パーティション内の現在の行のランク (ギャップなし)
ROW_NUBER():パーティション内の現在の行数

mysql> SELECT
	  year, country, product, profit,
	  RANK() OVER(PARTITION BY country ORDER BY profit DESC) AS ranking,
	  DENSE_RANK() OVER(PARTITION BY country ORDER BY profit DESC) AS dense_ranking,
	  ROW_NUMBER() OVER(PARTITION BY country ORDER BY profit DESC) AS row_num
       FROM sales;
+------+---------+------------+--------+---------+---------------+---------+
| year | country | product    | profit | ranking | dense_ranking | row_num |
+------+---------+------------+--------+---------+---------------+---------+
| 2000 | Finland | Computer   |   1500 |       1 |             1 |       1 |
| 2000 | Finland | Phone      |    100 |       2 |             2 |       2 |
| 2001 | Finland | Phone      |     10 |       3 |             3 |       3 |
| 2000 | India   | Computer   |   1200 |       1 |             1 |       1 |
| 2000 | India   | Calculator |     75 |       2 |             2 |       2 |
| 2000 | India   | Calculator |     75 |       2 |             2 |       3 |
| 2000 | USA     | Computer   |   1500 |       1 |             1 |       1 |
| 2001 | USA     | Computer   |   1500 |       1 |             1 |       2 |
| 2001 | USA     | Computer   |   1200 |       3 |             2 |       3 |
| 2001 | USA     | TV         |    150 |       4 |             3 |       4 |
| 2001 | USA     | TV         |    100 |       5 |             4 |       5 |
| 2000 | USA     | Calculator |     75 |       6 |             5 |       6 |
| 2001 | USA     | Calculator |     50 |       7 |             6 |       7 |
+------+---------+------------+--------+---------+---------------+---------+

上記の例では、countryごとに、profitの降順でランキングと連番を生成しています。

  • rankingでは同じ順位が存在した場合に、後続の順位が飛んでいる。
  • dense_rankingでは同じ順位が存在した場合に、後続の順位が飛んでいない。
  • row_numではcountryごとに連番が生成されている。
    以上のことが確認できた。

まとめ

こちらのサイトでWindow関数について学びました。Window関数の概要と通常の集約関数との違いを確認したあとに、Window関数の基本的な構文について学びました。最後に、自分でWindow関数を使用して動きを確認しました。

Discussion