Oracle分析函数的使用

序言

           有时候我们需要返回一条记录,该记录中需要获取多个记录比如:指定记录的排序,指定分组的sum值等. 这样一条sql肯定不能满足的.就需要嵌套sql来计算.

        相对于聚合函数,数据库中也提供能了分析函数,分析函数就是用于解决这种问题的.

select * from t1;

Oracle-分析函数之连续求和 

该给是如下所示:    sum(…) over(…)

1. sum(…) over( )，对所有行求和
2. sum(…) over( order by … )， 连续求和
3. sum(…) over( partition by… )，同组内所行求和
4. sum(…) over( partition by… order by … )，同第1点中的排序求和原理，只是范围限制在组内

sum(…) over( )

这个相当于把 sum(字段) 放到了一个字段里,并且返会所有记录,不像group by 那样只能返回一组的统计记录,或者所有的记录统计.

select sales_id, sales, dest, dept, revenue, sum(revenue) over() sum_order from test;

sum(…) over( order by … )， 连续求和

这样的话就是 根据order by 排序.并且因为 revenue 如果值相同的话,那就是有多个记录排序是一样的,所以 排序一样的记录的sum值是一样的.

最后一个记录的就是所有记录的综合.只是相同排序记录的sum值一样,但是数据也是相同排序值的和 加上上一个排序的和.

select sales_id, sales, dest, dept, revenue, sum(revenue) over(order by revenue) sum_order from test;

sum(…) over( partition by… )

partition by 相当于group by了 ,sum只统计 组内的值.  这个对比上面的只有order by 的时候 就是sum所有的,只是排名一样的多个记录的sum一样.

 select sales_id, sales, dest, dept, revenue, sum(revenue) over(partition by  sales_id) sum_order from test;

sum(…) over( partition by… order by … )

这个就是 先分组 ,然后在根据order by 后的字段给分组排序

select sales_id, sales, dest, dept, revenue, sum(revenue) over(partition by  sales_id order by revenue) sum_order from test;

Oracle-分析函数之排序值

这个rank() 也是结合over()函数使用.跟上面的是一样的.

1. rank(): 是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）   
2. dense_rank():是连续排序，有两个第二名时仍然跟着第三名

rank()

如下的实例中是在分组内排序,也可以不在分组中排序.

如果有并列的第三名,这后面是第五名,因为有2个第三名

SELECT a.col1,a.col2,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM xgj a;

dense_rank()

如果有相同的排名则并列 第3,后面接着第四.