oracle count over partition by,over （Partition by...） of oracle

旧城等待， 2023-01-18 15:22 110阅读 0赞

over()分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

例子：

select \*from (select name,class,score,rank() over(partition by class order by score desc) mm from t2 ) where mm=1

通过class班级进行分组，并根据score分数进行排序，用rank()函数排序方法为mm列赋予序号，然后mm=1就可以找到每组的第一名，当然可以根据score就行倒序可以找到最后一名。

row\_number() over(partition by ... order by ...)

简单的说row\_number()从1开始，为每一条分组记录返回一个数字， row\_number() over(order by score desc)是先把score 列降序，再为降序以后的没条xlh记录返回一个序号。(如果没有分组可以理解成将整个结果作为一个分组)

row\_number() over(partition by class order by score desc)表示根据class分组，在分组内部根据 score 排序，而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

rank() over(partition by ... order by ...)

dense\_rank() over(partition by ... order by ...)

作为分数函数中有关排序的rank(),dense\_rank(),row\_number()。

rank() over是的作用是查出指定条件后进行一个排名，但是有一个特点。假如是对学生排名，那么实用这个函数，成绩相同的两名是并列(名次为1,2,2,4)

dense\_rank()的作用和rank()很像，唯一区别就是，相同成绩并列以后，下一位同学并不空出并列所占的名次(名次为1,2,2,3)

row\_number()就不一样了，它和上面两种的区别就很明显了，这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名。

对于多表查询，可以为空置加上一个判断来显示查询数据为空的数据。

case when score is null then 1 else rank() over (partition by class order by score desc ) end as mm

其他常用的分析函数:

count() over(partition by ... order by ...) max() over(partition by ... order by ...) min() over(partition by ... order by ...) sum() over(partition by ... order by ...) avg() over(partition by ... order by ...) first\_value() over(partition by ... order by ...) last\_value() over(partition by ... order by ...) lag() over(partition by ... order by ...) lead() over(partition by ... order by ...)