分析函数

传统SQL的问题
虽然利用SQL之中提供的各种查询命令可以完成大部分的查询要求，但是还有 许多功能是无法实现的，例如： 计算运行总量：逐一累加当前行与其之前行的每行记录数据； 查找当前行数据占总数据的百分比； 分区显示：按照不同的部门或职位进行排列、统计； 计算流动数据行的平均值等。

分析函数的基本语法
基本语法： 函数名称([参数 , …]) OVER ( PARTITION BY 子句 字段 , … [ORDER BY 子句 字段 , … [ASC | DESC] [NULLS FIRST | NULLS LAST] [WINDOWING 子句]) ; 本语法组成如下： 函数名称：类似于统计函数（COUNT()、SUM()等），但是在此时有了更多的函数支持； OVER子句：为分析函数指明一个查询结果集，此语句在SELECT子句之中使用； PARTITION BY子句：将一个简单的结果集分为N组（或称为分区），而后按照不同的组对数据进行统 计； ORDER BY 子句：明确指明数据在每个组内的排列顺序，分析函数的结果与排列顺序有关； NULLS FIRST | NULLS LAST：表示返回数据行中包含NULL值是出现在排序序列前还是尾； WINDOWING 子句（代名词）：给出在定义变化的固定的数据窗口方法，分析函数将对此数据进行操 作。

组合顺序
在分析函数之中存在有三种子句：PARTITION BY、ORDER BY、WINDOWING，而这三种子句的组合 顺序有如下几种： 第一种组合：函数名称([参数 ,…]) OVER(PARTITION BY 子句 , ORDER BY 子句 , WINDOWING子 句)； 第二种组合：函数名称([参数 ,…]) OVER(PARTITION BY 子句 , ORDER BY 子句)； 第三种组合：函数名称([参数 ,…]) OVER(PARTITION BY 子句)； 第四种组合：函数名称([参数 ,…]) OVER(ORDER BY 子句 , WINDOWING子句)； 第五种组合：函数名称([参数 ,…]) OVER(ORDER BY 子句)； 第六种组合：函数名称([参数 ,…]) OVER()；

范例
使用PARTITION子句
SELECT deptno , ename, sal ,
SUM(sal) OVER (PARTITION BY deptno) sum
FROM emp ;

范例 不使用PARTITION进行分区，直接利用OVER子句操作
SELECT deptno , ename, sal ,
SUM(sal) OVER () sum
FROM emp ;

范例
观察ORDER BY子句（用于设置在每个分区内数据的排序结果）
SELECT deptno , ename, sal ,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rk
FROM emp ;

范例
直接利用ORDER BY排序所有数据 ，这里是没对部门进行分组，看看会有什么变化
SELECT deptno , ename, sal , hiredate ,
SUM(sal) OVER (ORDER BY ename DESC) SUM
FROM emp ;

只要不按照部门排序，就是这种输出结果，如果用部门排序，就和对部门分组一样

在ORDER BY子句之中还存在两个选项：NULLS FIRST和NULLS LAST。 其中NULLS FIRST表示在进行排序前，出现null值的数据行排列在最前面， 而NULLS LAST则表示出现的null值数据行排列在最后面

范例
使用NULLS LAST
SELECT deptno , ename, sal , comm ,
RANK() OVER (ORDER BY comm DESC NULLS LAST) rk ,
SUM(sal) OVER (ORDER BY comm DESC NULLS LAST) SUM
FROM emp ;

数据统计函数

NO.	函数名称	描述
01	SUM（）	计算分区中数据累加和
02	MIN()	查找分区中最小值
03	MAX()	查找分区中最大值
04	AVG()	计算分区中数据平均值
05	COUNT()	计算分区中数据量

范例
查询雇员编号是7369的雇员姓名、职位、基本工资、部门编号、部门的人数、 平均工资、最高工资、最低工资、总工资

等级函数

NO.	函数名称	描述
1	RANK()	根据order by字句排序，按照排序生成序号，会有重复值，并自动跳好
2	DENSE_RANK()	根据order by字句排序，按照排序生成序号，会有重复值，但不会跳好
3	FIRST	取出DENSE_RANK返回的第一行数据
4	LAST	取出DENSE_RANK返回的最后一行数据
5	FIRST（列）	返回分区（分组）第一个值
6	LAST_VALUE(列)	返回分区（分组）最后一个值
7	LAG(列名称，行数字，默认值)	访问分区（分组）指定前N行记录，没有返回默认值
8	LAG(列名称，行数字，默认值)	访问分区（分组）指定后N行记录，没有返回默认值
9	ROW_NUMBER()	返回每组中的行号

观察RANK()和DENSE_RANK()函数
SELECT deptno,ename,sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal) rank_result ,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) dense_rank_result
FROM emp ;

范例
使用ROW_NUMBER()函数
SELECT deptno,ename,sal,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) row_result_deptno , ROW_NUMBER() OVER (ORDER BY sal) row_result_all
FROM emp ;

范例
计算各部门工资所占的总工资比率
SELECT deptno ,SUM(sal) ,
ROUND(RATIO_TO_REPORT(SUM(sal)) OVER () ,5) rate , ROUND(RATIO_TO_REPORT(SUM(sal)) OVER () ,5) * 100 || ‘%’ precent
FROM emp GROUP BY deptno;