MySQL 8.0 リファレンスマニュアル MySQL NDB Cluster 8.0 を含む

このページは機械翻訳したものです。

12.21.2 Window 関数の概念と構文

このセクションでは、ウィンドウ関数の使用方法について説明します。 例では、セクション12.20.2「GROUP BY 修飾子」 の GROUPING() 関数の説明にあるものと同じ販売情報データセットを使用します: 

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

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

たとえば、売上情報テーブルを使用すると、次の 2 つのクエリーで集計操作が実行され、グループとして取得されたすべての行に対して単一のグローバル合計が生成され、国ごとにグループ化されます: 

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

対照的に、ウィンドウ操作では、クエリー行のグループは単一の出力行に縮小されません。 かわりに、行ごとに結果が生成されます。 前述のクエリーと同様に、次のクエリーでは SUM() を使用しますが、今回はウィンドウ関数として使用します: 

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

クエリーの各ウィンドウ操作は、ウィンドウ関数で処理するためにクエリー行をグループにパーティション化する方法を指定する OVER 句を含めることで指定されます:

ウィンドウ機能は、選択リストおよび ORDER BY 句でのみ使用できます。 クエリー結果行は、WHERE、GROUP BY および HAVING の処理後に FROM 句から決定され、ORDER BY、LIMIT および SELECT DISTINCT の前にウィンドウ実行が行われます。

OVER 句は多くの集計関数で許可されているため、OVER 句が存在するかどうかに応じて、ウィンドウ関数または非ウィンドウ関数として使用できます: 

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

各集計関数の詳細は、セクション12.20.1「集計関数の説明」 を参照してください。

MySQL では、ウィンドウ関数としてのみ使用される非集計関数もサポートされています。 これらの場合、OVER 句は必須です: 

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

各非集計関数の詳細は、セクション12.21.1「Window 関数の説明」 を参照してください。

これらの非集計ウィンドウ関数のいずれかの例として、このクエリーは、パーティション内の各行の行番号を生成する ROW_NUMBER() を使用します。 この場合、行には国ごとに番号が付けられます。 デフォルトでは、パーティション行は順序付けられず、行番号付けは非決定的です。 パーティション行をソートするには、ウィンドウ定義に ORDER BY 句を含めます。 このクエリーでは、順序付けされていないパーティション (row_num1 カラムと row_num2 カラム) を使用して、ORDER BY を省略した場合と含めた場合の違いを示します: 

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

前述のように、ウィンドウ関数を使用する (または集計関数をウィンドウ関数として処理する) には、関数コールの後に OVER 句を含めます。 OVER 句には、次の 2 つの形式があります: 

over_clause:
    {OVER (window_spec) | OVER window_name}

どちらのフォームでも、ウィンドウ関数によるクエリー行の処理方法を定義します。 これらは、ウィンドウが OVER 句で直接定義されているか、クエリーの他の場所で定義された名前付きウィンドウへの参照によって提供されているかによって異なります:

OVER (window_spec) 構文の場合、ウィンドウ指定にはいくつかの部分があり、すべてオプションです: 

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

OVER() が空の場合、ウィンドウはすべてのクエリー行で構成され、ウィンドウ関数はすべての行を使用して結果を計算します。 それ以外の場合は、カッコ内にある句によって、関数結果の計算に使用されるクエリー行と、それらのパーティション化および順序付け方法が決まります:

MySQL Documentation Library