分析函数

SELECT ename, deptno, sal, SUM(sal) OVER (ORDER BY deptno,ename) 总计,
SUM(sal) OVER (PARTITION BY deptno ORDER BY ename) 分部门总计,
ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) 序列
FROM scott.emp 
WHERE deptno IN (10,20)
ORDER BY deptno,ename;

• SUM和OVER时分析函数语句，SUM时一个分析函数，尽管这个函数与分组函数中的SUM同名，但是在与OVER关键字连用时，SUM被标识为分析函数
• ORDER BY 时可选关键字，有些函数需要，有些不需要，依赖于函数是否许哟啊排序的结果
• PARTITION BY 时可选的分区子句，如果不存在任何分区子句，则全部的结果集可以看作是一个单一的大的分区。使用SUM进行运行总计计算时，没切换一个不同的部门，将从0开始计算
• ROW_NUMBER()函数时根据排序标准，返回每个组的行编号，ROW_NUMBER()返回的行号根据分区与排序的不同而不同。

分析函数名

• 等级（Randking）函数：用于寻找前N种查询

select o.deptno, o.job,sum(o.sal) 职级,
RANK() OVER(ORDER BY SUM(o.sal) DESC) 薪资,
DENSE_RANK() OVER(ORDER BY SUM(o.sal) DESC) DENSE排名,
ROW_NUMBER() OVER(PARTITION BY o.deptno ORDER BY SUM(o.sal) DESC) 分组行号,
SUM(SUM(o.sal)) OVER(PARTITION BY o.deptno ORDER BY o.job) 部门薪资总计
FROM SCOTT.EMP O
WHERE deptno IN (10,20,30) AND JOB IS NOT NULL
GROUP BY o.dePtno,o.JOB;

• 开窗（windowing）函数：用来计算累计值，这些函数与分组函数同名
• 制表（Reporting）函数：与开窗函数相似，制表函数也允许对一个结果执行多种聚合运算
• LAG和LEAD函数：允许在结果集中向前或向后检索值
• 其他统计函数：VAR_POP、VAR_SAMP和STDEV_POP及线性的衰减函数等

分区子句

SELECT deptno, empno,ename, 
	ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) 分析行号
FROM scott.emp WHERE deptno IN (10,20,30);

排序子句

SELECT ename, sal, AVG(sal) OVER(ORDER BY ename)
FROM scott.emp WHERE ROWNUM <=3;

COUNT 函数

SELECT empno,ename, COUNT(*) OVER (PARTITION BY deptno ORDER BY empno) 条数小计
FROM scott.emp;

SELECT empno, ename, sal, COUNT(*) OVER (ORDER BY sal RANGE BETWEEN
											 50 PRECEDING AND 150 FOLLOWING) 薪水差异个数
FROM scott.emp;

SELECT deptno,
	MIN(sal) KEEP(DENSE_RANK FIRST ORDER BY comm) MIN,
	MAX(sal) KEEP(DENSE_RANK LAST ORDER BY comm) MAX
FROM scott.emp WHERE deptno IN (10,20,30)
GROUP BY deptno;

SELECT deptno, ename, sal, 
	FIRST_VALUE(sal) OVER(PARTITION BY deptno order by empno) first,
	LAST_VALUE(sal) OVER(PARTITION BY deptno order by empno)  last
FROM scott.emp WHERE deptno IN (10,20);

LAG和LEAD函数
LAG返回指定列前n1行的值，LEAD函数与此相反，返回指定列后n行的值

SELECT ename, hiredate,sal,deptno,
	LAG(sal,1,0) OVER(ORDER BY hiredate) AS before,
	LEAD(sal,1,0) OVER(ORDER BY hiredate) AS after 
FROM scott.emp WHERE deptno = 30;

记录排名

SELECT deptno, epmno, ename, SUM(sal) dept_sales,
	RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal)) 
	DESC NULLS LAST) 跳号,
	DENSE_RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal)) 
	DESC NULLS LAST) 同级同号,
	ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY SUM(sal))
	DESC NULLS LAST) 不跳号
FROM scott.emp 
GROUP BY deptno, empno, ename;

首尾记录查询

