MySQL 8.0 ウィンドウ関数の紹介と概要

MySQL 8.0 ウィンドウ関数の紹介と概要

序文

MySQL 8.0 より前は、Oracle、SQL SERVER、PostgreSQL などの他のデータベースのようなウィンドウ関数がなかったため、データ ランキング統計を実行するのは非常に困難でした。ただし、MySQL 8.0 でウィンドウ関数が追加されたため、このタイプの統計は問題になりません。この記事では、よく使用されるソートの例を使用して、MySQL ウィンドウ関数を紹介します。

1. 準備

テーブルとテストデータを作成する

mysql> testdb を使用します。
データベースが変更されました
/* テーブルを作成 */
mysql> テーブル tb_score(id int primary key auto_increment,stu_no varchar(10),course varchar(50),score decimal(4,1),key idx_stuNo_course(stu_no,course)); を作成します。
クエリは正常、影響を受けた行は 0 行 (0.03 秒)

mysql> テーブルを表示します。
+------------------+
| テストデータベース内のテーブル |
+------------------+
|tb_スコア|
+------------------+

/* 新しいテストデータのバッチを追加します*/
mysql> tb_score(stu_no,course,score) に値を挿入します('2020001','mysql',90),('2020001','C++',85),('2020003','English',100),('2020002','mysql',50),('2020002','C++',70),('2020002','English',99);
クエリは正常、6 行が影響を受けました (0.00 秒)
記録: 6 重複: 0 警告: 0
mysql> tb_score(stu_no,course,score) に値を挿入します('2020003','mysql',78),('2020003','C++',81),('2020003','English',80),('2020004','mysql',80),('2020004','C++',60),('2020004','English',100);
クエリは正常、6 行が影響を受けました (0.01 秒)
記録: 6 重複: 0 警告: 0
mysql> tb_score(stu_no,course,score) に値を挿入します('2020005','mysql',98),('2020005','C++',96),('2020005','English',70),('2020006','mysql',60),('2020006','C++',90),('2020006','English',70);
クエリは正常、6 行が影響を受けました (0.01 秒)
記録: 6 重複: 0 警告: 0
mysql> tb_score(stu_no,course,score) に値を挿入します('2020007','mysql',50),('2020007','C++',66),('2020007','English',76),('2020008','mysql',90),('2020008','C++',69),('2020008','English',86);
クエリは正常、6 行が影響を受けました (0.01 秒)
記録: 6 重複: 0 警告: 0
mysql> tb_score(stu_no,course,score) に値を挿入します('2020009','mysql',70),('2020009','C++',66),('2020009','English',86),('2020010','mysql',75),('2020010','C++',76),('2020010','English',81);
クエリは正常、6 行が影響を受けました (0.01 秒)
記録: 6 重複: 0 警告: 0
mysql> tb_score(stu_no,course,score) に値を挿入します('2020011','mysql',90),('2020012','C++',85),('2020011','English',84),('2020012','English',75),('2020013','C++',96),('2020013','English',88);
クエリは正常、6 行が影響を受けました (0.01 秒)
記録: 6 重複: 0 警告: 0

2. 各コースのスコアの順位を計算する

各コースのスコアは高いものから低いものの順にランク付けされています。このとき、同じスコアをどのように処理するかという問題が発生します。以下では、異なるウィンドウ関数を使用して、さまざまなシナリオのニーズに対応します。

行番号

結果から、得点が同じ場合は生徒番号順に順位付けされていることがわかります。

mysql> select stu_no,course,score, row_number()over(partition by course order by score desc) rn
 -> tb_score から;
