結合order by關鍵詞和limit關鍵詞是可以解決很多的topN問題，比如從二手房數據集中查詢出某個地區的最貴的10套房，從電商交易數據集中查詢出實付金額最高的5筆交易，從學員信息表中查詢出年齡最小的3個學員等。但是，如果需求變成從二手房數據集中查詢出各個地區最貴的10套房，從電商數據集中查詢出每月實付金額最高的5筆交易，從學員信息表中查詢出各個科系下年齡最小的3個學員，該如何解決呢？

其實這類問題的核心就是，篩選出組內的topN，而不是從全部數據集中挑選出topN。遇到這種既需要分組也需要排序的問題，直接上開窗函數就能解決了。

（1）開窗函數的定義
開窗函數也叫OLAP函數（Online Analytical Processing，聯機分析處理），主要用來實時分析處理數據。MySQL之前的版本是不支持開窗函數的，從8.0版本之后開始支持開窗函數。

# 開窗函數語法 func_name(<parameter>) OVER([PARTITION BY <part_by_condition>] [ORDER BY <order_by_list> ASC|DESC])

開窗函數語句解析：
函數分為兩部分，一部分是函數名稱，開窗函數的數量比較少，總共才11個開窗函數+聚合函數（所有的聚合函數都可以用作開窗函數）。根據函數的性質，有的需要寫參數，有的不需要寫參數。

另一部分為over語句，over()是必須要寫的，里面的參數都是非必須參數，可以根據需求有選擇地使用：

• 第一個參數是partition by + 字段，含義是根據此字段將數據集分為多份
• 第二個參數是order by + 字段，每個窗口的數據依據此字段進行升序或降序排列

開窗函數與分組聚合函數比較相似，都是通過指定字段將數據分成多份，區別在于：

• SQL 標準允許將所有聚合函數用作開窗函數，用OVER 關鍵字區分開窗函數和聚合函數。
• 聚合函數每組只返回一個值，開窗函數每組可返回多個值。

在這11個開窗函數中，實際工作中用的最多的當屬ROW_NUMBER()、RANK()、DENSE_RANK()這三個排序函數了。下面我們通過一個簡單的數據集學習一下這三個開窗函數。

# 首先創建虛擬的業務員銷售數據 CREATE TABLE Sales ( idate date, iname char(2), sales int ); # 向表中插入數據 INSERT INTO Sales VALUES ('2021/1/1', '丁一', 200), ('2021/2/1', '丁一', 180), ('2021/2/1', '李四', 100), ('2021/3/1', '李四', 150), ('2021/2/1', '劉猛', 180), ('2021/3/1', '劉猛', 150), ('2021/1/1', '王二', 200), ('2021/2/1', '王二', 180), ('2021/3/1', '王二', 300), ('2021/1/1', '張三', 300), ('2021/2/1', '張三', 280), ('2021/3/1', '張三', 280); # 數據查詢 SELECT * FROM Sales; # 查詢各月中銷售業績最差的業務員 SELECT month(idate),iname,sales, ROW_NUMBER() OVER(PARTITION BY month(idate) ORDER BY sales) as sales_order FROM Sales;SELECT * FROM (SELECT month(idate),iname,sales, ROW_NUMBER() OVER(PARTITION BY month(idate) ORDER BY sales) as sales_order FROM Sales) as t WHERE sales_order=1;

# ROW_NUMBER()、RANK()、DENSE_RANK()的區別 SELECT * FROM (SELECT month(idate) as imonth,iname,sales, ROW_NUMBER() OVER(PARTITION BY month(idate) ORDER BY sales) as row_order, RANK() OVER(PARTITION BY month(idate) ORDER BY sales) as rank_order, DENSE_RANK() OVER(PARTITION BY month(idate) ORDER BY sales) as dense_order FROM Sales) as t;

ROW_NUMBER()：順序排序——1、2、3
RANK()：并列排序，跳過重復序號——1、1、3
DENSE_RANK()：并列排序，不跳過重復序號——1、1、2
（2）開窗函數的實際應用場景
在實際工作或者面試中，可能會遇到求用戶連續登錄天數、連續簽到天數等問題。下面就提供一個用開窗函數解決此類問題的思路。

# 首先創建虛擬的用戶登錄表，并插入數據 create table user_login ( user_id varchar(100), login_time datetime ); insert into user_login values (1,'2020-11-25 13:21:12'), (1,'2020-11-24 13:15:22'), (1,'2020-11-24 10:30:15'), (1,'2020-11-24 09:18:27'), (1,'2020-11-23 07:43:54'), (1,'2020-11-10 09:48:36'), (1,'2020-11-09 03:30:22'), (1,'2020-11-01 15:28:29'), (1,'2020-10-31 09:37:45'), (2,'2020-11-25 13:54:40'), (2,'2020-11-24 13:22:32'), (2,'2020-11-23 10:55:52'), (2,'2020-11-22 06:30:09'), (2,'2020-11-21 08:33:15'), (2,'2020-11-20 05:38:18'), (2,'2020-11-19 09:21:42'), (2,'2020-11-02 00:19:38'), (2,'2020-11-01 09:03:11'), (2,'2020-10-31 07:44:55'), (2,'2020-10-30 08:56:33'), (2,'2020-10-29 09:30:28'); # 查看數據 SELECT * FROM user_login;

