MySQL 開窗函數

2022-02-15 10:02:43

結合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

到此這篇關於MySQL 開窗函數的文章就介紹到這了,更多相關MySQL 開窗函數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL 開窗函數

目錄

（1）開窗函數的定義

（2）開窗函數的實際應用場景

熱門文章