详解窗口函数 面试必备
窗口函数常用函数方法
row_number() over(partition by … order by …):排序1-2-3-4-5
rank() over(partition by … order by …):排序 1-1-3
dense_rank() over(partition by … order by …):排序 1-1-2-3
count() over(partition by … order by …):求分组后的总数。
max() over(partition by … order by …):求分组后的最大值。
min() over(partition by … order by …):求分组后的最小值。
avg() over(partition by … order by …):求分组后的平均值。
lag(field, num, defaultvalue) over(partition by … order by …):取出前第n行数据。
#field需要查找的字段,num往前查找第num行的数据,defaultvalue没有符合条件的默认值
lead(field, num, defaultvalue) over(partition by … order by …):取出后n行数据。
#field需要查找的字段,num往后查找第num行的数据,defaultvalue没有符合条件的默认值
ratio_to_report() over(partition by … order by …):Ratio_to_report() 括号中就是分子,over() 括号中就是分母。
percent_rank() over(partition by … order by …)
窗口函数其他方法:LAG、LEAD、FIRST_VALUE、LAST_VALUE
总结
FIRST_VALUE
功能:取每个分区内某列的第一个值
语法:FIRST_VALUE(col) over (partition by col1 order by col2)
LAST_VALUE
功能:取每个分区内某列的最后一个值
语法:LAST_VALUE(col) over (partition by col1 order by col2)
注意:默认窗口是从第一条到当前条
LAG
功能:取每个分区内某列的前面的第N个值
语法:LAG(col,N,defaultValue) over (partition by col1 order by col2)
LEAD
功能:取每个分区内某列的后面的第N个值
语法:LEAD(col,N,defaultValue) over (partition by col1 order by col2)
round函数
在mysql中,round函数用于数据的四舍五入,它有两种形式:
1、round(x,d) ,x指要处理的数,d是指保留几位小数
这里有个值得注意的地方是,d可以是负数,这时是指定小数点左边的d位整数位为0,同时小数位均为0;
2、round(x) ,其实就是round(x,0),也就是默认d为0;
下面是几个实例
1、查询: select round(1123.26723,2); //结果:1123.27
2、查询: select round(1123.26723,1); //结果: 1123.3
3、查询: select round(1123.26723,0); //结果:1123
4、查询: select round(1123.26723,-1); //结果: 1120
5、查询: select round(1123.26723,-2); // 结果:1100
6、查询: select round(1123.26723); // 结果:1123
例1,511. 游戏玩法分析 I
写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期
解法一:力扣上面不支持(加不加distinct结果一样 因为采用常规写法在后面已经greoup by 了)
select distinct player_id, event_date as first_login from Activity group by player_id order by player_id,event_date
你会发现,对player_id进行分组,然后对event_date进行排序(默认是升序),你会发现会取最小的数据
解法二:采用窗口函数 ,窗口函数中over里面的只是条件,最终要展示的是min计算出来的值
select distinct player_id ,min(event_date) over(partition by player_id ) as first_login from activity
若不加distinct,则结果:first_login 中03-01出现了两次,所以over中的函数只是约束窗口函数。其他不能约束
解法三:
select distinct player_id ,first_value(event_date) over(partition by player_id order by event_date) as first_login from activity
若不加 distinct去重 则first_login 中03-01出现了两次,所以over中的函数只是约束窗口函数。其他不能约束
512. 游戏玩法分析 II 结果图:
请编写一个 SQL 查询,描述每一个玩家首次登陆的设备名称
解法一:采用窗口函数中的first_value
select distinct player_id ,first_value(device_id) over(partition by player_id order by event_date) as device_id from activity
若去掉distinct 去重操作。则:多出现一次
解法二:力扣上面报错 但在navicat上面执行通过
select player_id ,device_id from activity where (player_id,event_date) in (select distinct player_id,event_date from activity group by player_id order by player_id, event_date )
解法三: 该sql中没有添加distinct是因为在子查询中,已经过滤掉了
select player_id ,device_id from activity where (player_id,event_date) in (select distinct player_id,min(event_date) over(partition by player_id order by player_id) from activity )
534. 游戏玩法分析 III‘
编写一个 SQL 查询,同时报告每组玩家和日期,以及玩家到目前为止玩了多少游戏。也就是说,在此日期之前玩家所玩的游戏总数。详细情况请查看示例。
解法一:采用窗口函数
select player_id ,event_date ,sum(games_played) over(partition by player_id order by event_date) as games_played_so_far from activity
解法二:自连接
select
a1.player_id,
a1.event_date,
sum(a2.games_played) as games_played_so_far
from activity a1
join activity a2
on a1.player_id = a2.player_id
and a1.event_date>=a2.event_date
group by a1.player_id,a1.event_date
order by a1.player_id,a1.event_date;
550. 游戏玩法分析 IV
编写一个 SQL 查询,报告在首次登录的第二天再次登录的玩家的比率,四舍五入到小数点后两位。换句话说,您需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数。
查询结果格式如下所示:
只有 ID 为 1 的玩家在第一天登录后才重新登录,所以答案是 1/3 = 0.33
SQL:方法一
select round((select count(player_id) from (
select
player_id,
event_date,
lag(event_date)
over(partition by player_id order by event_date) as next_date,
row_number() over(partition by player_id order by event_date) as login_times
from activity
) as temp where datediff(event_date, next_date) = 1 and login_times = 2)
/
(select count(player_id) from (
select player_id from activity group by player_id
) as temp), 2) as fraction;
解析
需要求出两个值:第一个值是总人数,第二个值是连续登录两天的玩家数
1.求总人数:
将 activity 表按照 player_id 进行分组在计算出总人数
select count(player_id) from (
select player_id from activity group by player_id
) as temp
还有一种写法:
select count(distinct player_id) from activity
2.求连续两天登录游戏的玩家数:
使用窗口函数 lag(event_date) 将日期按照 player_id 分组,并按照 event_date 升序排列,然后偏移一天:
lag(event_date) over(partition by player_id order by event_date) as next_date
使用row_number()对日期按照 player_id 分组,并按照 event_date 升序排列,然后排序:
row_number() over(partition by player_id order by event_date) as login_times
组合成完整的 SQL :
select
player_id,
event_date,
lag(event_date) over(partition by player_id order by event_date) as next_date,
row_number() over(partition by player_id order by event_date) as login_times
from activity
筛选出日期相差一天,并且是第一次连续登录,这里 login_times 取值 2 ,因为上面按照登录日期进行排序过了,第一天登录是 1 ,第二天登录是 2 。
计算出 player_id 的个数就可以算出连续登录两天的玩家了数了:
select count(player_id) from (
select
player_id,
event_date,
lag(event_date)
over(partition by player_id order by event_date) as next_date,
rank() over(partition by player_id order by event_date) as login_times
from activity
) as temp where datediff(event_date, next_date) = 1 and login_times = 2
3.最后:
使用 round() 保留两位小数