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 の基本ステートメントを最適化するための 10 の原則の概要

序文データベースの応用において、プログラマーは継続的な実践を通じて多くの経験を積んできました。これら...

ノードをMySQLデータベースに接続する際に発生する問題と解決策

今日、MySQL の新しいバージョン (8.0.21) をインストールしましたが、ノード フレームワ...

CSS3で線形グラデーションを実装するためのコードの詳細な説明

序文デモでは古いバージョンのブラウザのグラデーションが実装されています[IE9-]。 IE9 より前...

ReactでuseStateを使用する詳細な例

使用状態useState は、関数コンポーネント内で呼び出すことで、コンポーネントに内部状態を追加し...

Mac ノードの削除と再インストールのケーススタディ

Macノードの削除と再インストール消去 ノード -v sudo npm アンインストール npm -...

JavaScript における一般的な配列操作

目次1. 連結() 2. 結合() 3. プッシュ() 5. シフト() 6. シフト解除() 7....

Vue.js ドラッグ可能なテキストボックスコンポーネントの使用方法の詳細な説明

目次コンポーネントの登録コンポーネントの追加ソースコードドラッグ可能なテキスト ボックスでは、ユーザ...

Sublime Text - ブラウザのショートカットキーを設定するための推奨方法

コード効果を異なるブラウザで表示することはよくあることなので、異なるショートカットキーを使用して対応...

Founder フォント ライブラリの中国語と英語のファイル名比較表

Founder Type Library は、Founder Type Library ビジネス チ...

Linux システムをバックアップする docker コマンドの詳細な説明

tar バックアップ システム sudo tar cvpzf backup.tgz --exclud...

MySQL テーブルを削除する際の I/O エラーの原因分析と解決方法

問題現象最近、sysbench を使用して MySQL をテストしました。テストに長い時間がかかった...

CSS導入方法4つのまとめ(共有)

1. インライン参照:ラベルに直接使用されるが、メンテナンスコストが高い スタイル='フォ...

Centos8.2 クラウド サーバー環境に Tomcat8.5 をインストールするための詳細なチュートリアル

Tomcatをインストールする前に、まずJDK環境をインストールしてくださいLinux サーバー上で...

vue+element カスタムクエリコンポーネント

この記事では主に Vue プロジェクトを紹介します。要素の導入を前提として、コンポーネントを 2 回...

Vue+tsx のスロット使用の問題が置き換えられない

目次序文問題を見つける解決する追記序文最近、 UIコンポーネントを作成する予定で、 vue 2.xと...