1.什么是开窗函数
1.1 开窗函数/分析函数:over()
窗口函数称为OLAP函数(分析函数),是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。
1.2 开窗函数语法
开窗函数语法:其中[ ]中的内容可以省略
<开窗函数> over ([partition by <列清单>] -- 用于分z
order by <排序用列清单>)
注:PARTITION BY与GROUP BY区别
group by是分组函数,partition by是分析函数
在执行顺序上:from > where > group by > having > order by,而partition by应用在以上关键字之后,可以简单理解为就是在执行完select之后,在所得结果集之上进行partition by分组
partition by相比较于group by,能够在保留全部数据的基础上,只对其中某些字段做分组排序(类似excel中的操作),而group by则只保留参与分组的字段和聚合函数的结果(类似excel中的pivot透视表)
PARTITION BY的基本用法
在OVER()中添加 PARTITION BY
# 查询每种类型火车的ID,型号,一等座数量,同型号一等座数量总量
SELECT
id,
model,
first_class_places,
count(id) OVER (PARTITION BY model)
FROM train;
PARTITION BY传入多列
# 查询每天,每条线路速的最快车速
SELECT
journey.id,
journey.date,
train.model,
MAX(max_speed) OVER (PARTITION BY
route_id, date)
FROM journey
JOIN train
ON train.id = journey.train_id;
1.3 开窗函数分类
开窗函数大体可以分为以下两种:
1.能够作为开窗函数的聚合函数(sum,avg,count,max,min)
2.rank,dense_rank,row_number等专用开窗函数。
有时候一组数据只返回一组值是不能满足需求的,如我们经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。
1.4 开窗函数和聚合函数的区别
(1)SQL 标准允许将所有聚合函数用作开窗函数,用OVER 关键字区分开窗函数和聚合函数。
(2)聚合函数每组只返回一个值,开窗函数每组可返回多个值。
注:常见主流数据库目前都支持开窗函数,但mysql数据库目前还不支持。
1.5 开窗函数SQL题——分区排序
有如下学生成绩表:students_grades
查询每门课程course_name前三名的学生姓名及成绩,要求输出列格式如下:
course_name, number, stu_name, grades
查询语句如下:
select *
from
(select course_name,
rank_number() over(PARTITION by course_name ORDER BY grades desc) number,
stu_name,
grades
from students_grades
) as a
where a.number<=3
2.常用排序函数的区别
(1) row_number() over():对相等的值不进行区分,相等的值对应的排名不相同,序号从1到n连续。
(2) rank() over():相等的值排名相同,但若有相等的值,则序号从1到n不连续。如果有两个人都排在第3名,则没有第4名。
(3) dense_rank() over():对相等的值排名相同,但序号从1到n连续。如果有两个人都排在第一名,则排在第2名(假设仅有1个第二名)的人是第3个人。
(4) ntile( n ) over():可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行,排序对应的数字为桶号。如果不能平均分配,则较小桶号的桶分配额外的行,并且各个桶中能放的数据条数最多相差1。
2.1 排序函数对比
学生成绩表同上,查询语句如下:
select id,stu_name,course_name,grades,
row_number() over(order by grades) as row_num,
rank() over(order by grades) as rank,
dese_rank() over(order by grades) as dese_rank,
ntile(5) over(order by grades) as ntile
from students_grades
查询结果如下: