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;