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()函数:
5、巧妙使用over()函数:
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(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),
最后对全表进行汇总操作。