はじめに

MySQL8.0がリリースされたことにより、Window関数の利用が可能になりました。
Oracle、PosgreSQL、SQL Server等でも利用できる関数ですが、「難しそう」、「どういう場合に使えば良いかわからない」というイメージを持たれている方もいると思いますので、この記事ではシンプルにどのようなことが出来るのかを紹介します。

第一弾は、「集計関数×Window関数」のコラボです。
集計関数とは、MAXやSUMやAVG等のデータを集計する関数ですが、実はほどんどの集計関数がWindow関数として利用できます。集計関数の種類やWindow関数として利用可能かという点に関しては、以下のリファレンスに記載されておりますので、参照頂ければと思います。

・MySQL 8.0 リファレンスマニュアル 14.19.1 集計関数の説明

データ準備

検証用に１つのテーブルを作成し、テストデータを投入します。
テーブル構成としては、「年」、「国」、「商品」別の利益データを管理しています。

mysql> #Create Table
mysql> CREATE TABLE sales
    -> (
    ->     year    INT,
    ->     country VARCHAR(20),
    ->     product VARCHAR(32),
    ->     profit  INT
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> #Data Insert
mysql> INSERT INTO sales(year, country, product, profit) VALUES
    -> ( 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);
Query OK, 13 rows affected (0.01 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> #confirmation
mysql> SELECT * FROM sales;
+------+---------+------------+--------+
| 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 |
+------+---------+------------+--------+
13 rows in set (0.00 sec)

mysql> #Create Table

mysql> CREATE TABLE sales

-> (

-> year INT,

-> country VARCHAR(20),

-> product VARCHAR(32),

-> profit INT

-> );

Query OK, 0 rows affected (0.08 sec)

mysql> #Data Insert

mysql> INSERT INTO sales(year, country, product, profit) VALUES

-> ( 2000, 'Finland', 'Computer', 1500),

-> ( 2000, 'Finland', 'Phone', 100),

-> ( 2001, 'Finland', 'Phone', 10),

-> ( 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);

Query OK, 13 rows affected (0.01 sec)

Records: 13 Duplicates: 0 Warnings: 0

mysql> #confirmation

mysql> SELECT * FROM sales;

+------+---------+------------+--------+

+------+---------+------------+--------+

| 2000 | Finland | Computer | 1500 |

| 2000 | Finland | Phone | 100 |

| 2001 | Finland | Phone | 10 |

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

+------+---------+------------+--------+

13 rows in set (0.00 sec)

Window関数を使ってみる

Window関数を利用する/しない場合でSQL文はどうなる？

以下は集計関数を利用して、country毎のprofitの合計、平均、最大、最小、件数を出力した結果になります。
SELECT * FROM sales;の出力結果に対して、以下の集計関数の出力結果を付け足したい場合はどうすれば良いでしょうか？

mysql> SELECT
    ->     country
    ->   , SUM(profit)   AS sum_country_profit
    ->   , AVG(profit)   AS avg_country_profit
    ->   , MAX(profit)   AS max_country_profit
    ->   , MIN(profit)   AS min_country_profit
    ->   , COUNT(profit) AS count_country_profit
    -> FROM sales
    -> GROUP BY country;
+---------+--------------------+--------------------+--------------------+--------------------+----------------------+
| country | sum_country_profit | avg_country_profit | max_country_profit | min_country_profit | count_country_profit |
+---------+--------------------+--------------------+--------------------+--------------------+----------------------+
| Finland |               1610 |           536.6667 |               1500 |                 10 |                    3 |
| India   |               1350 |           450.0000 |               1200 |                 75 |                    3 |
| USA     |               4575 |           653.5714 |               1500 |                 50 |                    7 |
+---------+--------------------+--------------------+--------------------+--------------------+----------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> country

-> , SUM(profit) AS sum_country_profit

-> , AVG(profit) AS avg_country_profit

-> , MAX(profit) AS max_country_profit

-> , MIN(profit) AS min_country_profit

-> , COUNT(profit) AS count_country_profit

-> FROM sales

-> GROUP BY country;

+---------+--------------------+--------------------+--------------------+--------------------+----------------------+

+---------+--------------------+--------------------+--------------------+--------------------+----------------------+

| Finland | 1610 | 536.6667 | 1500 | 10 | 3 |

| India | 1350 | 450.0000 | 1200 | 75 | 3 |

| USA | 4575 | 653.5714 | 1500 | 50 | 7 |

+---------+--------------------+--------------------+--------------------+--------------------+----------------------+

3 rows in set (0.00 sec)

まずWindow関数を利用しない場合の確認から行います。
以下のように自己結合を利用することで想定結果は得られましたが、「もっとシンプルにSELECT文が書けないのかな・・・」と思われた方は、次に紹介する方法を試してください。

mysql> SELECT
    ->      main.*
    ->    , sub.sum_country_profit
    ->    , sub.avg_country_profit
    ->    , sub.max_country_profit
    ->    , sub.min_country_profit
    ->    , sub.count_country_profit
    -> FROM sales main
    ->     INNER JOIN 
    ->        (SELECT
    ->             country
    ->           , SUM(profit)   AS sum_country_profit
    ->           , AVG(profit)   AS avg_country_profit
    ->           , MAX(profit)   AS max_country_profit
    ->           , MIN(profit)   AS min_country_profit
    ->           , COUNT(profit) AS count_country_profit
    ->         FROM sales
    ->         GROUP BY country
    ->        ) sub
    ->       ON main.country = sub.country;
+------+---------+------------+--------+--------------------+--------------------+--------------------+--------------------+----------------------+
| year | country | product    | profit | sum_country_profit | avg_country_profit | max_country_profit | min_country_profit | count_country_profit |
+------+---------+------------+--------+--------------------+--------------------+--------------------+--------------------+----------------------+
| 2000 | Finland | Computer   |   1500 |               1610 |           536.6667 |               1500 |                 10 |                    3 |
| 2000 | Finland | Phone      |    100 |               1610 |           536.6667 |               1500 |                 10 |                    3 |
| 2001 | Finland | Phone      |     10 |               1610 |           536.6667 |               1500 |                 10 |                    3 |
| 2000 | India   | Calculator |     75 |               1350 |           450.0000 |               1200 |                 75 |                    3 |
| 2000 | India   | Calculator |     75 |               1350 |           450.0000 |               1200 |                 75 |                    3 |
| 2000 | India   | Computer   |   1200 |               1350 |           450.0000 |               1200 |                 75 |                    3 |
| 2000 | USA     | Calculator |     75 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2000 | USA     | Computer   |   1500 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | Calculator |     50 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | Computer   |   1500 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | Computer   |   1200 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | TV         |    150 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | TV         |    100 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
+------+---------+------------+--------+--------------------+--------------------+--------------------+--------------------+----------------------+
13 rows in set (0.00 sec)

mysql> SELECT

-> main.*

-> , sub.sum_country_profit

-> , sub.avg_country_profit

-> , sub.max_country_profit

-> , sub.min_country_profit

-> , sub.count_country_profit

-> FROM sales main

-> INNER JOIN

-> (SELECT

-> country

-> , SUM(profit) AS sum_country_profit

-> , AVG(profit) AS avg_country_profit

-> , MAX(profit) AS max_country_profit

-> , MIN(profit) AS min_country_profit

-> , COUNT(profit) AS count_country_profit

-> FROM sales

-> GROUP BY country

-> ) sub

-> ON main.country = sub.country;

+------+---------+------------+--------+--------------------+--------------------+--------------------+--------------------+----------------------+

+------+---------+------------+--------+--------------------+--------------------+--------------------+--------------------+----------------------+

| 2000 | Finland | Computer | 1500 | 1610 | 536.6667 | 1500 | 10 | 3 |

| 2000 | Finland | Phone | 100 | 1610 | 536.6667 | 1500 | 10 | 3 |

| 2001 | Finland | Phone | 10 | 1610 | 536.6667 | 1500 | 10 | 3 |

| 2000 | India | Calculator | 75 | 1350 | 450.0000 | 1200 | 75 | 3 |

| 2000 | India | Computer | 1200 | 1350 | 450.0000 | 1200 | 75 | 3 |

| 2000 | USA | Calculator | 75 | 4575 | 653.5714 | 1500 | 50 | 7 |

| 2000 | USA | Computer | 1500 | 4575 | 653.5714 | 1500 | 50 | 7 |

| 2001 | USA | Calculator | 50 | 4575 | 653.5714 | 1500 | 50 | 7 |

| 2001 | USA | Computer | 1500 | 4575 | 653.5714 | 1500 | 50 | 7 |

| 2001 | USA | Computer | 1200 | 4575 | 653.5714 | 1500 | 50 | 7 |

| 2001 | USA | TV | 150 | 4575 | 653.5714 | 1500 | 50 | 7 |

| 2001 | USA | TV | 100 | 4575 | 653.5714 | 1500 | 50 | 7 |

+------+---------+------------+--------+--------------------+--------------------+--------------------+--------------------+----------------------+

13 rows in set (0.00 sec)

次にWindow関数を利用した場合の確認です。
以下のSELECT文を見て頂くとシンプルな記述になっていること、SUM等の集計関数の後ろにOVER句があることがわかると思います。実はOVER句がある場合は、Window関数と呼ぶことができ、OVER句がなければただの集計関数であるという意味になります。

もう少し深く見ていくと、OVER句の後ろにPARTITION BY句があります。このPARTITION BY句はレコード分割の単位を指定しています。以下のようにPARTITION BY countryと指定することでcountry毎に区切って、profitの合計、平均、最大、最小、件数を出力することができます。

mysql> SELECT
    ->      *
    ->    , SUM(profit)   OVER(PARTITION BY country)   AS sum_country_profit
    ->    , AVG(profit)   OVER(PARTITION BY country)   AS avg_country_profit
    ->    , MAX(profit)   OVER(PARTITION BY country)   AS max_country_profit
    ->    , MIN(profit)   OVER(PARTITION BY country)   AS min_country_profit
    ->    , COUNT(profit) OVER(PARTITION BY country)   AS count_country_profit
    -> FROM sales;
+------+---------+------------+--------+--------------------+--------------------+--------------------+--------------------+----------------------+
| year | country | product    | profit | sum_country_profit | avg_country_profit | max_country_profit | min_country_profit | count_country_profit |
+------+---------+------------+--------+--------------------+--------------------+--------------------+--------------------+----------------------+
| 2000 | Finland | Computer   |   1500 |               1610 |           536.6667 |               1500 |                 10 |                    3 |
| 2000 | Finland | Phone      |    100 |               1610 |           536.6667 |               1500 |                 10 |                    3 |
| 2001 | Finland | Phone      |     10 |               1610 |           536.6667 |               1500 |                 10 |                    3 |
| 2000 | India   | Calculator |     75 |               1350 |           450.0000 |               1200 |                 75 |                    3 |
| 2000 | India   | Calculator |     75 |               1350 |           450.0000 |               1200 |                 75 |                    3 |
| 2000 | India   | Computer   |   1200 |               1350 |           450.0000 |               1200 |                 75 |                    3 |
| 2000 | USA     | Calculator |     75 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2000 | USA     | Computer   |   1500 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | Calculator |     50 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | Computer   |   1500 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | Computer   |   1200 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | TV         |    150 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
| 2001 | USA     | TV         |    100 |               4575 |           653.5714 |               1500 |                 50 |                    7 |
+------+---------+------------+--------+--------------------+--------------------+--------------------+--------------------+----------------------+
13 rows in set (0.00 sec)

mysql> SELECT

-> *

-> , SUM(profit) OVER(PARTITION BY country) AS sum_country_profit

-> , AVG(profit) OVER(PARTITION BY country) AS avg_country_profit

-> , MAX(profit) OVER(PARTITION BY country) AS max_country_profit

-> , MIN(profit) OVER(PARTITION BY country) AS min_country_profit

-> , COUNT(profit) OVER(PARTITION BY country) AS count_country_profit

-> FROM sales;