+---------+---------+-------+----+
| stu_no | コース | スコア | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 2 |
| 2020006 | C++ | 90.0 | 3 |
| 2020001 | C++ | 85.0 | 4 |
| 2020012 | C++ | 85.0 | 5 |
| 2020003 | C++ | 81.0 | 6 |
| 2020010 | C++ | 76.0 | 7 |
| 2020002 | C++ | 70.0 | 8 |
| 2020008 | C++ | 69.0 | 9 |
| 2020007 | C++ | 66.0 | 10 |
| 2020009 | C++ | 66.0 | 11 |
| 2020004 | C++ | 60.0 | 12 |
| 2020003 | 英語 | 100.0 | 1 |
| 2020004 | 英語 | 100.0 | 2 |
| 2020002 | 英語 | 99.0 | 3 |
| 2020013 | 英語 | 88.0 | 4 |
| 2020008 | 英語 | 86.0 | 5 |
| 2020009 | 英語 | 86.0 | 6 |
| 2020011 | 英語 | 84.0 | 7 |
| 2020010 | 英語 | 81.0 | 8 |
| 2020003 | 英語 | 80.0 | 9 |
| 2020007 | 英語 | 76.0 | 10 |
| 2020012 | 英語 | 75.0 | 11 |
| 2020005 | 英語 | 70.0 | 12 |
| 2020006 | 英語 | 70.0 | 13 |
| 2020005 | mysql | 98.0 | 1 |
| 2020001 | mysql | 90.0 | 2 |
| 2020008 | mysql | 90.0 | 3 |
| 2020011 | mysql | 90.0 | 4 |
| 2020004 | mysql | 80.0 | 5 |
| 2020003 | mysql | 78.0 | 6 |
| 2020010 | mysql | 75.0 | 7 |
| 2020009 | mysql | 70.0 | 8 |
| 2020006 | mysql | 60.0 | 9 |
| 2020002 | mysql | 50.0 | 10 |
| 2020007 | mysql | 50.0 | 11 |
+---------+---------+-------+----+
セット内の行数は 36 です (0.00 秒)mysql> select stu_no,course,score, row_number()over(partition by course order by score desc ) rn
 -> tb_score から;
+---------+---------+-------+----+
| stu_no | コース | スコア | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 2 |
| 2020006 | C++ | 90.0 | 3 |
| 2020001 | C++ | 85.0 | 4 |
| 2020012 | C++ | 85.0 | 5 |
| 2020003 | C++ | 81.0 | 6 |
| 2020010 | C++ | 76.0 | 7 |
| 2020002 | C++ | 70.0 | 8 |
| 2020008 | C++ | 69.0 | 9 |
| 2020007 | C++ | 66.0 | 10 |
| 2020009 | C++ | 66.0 | 11 |
| 2020004 | C++ | 60.0 | 12 |
| 2020003 | 英語 | 100.0 | 1 |
| 2020004 | 英語 | 100.0 | 2 |
| 2020002 | 英語 | 99.0 | 3 |
| 2020013 | 英語 | 88.0 | 4 |
| 2020008 | 英語 | 86.0 | 5 |
| 2020009 | 英語 | 86.0 | 6 |
| 2020011 | 英語 | 84.0 | 7 |
| 2020010 | 英語 | 81.0 | 8 |
| 2020003 | 英語 | 80.0 | 9 |
| 2020007 | 英語 | 76.0 | 10 |
| 2020012 | 英語 | 75.0 | 11 |
| 2020005 | 英語 | 70.0 | 12 |
| 2020006 | 英語 | 70.0 | 13 |
| 2020005 | mysql | 98.0 | 1 |
| 2020001 | mysql | 90.0 | 2 |
| 2020008 | mysql | 90.0 | 3 |
| 2020011 | mysql | 90.0 | 4 |
| 2020004 | mysql | 80.0 | 5 |
| 2020003 | mysql | 78.0 | 6 |
| 2020010 | mysql | 75.0 | 7 |
| 2020009 | mysql | 70.0 | 8 |
| 2020006 | mysql | 60.0 | 9 |
| 2020002 | mysql | 50.0 | 10 |
| 2020007 | mysql | 50.0 | 11 |
+---------+---------+-------+----+
セット内の行数は 36 です (0.00 秒)

密度_ランク

スコアが同じ場合にランキングを同じにするには、DENSE_RANK 関数を使用します。結果は次のようになります。

mysql> select stu_no,course,score, DENSE_RANK()over(partition by course order by score desc) rn 
 -> tb_score から; 
