MySQL 8の新機能ウィンドウ関数の役割

MySQL 8.0 の新機能は次のとおりです。

• Unicode 9.0 をすぐに完全にサポート
• ウィンドウ関数と再帰SQL構文をサポートします。これは、これまでは不可能または困難であったクエリの作成です。
• ネイティブJSONデータとドキュメントストレージ機能のサポート強化
• MySQL 8.0 のリリースでは、いくつかのバージョン番号 (5.5 から始まる) がスキップされました。6.0 は変更され、MySQL のクラスター バージョンを保持するために 7.0 が使用されたため、バージョン番号 8.0 が使用されました。

1. 問題

MySQL 8.0.2 では新しい主要機能であるウィンドウ関数が追加されました

この機能は具体的にどのような問題を解決しますか?まず、SQL クエリのシナリオを見て、通常どのように実行されるかを確認し、次にウィンドウ関数を使用してより便利に解決する方法を確認します。

（１）テストテーブルとデータの準備

次のフィールドを持つ簡単な映画情報テーブルを作成します。

ID
リリース年
category_id (カテゴリID)
評価
CREATE TABLE フィルム (
 id int(11)、
 リリース年 int(11),
 カテゴリID int(11)、
 評価 小数点(3,2)
）

テストデータを挿入

films2の値に挿入
(1,2015,1,8.00)、
(2,2015,2,8.50)、
(3,2015,3,9.00)、
(4,2016,2,8.20)、
(5,2016,1,8.40)、
（6,2017,2,7.00）;

全体の形は次のようになります

（２）クエリ要件

各年の平均スコアを照会し、各レコードの後に​​その年の平均スコアが表示されるように要求します。

たとえば、2015 年には、スコアが 8.00、8.50、9.00 のレコードが 3 件あり、平均スコアは 8.5 です。2016 年には、平均スコアが 8.3 のレコードが 2 件あります。2017 年には、平均スコアが 7.00 のレコードが 1 件あります。

最終結果は次の形式になります。

サブクエリを使用して各年の平均スコアを計算し、結合を使用して結果を再び結合することができます。

選択
 f.id、f.リリース年、 
 f.rating、年数.year_avg
映画から
左結合（
 f.release_yearを選択します。 
  AVG(評価) AS year_avg
 映画から
 GROUP BY f.release_year
) 年 
ON f.release_year = years.release_year

ちょっと複雑ですね。ウィンドウ関数がどのように処理されるかを見てみましょう。

2. ウィンドウ関数による解決

ウィンドウ関数とは何か

ウィンドウ関数は、データのセットを計算するために使用されます。group by とは異なり、単一行の結果を出力するのではなく、各レコードに関連付けられます。

構文例:

選択
関数名 OVER ( ウィンドウ定義 )
から （...）

Window_definition は、全体のデータ セットの一部を表示する小さなウィンドウのように、計算されるレコードのセットです。

function_nameは、ウィンドウ内のデータセットに対して実行する計算を指定します。

上記のクエリを振り返ると、各年のすべての映画の平均評価を計算する必要があります。これを処理するためにウィンドウ関数を使用します。

選択
 f.id、f.リリース年、 
 f.category_id、f.rating、
 AVG(評価) OVER 
 (partition by release_year) AS year_avg
映画から

window_definition 部分では PARTITION BY 句が使用され、データベースに結果のデータ セットを小さな部分に分割して、同じ release_year をまとめるように指示します。関数 AVG(rating) は各ウィンドウ データに対して計算され、その結果が各行に配置されます。

クエリ例 1

各映画のその年の評価ランキングの位置を計算する

クエリステートメント

選択
 f.id、f.リリース年、 
 f.category_id、f.rating、
 RANK() OVER (PARTITION BY release_year 
  ORDER BY 評価 (降順) AS year_rank
映画から

window_definition 部分では、PARTITION BY を使用してウィンドウを release_year でパーティション化し、ORDER BY を使用してウィンドウ内でソートします。

RANK() 関数は、このウィンドウ内のデータ行の位置を返します。

クエリ結果

クエリ例 2

各映画が総合ランキングでどの位置にランクされているか確認する

クエリステートメント

選択
 f.id、f.リリース年、 
 f.category_id、f.rating、
 RANK() OVER (評価順で並べ替え) 
  AS 将軍ランク
映画からID順

メイン ステートメントの order by 句により、データ セット全体がソートされることが保証されます。

window_definition で PARTITION BY が使用されていない場合、結果セット全体がウィンドウとして扱われ、ORDER BY はウィンドウ内のデータを評価に従って降順で並べ替え、最高スコアのデータを先頭に配置します。

RANK()関数はウィンドウ内の各レコードの位置を取得します。

クエリ結果

3. まとめ

ウィンドウ関数は、MySQL 8.0.2 の高度な機能であり、結果セットを実際に集計せずに簡単に集計計算を実行できるため、柔軟性と可読性が大幅に向上し、メンテナンスが容易になります。