MySQL?開窗函數(shù)
結(jié)合order by關(guān)鍵詞和limit關(guān)鍵詞是可以解決很多的topN問題，比如從二手房數(shù)據(jù)集中查詢出某個地區(qū)的最貴的10套房，從電商交易數(shù)據(jù)集中查詢出實付金額最高的5筆交易，從學員信息表中查詢出年齡最小的3個學員等。但是，如果需求變成從二手房數(shù)據(jù)集中查詢出各個地區(qū)最貴的10套房，從電商數(shù)據(jù)集中查詢出每月實付金額最高的5筆交易，從學員信息表中查詢出各個科系下年齡最小的3個學員，該如何解決呢？
其實這類問題的核心就是，篩選出組內(nèi)的topN，而不是從全部數(shù)據(jù)集中挑選出topN。遇到這種既需要分組也需要排序的問題，直接上開窗函數(shù)就能解決了。
（1）開窗函數(shù)的定義
開窗函數(shù)也叫OLAP函數(shù)（Online Analytical Processing，聯(lián)機分析處理），主要用來實時分析處理數(shù)據(jù)。MySQL之前的版本是不支持開窗函數(shù)的，從8.0版本之后開始支持開窗函數(shù)。
# 開窗函數(shù)語法 func_name(<parameter>) OVER([PARTITION BY <part_by_condition>] [ORDER BY <order_by_list> ASC|DESC])
開窗函數(shù)語句解析：
函數(shù)分為兩部分，一部分是函數(shù)名稱，開窗函數(shù)的數(shù)量比較少，總共才11個開窗函數(shù)+聚合函數(shù)（所有的聚合函數(shù)都可以用作開窗函數(shù)）。根據(jù)函數(shù)的性質(zhì)，有的需要寫參數(shù)，有的不需要寫參數(shù)。
另一部分為over語句，over()是必須要寫的，里面的參數(shù)都是非必須參數(shù)，可以根據(jù)需求有選擇地使用：
- 第一個參數(shù)是partition by + 字段，含義是根據(jù)此字段將數(shù)據(jù)集分為多份
- 第二個參數(shù)是order by + 字段，每個窗口的數(shù)據(jù)依據(jù)此字段進行升序或降序排列
開窗函數(shù)與分組聚合函數(shù)比較相似，都是通過指定字段將數(shù)據(jù)分成多份，區(qū)別在于：
- SQL 標準允許將所有聚合函數(shù)用作開窗函數(shù)，用OVER 關(guān)鍵字區(qū)分開窗函數(shù)和聚合函數(shù)。
- 聚合函數(shù)每組只返回一個值，開窗函數(shù)每組可返回多個值。
在這11個開窗函數(shù)中，實際工作中用的最多的當屬ROW_NUMBER()、RANK()、DENSE_RANK()這三個排序函數(shù)了。下面我們通過一個簡單的數(shù)據(jù)集學習一下這三個開窗函數(shù)。
# 首先創(chuàng)建虛擬的業(yè)務(wù)員銷售數(shù)據(jù)
CREATE TABLE Sales
(
idate date,
iname char(2),
sales int
);
# 向表中插入數(shù)據(jù)
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);
# 數(shù)據(jù)查詢
SELECT * FROM Sales;
# 查詢各月中銷售業(yè)績最差的業(yè)務(wù)員
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()的區(qū)別 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）開窗函數(shù)的實際應(yīng)用場景
在實際工作或者面試中，可能會遇到求用戶連續(xù)登錄天數(shù)、連續(xù)簽到天數(shù)等問題。下面就提供一個用開窗函數(shù)解決此類問題的思路。
# 首先創(chuàng)建虛擬的用戶登錄表，并插入數(shù)據(jù) 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'); # 查看數(shù)據(jù) SELECT * FROM user_login;
計算連續(xù)登錄天數(shù)通常會有以下三種情況：
- 查看每位用戶連續(xù)登錄的情況
- 查看每位用戶最大連續(xù)登錄的天數(shù)
- 查看在某個時間段里連續(xù)登錄天數(shù)超過N天的用戶
針對第一種情況：查看每位用戶連續(xù)登錄的情況
根據(jù)實際經(jīng)驗，我們知道在一段時間內(nèi)，用戶可能出現(xiàn)多次連續(xù)登錄，這些信息我們都要輸出，所以最后結(jié)果輸出的字段可以是用戶ID、首次登錄日期、結(jié)束登錄日期、連續(xù)登錄天數(shù)這四個。
# 數(shù)據(jù)預處理：由于統(tǒng)計的窗口期是天數(shù)，所以可以對登錄時間字段進行格式轉(zhuǎn)換，將其變成日期格式然后再去重（去掉用戶同一天內(nèi)多次登錄的情況）
# 為方便后續(xù)代碼查看，將處理結(jié)果放置新表中，一步一步操作
create table user_login_date(
select distinct user_id, date(login_time) login_date from user_login);
# 處理后的數(shù)據(jù)如下：
select * from user_login_date;
# 第一種情況：查看每位用戶連續(xù)登陸的情況
# 對用戶登錄數(shù)據(jù)進行排序
create table user_login_date_1(
select *,
rank() over(partition by user_id order by login_date) irank
from user_login_date);
#查看結(jié)果
select * from user_login_date_1;
# 增加輔助列，幫助判斷用戶是否連續(xù)登錄
create table user_login_date_2(
select *,
date_sub(login_date, interval irank DAY) idate #data_sub從指定的日期減去指定的時間間隔
from user_login_date_1);
# 查看結(jié)果
select * from user_login_date_2;
# 計算每位用戶連續(xù)登錄天數(shù)
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;
# ===============【整合代碼，解決用戶連續(xù)登錄問題】===================
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;
針對第二種情況：查看每位用戶最大連續(xù)登錄的天數(shù)
# 計算每個用戶最大連續(xù)登錄天數(shù) 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;
針對第三種情況：查看在某個時間段里連續(xù)登錄天數(shù)超過N天的用戶
假如說，我們的需求是查看10/29-11/25在這段時間內(nèi)連續(xù)登錄天數(shù)≥5天的用戶。這個需求也可以用第一種情況查詢的結(jié)果進行篩選。
# 查看在這段時間內(nèi)連續(xù)登錄天數(shù)≥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;
這種寫法是可以得出結(jié)果，但是針對這個問題來說有點麻煩了，下面介紹一個簡單的方法：引用一個新的靜態(tài)窗口函數(shù)lead()
select *, lead(login_date,4) over(partition by user_id order by login_date) as idate5 from user_login_date;
lead函數(shù)有三個參數(shù)，第一個參數(shù)是指定的列（這里用登陸日期），第二個參數(shù)是當前行向后幾行的值，這里用的是4，也就是第五次登錄的日期，第三個參數(shù)是如果返回的空值可以用指定值替代，這里沒有使用第三個參數(shù)。 over語句里面是針對user_id分窗，每個窗口針對登錄日期升序。
用第五次登錄日期 - login_date+1，如果等于5，說明是連續(xù)登錄五天的，如果得到空值或者大于5，說明沒有連續(xù)登錄五天，代碼和結(jié)果如下：
# 計算第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; # 找出相差天數(shù)為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;
【練習】美團外賣平臺數(shù)據(jù)分析面試題——SQL
現(xiàn)有交易數(shù)據(jù)表user_goods_table如下：
現(xiàn)在老板想知道每個用戶購買的外賣品類偏好分布，并找出每個用戶購買最多的外賣品類是哪個。
# 分析題目：要求輸出字段為用戶名user_name，該用戶購買最多的外賣品類goods_kind # 解題思路：這是一個分組排序的問題，可以考慮窗口函數(shù) # 第一步：使用窗口函數(shù)row_number()，對每個用戶購買的外賣品類進行分組統(tǒng)計與排名 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
到此這篇關(guān)于MySQL 開窗函數(shù)的文章就介紹到這了,更多相關(guān)MySQL 開窗函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要為大家介紹了MySQL表級鎖使用說明，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-10-10
定時器可以用于定期執(zhí)行特定的SQL語句、備份數(shù)據(jù)、生成報表等操作，本文將詳細介紹MySQL中定時器的底層實現(xiàn)機制以及如何使用它2023-07-07
本文主要介紹了MySQL運算符!=和＜＞及=和＜=＞的使用區(qū)別，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-05-05
這篇文章主要介紹了MySQL索引優(yōu)化實例分析，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，需要的朋友可以參考一下2022-07-07
有效查詢MySQL表中重復數(shù)據(jù)的方法和技巧分享
在MySQL數(shù)據(jù)庫中,偶爾會遇到需要查找表中出現(xiàn)的重復數(shù)據(jù)的情況,這種情況下,我們可以通過編寫一些SQL查詢語句輕松地找到并處理這些重復行,本文將介紹一些常見的方法和技巧,幫助你有效地查詢MySQL表中的重復數(shù)據(jù),需要的朋友可以參考下2023-10-10
監(jiān)聽mysql表內(nèi)容變化 mysql開啟binlog
這篇文章主要給大家介紹了關(guān)于監(jiān)聽mysql表內(nèi)容變化，mysql開啟binlog的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-08-08
在mysql中timestamp數(shù)據(jù)類型是一個比較特殊的數(shù)據(jù)類型，他可以自動在你不使用程序更新情況下只要你更新了記錄timestamp會自動更新時間2013-06-06
Mysql 的存儲引擎,myisam和innodb的區(qū)別
這篇文章主要介紹了Mysql 的存儲引擎,myisam和innodb的區(qū)別,需要的朋友可以參考下2014-12-12
Xtrabackup使用指南 InnoDB數(shù)據(jù)備份工具
Xtrabackup是一個對InnoDB做數(shù)據(jù)備份的工具，支持在線熱備份(備份時不影響數(shù)據(jù)讀寫)，是商業(yè)備份工具InnoDB Hotbackup的一個很好的替代品2011-10-10
探索MySQL?8中utf8mb4釋放多語言數(shù)據(jù)的強大潛力
這篇文章主要為大家介紹了探索MySQL?8中utf8mb4釋放多語言數(shù)據(jù)的強大潛力,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2023-12-12