+---------+---------+-------+----+
| stu_no | コース | スコア | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 1 |
| 2020006 | C++ | 90.0 | 2 |
| 2020001 | C++ | 85.0 | 3 |
| 2020012 | C++ | 85.0 | 3 |
| 2020003 | C++ | 81.0 | 4 |
| 2020010 | C++ | 76.0 | 5 |
| 2020002 | C++ | 70.0 | 6 |
| 2020008 | C++ | 69.0 | 7 |
| 2020007 | C++ | 66.0 | 8 |
| 2020009 | C++ | 66.0 | 8 |
| 2020004 | C++ | 60.0 | 9 |
| 2020003 | 英語 | 100.0 | 1 |
| 2020004 | 英語 | 100.0 | 1 |
| 2020002 | 英語 | 99.0 | 2 |
| 2020013 | 英語 | 88.0 | 3 |
| 2020008 | 英語 | 86.0 | 4 |
| 2020009 | 英語 | 86.0 | 4 |
| 2020011 | 英語 | 84.0 | 5 |
| 2020010 | 英語 | 81.0 | 6 |
| 2020003 | 英語 | 80.0 | 7 |
| 2020007 | 英語 | 76.0 | 8 |
| 2020012 | 英語 | 75.0 | 9 |
| 2020005 | 英語 | 70.0 | 10 |
| 2020006 | 英語 | 70.0 | 10 |
| 2020005 | mysql | 98.0 | 1 |
| 2020001 | mysql | 90.0 | 2 |
| 2020008 | mysql | 90.0 | 2 |
| 2020011 | mysql | 90.0 | 2 |
| 2020004 | mysql | 80.0 | 3 |
| 2020003 | mysql | 78.0 | 4 |
| 2020010 | mysql | 75.0 | 5 |
| 2020009 | mysql | 70.0 | 6 |
| 2020006 | mysql | 60.0 | 7 |
| 2020002 | mysql | 50.0 | 8 |
| 2020007 | mysql | 50.0 | 8 |
+---------+---------+-------+----+
セット内の行数は 36 です (0.00 秒)

ランク

DENSE_RANKの結果は、スコアが同じ場合、順位は同じですが、次の順位は前の順位の直後になります。1位が2つ同点の場合、次に欲しいのは3位です。RANK関数を使用して実現できます。

mysql> select stu_no,course,score, rank()over(partition by course order by score desc) rn 
 -> tb_score から;
+---------+---------+-------+----+
| stu_no | コース | スコア | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 1 |
| 2020006 | C++ | 90.0 | 3 |
| 2020001 | C++ | 85.0 | 4 |
| 2020012 | C++ | 85.0 | 4 |
| 2020003 | C++ | 81.0 | 6 |
| 2020010 | C++ | 76.0 | 7 |
| 2020002 | C++ | 70.0 | 8 |
| 2020008 | C++ | 69.0 | 9 |
| 2020007 | C++ | 66.0 | 10 |
| 2020009 | C++ | 66.0 | 10 |
| 2020004 | C++ | 60.0 | 12 |
| 2020003 | 英語 | 100.0 | 1 |
| 2020004 | 英語 | 100.0 | 1 |
| 2020002 | 英語 | 99.0 | 3 |
| 2020013 | 英語 | 88.0 | 4 |
| 2020008 | 英語 | 86.0 | 5 |
| 2020009 | 英語 | 86.0 | 5 |
| 2020011 | 英語 | 84.0 | 7 |
| 2020010 | 英語 | 81.0 | 8 |
| 2020003 | 英語 | 80.0 | 9 |
| 2020007 | 英語 | 76.0 | 10 |
| 2020012 | 英語 | 75.0 | 11 |
| 2020005 | 英語 | 70.0 | 12 |
| 2020006 | 英語 | 70.0 | 12 |
| 2020005 | mysql | 98.0 | 1 |
| 2020001 | mysql | 90.0 | 2 |
| 2020008 | mysql | 90.0 | 2 |
| 2020011 | mysql | 90.0 | 2 |
| 2020004 | mysql | 80.0 | 5 |
| 2020003 | mysql | 78.0 | 6 |
| 2020010 | mysql | 75.0 | 7 |
| 2020009 | mysql | 70.0 | 8 |
| 2020006 | mysql | 60.0 | 9 |
| 2020002 | mysql | 50.0 | 10 |
| 2020007 | mysql | 50.0 | 10 |
+---------+---------+-------+----+
セット内の行数は 36 行 (0.01 秒)

これにより、さまざまなソート要件が達成されます。

ニタイル

