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

# 首先創(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;

# 首先創(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;

# 數(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ù) 
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è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;

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

# 計算第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;

# 分析題目：要求輸出字段為用戶名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