详解窗口函数 面试必备

详解窗口函数 面试必备

窗口函数常用函数方法
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() 保留两位小数