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 を使用した負荷分散モジュールの解釈

推薦する

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

MySQL は最も人気のあるリレーショナル データベース管理システムです。WEB アプリケーションに...

iframe テクニックを使用して訪問者 QQ 実装のアイデアとサンプル コードを取得する

今日、仕事中に、一時的に追加した友人から、Web ページを使用して訪問者の QQ を取得する方法を尋...

MySQL の完全なデータベース バックアップからデータベースとテーブルを復元する方法

公式の MySQL ダンプ ツールで、特定のデータベースのみを復元するにはどうすればよいですか?完全...

Zabbixについて管理者ログインパスワードを忘れた場合、パスワードをリセットする

Zabbix 管理者ログイン パスワードのリセットに関する問題は次のとおりです。 1. 問題の説明:...

Vue コンポーネント化の一般的な方法: コンポーネント値の転送と通信

関連する知識ポイント親コンポーネントから子コンポーネントに値を渡す子コンポーネントから親コンポーネン...

Docker のタイムゾーンの問題とデータ移行の問題

最新のソリューション: -v /usr/share/zoneinfo/Asia/Shanghai:/...

JavaScriptプロトタイプとプロトタイプチェーンを徹底的に理解する

目次序文基礎を築くプロトタイプコンストラクタのプロパティ__プロト__プロトタイプチェーン改善する要...

CSS を解析して画像のテーマカラー機能を抽出する (ヒント)

背景すべては、WeChat 技術グループのクラスメートが「写真の主な色を取得する方法はあるか」と尋ね...

Linuxディレクトリ構造の詳細な紹介

Linuxを学び始めるときは、まずLinuxの標準ディレクトリ構造を理解する必要があります。 / r...

HTML、CSS、RSSフィードが正しいかどうかを確認する無料ツール

この種のエラーに対処するための 1 つの方法は、まずマークアップとスタイルシートを検証することです。...

Word のコンテンツを Web サイトのエディターに直接コピーすることはお勧めしません。

<br />質問: Word のコンテンツを Web サイトのエディターに直接コピーする...

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

MySQL-8.0.22-winx64のデータベースインストールチュートリアルは参考になります。具体...

Docker でローカルにイメージをインポート/保存/読み込み/削除する方法

1. Dockerはローカルイメージをインポートする場合によっては、イメージをローカルまたは別の友人...

Linux ネットワーク システムの紹介

目次ネットワーク情報ホスト名を変更するDNSドメイン名解決ネットワーク関連コマンドファイアウォール暗...

IE における条件付きコメントの利点と欠点

IE の条件付きコメントは、通常の (X)HTML コメントに対する Microsoft 独自の (...