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

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 クエリのシナリオを見て、通常どのように実行されるかを確認し、次にウィンドウ関数を使用してより便利に解決する方法を確認します。

(1)テストテーブルとデータの準備

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

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

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

(2)クエリ要件

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

たとえば、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 の高度な機能であり、結果セットを実際に集計せずに簡単に集計計算を実行できるため、柔軟性と可読性が大幅に向上し、メンテナンスが容易になります。

以下もご興味があるかもしれません:
  • MySQL 8.0 の新しいリレーショナル データベース機能の詳細な説明
  • MySQL 8 の新機能: 非表示のインデックス
  • MySQL 8 の新機能: 永続的なグローバル変数を変更する方法
  • MySQL 8 の新機能: 自動増分主キーの永続性に関する詳細な説明
  • MySQL 8 の新機能: 降順インデックスの詳細

<<:  jQuery はピッカーをシミュレートしてスライド選択効果を実現します

>>:  Centos7.3 Nginx をインストールして展開し、https を設定する方法

推薦する

MySQL の詳細な単一テーブルの追加、削除、変更、クエリの CRUD ステートメント

MySQL の追加、削除、変更、クエリステートメント1. 練習シートを作成するここでの練習表は3つの...

Centos8 (最小インストール) Python3.8+pip のインストール方法に関するチュートリアル

Python8のインストールを最小化した後、Python3.8.1をインストールしました。オンライン...

react setStateの詳細な説明

目次setState は同期ですか、それとも非同期ですか?カスタム合成イベントと React フック...

CSS 使用のヒントのまとめ

最近、ブログのアップグレードを始めました。テンプレートを変更する過程で、CSS スタイルシートを書き...

JavaScript オブジェクト指向の実践の詳細説明: カプセル化とオブジェクトのドラッグ

目次概要1. DOM要素をアニメーション化する方法2. 現在のブラウザでサポートされている変換互換の...

jsは配列の平坦化を実装します

目次配列をフラット化する方法1.flat() の使用2. 正規表現を使用する3.reduce()+c...

MYSQL における char と varchar の違い

CHAR 型と VARCHAR 型は似ていますが、主に格納場所、末尾のスペース、取得方法が異なります...

MySql バージョンの問題に対する完璧なソリューション sql_mode=only_full_group_by

1. sql_modeを確認する @@sql_mode を選択照会された値は次のとおりです。 ON...

jQueryフレームワークは、要素の表示と非表示の3つのアニメーションメソッドを実装しています。

目次1. デフォルトで表示と非表示を切り替える2. スライドして表示と非表示を切り替える3. フェー...

html+vue+element-ui のスムーズさを 1 分で体験

テクノロジーファンHTMLウェブページ、知っておくべきYouyou が開発した vue フロントエン...

外部ファイル(js/vbs/css)をインポートするときに文字化けを回避する方法

ページ内にはjs、cssなどの外部ファイルが導入されており、外部ファイルのエンコードが現在のページフ...

Navicat for SQLite で中国語データを CSV にインポートする方法

この記事では、参考までに、csv中国語データをNavicat for SQLiteにインポートする具...

MySQL 8.0.2 オフラインインストールと設定方法のグラフィックチュートリアル

MySQL_8.0.2のオフラインインストール方法は参考までに。具体的な内容は以下のとおりです。次の...

JavaScript BOMの構成と一般的なイベントの詳細な説明

目次1. 部品2. BOMの構成2. ウィンドウオブジェクトの共通イベント1. ウィンドウ読み込みイ...