oracle分析函数
Oracle分析函数与聚合函数的区别:
分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。
几个排名函数的语法和用法:
rank ( ) over ([partition by col] order by col )
dense_rank ( ) over ([partition by col] order by col )
rownumber ( ) over ( [partition by col] order by col )
rank() 是排名的函数,该函数组内排序后会进行跳号,分数相同的作为并列。
dense_rank() 该函数不会跳号,分数相同为并列第一,下一个是第二。
row_number() 可以通过over 根据某字段排序完之后进行组内(如果有partition by)排序。
例:
with scores as (
select regexp_substr(‘110,30,130,80,80,105,234,89,22,66’,’[^,]+’,1,level) score
from dual connect by level <= 10
)
select score,
rank()over (order by to_number(score) desc) “rank”,
dense_rank()over (order by to_number(score) desc) “dense_rank” ,
row_number()over (order by to_number(score) desc) “row_number”
from scores