開窗函數也叫OLAP函數（Online Analytical Processing，線上分析處理），主要用來即時分析處理資料。在MySQL的8.0版本之前，開窗函數是不被支援的，但從這個版本開始就提供了對開窗函數的支援。
# 开窗函数语法 func_name() OVER([PARTITION BY ] [ORDER BY 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
在工作或面試中，可能會遇到需要求使用者連續登入天數或簽到天數的情況。下面就提供一個用開窗函數解決這類問題的思路。
# 首先创建虚拟的用户登录表，并插入数据 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中如何使用開窗函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！
