一、窗口函数
1、基本语法
over(partition by order by)
2、大概分类专用窗口函数
比如 rank,dense_rank, row_number等聚合函数
比如 sum,count,avg,max,min等
二、如何使用窗口函数(聚合函数作为窗口函数)
1、聚合函数 + over()函数问题:求每个店铺,每天的销量和全部销量的均值对比;店铺销量表——shop_sale
-- 一般的解决办法select shopname,
sales,
sale_date,
(select avg(sales) from shop_sales) avg_sales
form shop_sale;
-- 用窗口函数来解决select shopname,
sales,
sale_date,
avg(sales) over() as avg_sales
form shop_sale;
第一段代码虽然可以实现我们的需求,但代码略繁琐,窗口函数可以简化代码;
over()函数的作用,就是将聚合结果显示在每条单独的记录中。
2、partition by 子句
上面的例子,展示的是“每个店铺,每天销量和全部销量均值作比较”;
但如果店铺之间的差异不大,那么用全部销量的均值作为比较标准还可以,如果店铺之间的销量差异很大,那么就不具备可比性了,店铺销量自己和自己比;
这里的需求分成2部分,一是所有店铺的每日销量,二是店铺的销量均值;
-- 一般的解决方案select shopname,
sales,
sale_date,
b.avg_sales
form shop_sale a
left join
(select shopname, avg(sales) avg_sales
from shop_sale
group by shopname) b
on a.shopname = b.shopname
-- 用窗口函数select shopname,
sales,
sale_date,
avg(sales) over(partition by shopname) as avg_sales
form shop_sale
partition by 的作用类似于 group by,但确实在over()函数中使用,按照指定的列进行分组,聚合函数就会在分好的组内运算。
3、order by 子句
order by 子句比较好理解,即在over()函数中进行指定的排序;
为什么需要order by子句呢?还是用上面的例子。
上面的例子,考虑到了店铺之间销量的差异问题,所以店铺用自己的销量均值作比较,但还是不够严谨,因为我们不能用未来的信息和现在的信息进行比较;
比如 1月2日的销量,在求均值时就不应该包含1月3日的销量,而只应该包括1月2日及之前的销量,这种聚合方式也被叫做【顺序聚合】。
select shopname,
sales,
sale_date,
avg(sales) over(partition by shopname order by sale_date) as avg_sales
form shop_sale3
三、专用窗口函数
1、序列函数:rank,dense_4rank,row_number,以及3者的区别:
select * ,
rank() over(order by 成绩 desc) as ranking,
dense_rank() over(order by 成绩 desc) as dense_rank,
row_number() over(order by 成绩 desc) as row_num
from score
案例:如何使用排名函数解决 Top N 问题——每组最大/小的N条记录按课程号分组取成绩最大值所在行的数据笨办法 , 关联子查询
select 学号, 课程号, 成绩
from score a
where 成绩 = (
select max(成绩)
from score b
where a.课程号 = b.课程号)
order by 课程号;窗口函数的解题思路
select *
from (
select 学号, 课程号, 成绩, rank() over(partition by 课程号 order by 成绩 desc) '排名'
from score) c
where c.排名 = 1
2、ntile()函数
ntile()函数,是在默认不对表进行任何操作之前,进行切片分组。
select shopname, sales, sale_date,
ntile() over(partition by shopname order by sales) 'cut_group'
from shopsale
先按照 shopname 分组,然后在组内按照销量升序排列,最后切片分组
3、lag() 和 lead() 函数
lag()是指向后移动,lead()是指向前移动;怎么理解这2个函数?
lag(),就相当于在Excel中,某个单元格前面插入一行时,【活动单元格下移】;
lead(),则相当于【活动单元格上移】,只不过Excel中没有这个选项罢了;这2个函数有什么用?
比如说:按照shopname分组,求各分组内,前后2天的销量之差。
select shopname, sales, sale_date,
lag(sales, 1) over(partition by shopname order by sale_date) lag_value,
lead(sales, 1) over(partition by shopname order by sale_date) lead_value
from shopsale运行结果
4、移动平均
rows 行数 preceding,表示本行以及前2行的平均。
select *,
avg() over(order by rows 行数 preceding) as cur_avg
from table
四、总结
1、窗口函数的功能:1、同时具有分组和排序的功能;
2、不减少原表的行数,经常用于组内排名;
2、注意事项:窗口函数是对where 或 group by 子句处理后的结果进行操作,所以窗口函数理论上只能写在select子句中。
3、窗口函数的使用场景:1、Top N问题
2、排名问题
3、组内比较问题(聚合函数)
4、移动平均