計算連續登錄天數通常會有以下三種情況：

• 查看每位用戶連續登錄的情況
• 查看每位用戶最大連續登錄的天數
• 查看在某個時間段里連續登錄天數超過N天的用戶

針對第一種情況：查看每位用戶連續登錄的情況
根據實際經驗，我們知道在一段時間內，用戶可能出現多次連續登錄，這些信息我們都要輸出，所以最后結果輸出的字段可以是用戶ID、首次登錄日期、結束登錄日期、連續登錄天數這四個。

# 數據預處理：由于統計的窗口期是天數，所以可以對登錄時間字段進行格式轉換，將其變成日期格式然后再去重（去掉用戶同一天內多次登錄的情況） # 為方便后續代碼查看，將處理結果放置新表中，一步一步操作 create table user_login_date( select distinct user_id, date(login_time) login_date from user_login); # 處理后的數據如下： select * from user_login_date;# 第一種情況：查看每位用戶連續登陸的情況 # 對用戶登錄數據進行排序 create table user_login_date_1( select *, rank() over(partition by user_id order by login_date) irank from user_login_date); #查看結果 select * from user_login_date_1;# 增加輔助列，幫助判斷用戶是否連續登錄 create table user_login_date_2( select *, date_sub(login_date, interval irank DAY) idate #data_sub從指定的日期減去指定的時間間隔 from user_login_date_1); # 查看結果 select * from user_login_date_2; # 計算每位用戶連續登錄天數 select user_id, min(login_date) as start_date, max(login_date) as end_date, count(login_date) as days from user_login_date_2 group by user_id,idate;# ===============【整合代碼，解決用戶連續登錄問題】=================== select user_id, min(login_date) start_date, max(login_date) end_date, count(login_date) days from (select *,date_sub(login_date, interval irank day) idate from (select *,rank() over(partition by user_id order by login_date) irank from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c group by user_id,idate;

針對第二種情況：查看每位用戶最大連續登錄的天數

# 計算每個用戶最大連續登錄天數 select user_id,max(days) from (select user_id, min(login_date) start_date, max(login_date) end_date, count(login_date) days from (select *,date_sub(login_date, interval irank day) idate from (select *,rank() over(partition by user_id order by login_date) irank from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c group by user_id,idate) as d group by user_id;

針對第三種情況：查看在某個時間段里連續登錄天數超過N天的用戶

假如說，我們的需求是查看10/29-11/25在這段時間內連續登錄天數≥5天的用戶。這個需求也可以用第一種情況查詢的結果進行篩選。

# 查看在這段時間內連續登錄天數≥5天的用戶 select distinct user_id from (select user_id, min(login_date) start_date, max(login_date) end_date, count(login_date) days from (select *,date_sub(login_date, interval irank day) idate from (select *,rank() over(partition by user_id order by login_date) irank from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c group by user_id,idate having days>=5 ) as d;

這種寫法是可以得出結果，但是針對這個問題來說有點麻煩了，下面介紹一個簡單的方法：引用一個新的靜態窗口函數lead()

select *, lead(login_date,4) over(partition by user_id order by login_date) as idate5 from user_login_date;

lead函數有三個參數，第一個參數是指定的列（這里用登陸日期），第二個參數是當前行向后幾行的值，這里用的是4，也就是第五次登錄的日期，第三個參數是如果返回的空值可以用指定值替代，這里沒有使用第三個參數。 over語句里面是針對user_id分窗，每個窗口針對登錄日期升序。

用第五次登錄日期 - login_date+1，如果等于5，說明是連續登錄五天的，如果得到空值或者大于5，說明沒有連續登錄五天，代碼和結果如下：

# 計算第5次登錄日期與當天的差值 select *,datediff(idate5,login_date)+1 days from (select *,lead(login_date,4) over(partition by user_id order by login_date) idate5 from user_login_date) as a; # 找出相差天數為5的記錄 select distinct user_id from (select *,datediff(idate5,login_date)+1 as days from (select *,lead(login_date,4) over(partition by user_id order by login_date) idate5 from user_logrin_date) as a)as b where days = 5;

【練習】美團外賣平臺數據分析面試題——SQL
現有交易數據表user_goods_table如下：

現在老板想知道每個用戶購買的外賣品類偏好分布，并找出每個用戶購買最多的外賣品類是哪個。

# 分析題目：要求輸出字段為用戶名user_name，該用戶購買最多的外賣品類goods_kind # 解題思路：這是一個分組排序的問題，可以考慮窗口函數 # 第一步：使用窗口函數row_number()，對每個用戶購買的外賣品類進行分組統計與排名 select user_name,goods_kind,count(goods_kind), rank() over (partition by user_name order by count(goods_kind) desc) as irank from user_goods_table group by user_name,goods_kind;# 第二步：篩選出每個用戶排名第一的外賣品類 select user_id,goods_kind from (select user_name,goods_kind,count(goods_kind), rank() over (partition by user_name order by count(goods_kind) desc) as irank from user_goods_table group by user_name,goods_kind) as a where irank=1 《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的MySQL——开窗函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。