Oracle分析函数

Oracle的分析函数入门:

  1、什么是分析函数：

分析函数是Oracle专门用于解决复杂报表的功能强大的函数，它可以在数据中进行分组和排序后返回数据，并且与group by 不同的地方是，它的每一组的每一行都能返回一条数据。

  2、如何使用分析函数：

       分析函数都带有一个开窗函数over(),包含三个分析子句：partition by、order by 、rows between  ... and ...

  3、示例：

显示各部门员工的工资，以及该部门的最高工资SELECT EMPNO,  ENAME,  DEPTNO,  SAL,  LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)FROM EMP;

 LAS_VALUE和FIRST_VALUE分别表示分组函数返回数据中的最后一条和第一条

 UNBOUNDED PRECEDING 表示第一条数据开始

 UNBOUNDED FOLLOWING 表示到最后一条数据结束

 CURRENT ROW 表示到当前数据就结束

 这里特别需要注意的是:LAST_VALUE 默认查找范围是 UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

4、深入理解over()函数：

   窗口函数就是分析函数处理数据时的范围，比如sum，那么该函数对应的数据就是该窗口的数据，而不是整个结果的数据，所以我们还可以通过rows来指定开窗函数的数据范围。order by执行的时机，开窗函数是在整个sql语句执行完毕之后在对数据进行处理，所以开窗函数里的排序在sql语句排序后再进行排序

5、巧妙使用over()函数:

  如果over()函数中使用了order by，那么如果没有指定数据的范围，默认是第一条数据到当前数据，即unbounded preceding and current row

  现在假设有这么一个需求，有张存有学生成绩的表，现在要统计每个分数以上的人数，那么我们就可以使用over(order by )

SELECT T.S,  SUM(CC) OVER(ORDER BY T.S DESC)FROM  ( SELECT SCORE S,COUNT(*) CC FROM TEST GROUP BY SCORE) T

6、常用配合over()的排序函数:

  rank，dense_rank，row_number函数为每条记录产生一个从1开始至n的自然数，n的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据  时的排名策略。
  ①row_number： 
row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
  ②dense_rank： 
dense_rank函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的。
  ③rank： 
rank函数返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

7、lag()与lead()：

   lag和lead函数可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。
   lag(arg1,arg2,arg3)
   第一个参数是列名，
   第二个参数是偏移的offset，
   第三个参数是超出记录窗口时的默认值。

8、rollup()与cube()

   1)、group by rollup(a, b, c)：
   首先会对(a、b、c)进行group by，
   然后再对(a、b)进行group by，
   其后再对(a)进行group by，
   最后对全表进行汇总操作。

     

   2)、group by cube(a, b, c)：
   则首先会对(a、b、c)进行group by，
   然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，
   最后对全表进行汇总操作。