NTILE関数の機能は、各グループをランク付けし、対応するグループをN個の​​グループに分割することです。たとえば、

mysql> select stu_no,course,score, rank()over(partition by course order by score desc )rn,NTILE(2)over(partition by course order by score desc )rn_group from tb_score;
+---------+---------+-------+----+----------+
| stu_no | コース | スコア | rn | rn_group |
+---------+---------+-------+----+----------+
| 2020005 | C++ | 96.0 | 1 | 1 |
| 2020013 | C++ | 96.0 | 1 | 1 |
| 2020006 | C++ | 90.0 | 3 | 1 |
| 2020001 | C++ | 85.0 | 4 | 1 |
| 2020012 | C++ | 85.0 | 4 | 1 |
| 2020003 | C++ | 81.0 | 6 | 1 |
| 2020010 | C++ | 76.0 | 7 | 2 |
| 2020002 | C++ | 70.0 | 8 | 2 |
| 2020008 | C++ | 69.0 | 9 | 2 |
| 2020007 | C++ | 66.0 | 10 | 2 |
| 2020009 | C++ | 66.0 | 10 | 2 |
| 2020004 | C++ | 60.0 | 12 | 2 |
| 2020003 | 英語 | 100.0 | 1 | 1 |
| 2020004 | 英語 | 100.0 | 1 | 1 |
| 2020002 | 英語 | 99.0 | 3 | 1 |
| 2020013 | 英語 | 88.0 | 4 | 1 |
| 2020008 | 英語 | 86.0 | 5 | 1 |
| 2020009 | 英語 | 86.0 | 5 | 1 |
| 2020011 | 英語 | 84.0 | 7 | 1 |
| 2020010 | 英語 | 81.0 | 8 | 2 |
| 2020003 | 英語 | 80.0 | 9 | 2 |
| 2020007 | 英語 | 76.0 | 10 | 2 |
| 2020012 | 英語 | 75.0 | 11 | 2 |
| 2020005 | 英語 | 70.0 | 12 | 2 |
| 2020006 | 英語 | 70.0 | 12 | 2 |
| 2020005 | mysql | 98.0 | 1 | 1 |
| 2020001 | mysql | 90.0 | 2 | 1 |
| 2020008 | mysql | 90.0 | 2 | 1 |
| 2020011 | mysql | 90.0 | 2 | 1 |
| 2020004 | mysql | 80.0 | 5 | 1 |
| 2020003 | mysql | 78.0 | 6 | 1 |
| 2020010 | mysql | 75.0 | 7 | 2 |
| 2020009 | mysql | 70.0 | 8 | 2 |
| 2020006 | mysql | 60.0 | 9 | 2 |
| 2020002 | mysql | 50.0 | 10 | 2 |
| 2020007 | mysql | 50.0 | 10 | 2 |
+---------+---------+-------+----+----------+
セット内の行数は 36 行 (0.01 秒)

3. ウィンドウ関数の概要

MySQL には他にも多くのウィンドウ関数があります。この記事ではそれらのいくつかをリストし、自分でテストすることができます。

カテゴリ関数例示する
ソート行番号テーブルの各行にシーケンス番号を割り当て、グループ化（または非グループ化）および並べ替えフィールドを指定します。
密度_ランクソート フィールドに基づいて、各グループの各行にシーケンス番号を割り当てます。 順位値が同じで、シリアル番号が同じで、シリアル番号に隙間がない（1,1,2,3など）場合
ランクソート フィールドに基づいて、各グループの各行にシーケンス番号を割り当てます。 ランキング値が同じ場合、シリアル番号は同じですが、シリアル番号に隙間があります（1、1、3、4など）
ニタイルソートフィールドに従って、各グループは指定されたフィールドのソートに従って対応するグループに分割されます。
分散したパーセントランク結果セット内の各グループまたは行のパーセンタイル順位を計算します
CUME_DIST順序付けられたデータセット内の値の累積分布を計算する
前後鉛グループ内の現在の行の後の N 行目の値を返します。対応する行が存在しない場合は、NULL が返されます。例えば、N=1の場合、1位に対応する値は2位となり、最後の位の結果はNULLとなる。
遅れグループ内の現在の行の N 行前の行の値を返します。対応する行が存在しない場合は、NULL が返されます。たとえば、N=1 の場合、最初の位置に対応する値は NUL になり、最後の位置は最後から 2 番目の値になります。
始まりと終わり最初の値各グループの 1 位に対応するフィールド (または式) の値を返します。たとえば、この記事では、1 位のスコア、学生 ID など、任意のフィールドの値になります。
最後の値各グループの最後の人物に対応するフィールド (または式) の値を返します。たとえば、この記事では、最後の人物のスコアや学生 ID など、任意のフィールドの値になります。
NTH_値

