Oracle12C--分析函数（十四）

此笔记需要使用的SQL建表语句

知识点梳理

分析函数最重要的概念就是将数据进行分区，而后才可以利用分析函数针对每一区的数据进行操作。如果在定义时没有使用分区，则表示全部的结果集将成为一组

分析函数基本语法

函数名称（[参数,....]） over(
	partition by 子句 字段,.....[order by 子句 字段,....[asc | desc] [nulls | nulls last] ]
	[windowing 子句]
);

本语法组成如下：

1. 函数名称：类似于聚合函数（sum(),count()等）；
2. over子句：为分析函数指明一个查询结果集，此语句在select子句中使用；
3. partition by子句：明确指明数据在每个组内的排序顺序，分析函数的结果与排列顺序有关；
4. nulls first | nulls last：表示返回数据行中包含Null值是出现在排序序列前还是排序尾；
5. windowing子句（代名词）：给出在定义变化的固定的数据窗口方法，分析函数将对此数据进行操作；

子句的组合顺序

基本语法中存在3个子句，分别是：partition by，order by，windowing，它们的组合顺序有如下几种：

1. 第一种组合：函数名称（[参数,.....]）over(partition by 子句,order by子句,windowing子句)；
2. 第二种组合：函数名称（[参数,.....]）over(partition by 子句,order by子句);
3. 第三种组合：函数名称（[参数,.....]）over(partition by 子句);
4. 第四种组合：函数名称（[参数,.....]）over(order by 子句,windowing子句);
5. 第五种组合：函数名称（[参数,.....]）over(order by 子句);
6. 第六种组合：函数名称（[参数,.....]） over();
   

partition by 子句

示例1：使用partition by子句

该示例，over将查询结果按照deptno进行了分组，此例中sum()不是聚合函数，而是分析函数，此函数按照over的分组，对组内的字段进行统计。一定要使用over子句设置要操作的结果集；

select deptno,ename,sal,sum(sal) over (partition by deptno) sum from emp;

示例 2 ：不使用 partition by 子句进行分区，直接利用 over 子句操作

此实例over内没有使用partition by子句，所以hi将全部的数据合并为一个分区，而sum()函数所计算的结果也是将这个分区的全部数据进行统计，及全部员工的工资总和；

select deptno,ename,sal, sum(sal) over() sum from emp;

示例三：通过 partition by 子句设置多个分区字段

此示例设置了两个分区字段（deptno,job），所以程序首先会按照部门编号进行分区，在每个分区中，又会按照职位进行分区，对相同的职位进行求和，在20部门中由于存在两个职位相同的信息，所以统计的结果为6000

select deptno,ename,sal,job,     sum(sal) over(partition by deptno,job) sum from emp;

Order by 子句

Order by子句用于设置在每个分区内数据的排序结果，排序结果将直接影响分析函数的结算结果

示例1：rank()表示的是每行数据在分区内的相对位置。此例中，首先按照部门编号对数据进行分区，在分区时使用了order by子句，将每一个分区中的数据按照工资进行降序排列，如果出现了相同的工资，则使用同一个位置号进行标记

select deptno ,ename,sal,    rank() over(partition by deptno order by sal desc) rk from emp;

示例2：直接利用Order by 排序所有数据

elect deptno,ename,sal,hiredate,    sum(sal) over(order by ename desc) sum from emp;

示例 3 ：使用 nulls last

nulls first表示在进行排序前，出现null值的数据行排列在最前面，而nulls last则表示出现的null值数据行排列在最后面

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;

windowing子句

分窗子句主要用于定义一个变化或固定的数据窗口方法，以及分析函数在操作行的集合，它有两种实现方式：

1. 实现一：值域窗（range window），逻辑偏移。当前分区中当前行的前N行到当前行的记录集；
2. 实现二：行窗（rows window），物理偏移。以排序的结果顺序计算偏移当前行的起始行记录集；

如果要指定RANGE或ROWS的偏移量，可以采用如下几种排序列：

1. RANGE|ROWS 数字 PRECEDING；
2. RANGE|ROWSBETWEEN UNBOUNDED PRECEDING AND CURRENT ROW;
3. RANGE|ROWSBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING;
   

以上的几种排列中包含的概念如下：

1. PRECEDING：主要是设置一个偏移量，这个偏移量可以是用户设置的数字，或者是其他标记；
2. BETWEEN...AND：设置一个偏移量的操作范围；
   
3. UNBOUNDED PRECEDING：不限制偏移量大小；
4. CURRENT ROW：表示当前行；
5. FOLLOWING：如果不写此语句，表示使用上N行与当前行指定数据比较，如果编写此语句，表示当前行与下N行数据进行比较；

RANGE子句：

说明：该子句设置的是一个查询范围的偏移量，排序列的数值必须大于等于（或小于等于）“当前行列数值 +/-偏移量”的所有行；

示例1：在sal上设置偏移量

select deptno,ename,sal,sum(sal) over(partition by deptno order by sal range 300 preceding) sum from emp;

示例 2 ：设置偏移量为 300 ，采用以下匹配方式处理：

select deptno,ename,sal,   sum(sal) over(partition by deptno order by sal range between 0 
	preceding and 300 following) sum
from emp;

示例 3 ：匹配当前行数据，此处使用 current row 选项，表示与当前行数据相同，所以只有相同的数据才会使用 sum() 函数计算出总和；

select deptno,ename,sal,   sum(sal) over(partition by deptno order by sal range between 0 
	preceding and current row) sum
from emp;

示例 4 ：使用 unbounded 不设置边界

select deptno,ename,sal,   sum(sal) over(partition by deptno order by sal range between unbounded 
	preceding and current row) sum
