开窗函数具体介绍:
https://zhuanlan.zhihu.com/p/92654574 (已读)
https://www.cnblogs.com/lihaoyang/p/6756956.html
distribute/partition/sort by:
https://blog.csdn.net/qq_16320025/article/details/102976995
窗口函数的基本语法如下:
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>
rows between m preceding and n following <开窗(!用于聚合函数!)范围从m行之前到n行后>)
over是新建了列,partition是根据属性分区(所以可以不要)order是对新的序列排序(所以也可以不要)
简单来说,窗口函数有以下功能:
1)同时具有分组和排序的功能
2)不减少原表的行数
<窗口函数>的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
三个函数区别如下图:
注意:用开窗函数新建的列不能用having筛选
2) 聚合函数,如sum. avg, count, max, min等
不仅是sum求和,平均、计数、最大最小值,也是同理,都是针对自身记录、以及自身记录之上的所有数据进行计算,现在再结合刚才得到的结果
最近刷牛客网的题刷到了开窗函数的应用,系统学习一下:
题目:现在运营想要找到每个学校gpa最低的同学来做调研,请你取出每个学校的最低gpa。
示例:user_profile
根据示例,你的查询结果应参考以下格式:
这道题有个思路:一是用开窗函数标号后取最小的号,二是用自查询找到最小的gpa然后连结表格找到最小gpa同学的id
第一种方法:
select
a.device_id,
a.university,
a.gpa
from(
select
device_id,
university,
gpa,
ROW_NUMBER() over (PARTITION BY university ORDER BY gpa) as gpaorder
FROM user_profile
) a
where a.gpaorder = 1
开窗函数的另一种方法(不标号,匹配最小gpa)
SELECT
device_id,
university,
gpa
FROM(
SELECT
device_id,
university,
gpa,
min(gpa) over (PARTITION BY university ) as newgpa
FROM user_profile
) a
WHERE gpa = newgpa
第二种方法:
select
a.device_id,
b.university,
b.gpa
from user_profile a
inner join
(SELECT
university,
min(gpa) as gpa
FROM user_profile
GROUP BY university
) b
ON a.gpa = b.gpa and a.university = b.university
ORDER BY b.university```