窗口函數也叫分析函數,在oracle中早就已經支持,而mysql直到8.0才正式支持窗口函數。
我們還是以employees庫為例進行實踐操作。
窗口函數也叫分析函數,在oracle中早就已經支持,而mysql直到8.0才正式支持窗口函數。
我們還是以employees庫為例進行實踐操作。
什麼是窗口函數
首先我們回顧下group by的聚合函數,常見的有sum,max,min,avg等等,它們的功能都是聚集然後合併,都是給定一組數字進行聚合計算,計算後的結果是一個值或者某個類別對應的值。例如
窗口函數也叫分析函數,在oracle中早就已經支持,而mysql直到8.0才正式支持窗口函數。
我們還是以employees庫為例進行實踐操作。
什麼是窗口函數
首先我們回顧下group by的聚合函數,常見的有sum,max,min,avg等等,它們的功能都是聚集然後合併,都是給定一組數字進行聚合計算,計算後的結果是一個值或者某個類別對應的值。例如
直接聚合的結果就是所有員工工資總和,分組聚合得到結果就是每個員工的工資總和。這種方式的聚合統計得到結果集合小於原始表的結果集合的。實際場景中有這樣的需求,我們需要在原始記錄每一行集上,再看到聚合後的數字。例如我們想看到所有人的工資,並且看到他工資排名,這時候,用原來的group by聚合就不能滿足需求量,這時候就需要窗口函數,它可以在原始的數據行集上再進行聚合計算,並顯示出來,現在我們看看具體應用。
窗口函數 +OVER()
現在我們通過員工表(employees)和薪資表(salaries)進行關聯,假設我們查詢出員工編號等於10001的年薪工資:
SELECT a.first_name,b.from_date,b.to_date,b.salary from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
窗口函數也叫分析函數,在oracle中早就已經支持,而mysql直到8.0才正式支持窗口函數。
我們還是以employees庫為例進行實踐操作。
什麼是窗口函數
首先我們回顧下group by的聚合函數,常見的有sum,max,min,avg等等,它們的功能都是聚集然後合併,都是給定一組數字進行聚合計算,計算後的結果是一個值或者某個類別對應的值。例如
直接聚合的結果就是所有員工工資總和,分組聚合得到結果就是每個員工的工資總和。這種方式的聚合統計得到結果集合小於原始表的結果集合的。實際場景中有這樣的需求,我們需要在原始記錄每一行集上,再看到聚合後的數字。例如我們想看到所有人的工資,並且看到他工資排名,這時候,用原來的group by聚合就不能滿足需求量,這時候就需要窗口函數,它可以在原始的數據行集上再進行聚合計算,並顯示出來,現在我們看看具體應用。
窗口函數 +OVER()
現在我們通過員工表(employees)和薪資表(salaries)進行關聯,假設我們查詢出員工編號等於10001的年薪工資:
SELECT a.first_name,b.from_date,b.to_date,b.salary from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
現在我們想增加一列員工(10001)的所有工資的平均值,那麼可以使用窗口函數實現,只要使用聚合avg函數後面跟上 over(),具體如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over() from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
窗口函數也叫分析函數,在oracle中早就已經支持,而mysql直到8.0才正式支持窗口函數。
我們還是以employees庫為例進行實踐操作。
什麼是窗口函數
首先我們回顧下group by的聚合函數,常見的有sum,max,min,avg等等,它們的功能都是聚集然後合併,都是給定一組數字進行聚合計算,計算後的結果是一個值或者某個類別對應的值。例如
直接聚合的結果就是所有員工工資總和,分組聚合得到結果就是每個員工的工資總和。這種方式的聚合統計得到結果集合小於原始表的結果集合的。實際場景中有這樣的需求,我們需要在原始記錄每一行集上,再看到聚合後的數字。例如我們想看到所有人的工資,並且看到他工資排名,這時候,用原來的group by聚合就不能滿足需求量,這時候就需要窗口函數,它可以在原始的數據行集上再進行聚合計算,並顯示出來,現在我們看看具體應用。
窗口函數 +OVER()
現在我們通過員工表(employees)和薪資表(salaries)進行關聯,假設我們查詢出員工編號等於10001的年薪工資:
SELECT a.first_name,b.from_date,b.to_date,b.salary from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
現在我們想增加一列員工(10001)的所有工資的平均值,那麼可以使用窗口函數實現,只要使用聚合avg函數後面跟上 over(),具體如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over() from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
也許你會問,這有啥用,這樣可以引申出更多的統計,例如員工每年工資與平均工資的佔比,是不是直接salary除以窗口函數值,就可以了。
partition by 子句
假設現在有2個員工(編號:10001,10002),需要統計他們各自工資和所有平均工資的值,這時候可以使用 partition by ,它的作用和group by 類似,都是聚合使用,需要和over()搭配,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over(partition by a.first_name) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
窗口函數也叫分析函數,在oracle中早就已經支持,而mysql直到8.0才正式支持窗口函數。
我們還是以employees庫為例進行實踐操作。
什麼是窗口函數
首先我們回顧下group by的聚合函數,常見的有sum,max,min,avg等等,它們的功能都是聚集然後合併,都是給定一組數字進行聚合計算,計算後的結果是一個值或者某個類別對應的值。例如
直接聚合的結果就是所有員工工資總和,分組聚合得到結果就是每個員工的工資總和。這種方式的聚合統計得到結果集合小於原始表的結果集合的。實際場景中有這樣的需求,我們需要在原始記錄每一行集上,再看到聚合後的數字。例如我們想看到所有人的工資,並且看到他工資排名,這時候,用原來的group by聚合就不能滿足需求量,這時候就需要窗口函數,它可以在原始的數據行集上再進行聚合計算,並顯示出來,現在我們看看具體應用。
窗口函數 +OVER()
現在我們通過員工表(employees)和薪資表(salaries)進行關聯,假設我們查詢出員工編號等於10001的年薪工資:
SELECT a.first_name,b.from_date,b.to_date,b.salary from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
現在我們想增加一列員工(10001)的所有工資的平均值,那麼可以使用窗口函數實現,只要使用聚合avg函數後面跟上 over(),具體如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over() from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
也許你會問,這有啥用,這樣可以引申出更多的統計,例如員工每年工資與平均工資的佔比,是不是直接salary除以窗口函數值,就可以了。
partition by 子句
假設現在有2個員工(編號:10001,10002),需要統計他們各自工資和所有平均工資的值,這時候可以使用 partition by ,它的作用和group by 類似,都是聚合使用,需要和over()搭配,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over(partition by a.first_name) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
order by 子句
含義是按照某一列數值進行排序,主要和序列函數進行使用,還是舉一個員工(10001)例子,當order by子句與聚合函數一起使用,也叫順序聚合,舉個例子,求和聚合類似一個累積和的效果,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,sum(b.salary) over(partition by a.first_name order by b.salary desc
) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
窗口函數也叫分析函數,在oracle中早就已經支持,而mysql直到8.0才正式支持窗口函數。
我們還是以employees庫為例進行實踐操作。
什麼是窗口函數
首先我們回顧下group by的聚合函數,常見的有sum,max,min,avg等等,它們的功能都是聚集然後合併,都是給定一組數字進行聚合計算,計算後的結果是一個值或者某個類別對應的值。例如
直接聚合的結果就是所有員工工資總和,分組聚合得到結果就是每個員工的工資總和。這種方式的聚合統計得到結果集合小於原始表的結果集合的。實際場景中有這樣的需求,我們需要在原始記錄每一行集上,再看到聚合後的數字。例如我們想看到所有人的工資,並且看到他工資排名,這時候,用原來的group by聚合就不能滿足需求量,這時候就需要窗口函數,它可以在原始的數據行集上再進行聚合計算,並顯示出來,現在我們看看具體應用。
窗口函數 +OVER()
現在我們通過員工表(employees)和薪資表(salaries)進行關聯,假設我們查詢出員工編號等於10001的年薪工資:
SELECT a.first_name,b.from_date,b.to_date,b.salary from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
現在我們想增加一列員工(10001)的所有工資的平均值,那麼可以使用窗口函數實現,只要使用聚合avg函數後面跟上 over(),具體如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over() from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
也許你會問,這有啥用,這樣可以引申出更多的統計,例如員工每年工資與平均工資的佔比,是不是直接salary除以窗口函數值,就可以了。
partition by 子句
假設現在有2個員工(編號:10001,10002),需要統計他們各自工資和所有平均工資的值,這時候可以使用 partition by ,它的作用和group by 類似,都是聚合使用,需要和over()搭配,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over(partition by a.first_name) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
order by 子句
含義是按照某一列數值進行排序,主要和序列函數進行使用,還是舉一個員工(10001)例子,當order by子句與聚合函數一起使用,也叫順序聚合,舉個例子,求和聚合類似一個累積和的效果,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,sum(b.salary) over(partition by a.first_name order by b.salary desc
) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
可以看到排序之後,第一條記錄的求和值為72527,第二條: 72527加上71963等於144490,以此類推,得到累加和。
序列函數
序列函數有好幾個,這裡只講講常用的row_number。row_number從1開始,按照順序生成該條數據在分組內的對應的序列。因為它按照順序生成對於序列,而不是按照排序生成序列,所以一般與order by 結合使用。
舉例:查詢每個員工哪一年開始領取工資?我們要看到最早開始領取工資年份,只需根據from_date 進行排序,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,row_number() over(partition by a.first_name order by b.from_date
) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
窗口函數也叫分析函數,在oracle中早就已經支持,而mysql直到8.0才正式支持窗口函數。
我們還是以employees庫為例進行實踐操作。
什麼是窗口函數
首先我們回顧下group by的聚合函數,常見的有sum,max,min,avg等等,它們的功能都是聚集然後合併,都是給定一組數字進行聚合計算,計算後的結果是一個值或者某個類別對應的值。例如
直接聚合的結果就是所有員工工資總和,分組聚合得到結果就是每個員工的工資總和。這種方式的聚合統計得到結果集合小於原始表的結果集合的。實際場景中有這樣的需求,我們需要在原始記錄每一行集上,再看到聚合後的數字。例如我們想看到所有人的工資,並且看到他工資排名,這時候,用原來的group by聚合就不能滿足需求量,這時候就需要窗口函數,它可以在原始的數據行集上再進行聚合計算,並顯示出來,現在我們看看具體應用。
窗口函數 +OVER()
現在我們通過員工表(employees)和薪資表(salaries)進行關聯,假設我們查詢出員工編號等於10001的年薪工資:
SELECT a.first_name,b.from_date,b.to_date,b.salary from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
現在我們想增加一列員工(10001)的所有工資的平均值,那麼可以使用窗口函數實現,只要使用聚合avg函數後面跟上 over(),具體如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over() from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
也許你會問,這有啥用,這樣可以引申出更多的統計,例如員工每年工資與平均工資的佔比,是不是直接salary除以窗口函數值,就可以了。
partition by 子句
假設現在有2個員工(編號:10001,10002),需要統計他們各自工資和所有平均工資的值,這時候可以使用 partition by ,它的作用和group by 類似,都是聚合使用,需要和over()搭配,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over(partition by a.first_name) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
order by 子句
含義是按照某一列數值進行排序,主要和序列函數進行使用,還是舉一個員工(10001)例子,當order by子句與聚合函數一起使用,也叫順序聚合,舉個例子,求和聚合類似一個累積和的效果,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,sum(b.salary) over(partition by a.first_name order by b.salary desc
) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
可以看到排序之後,第一條記錄的求和值為72527,第二條: 72527加上71963等於144490,以此類推,得到累加和。
序列函數
序列函數有好幾個,這裡只講講常用的row_number。row_number從1開始,按照順序生成該條數據在分組內的對應的序列。因為它按照順序生成對於序列,而不是按照排序生成序列,所以一般與order by 結合使用。
舉例:查詢每個員工哪一年開始領取工資?我們要看到最早開始領取工資年份,只需根據from_date 進行排序,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,row_number() over(partition by a.first_name order by b.from_date
) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
最後只要把等於1的數據取出來,就是最早的每個員工領取工資的記錄。
窗口函數也叫分析函數,在oracle中早就已經支持,而mysql直到8.0才正式支持窗口函數。
我們還是以employees庫為例進行實踐操作。
什麼是窗口函數
首先我們回顧下group by的聚合函數,常見的有sum,max,min,avg等等,它們的功能都是聚集然後合併,都是給定一組數字進行聚合計算,計算後的結果是一個值或者某個類別對應的值。例如
直接聚合的結果就是所有員工工資總和,分組聚合得到結果就是每個員工的工資總和。這種方式的聚合統計得到結果集合小於原始表的結果集合的。實際場景中有這樣的需求,我們需要在原始記錄每一行集上,再看到聚合後的數字。例如我們想看到所有人的工資,並且看到他工資排名,這時候,用原來的group by聚合就不能滿足需求量,這時候就需要窗口函數,它可以在原始的數據行集上再進行聚合計算,並顯示出來,現在我們看看具體應用。
窗口函數 +OVER()
現在我們通過員工表(employees)和薪資表(salaries)進行關聯,假設我們查詢出員工編號等於10001的年薪工資:
SELECT a.first_name,b.from_date,b.to_date,b.salary from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
現在我們想增加一列員工(10001)的所有工資的平均值,那麼可以使用窗口函數實現,只要使用聚合avg函數後面跟上 over(),具體如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over() from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no ='10001'
也許你會問,這有啥用,這樣可以引申出更多的統計,例如員工每年工資與平均工資的佔比,是不是直接salary除以窗口函數值,就可以了。
partition by 子句
假設現在有2個員工(編號:10001,10002),需要統計他們各自工資和所有平均工資的值,這時候可以使用 partition by ,它的作用和group by 類似,都是聚合使用,需要和over()搭配,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,avg(b.salary) over(partition by a.first_name) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
order by 子句
含義是按照某一列數值進行排序,主要和序列函數進行使用,還是舉一個員工(10001)例子,當order by子句與聚合函數一起使用,也叫順序聚合,舉個例子,求和聚合類似一個累積和的效果,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,sum(b.salary) over(partition by a.first_name order by b.salary desc
) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
可以看到排序之後,第一條記錄的求和值為72527,第二條: 72527加上71963等於144490,以此類推,得到累加和。
序列函數
序列函數有好幾個,這裡只講講常用的row_number。row_number從1開始,按照順序生成該條數據在分組內的對應的序列。因為它按照順序生成對於序列,而不是按照排序生成序列,所以一般與order by 結合使用。
舉例:查詢每個員工哪一年開始領取工資?我們要看到最早開始領取工資年份,只需根據from_date 進行排序,代碼如下:
SELECT a.first_name,b.from_date,b.to_date,b.salary ,row_number() over(partition by a.first_name order by b.from_date
) from employees a LEFT JOIN salaries b on a.emp_no = b.emp_no
where a.emp_no in('10001','10002')
最後只要把等於1的數據取出來,就是最早的每個員工領取工資的記錄。
總結
窗口函數可以處理一些複雜的業務,主要特點:不會改變原來數據集的行數,針對每一行繼續通過over()子句中的作用域再進行一次聚合運算,從而達到某些統計分析的目的。當然,窗口函數相對其他函數比較抽象,需要結合具體業務才能真正理解,本節當作入門實踐吧。