from emp;

ROWS子句：

说明：ROWS子句最大的功能是设置一个当前行的起始物理偏移行，并依据此偏移量进行统计操作

示例1：设置两行物理偏移

elect deptno,ename,sal,   sum(sal) over(partition by deptno order by sal rows 2 preceding) sum from emp;

示例 2 ：设置查询行范围

select deptno,ename,sal,    sum(sal) over(partition by deptno order by sal rows between unbounded
           preceding and unbounded following) sum
from emp;

分析函数范例

数据统计包括以下函数：

SUM([DISTINCT | ALL]表达式)	计算分区（分组）中的数据累加和
MIN([DISTINCT | ALL]表达式)	查找分区（分组）中的最小值
MAX([DISTINCT | ALL]表达式)	查找分区（分组）中的最大值
AVG([DISTINCT | ALL]表达式)	计算分区（分组）中的数据平均值
COUNT(* | [DISTINCT | ALL]表达式)	计算分区（分组）中的数据量

这些函数与统计函数区别不大；

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

分析：现在的程序需要进行统计查询，在学习分析函数前，这些统计查询需要在from子句中编写子查询后才可以使用，如果有了分析函数，则可以利用partition进行数据的分区，从而取得统计结果

select *from(
	select empno,ename,job,sal,deptno,
		count(empno) over (partition by deptno) count,
		round(avg(sal) over (partition by deptno)) avg,
		sum(sal) over (partition by deptno) sum,
		max(sal) over(partition by deptno) max,
		min(sal) over (partition by deptno) min
	from emp) temp
where temp.empno=7369;

等级函数

此函数主要是为数据按照逻辑顺序或者物理顺序进行编号的操作：

rank()	根据order by子句的排序字段，从分区（分组）查询每一行数据，按照排序生成序号，会出现相同序号
dense_rank()	根据order by子句的排序字段，从分区（分组）查询每一行数据，按照排序生成序号，不会出现相同的序号
first	取出dense_rank返回集合中第一行数据
last	取出dense_rank返回集合中最后一行数据
first_value(列)	返回分区（分组）中的第一个值
last_value(列)	返回分区（分组）中的最后一个值
lag(列名称 [,行数字] [,默认值])	访问分区（分组）中指定前N行的记录，如果没有则返回默认值
lead(列名称 [,行数字] [,默认值])	访问分区（分组）中指定后N行的记录，如果没有则返回默认值
row_number()	返回每组中的行号

记录标记函数

说明：rank()与dense_rank()会根据order by子句表达式的值自动为每一行设置一个数字序号

示例1：

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_resultfrom emp;

行标记函数

能力：row_number()的功能是自动生成一个行的记录好，并且不管其内容是否重复，都可以连续编号

示例1：使用此函数为行数据自动编号

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;

取出首行或尾行数据

使用dense_rank()进行数据排列后，可以利用first取得返回结果集中的首行，或者使用Last取得返回结果集中的最后一行。如果要使用这两个函数则必须利用keep语句来完成

keep语法：该语句的作用是保留满足条件的数据，而且在使用dense_rank()函数确定了要操作的数据集合，而后才可以通过first或last取得集合中的数据

分组函数() keep (dense_rank first | last order by 表达式 [ASC | DESC] [NULLS [FIRST | LAST ] ],....) [over() 分区查询];

示例：查询每个部门的最高及最低工资

分析：要先按照部门编号进行分组，可以利用first和last来排序，取出第一个和最后一个

select deptno,
    max(sal) keep(dense_rank first order by sal desc) max_salary,
    min(sal) keep(dense_rank last order by sal desc) min_salary
from emp
group by deptno;

取出首行或尾行记录

使用over取得一个结果集时，可以利用first_value()或last_value()来取得集合中首行或者尾行的记录

示例：验证first_value()与last_value()函数

					select deptno,empno,ename,sal,     first_value(sal) over (partition by deptno order by sal
					             range between unbounded preceding and unbounded following)
					        first_result,
					    last_value(sal) over(partition by deptno order by sal
					        range between unbounded preceding and unbounded following)
					        last_result
					from emp
					where deptno=10;

比较相邻记录

lag()：取得之前所列数据行的第N行记录进行显示，如果没有，则使用默认值（不设置默认值则返回null）

lead()：与lag()相反，是取得之后所列数据行的记录；

select deptno,empno,ename,sal,
    lag(sal,2,0) over(partition by deptno order by sal )lag_result,
    lead(sal,2,0) over(partition by deptno order by sal )lead_result
from emp
where deptno=20;

报表函数

此函数可将分区中的数据进行统一的规范划分：

cume_dist()	计算一行在分区中的相对位置
ntile(数字)	将一个分区分为"表达式"的散列表示
ratio_to_report(表达式)	该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比

示例：cume_dist()会取得整个数据的相对位置，如果假设分区内的数据有5条，那cume_dist()会将这5条按照“1,0.8,0.6,0.4,0.2”进行划分，当出现相同数据时，位置号就不会连续了；

select deptno,ename,sal,
    cume_dist() over(partition by deptno order by sal) cume
from emp
where deptno in(10,20);

示例：ntile()函数对一个数据分区中的有序结果集进行划分，并为每个小组分配一个唯一的组编号

select deptno,sal,
    sum(sal) over(partition by deptno order by sal) sum_result,
     ntile(3) over(partition by deptno order by sal) ntile_result_a,
     ntile(6) over(partition by deptno order by sal) ntile_result_b
from emp;

示例： ratio_to_report() 函数可以将需要统计的数据按照整体数据的百分比进行显示

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;