华大基因 网站建设公司,宁波seo外包平台,wordpress指定id文章,2016网站设计欣赏在面试过程中窗口函数的应用可谓是数不胜数#xff0c;前提你要知道什么是窗口函数#xff0c;最常用的窗口函数有哪些#xff1f;语法是什么#xff1f;分别用的场景是什么#xff1f;今天会以这三个问题开始我们今天的学习
什么是窗口函数#xff1f; 所谓的窗口函数就… 在面试过程中窗口函数的应用可谓是数不胜数前提你要知道什么是窗口函数最常用的窗口函数有哪些语法是什么分别用的场景是什么今天会以这三个问题开始我们今天的学习
什么是窗口函数 所谓的窗口函数就是动态处理数据的一类函数也叫做“OLAP”函数可以对数据库数据进行实时分析处理“窗口函数”由两部分组成分析函数窗口函数
--模板
函数名(字段名) over(partition by xxx,yyy order by zzz)--详细介绍
分析函数 over (partition by 要分组的列)order by 要排序的列rows between 开始的行 and 结束的行) over部分才是“窗口函数”只不过因为它的特点大家都这样叫其作用就是划定窗口分析函数的作用是在窗口内对记录进行统计
over函数中有3个参数
partition by 其表示的是分区或者分组可以理解成为group by,但是低位有所不同如果partition by 和group by 后的字段相同partition by 就会失效因为group by先分组划分窗口如果字段相同则partition by 就没有可分的了
如果partition函数想要做到像group by一样的效果必须要和distinct关键字进行去重
order by 表示排序排序、累加都是有排序的操作在里面根据设么条件排名、根据什么条件进行累加order by后面可以跟一个字段也可以跟多个字段位置优先越在前面排序的优先级越高默认“asc(升序)” 小-大desc(降序)大-小
rows between 开始的行 and 结束的行
这部分是控制窗口大小的地方一般默认就行也不会进行书写但是也可以自定义窗口大小
proceding:往前
following:往后
unbounded:无穷尽的
current row:当前行
unbounded proceding:往前所有行
unbounded following:往后所有行 举几个例子方便理解
rows between 2 preceding and current row --取当前行和前2行这个可用于求移动平均
rows between current row and 2 following --取当前行和后2行这个可用于求移动平均
rows between unbounded preceding and current row --取当前行和之前所有行这个可用于求累加如果没有写rows between参数则默认的窗口就是这个范围
rows between current row and unbounded following --取当前行和之后所有行
rows between 2 preceding and 2 following --取当前行的前面2行和下面2行总共5行这个可用于求移动平均
动态窗口的动态演示图 接下来我们来讲一般常用的三种窗口函数
我们现在有一个班级的成绩数据要求按规定进行查询
CREATE TABLE class(
cid VARCHAR(10),
NAME VARCHAR(20),
grades INT
)
DROP TABLE classINSERT INTO class (cid,NAME,grades) VALUES(1,路飞,60),(1,鸣人,70),(1,佐助,75),(1,萧炎,80),(1,唐三,90),(1,奕星,91),(2,霸天虎,50),(2,玉环,60),(2,索隆,66),(2,墨子,85),(2,铁甲小宝,89) 假如动漫学院期末成绩出来了老师让你帮根据一定的规则去查询成绩你可以帮忙完成这个工作么
聚合类的窗口函数 sum() over()举例 count\avg\max\min
情境一现在要求让每位同学后面都新增一列各自班级的总分
SELECT*,SUM(grades) OVER (PARTITION BY cid) AS 班级总分
FROM class 情境二:计算同一个班级内每个同学和比他分数低的总分是多少
SELECT*,SUM(grades) OVER (PARTITION BY cid ORDER BY grades) AS 累加分数
FROM class SELECT*,SUM(grades) OVER (PARTITION BY cid ORDER BY grades ROWS BETWEEN unbounded preceding AND current ROW ) AS 累加分数
FROM class 这条SQL语句的效果和上面那条SQL语句效果一致不写的话默认就是这个范围 排序类的窗口函数
row_number(顺序相同的不做特别处理) 假如现在让你将1、2班的成绩排个名从低到高你有没有什么好的办法| SELECT *,ROW_NUMBER() OVER (PARTITION BY cid ORDER BY grades DESC) AS 成绩排名
FROM class; rank相同的做特别处理会进行占位 在拍完名之后发现相同成绩的同学居然排名不一样这又让老师犯难了怎么样才能使得相同成绩的同学拥有相同的排名 SELECT *,RANK() OVER (PARTITION BY cid ORDER BY grades DESC) AS 成绩排名
FROM class; dense_rank相同的做特殊处理不占位 但是又新出现了一个问题相同的成绩的排名确实排名也相同了但是后面的那个同学居然是从3开始的自动的将2跳过了这也是我们不希望看到的 SELECT *,DENSE_RANK() OVER (PARTITION BY cid ORDER BY grades DESC) AS 成绩排名
FROM class;
偏移类的、跨行的窗口函数
lag(后面) 如果现在又有一个任务让每位同学知道比自己低1N名的同学的成绩 SELECT*,
LAG(grades,1) OVER (PARTITION BY cid ORDER BY grades) AS 低一名的成绩
FROM class 但是最后的结果中却出现了null值我们想如果查不到数据结果置为0该怎么去实现呢 LAG(字段差值默认值) SELECT*,
LAG(grades,1,0) OVER (PARTITION BY cid ORDER BY grades) AS 低一名的成绩
FROM class 2.lead(前面) 如果现在又有一个任务让每位同学知道比自己高1N名的同学的成绩 SELECT*,
LEAD(grades,1,0) OVER (PARTITION BY cid ORDER BY grades) AS 高一名的成绩
FROM class 最常用的窗口函数已经介绍完了学习SQL的路上一路有我
