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

12.21.2 Window 関数の概念と構文

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

Press CTRL+C to copy
 
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 つのクエリーで集計操作が実行され、グループとして取得されたすべての行に対して単一のグローバル合計が生成され、国ごとにグループ化されます:

Press CTRL+C to copy
 
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() を使用しますが、今回はウィンドウ関数として使用します:

Press CTRL+C to copy
 
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 句を含めることで指定されます:

• 最初の OVER 句は空で、クエリー行のセット全体が単一のパーティションとして扱われます。 このため、ウィンドウ関数ではグローバル合計が生成されますが、各行に対して生成されます。

• 2 番目の OVER 句では、国ごとに行がパーティション化され、パーティションごとの合計が生成されます (国ごと)。 この関数は、パーティション行ごとにこの合計を生成します。

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

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

Press CTRL+C to copy
 
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 句は必須です:

Press CTRL+C to copy
 
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 を省略した場合と含めた場合の違いを示します:

Press CTRL+C to copy
 
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 つの形式があります:

Press CTRL+C to copy
 
over_clause:
    {OVER (window_spec) | OVER window_name}

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

• 最初のケースでは、ウィンドウ指定は OVER 句のカッコの間に直接表示されます。

• 2 番目の場合、window_name は、クエリーの他の場所で WINDOW 句によって定義されたウィンドウ指定の名前です。 詳細は、セクション12.21.4「名前付きウィンドウ」を参照してください。

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

Press CTRL+C to copy
 
window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

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

• window_name: クエリーの他の場所で WINDOW 句によって定義されたウィンドウの名前。 window_name 自体が OVER 句内に出現する場合は、ウィンドウを完全に定義します。 パーティション化、順序付けまたはフレーム化句も指定されている場合は、名前付きウィンドウの解釈が変更されます。 詳細は、セクション12.21.4「名前付きウィンドウ」を参照してください。

• partition_clause: PARTITION BY 句は、クエリー行をグループに分割する方法を指定します。 特定の行のウィンドウ関数の結果は、その行を含むパーティションの行に基づきます。 PARTITION BY を省略すると、すべてのクエリー行で構成される単一のパーティションが存在します。

  注記

  ウィンドウ関数のパーティション化は、テーブルのパーティション化とは異なります。 テーブルのパーティション化の詳細は、第24章「パーティション化」 を参照してください。

  partition_clause の構文は次のとおりです:

  Press CTRL+C to copy
   
  partition_clause:
      PARTITION BY expr [, expr] ...

  標準 SQL では、PARTITION BY の後にカラム名のみが続く必要があります。 MySQL 拡張機能では、カラム名のみでなく式を使用できます。 たとえば、テーブルに ts という名前の TIMESTAMP カラムが含まれている場合、標準 SQL では PARTITION BY ts は許可されますが PARTITION BY HOUR(ts) は許可されませんが、MySQL では両方が許可されます。

• order_clause: ORDER BY 句は、各パーティションの行をソートする方法を指定します。 ORDER BY 句に従って等しいパーティション行はピアとみなされます。 ORDER BY を省略すると、パーティション行は順序付けられず、処理順序は暗黙的に指定されず、すべてのパーティション行がピアになります。

  order_clause の構文は次のとおりです:

  Press CTRL+C to copy
   
  order_clause:
      ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

  オプションで、各 ORDER BY 式の後に ASC または DESC を指定してソート方向を示すことができます。 方向が指定されていない場合、デフォルトは ASC です。 NULL 値は、昇順ソートの場合は最初にソートされ、降順ソートの場合は最後にソートされます。

  ウィンドウ定義の ORDER BY は、個々のパーティション内に適用されます。 結果セット全体をソートするには、クエリーの最上位レベルに ORDER BY を含めます。

• frame_clause: フレームは現在のパーティションのサブセットであり、frame 句はサブセットの定義方法を指定します。 frame 句には、独自の副次句が多数あります。 詳細は、セクション12.21.3「ウィンドウ機能フレーム仕様」を参照してください。