MySQL8.0窗口函數做數據排名統計詳細教程

2020-11-23 站長之家

MySQL8.0新增了窗口函數，大大的方便了做數據排名統計的人，很多朋友還不清楚怎麼用MySQL8.0做數據統計排名，下面就來為大家分享一篇心得文章。

MySQL8.0之前，做數據排名統計等相當痛苦，因為沒有像Oracle、SQL SERVER 、PostgreSQL等其他資料庫那樣的窗口函數。但隨著MySQL8.0中新增了窗口函數之後，針對這類統計就再也不是事了，本文就以常用的排序實例介紹MySQL的窗口函數。

1、準備工作

創建表及測試數據

mysql> use testdb;Database changed/*創建表 */mysql> create  table 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));Query OK， 0 rows affected (0.03 sec)mysql> show tables;+------------------+| Tables_in_testdb |+------------------+| tb_score         |+------------------+/* 新增一批測試數據 */mysql> insert into tb_score(stu_no，course，score)values('2020001'，'mysql'，90)，('2020001'，'C++'，85)，('2020003'，'English'，100)，('2020002'，'mysql'，50)，('2020002'，'C++'，70)，('2020002'，'English'，99);Query OK， 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020003'，'mysql'，78)，('2020003'，'C++'，81)，('2020003'，'English'，80)，('2020004'，'mysql'，80)，('2020004'，'C++'，60)，('2020004'，'English'，100);Query OK， 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020005'，'mysql'，98)，('2020005'，'C++'，96)，('2020005'，'English'，70)，('2020006'，'mysql'，60)，('2020006'，'C++'，90)，('2020006'，'English'，70);Query OK， 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020007'，'mysql'，50)，('2020007'，'C++'，66)，('2020007'，'English'，76)，('2020008'，'mysql'，90)，('2020008'，'C++'，69)，('2020008'，'English'，86);Query OK， 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020009'，'mysql'，70)，('2020009'，'C++'，66)，('2020009'，'English'，86)，('2020010'，'mysql'，75)，('2020010'，'C++'，76)，('2020010'，'English'，81);Query OK， 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no，course，score)values('2020011'，'mysql'，90)，('2020012'，'C++'，85)，('2020011'，'English'，84)，('2020012'，'English'，75)，('2020013'，'C++'，96)，('2020013'，'English'，88);Query OK， 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0

2、統計每門課程分數的排名

根據每門課程的分數從高到低進行排名，此時，會出現分數相同時怎麼處理的問題，下面就根據不同的窗口函數來處理不同場景的需求

ROW_NUMBER

由結果可以看出，分數相同時按照學號順序進行排名

mysql> select stu_no，course，score， row_number()over(partition by course order by score desc ) rn-> from tb_score;+---------+---------+-------+----+| stu_no  | course  | score | 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 | English | 100.0 |  1 || 2020004 | English | 100.0 |  2 || 2020002 | English |  99.0 |  3 || 2020013 | English |  88.0 |  4 || 2020008 | English |  86.0 |  5 || 2020009 | English |  86.0 |  6 || 2020011 | English |  84.0 |  7 || 2020010 | English |  81.0 |  8 || 2020003 | English |  80.0 |  9 || 2020007 | English |  76.0 | 10 || 2020012 | English |  75.0 | 11 || 2020005 | English |  70.0 | 12 || 2020006 | English |  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 rows in set (0.00 sec)

DENSE_RANK

為了讓分數相同時排名也相同，則可以使用DENSE_RANK函數，結果如下：

mysql> select stu_no，course，score， DENSE_RANK()over(partition by course order by score desc ) rn -> fromtb_score;+---------+---------+-------+----+| stu_no  | course  | score | 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 | English | 100.0 |  1 || 2020004 | English | 100.0 |  1 || 2020002 | English |  99.0 |  2 || 2020013 | English |  88.0 |  3 || 2020008 | English |  86.0 |  4 || 2020009 | English |  86.0 |  4 || 2020011 | English |  84.0 |  5 || 2020010 | English |  81.0 |  6 || 2020003 | English |  80.0 |  7 || 2020007 | English |  76.0 |  8 || 2020012 | English |  75.0 |  9 || 2020005 | English |  70.0 | 10 || 2020006 | English |  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 rows in set (0.00 sec)

