oracle的聚合函数是在group by分组之后执行的,所以聚合函数里的分组,比如partition by是在完成整个select之后的结果集里执行的,换句话说group by不会影响聚合函数里的partition by分组;聚合函数可以使用group by的分组作为聚合的依据,也可以通过partition by自己定义分组,再执行聚合操作
窗口函数与聚合函数的区别
聚合函数通常只返回聚合的结果以及聚合的关键字段,如果需要返回聚合的列以外的列的值,那么就需要用到开窗函数。与聚合函数不同的是,开窗函数会将聚合的结果合并到原本的结果集中
使用场景:例如 一个表Table1中存在 列 KEY,VALUE,DATE
KEY(学生) | VALUE (成绩) | DATE(日期) |
---|---|---|
张三 | 89 | 2021/01/02 |
张三 | 72 | 2021/11/12 |
李四 | 63 | 2021/10/12 |
李四 | 91 | 2021/11/14 |
某个业务需要根据学生汇总,去除重复项,只取最新日期的成绩
Group BY语句:
SELECT A.KEY,A.VALUE,A.DATE FROM TABLE1 A
(SELECT MAX(DATE),KEY FROM TABLE1 GROUP BY KEY) B
WHERE A.KEY = B.KEY AND A.DATE = B.DATE
GROUP BY结果:
KEY(学生) | VALUE (成绩) | DATE(日期) |
---|---|---|
张三 | 72 | 2021/11/12 |
李四 | 91 | 2021/11/14 |
使用开窗函数FIRST_VALUE取按KEY聚合后,ORDER 排序的第一项FIRST_VALUE(A.VALUE)OVER( PARTITION BY A.KEY ORDER BY A.DATE DESC)
SELECT KEY,FIRST_VALUE(A.VALUE)OVER(
PARTITION BY A.KEY ORDER BY A.DATE DESC) AS NVALUE,VALUE,DATE FROM TABLE1 A
开窗函数结果
KEY(学生) | VALUE (成绩) | NVALUE(最新成绩) | DATE(日期) |
---|---|---|---|
张三 | 89 | 72 | 2021/01/02 |
张三 | 72 | 72 | 2021/11/12 |
李四 | 63 | 91 | 2021/10/12 |
李四 | 91 | 91 | 2021/11/14 |
再去除重复项,使用ROW_NUMBER(),将同一类数据记录,再通过筛选取出最新项ROW_NUMBER() OVER( PARTITION BY A.KEY ORDER BY A.DATE DESC)
SELECT * FROM (SELECT KEY,FIRST_VALUE(A.VALUE)OVER(
PARTITION BY A.KEY ORDER BY A.DATE DESC) AS NVALUE,ROW_NUMBER() OVER(
PARTITION BY A.KEY ORDER BY A.DATE DESC) AS
RWORDER ,VALUE,DATE FROM TABLE1 A) WHERE RWORDER =1
最终结果
KEY(学生) | VALUE (成绩) | NVALUE(最新成绩) | DATE(日期) | MM |
---|---|---|---|---|
张三 | 72 | 72 | 2021/11/12 | 1 |
李四 | 91 | 91 | 2021/11/14 | 1 |
https://blog.csdn.net/qq_41708308/article/details/89374701
https://blog.csdn.net/qq_37816503/article/details/108408875