MySQL 8.0 リファレンスマニュアル MySQL NDB Cluster 8.0 を含む
このページは機械翻訳したものです。
このセクションでは、ウィンドウ関数の使用方法について説明します。 例では、セクション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
句を含めることで指定されます:
最初の OVER
句は空で、クエリー行のセット全体が単一のパーティションとして扱われます。 このため、ウィンドウ関数ではグローバル合計が生成されますが、各行に対して生成されます。
2 番目の 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
) | OVERwindow_name
}
どちらのフォームでも、ウィンドウ関数によるクエリー行の処理方法を定義します。 これらは、ウィンドウが OVER
句で直接定義されているか、クエリーの他の場所で定義された名前付きウィンドウへの参照によって提供されているかによって異なります:
最初のケースでは、ウィンドウ指定は OVER
句のカッコの間に直接表示されます。
2 番目の場合、window_name
は、クエリーの他の場所で WINDOW
句によって定義されたウィンドウ指定の名前です。 詳細は、セクション12.21.4「名前付きウィンドウ」を参照してください。
OVER (
構文の場合、ウィンドウ指定にはいくつかの部分があり、すべてオプションです:
window_spec
)
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
の構文は次のとおりです:
partition_clause
: PARTITION BYexpr
[,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
の構文は次のとおりです:
order_clause
: ORDER BYexpr
[ASC|DESC] [,expr
[ASC|DESC]] ...
オプションで、各 ORDER BY
式の後に ASC
または DESC
を指定してソート方向を示すことができます。 方向が指定されていない場合、デフォルトは ASC
です。 NULL
値は、昇順ソートの場合は最初にソートされ、降順ソートの場合は最後にソートされます。
ウィンドウ定義の ORDER BY
は、個々のパーティション内に適用されます。 結果セット全体をソートするには、クエリーの最上位レベルに ORDER BY
を含めます。
frame_clause
: フレームは現在のパーティションのサブセットであり、frame 句はサブセットの定義方法を指定します。 frame 句には、独自の副次句が多数あります。 詳細は、セクション12.21.3「ウィンドウ機能フレーム仕様」を参照してください。