RANK

DENSE_RANK的結果是分數相同時排名相同了，但是下一個名次是緊接著上一個名次的，如果 2 個並列的第 1 之後，下一個我想是第 3 名，則可以使用RANK函數實現

mysql> select stu_no，course，score， rank()over(partition by course order by score desc ) rn -> fromtb_score;+---------+---------+-------+----+| stu_no  | course  | score | 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 | English | 100.0 |  1 || 2020004 | English | 100.0 |  1 || 2020002 | English |  99.0 |  3 || 2020013 | English |  88.0 |  4 || 2020008 | English |  86.0 |  5 || 2020009 | English |  86.0 |  5 || 2020011 | English |  84.0 |  7 || 2020010 | English |  81.0 |  8 || 2020003 | English |  80.0 |  9 || 2020007 | English |  76.0 | 10 || 2020012 | English |  75.0 | 11 || 2020005 | English |  70.0 | 12 || 2020006 | English |  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 rows in set (0.01 sec)

這樣就實現了各種排序需求。

NTILE

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  fromtb_score;+---------+---------+-------+----+----------+| stu_no  | course  | score | 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 | English | 100.0 |  1 |        1 || 2020004 | English | 100.0 |  1 |        1 || 2020002 | English |  99.0 |  3 |        1 || 2020013 | English |  88.0 |  4 |        1 || 2020008 | English |  86.0 |  5 |        1 || 2020009 | English |  86.0 |  5 |        1 || 2020011 | English |  84.0 |  7 |        1 || 2020010 | English |  81.0 |  8 |        2 || 2020003 | English |  80.0 |  9 |        2 || 2020007 | English |  76.0 | 10 |        2 || 2020012 | English |  75.0 | 11 |        2 || 2020005 | English |  70.0 | 12 |        2 || 2020006 | English |  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 rows in set (0.01 sec)

3、窗口函數小結

類別函數說明
排序ROW_NUMBER為表中的每一行分配一個序號，可以指定分組（也可以不指定）及排序欄位
DENSE_RANK根據排序欄位為每個分組中的每一行分配一個序號。 排名值相同時，序號相同，序號中沒有間隙（1，1，2， 3 這種）
RANK根據排序欄位為每個分組中的每一行分配一個序號。 排名值相同時，序號相同，但序號中存在間隙（1，1，3， 4 這種）
NTILE根據排序欄位為每個分組中根據指定欄位的排序再分成對應的組
分布PERCENT_RANK計算各分組或結果集中行的百分數等級
CUME_DIST計算某個值在一組有序的數據中累計的分布
前後LEAD返回分組中當前行之後的第N行的值。如果不存在對應行，則返回NULL。比如N= 1 時，第一名對應的值是第二名的，最後一名結果是NULL
LAG返回分組中當前行之前的第N行的值。如果不存在對應行，則返回NULL。比如N= 1 時，第一名對應的值是是NUL，最後一名結果是倒數第 2 的值
首尾中FIRST_VALUE返回每個分組中第一名對應的欄位（或表達式）的值，例如本文中可以是第一名的分數、學號等任意欄位的值
LAST_VALUE返回每個分組中最後一名對應的欄位（或表達式）的值，例如本文中可以是最後一名的分數、學號等任意欄位的值
NTH_VALUE返回每個分組中排名第N的對應欄位（或表達式）的值，但小於N的行對應的值是NULL

MySQL中主要的窗口函數先總結這麼多，建議還是得動手實踐一番。另外，MySQL5. 7 及之前版本的排序方式的實現很多人已總結，也建議實操一番。

本文轉載自微信公眾號【資料庫乾貨鋪】

