MySQL5.7中如何使用開窗函數

2020-12-11 程序猿集錦

關注我，獲的更多分享。

背景準備實驗環境準備建表語句初始化數據分組排序分組統計分組求最大值背景

在SQL中我們經常遇到一種需求：分組排序，分組求和等各種需求。像這樣的需求，如果在Oracle、SQLserver、postgresql等資料庫中很容易實現，一個開窗函數row_nubmer() over(partition by xxx,yyy order by zzz)就可以解決。

但是在MySQL8.0版本之前，是沒有這樣的開窗函數的。好在8.0之後的版本已經內置了開窗函數。不必自己寫實現邏輯了。但是我們目前還有很多人在使用5.7版本。那麼在5.7版本中，如何實現開窗函數的功能呢？

準備實驗環境

準備建表語句

CREATETABLE`emp`(`id`int(11)NOTNULL,`emp_name`varchar(255)DEFAULTNULL,`dept_no`varchar(255)DEFAULTNULL,`emp_salary`int(10)DEFAULTNULL,`emp_hire_date`dateDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

初始化數據

INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(1,'張三','0001',5000,'2017-01-11');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(2,'李四','0002',1000,'2018-10-10');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(3,'王五','0003',2000,'2018-12-19');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(4,'趙六','0002',4000,'2019-09-11');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(5,'王強強','0001',3000,'2019-03-14');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(6,'劉陽','0002',6000,'2019-08-08');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(7,'周心怡','0003',500,'2015-06-10');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(8,'毛志宇','0004',4500,'2016-09-20');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(9,'劉德仁','0002',3500,'2016-02-25');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(10,'範德武','0001',3000,'2020-02-12');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(11,'梅婷婷','0005',8000,'2013-07-07');INSERTINTO`mysql_db`.`emp`(`id`,`emp_name`,`dept_no`,`emp_salary`,`emp_hire_date`)VALUES(12,'鄭冰','0005',1000,'2014-11-17');

最後的環境如下：

分組排序

需求描述：獲取每一個部門薪水最高的員工的信息。

需求分析：

按照部門分組，每一部門的員工放在一組，然後基於這個組中的數據，按照工資降序排列。然後再根據排序後的結果集，獲取排序為1的數據行即為結果。步驟1的SQL如下：定義兩個變量，row_num_t用於存儲每一個組中的排序結果。dept_no_t用於存儲判斷數據行是否是同一組。同事給他們分別初始化數據。然後再查詢的欄位當中，先判斷一下當前是否和上一行的部門編號是同一個部門編號。如果是同一個部門編號，則將排序結果欄位加1；如果不是同個部門編號，表示要切換為新的組了，這個時候，把排序結果欄位重置為1。selectcasewhen@dept_no_t!= x.dept_no then@row_num_t :=1else@row_num_t :=@row_num_t+1endas sort_result, x.id, x.emp_name,-- x.dept_no,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_datefrom emp as x, (select@dept_no_t :='')as t1,(select@row_num_t :=0)as t2orderby dept_no, emp_salary desc;

步驟1的示例結果如下：

步驟2的SQL語句如下：在步驟1的SQL基礎之上，在外出包裹一層查詢，然後時候用where條件獲取上面內層查詢結果中排序為1的數據行。select*from(selectcasewhen@dept_no_t!= x.dept_no then@row_num_t :=1else@row_num_t :=@row_num_t+1endas sort_result, x.id, x.emp_name,-- x.dept_no,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_datefrom emp as x, (select@dept_no_t :='')as t1,(select@row_num_t :=0)as t2orderby dept_no, emp_salary desc)as y where y.sort_result =1;

步驟2的示例結果如下：

分組統計

需求描述：累計統計每一個部門下所有員工的工資之和。

分析：按照部門分組，每一部門的員工放在一組，然後基於這個組中的數據，逐行累加該部門下所有員工的工資。

SQL如下：定義一個用於存儲最後每組員工工資之和的變量emp_salary_sum_t，然後再每一行數據是否為同一組數據，如果是同一組數據，則將這行數據的工資，累加到工資之和的變量中；如果不是同一組數據，把當前行的工資賦值給每組工資之和的變量。selectcasewhen@dept_no_t!= x.dept_no then@row_num_t :=1else@row_num_t :=@row_num_t+1endas sort_result,casewhen@dept_no_t!= x.dept_no then@emp_salary_sum_t := x.emp_salary when@dept_no_t= x.dept_no then@emp_salary_sum_t :=@emp_salary_sum_t+ x.emp_salary endas emp_salary_sum, x.id, x.emp_name,-- x.dept_no,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_datefrom emp as x,(select@dept_no_t :='')as t1,(select@row_num_t :=0)as t2,(select@emp_salary_sum_t :=0)as t3orderby dept_no, emp_salary desc;

最後示例結果如下：

分組求最大值

需求描述：計算每個員工和部門中工資最高員工的工資差。

需求分析：

根據員工的部門分組，然後判斷得到每組數據中，工資最高的員工的工資。把這個作為一個新列查詢出出來。基於步驟1的結果集中的新列，和員工的工資列做減法得到差值。步驟1SQL語句如下：select case when@dept_no_t!= x.dept_no then @emp_salary_max_t := x.emp_salarywhen@dept_no_t= x.dept_noand x.emp_salary >@emp_salary_max_tthen @emp_salary_max_t := x.emp_salaryelse@emp_salary_max_tendas emp_salary_max, x.id, x.emp_name,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_date from emp as x,(select@dept_no_t :='')as t1, (select@emp_salary_max_t :=0)as t4 orderby dept_no, emp_salary desc

步驟1實驗結果如下：

步驟2SQL語句如下：select y.emp_salary_max, y.emp_salary_max - y.emp_salary as cha, y.id, y.emp_name, y.dept_no, y.emp_salary, y.emp_hire_datefrom(selectcasewhen@dept_no_t!= x.dept_no then@emp_salary_max_t := x.emp_salarywhen@dept_no_t= x.dept_no and x.emp_salary >@emp_salary_max_tthen@emp_salary_max_t := x.emp_salary else@emp_salary_max_tendas emp_salary_max, x.id, x.emp_name,@dept_no_t := x.dept_no as dept_no, x.emp_salary, x.emp_hire_date from emp as x,(select@dept_no_t :='')as t1, (select@emp_salary_max_t :=0)as t4 orderby dept_no, emp_salary desc)as y;

步驟2實驗結果如下：

以上就是MySQL5.7版本中，如何使用開窗函數的示例。希望能這篇文章能夠幫到你，也還原你把文章分享給更多的朋友。如果有什麼問題請留言，我看到後會第一時間恢復你。

相關焦點

  • mysql中find_in_set()函數的使用
    #39;);使用find_in_set函數一次返回多條記錄，其中id 是表的欄位，然後每條記錄分別是id等於1,2,3,4,5的時候，類似in （集合）select * from tb_user where id in (1,2,3,4,5);find_in_set
  • MySQL5.7 使用 mysqldump 重要避坑事項
    用戶備份習慣都是全備（-A），且都是備份恢復後出現 sys 庫 ERROR 1356，檢查用戶 MySQL 環境主要幾大版本分布 MySQL 5.7.13，5.7.25，5.7.28。於是把問題定位到了 mysqldump 的備份上。
  • MySQL常用的函數
    select LOCATE('bar', 'foobarbar',5); -> 74 INSTR(str,substr) 返回子串substr在字符串str中的第一個出現的位置。這與有2個參數形式的LOCATE()相同，除了參數 被顛倒。
  • Spark 開窗函數中order By的作用
    最近幾天，受到打擊了，總是被人問到 開窗函數中加入 order by 和 不加 order by 有什麼區別。例如 sum(x1) over (partition by x2 order by x1) 和 sum(x1) over (partition by x2)的結果一樣嗎？為什麼？
  • 《MySQL 入門教程》第15篇MySQL常用函數之字符函數
    strlist 中出現的位置，strlist 由 N 個子串使用逗號分隔組成。例如：select insert(&39;, 7, 3, &39;), insert(&39;, 10, 3, &39;);insert(&39;, 7, 3, &39;)|insert(&39;, 10, 3, &39;)|-------------------------------|--------------------------------|mysql.cn
  • 在python中使用aiomysql異步操作mysql
    ，所以在在網上查了下關於在python中異步的操作mysql，找來找去最後發現aiomysql的是實現最好的，現在簡單介紹一下它的使用。aiomysql的文檔地址 https://aiomysql.readthedocs.io/en/latest/需要根據項目中使用mysql查詢的頻率來選擇是使用單獨的connection還是使用連接池，查詢較少的可以選擇使用connection，使用一次以後就斷開，再次使用再次連接，但是對於mysql，每次連接的開銷都很高，所以建議還是使用連接池，由於不同的mysql服務對於
  • Hive的開窗函數
    開窗函數和聚合函數的區別：sql標準允許將所有聚合函數用作開窗函數，用over關鍵字區分開窗函數和聚合函數聚合函數每組只返回一個值，開窗函數每組可以返回多個值>舉例：rum_number()-----作為開窗函數 over （parition by ()----作為聚合函數）開窗函數的種類：row_number() over()：對相等的值不進行區分，相等的值對應的排名相同，序號從1到n連續
  • MySQL 5.7&MySQL 8.0 性能對比
    和mysql8.0分別在讀寫，選定，只寫模式下不同並發時的性能（tps，qps） 最早 測試使用版本為mysql5.7.22和mysql8.0.15 sysbench測試前先重啟mysql服務，並清除os的緩存（避免多次測試時命中緩存） 每次進行測試都是新生成測試數據後再進行
  • Hive的分析函數的使用
    我們先準備資料庫、表和數據開窗分析函數相對於mysql中的聚合函數區別是：分析函數是基於某個組多個數據進行計算聚合，而聚合函數是每個組返回一行數據。>結果是一樣的，但是窗口大小我們做了變化我們準備數據創建一個表載入數據load data local inpath '/data/hivetest/anal.txt' into table analytical;語句1：我們看結果語句2語句3語句4語句5
  • 如何計算100萬個數據的平均值？MySQL的AVG函數了解下
    MySQL是目前世界範圍內使用得非常廣泛得一種資料庫軟體，用MySQL的AVG函數就可以實現計算100萬個數據平均值的任務。AVG()函數語法：AVG(DISTINCT expression)AVG()函數句型：SELECT AVG(column_name) FROM table_nameAVG()函數例句：從EMPLOYEE_TBL表中獲取
  • 談談在mysql中使用聚合函數時容易忽略的一些小細節
    今天和大家一起學習一下mysql的聚合函數，話不多說，直接開始。第一個聚合函數：count這個count表示計算個數，下面我們來看一張表統計一下個數：select count(*) from course一共12條，我們再試一試其他的方式來統計：select count(name) from course我們發現出現的結果不一樣了，怎麼會少了呢？
  • CentOS7.X使用mysql_multi部署MySQL5.7.28多實例
    MySQL安裝包下載[root@~] wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz[root@~] mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql[root
  • MySQL5.5 , 5.6, 5.7, 8.0的新特性
    MySQL 5.72015年，MySQL 5.7發布，其包括如下重要特性及更新。引入了虛擬列，類似於Oracle中的函數索引。新實例不再默認創建test資料庫及匿名用戶。引入ALTER USER命令，可用來修改用戶密碼，密碼的過期策略，及鎖定用戶等。mysql.user表中存儲密碼的欄位從password修改為authentication_string。表空間加密。
  • mysql 時間函數用法 集合 - 軟體與服務 - 中國軟體網-推動ICT產業...
    對於每個類型擁有的值範圍以及並且指定日期何時間值的有效格式的描述見7.3.6 日期和時間類型。這裡是一個使用日期函數的例子。在MySQL 3.23中，你可以使用+和-而不是DATE_ADD()和DATE_SUB()。（見例子）date是一個指定開始日期的DATETIME或DATE值，expr是指定加到開始日期或從開始日期減去的間隔值一個表達式，expr是一個字符串；它可以以一個「-」開始表示負間隔。type是一個關鍵詞，指明表達式應該如何被解釋。
  • WEB滲透——mysql注入常用函數
    2、 session_user 連結資料庫的用戶名3、 @@basedir mysql安裝路徑4、 @@datadir 資料庫路徑5、 @@version_compile_os 作業系統版本這些函數我們都可以在裡面去敲一下。
  • MySQL切分函數substring()-大家get到這些點
    MySQL字符串截取函數主要有：left(), right(), substring(), substring_index() 四種。各有其使用場景。今天，讓我帶大家花幾分鐘時間來熟知它們，Mark！小夥伴想精準查找自己想看的MySQL文章？
  • 在MySQL中,如何對查詢結果進行條件過濾
    昨天和大家一起學習了在mysql中，如何分組查詢統計，但是有時候也會遇到這樣的一個問題，比如我在查詢中，已經做了分組統計，但是我想對於這個結果進行條件過濾？應該如何處理呢？我們一樣用之前的員工表emp，及部門表dept，若是我們要統計人數大於1的部門，應該如何寫mysql的代碼呢？首先第一步老步驟，先連接我們的資料庫，進入資料庫oaec；分析：之前我們有說過，條件篩選是什麼函數的？應該是我們的where函數，那若是我們這樣寫代碼，有沒有問題？
  • MySQL8.0窗口函數做數據排名統計詳細教程
    但隨著MySQL8.0中新增了窗口函數之後，針對這類統計就再也不是事了，本文就以常用的排序實例介紹MySQL的窗口函數。 |  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
  • Centos7 安裝MySQL 5.7
    在CentOS中默認安裝有MariaDB，這個是MySQL的分支，但為了需要，還是要在系統中安裝MySQL，而且安裝完成之後可以直接覆蓋掉MariaDB。mysql57-community-release-el7-10.noarch.rpm使用上面的命令就直接下載了安裝用的Yum Repository，，然後就可以直接yum安裝了。
  • 變量類型測試函數的使用:七、is_resource的用法
    講完PHP變量類型測試函數【is_object】的用法，今天來講講PHP變量類型測試函數【is_resource】的用法。關於is_resource()方法的問題問題碰到的問題如下在將php5.3的程序向php7遷移過程中 出現這樣的問題：1、我將mysql全換成mysqli