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/82710477

    5. 应用举例

    —所有科目，成绩排名学生基本信息
    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;