相關焦點

  • MySQL 8.0 正式版 8.0.11 發布:比 MySQL 5.7 快 2 倍
    注意：從 MySQL 5.7 升級到 MySQL 8.0 僅支持通過使用 in-place 方式進行升級，並且不支持從 MySQL 8.0 降級到 MySQL 5.7（或從某個 MySQL 8.0 版本降級到任意一個更早的 MySQL 8.0 版本）。唯一受支持的替代方案是在升級之前對數據進行備份。
  • MySQL 8.0 正式版 8.0.11 發布:比 MySQL 5.7 快 2 倍 - OS...
    注意：從 MySQL 5.7 升級到 MySQL 8.0 僅支持通過使用 in-place 方式進行升級，並且不支持從 MySQL 8.0 降級到 MySQL 5.7（或從某個 MySQL 8.0 版本降級到任意一個更早的 MySQL 8.0 版本）。唯一受支持的替代方案是在升級之前對數據進行備份。
  • mysql查詢——統計每日新用戶數量
    下圖是一張網站用戶活動表（User_Activities），表中包含欄位UserId（用戶編號）、Activity（用戶活動，可取值login、orders、logout）、Date（活動日期），現需要編寫一個查詢語句，統計網站每日的新用戶數量，即統計每日登錄用戶中首次登錄網站的用戶數
  • MySQL 5.7和MySQL 8.0的4個細節差異
    而讓業務有所顧慮從MySQL 5.7升級到MySQL 8.0的一個主要原因是：驅動版本升級，所以對於MySQL 5.7升級到MySQL 8.0來說，總體的升級動力明顯要低一些，但是規劃的一個優點就是可以把一些工作前置，或者讓它的推行更加順暢，比如我們對於新業務的推行，都是默認按照MySQL 8.0的方案來做。
  • HIVE 窗口函數詳解
    聚合函數對其所作用的每一組記錄輸 出一條結果，而窗口函數對其所作用的窗口中的每一行記錄輸出一條結果。一些聚合函 數，如 sum, max, min, avg,count 等也可以當作窗口函數使用。窗口函數的實現原理在用group-by處理數據分組時，每一行只能進入一個分組。
  • mysql查詢前一周的數據_mysql查詢當天的數據 - CSDN
    mysql 昨天 一周前 一月前 一年前的數據 這裡主要用到了DATE_SUB，參考如下代碼如下:SELECT * FROM
  • MySQL如何計算統計redo log大小
    在MySQL中如何計算、統計重做日誌（redo log)的生成情況呢？ 例如10分鐘內，生成了多少M的redo log呢?30分鐘內又生成了多少M的redo log.....。MySQL沒有像Oracle中那樣的系統視圖統計這些數據，但是我們可以通過一些方法曲線的統計二進位日誌的生成量。
  • mysql昨天的日期時間函數 - CSDN
    mysql時間日期內置函數–非常方便用於查詢今天、昨天、上周，本月，上月，年度查詢等，但是千萬要監控mysql語句的性能，適當加索引優化一下查詢速度哦今天select * from 表名 where to_days(時間欄位名) = to_days(now());
  • 京東雲正式宣布支持MySQL 8.0
    MySQL 8.0 是目前 MySQL 社區的最新版本，相比較 MySQL 5.7，支持了更多新功能，對原有功能做了重大更新和優化，同時在性能方面也有顯著的提升。此次京東雲首發MySQL 8. 0 版本，不僅證明了京東雲技術團隊的研發實力和創新能力，同時也表現了京東雲希望通過更加優質的雲計算服務回饋用戶，並助力用戶運用新技術不斷創新，實現更多的商業價值。
  • MySQL 5.7 vs 8.0,哪個性能更牛?
    測試前先重啟mysql服務，並清空os的cache（避免多次測試時命中緩存） 每次進行測試都是新生成測試數據後再進行mysql5.7和mysql8.0的測試 每次測試時保證mysql5.7和mysql8.0的配置參數一致 環境 機器
  • excel查重技巧:如何用組合函數快速統計重複數據(下)
    在前幾天的教程中我們通過學習公式求值和F9鍵，看懂了統計不重複數據的第一種套路。今天我們接著上回所說，繼續給大家分享第二種套路。趕緊來看看吧~＊＊＊＊＊＊＊＊＊數據源照舊，如下圖所示，要求統計出不重複的客戶數：在上期我們掌握了破解公式的方法後，今天我們再來看看計算不重複數據個數的第二個公式套路。
  • python3.8操作(插入,刪除)mysql/MariaDB資料庫
    01主題大家好,我是義縣遊學電子科技.今天來跟大家說一個工作中常用到的操作,python3.8操作MariaDB資料庫.因為MariaDB屬於mysql分支因此資料庫命令語句都是通用的非常方便.02環境python-3.8 ,64位mairadb-10.4.7,64位python包:mysql-connector-2.2.9
  • MySQL 5.0 新特性教程 存儲過程:第四講
    8.DECLARE CONTINUE HANDLER example  mysql> CALL p23()//Query OK, 0 rows affected (0.00 sec)mysql> SELECT @x, @x2//+------+------+| @x | @x2
  • 函數與條件格式的完美結合:會變色的Excel銷售統計查詢模板
    小編最近幾天一直在分享與銷售統計報表有關的教程，有需要的小夥伴可以點擊我的頭像去主頁查看相關教程。今天小編再分享一個銷售統計查詢模板，支持動態查詢，並且查詢的數據會突出顏色顯示。需要模板的可以評論區留言或私信我。
  • mysql統計日活、周活、月活
    mysql分組函數、組函數、聚合函數、統計函數直接po代碼，代碼中有注釋#二、分組函數/*功能：用作統計使用，又稱為聚合函數或統計函數或組函數分類：sum 求和、avg 平均值
  • sparksql 窗口函數原理
    一、窗口函數是啥在單表數據操作中，一般有下面兩種操作範式：針對單條數據的映射操作，例如每條數據加一的時候。將數據分組後的聚合操作，例如進行分組統計的時候。在第一種範式中有這樣一種情況，當你要生成某條目標數據的時候你需要用到前後N條數據參與計算。
  • 未來函數檢測的詳細教程
    各位朋友下午好，今天來給大家講講通達信的未來函數的檢測教程，許多朋友都在使用指標公式，但談到「未來函數」便心生恐懼，下面我便帶來通達信指標未來函數的詳細檢測方法。3.在公式管理器中，選擇公式組中的技術指標公式，在技術指標公式中選擇其他類型4.在其他類型裡找到你想做未來函數檢測的指數公式。5.找到並打開未來函數檢測工具6.雙擊打開指數公式，並複製公式代碼。7.將複製的公式代碼粘貼到未來函數檢測工具裡。
  • 學會這10個簡單又實用的統計函數,再也不怕數據計算了
    1.sum函數：求和用表格做統計，怎麼少得了SUM求和函數。有了SUM函數在手，想求哪裡的和就求哪裡的和。如下圖所示，在單元格輸入sum函數，並引用E2到E8單元格區域，表示對E2到E8單元格的區域的數據進行求和統計。
  • excel查重技巧:如何用組合函數快速統計重複數據(上)
    統計不重複數據的個數，是小夥伴們經常遇到的問題。估計很多小夥伴也看過不少類似的文章，但大多都是給出公式並稍微講解一番，當時看了是懂了，等遇到問題的時候又懵了，歸根結底還是沒明白這公式的原理。其實理解這個公式的原理，並沒有大家想像的那麼難，只要你會這兩個神仙技巧，就可以破解公式的秘密了。由於教程篇幅較長，將分為上下兩篇，本篇為上篇。
  • python時間序列分析之_用pandas中的rolling函數計算時間窗口數據
    因此，引出滑動窗口(移動窗口)的概念，簡單點說，為了提升數據的可靠性，將某個點的取值擴大到包含這個點的一段區間，用區間來進行判斷，這個區間就是窗口。如下面的示意圖所示，其中時間序列數據代表的是15日每日的溫度，現在我們以3天為一個窗口，將這個窗口從左至右依次滑動，統計出3天的平均值作為這個點的值，比如3號的溫度就是1號、2號、3號的平均溫度，通過示意圖我們已經理解了滑動窗口，下面我們就看看pandas中使用的函數及各參數的含義。