序文 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 には他にも多くのウィンドウ関数があります。この記事ではそれらのいくつかをリストし、自分でテストすることができます。
MySQL の主なウィンドウ関数の概要です。実践してみることをお勧めします。また、MySQL 5.7以前のバージョンでのソート方法の実装については多くの人がまとめているので、実践してみるのもおすすめです。 要約する これで、MySQL 8.0 ウィンドウ関数の入門実践と概要に関するこの記事は終了です。より関連性の高い MySQL 8.0 ウィンドウ関数の実践コンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き閲覧してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。 以下もご興味があるかもしれません:
|
<<: コーディングスキルを向上させるためのJavaScriptのヒント
プロジェクトのテスト環境データベースのデータが失われてしまったので、記録しておきたいと思います。当時...
重要なのは、ローカルサーバーに書き込み権限がないことですキーはここにあります(アクセス拒否)。私は肯...
両側に隙間なし、各列間に隙間あり 幅: 100%; 表示: グリッド; グリッドテンプレート列: r...
目次MySQL 内部結合、左結合、右結合、外部結合、複数テーブルクエリビルド環境: 1. 内なる慈恩...
ページには多くのコントロール (要素またはタグ) があります。これらのタグをより便利に操作するには、...
1. tomcat とは誰ですか? 2. Tomcat は何ができますか? Tomcat は Web...
効果: アイデア:入力タイプ属性を使用して、タイプ値がテキストの場合はパスワードを表示し、タイプ値が...
コードをコピーコードは次のとおりです。 <フォームメソッド="post" ...
目次1. ReactとAntdを組み合わせてログイン機能を実現2. ReactとAntdを組み合わせ...
私たちウェブマスターは皆、ウェブサイトを最適化する際に記事内のキーワードを太字にすることが最適化に非...
前回の記事に引き続き、web02 サーバーを作成し、web01 サーバーと web02 サーバーの ...
目次ポータルエラー境界処理エラー境界を使用しない場合はどうなりますか?注記ポータルスロットとも言えま...
今日は618日、主要なショッピングモールはすべてプロモーション活動を行っています。今日は、次のように...
降順インデックスとは何ですか?インデックスについてはよくご存知かもしれませんが、降順インデックスにつ...
Web ページのデザインに関する質問です。すべてに答えられるでしょうか? 1. 単一選択の質問 (...