SELECT MIN(empno) KEEP (DENSE_RANK FIRST
		ORDER BY SUM(sal) DESC NULLS LAST) FIRST,
	   MIN(empno) KEEP (DENSE_RANK LAST
	   	ORDER BY SUM(sal) DESC NULLS LAST) LAST
FROM scott.emp 
GROUP BY deptno, empno, ename;

前后排名查询

SELECT deptno, empno, ename,dept_sales,排名 FROM (
SELECT deptno, empno, ename, SUM(sal) dept_sales,
		RANK() OVER (PARTITION BY deptno ORDER BY SUM(sal) DESC NULLS LAST) 排名
FROM scott.emp WHERE deptno is not null
GROUP BY deptno, empno, ename)
WHERE 排名 <= 2;

层次查询
指按照比率得出结果数

SELECT * FROM
(SELECT DEPTNO, EMPNO, ENAME, sum(SAL) dept_sales,
	NTILE(3) OVER (PARTITION BY DEPTNO ORDER BY SUM(SAL) NULLS LAST) RANK_RATIO
FROM scott.emp WHERE deptno IS NOT NULL 
GROUP BY deptno, empno,ename)
WHERE RANK_RATIO = 1;

范围统计查询

SELECT EMPNO, ENAME, HIREDATE, SAL, 
	MAX(SAL) OVER(ORDER BY HIREDATE ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) 前10天,
	MAX(SAL) OVER(ORDER BY HIREDATE ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING) 后10天
FROM scott.emp 
WHERE deptno IN (10,20,30) AND sal IS NOT NULL;

在查询与剧中，使用ROWS BETWEEN 10 PRECEDING AND CURRENT ROW 这样的语句滚动到当前行的hiredate前10条到当前记录，使用MAX（sal）统计这个时间段内最大薪资员工；然后使用ROWSBETWEEN CURRENT ROW AND 10 FOLLOWING 从当前行开始统计后10天的员工最大薪资
相邻记录比较

SELECT ENAME, HIREDATE, DEPTNO, SAL, SAL - PREV_SAL  与前面的差异,
	sal - next_sal 与后边的差异
	FROM (SELECT ENAME, HIREDATE, SAL, DEPTNO,
		LAG(SAL,1,0) OVER(ORDER BY HIREDATE) AS PREV_SAL,
		LEAD(SAL,1,0) OVER (ORDER BY HIREDATE) AS next_sal
		FROM Scott.emp 
		WHERE deptno IS NOT NULL AND SAL IS NOT NULL);

在查询语句中，将分析函数的查询写在一个内联视图中，内联视图使用LAG和LEAD函数取按hiredate排名的员工记录的前一个和后一个。可以看到在查询的滚动过程中，分析函数在不断地分析当前行的前一行记录与后一行记录。外层查询利用所取得的前一条与后一条记录的薪资值与当前记录薪资进行比较，得到结果
抑制重复

SELECT * FROM (SELECT EMPNO, ENAME, SAL, HIREDATE, 
					ROW_NUMBER() OVER (PARTITION BY EXTRACT 
								(YEAR FROM HIREDATE) ORDER BY EMPNO) RN
FROM SCOTT.EMP
WHERE HIREDATE IS NOT NULL
AND EXTRACT (YEAR FROM HIREDATE) IN (1981,1982, 1983))
WHERE RN = 1;

查询语句将分析查询的结果作为一个内联查询，在使用ROW_NUMBER函数后，按hiredate年份进行分区，相同分区下的其它记录会依序递增。在外层查询中，通过判断分析函数返回的rn结果等于1来只取第一条记录
行列转换查询

select job, empno, ename,
		ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY ENAME) RN 
FROM SCOTT.EMP 
WHERE JOB IS NOT NULL;

SELECT MAX (DECODE (JOB, '职员', ename, NULL))  职员,
	   MAX (DECODE (JOB, '分析', ename, NULL))	分析,
	   MAX (DECODE (JOB, '经理', ename，NULL))	经理,
	   MAX (DECODE (JOB, '老板', ename, NULL))	老板,
	   MAX (DECODE (JOB, '销售', ename, NULL))	销售
FROM (SELECT JOB, EMPNO, ENAME, 
		ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY ENAME) RN
	  FROM SCOTT.EMP 
	  WHERE JOB IS NOT NULL ) X 
GROUP BY RN;