1,给出开窗函数的范围查询相关示例
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between (unbounded,无边界)(数字 --当前行开始选上面几行,不包括当前行) preceding and current row(当前行),数字---当前行选下面几行) as c1
from employee;
2,简述一下CUME_DIST的使用
1,如果按升序排列,则统计:小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows)。如果是降序排列,则统计:大于等于当前值的行数/总行数。
2,应用场景:查询小于(或者大于)等于当前薪资(salary)的比例
3,简述一下PERCENT_RANK的使用
1,每行按照公式(rank-1) / (rows-1)进行计算。其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数
4,简述一下前后函数
1,返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))的expr的值 如果没有那么
记忆:lag为当前行的前n行,即以当前行为参考点,lag有落后的意思,即当前行为落后,所以取前几行;lead有前面的意思,即以当前行为参考点,当前行为在前,即取当前行的后几行,如果没有那么就取null
2,应用还是很广泛的,例如查询前1名同学的成绩和当前同学成绩的差值
5,简述一下头尾函数
1,返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr))expr的值
2,应用场景:虽然不太常用,但有个例子可以用到:截止到当前,按照日期排序查询第1个入职和最后1个入职员工的薪资
,要注意指定order by,不然排序会很混乱
6,简述一下其他函数NTH_VALUE(expr,n),NTILE(n)
1,NTH_VALUE(expr,n)这个函数就更少用到了,他的用途是返回窗口中第n个expr的值。expr可以是表达式,也可以是列名,可以应用到截止到当前薪资,显示每个员工的薪资中排名第2或者第3的薪资
2,NTILE(n)这个函数的用途是将分区中的有序数据分为n个等级,记录等级数,可以应用在将每个部门员工分成3组并按照入职日期排序,例如部门有6个人,分成三组,那么按入职日期来排则排成 112233
7,总结一下窗口函数
概述:
窗口函数指的是 over()函数, 它相当于给表新增一列, 至于新增的内容是什么, 取决于你的 窗口函数 结合了 什么函数一起使用.
问题: 能和窗口函数一起使用的函数有哪些呢?
答案:
情境1: 窗口函数 + 聚合函数(count, sum, max, min, avg)
情境2: 窗口函数 + 排序函数(row_number, rank, dense_rank, ntile)
情境3: 窗口函数 + 其他函数(lag, lead, first_value, last_value)
格式:
可以结合窗口函数一起使用的函数 over(partition by 分组的列 order by 排序列 rows between 起始行 and 结束行)
细节:
1. 窗口函数是MySQL8.0的特性, 你的MySQL必须是8.X及其以上版本才可以使用.
2. 窗口函数相当于给表新增一列, 至于加的内容是什么, 取决于 结合了什么函数一起使用.
3. 如果over()里边什么都不写, 默认操作的是: 表中 该列所有的数据.
4. 如果写了partition by(表示分组): 则默认操作 组内所有的数据.
5. 如果写了order by(表示排序): 则默认操作 组内第一行 至 当前行的数据.
6. 如果要执行操作数据的范围, 即: 从哪一行开始, 到哪一行结束, 可以使用 rows between 起始行 and 结束行, 它主要涉及的参数如下:
unbound preceding 表示第1行
n preceding n表示数字, 表示向上n行, 例如: 3 preceding 表示 向上 3行.
current row 表示 当前行
n following n表示数字, 表示向下n行, 例如: 3 following 表示 向下 3行.
unbound following 表示最后1行