1. 适用场景
适用于针对某个指标分类后统计汇总分析
over partition by 和 group by的区别
over partition by 可以将汇总数据和源数据在一行中显示
2. 函数说明
分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。
即由以下三部分组成:
分析函数名:如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等;
over: 关键字,表示前面的函数是分析函数,不是普通的集合函数;
分析子句:over关键字后面挂号内的内容;分析子句又由下面三部分组成:
PARTITION BY :分组子句,表示分析函数的计算范围,不同的组互不相干;
ORDER BY: 排序子句,表示分组后,组内的排序方式;
ROWS/RANGE:窗口子句,是在分组(PARTITION BY)后,组内的子分组(也称窗口),此时分析函数的计算范围窗口,而不是PARTITON。窗口有两种,ROWS和RANGE;
3. 分析函数
rank()/dense_rank over(partition by … order by …)
说明:over()在什么条件之上;
partition by 按哪个字段划分组;
order by 按哪个字段排序;
注意:
(1)使用rank()/dense_rank() 时,必须要带order by否则非法
(2)rank()/dense_rank()分级的区别:
rank(): 跳跃排序,如果有两个第一级时,接下来就是第三级。
dense_rank(): 连续排序,如果有两个第一级时,接下来仍然是第二级。
- min()/max() over(partition by …)
注:这里没有排序条件,若加上order by 排序条件,
MAX() OVER(PARTITION BY .. ORDER BY .. DESC) 排序规则只能为desc,否则不起作用,将查询到目前为止排序值最高字段的对应值
MIN() OVER(PARTITION BY .. ORDER BY .. ASC ) 排序规则只能为asc,否则不起作用,将查询到目前为止排序值最低的字段的对应值
- lead()/lag() over(partition by … order by …) 取前面/后面第n行记录
说明:
lead(列名,n,m): 当前记录后面第n行记录的<列名>的值,没有则默认值为m;如果不带参数n,m,则查找当前记录后面第一行的记录<列名>的值,没有则默认值为null。
lag(列名,n,m): 当前记录前面第n行记录的<列名>的值,没有则默认值为m;如果不带参数n,m,则查找当前记录前面第一行的记录<列名>的值,没有则默认值为null。
在实际应用当中,若要用到取今天和昨天的某字段差值时,Lag和Lead函数的应用就显得尤为重要。当然,这种操作可以用表的自连接实现,但是LAG和LEAD与left join、rightjoin等自连接相比,效率更高,SQL更简洁。
- FIRST_VALUE/LAST_VALUE() OVER(PARTITION BY …) 取首尾记录
- ROW_NUMBER() OVER(PARTITION BY.. ORDER BY ..) 排序(应用:分页)
- sum/avg/count() over(partition by ..)
4. 分析子句 ROWS和RANGE
参考https://blog.csdn.net/qq_29970875/article/details/827104775. 应用举例
—所有科目,成绩排名学生基本信息
SELECT S.S,S.SNAME,SC.C,RANK()OVER(PARTITION BY SC.C ORDER BY SC.SCORE DESC) FROM TEST_STUDENT S
LEFT JOIN TEST_SC SC ON S.S=SC.S;
— 检查 (成绩为空的也会包含进来)
—SELECT * FROM TEST_SC ORDER BY C;
—按照性别做成绩平均值排名情况 NOTE:以均值存在为参照排名,使用RIGHT JOIN
SELECT S.S,S.SNAME,S.SSEX,RANK()OVER(PARTITION BY S.SSEX ORDER BY MID.AVGSCORE DESC) FROM TEST_STUDENT S
RIGHT JOIN (SELECT SC.S,ROUND(AVG(SC.SCORE),2) AVGSCORE FROM TEST_SC SC GROUP BY SC.S)MID ON S.S=MID.S ;
— 检查
SELECT SC.S,ROUND(AVG(SC.SCORE),2) AVGSCORE FROM TEST_SC SC GROUP BY SC.S ORDER BY AVG(SC.SCORE) DESC
—各科成绩前3名
SELECT FROM (SELECT S.S,S.SNAME,SC.C,RANK()OVER(PARTITION BY SC.C ORDER BY SC.SCORE DESC) RNUM FROM TEST_STUDENT S
LEFT JOIN TEST_SC SC ON S.S=SC.S )
WHERE 1=1 AND RNUM<4;
—各科成绩后3名
SELECT FROM (SELECT S.S,S.SNAME,SC.C,RANK()OVER(PARTITION BY SC.C ORDER BY SC.SCORE ASC) RNUM FROM TEST_STUDENT S
LEFT JOIN TEST_SC SC ON S.S=SC.S )
WHERE 1=1 AND RNUM<4;
—各科成绩累计成绩百分比
SELECT S.S,S.SNAME,SC.C,SUM(SC.SCORE)OVER(PARTITION BY SC.C ORDER BY SC.SCORE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM TEST_STUDENT S
LEFT JOIN TEST_SC SC ON S.S=SC.S;