oracle分析函数窗口函数简单介绍
关于分析函数,有两个注意点
1> 查询(query)语句中,除了最后的ORDER BY子句,分析函数是最后处理的部分。换句话说,所有表连接,WHERE子句,GROUP by子句,HAVING子句都会在分析函数,前面处理。因此,分析函数只能出现在select中的选择列中或者ORDER BY子句中。
2>分析函数通常用于计算聚合值的,累计量,变化量,中间数,报告等
一般的分析函数语法格式为:
Function(arg1,…, argn) OVER ( [PARTITION BY <…>] [ORDER BY <…>] [<window_clause>] )
<window_clause> is like “ROW <…>” or “RANK <…>”
首先,分析函数同聚合函数有什么不同呢?对比下列查询-1和查询-2
查询-1
SELECT deptno,
COUNT(*) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30)
GROUP BY deptno;
DEPTNO DEPT_COUNT
---------------------- ----------------------
20 5
30 6
2 rows selected
查询-1返回的是部门号和部门下的员工数。GROUP BY 子句不允许非聚合函数应用的其它列出现在select选择列中,换句话说,这里select子句只能用deptno(因为用于group by)和dept_count(因为用于count函数)
查询-2
SELECT empno, deptno,
COUNT(*) OVER (PARTITION BY
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);
EMPNO DEPTNO DEPT_COUNT
---------- ---------- ----------
7369 20 5
7566 20 5
7788 20 5
7902 20 5
7876 20 5
7499 30 6
7900 30 6
7844 30 6
7698 30 6
7654 30 6
7521 30 6
11 rows selected.
查询-2,即是分析函数。对比查询-1,可见,分析函数,聚合函数,重要不同点,分析函数不归集结果集,它只为每条记录返回聚合值,因此,其它非聚合的列,也可以出现在select选择列中,这里比如empno列。
如果没有PARTITION OVER(<window_clause> ) 内容,分析函数将处理where子句返回的全部行。对比下列查询-3和查询-4
查询-3
SELECT empno, deptno,
COUNT(*) OVER ( ) CNT
FROM emp
WHERE deptno IN (10, 20)
ORDER BY 2, 1;
EMPNO DEPTNO CNT
---------- ---------- ----------
7782 10 8
7839 10 8
7934 10 8
7369 20 8
7566 20