各グループでN番目にランク付けされた対応するフィールド（または式）の値を返しますが、N未満の行の対応する値はNULLです。

MySQL の主なウィンドウ関数の概要です。実践してみることをお勧めします。また、MySQL 5.7以前のバージョンでのソート方法の実装については多くの人がまとめているので、実践してみるのもおすすめです。

要約する

これで、MySQL 8.0 ウィンドウ関数の入門実践と概要に関するこの記事は終了です。より関連性の高い MySQL 8.0 ウィンドウ関数の実践コンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き閲覧してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • Mysql8.0はソート問題を解決するためにウィンドウ関数を使用する
  • 効率的なページングクエリを実現するためのSQLウィンドウ関数のケース分析
  • MySQLウィンドウ関数の具体的な使用法
  • SQLウィンドウ関数について簡単に学ぶ

<<:  コーディングスキルを向上させるためのJavaScriptのヒント

>>:  nginx を使用した負荷分散モジュールの解釈

推薦する

WeChatアプレットbindtapとcatchtapの違いの詳細な説明

目次1. イベントとは何ですか? 2. イベントの使い方3. バインドタップとキャッチタップの違い4...

.html、.htm、.shtml、.shtm の違いと関連性について簡単に説明します。

ご存知のとおり、私たちが毎日閲覧する Web ページ、Web サイト、または Web ページには独自...

Vue3 Vue イベント処理ガイド

目次1. 基本的なイベント処理2. 親コンポーネントにカスタムイベントを送信するマウス修飾子4. キ...

Ubuntu 20.04は静的IPアドレスを設定します（異なるバージョンを含む）

Ubuntu 20.04はnetplanを通じてネットワークを管理するため、以前のバージョンとは少...

mysqlは、現在の時刻が開始時刻と終了時刻の間にあるかどうかを判断し、開始時刻と終了時刻が空であることが許可されます。

目次要件: 進行中のアクティビティ データを照会する次のSQLクエリは、上記の4つの要件を満たし、タ...

MySQL における UNION と UNION ALL の基本的な使い方

データベースでは、UNION キーワードと UNION ALL キーワードの両方が 2 つの結果セッ...

CSS3 フレックスボックス自動記入の書き方を詳しく解説

この記事では、主に CSS3 フレックス エラスティック ボックスの自動塗りつぶしの書き方について詳...

プロジェクトにおける CSS グリッドシステムの柔軟な使用方法の詳細な説明

序文CSS グリッドは通常、さまざまなフレームワークにバンドルされていますが、実際のビジネス ニーズ...

Dockerコンテナ内にkibanaトークナイザーをインストールする方法

ステップ： 1. 仮想マシンディレクトリに新しいdocker-compose.ymlファイルを作成し...

mysql-connector-java.jar パッケージのダウンロード プロセスの詳細な説明

mysql-connector-java.jar パッケージのチュートリアルをダウンロードします: ...

DockerでGPUを使用するプロセスの詳細な説明

目次tf-gpu をダウンロード取得したtf-gpuイメージに基づいて独自のイメージを構築するイメー...

Mysqlのprepare前処理の具体的な使用法

目次1. 前処理2. 前処理塗布方法A. 例: B. 実行計画の変更を追跡するための前処理C. スト...

SSHトンネルを使用してMySQLサーバーに接続する方法

序文場合によっては、データベースのイントラネット アドレスしか知らず、イントラネット経由で接続できな...

React Native の基本原則の深い理解 (Bridge of React Native)

この記事では、React Native の基本をすでに理解していることを前提とし、ネイティブと Ja...

Vue で Axios カプセル化を使用するための完全なチュートリアル

序文現在、プロジェクトでは、Axios ライブラリが HTTP インターフェース リクエストによく使...