传统SQL的问题
虽然利用SQL之中提供的各种查询命令可以完成大部分的查询要求,但是还有许多功能是无法实现的,例如:
计算运行总量:逐一累加当前行与其之前行的每行记录数据;
查找当前行数据占总数据的百分比;
分区显示:按照不同的部门或职位进行排列、统计;
计算流动数据行的平均值等。
分析函数就解决了大部分传统SQL的问题:
使用分析函数可以进行更为复杂的查询报表显示。
在分析函数中可以使用若干统计函数。
着重讲讲分析函数的使用
分析函数的基本语法:
函数名称([参数 , ....]) OVER (
PARTITION BY 子句 字段 , …
[ORDER BY 子句 字段 , … [ASC | DESC] [NULLS FIRST | NULLS LAST]
[WINDOWING 子句])
语法组成:
函数名称: 类似于统计函数(COUNT()、SUM()、MAX()、MIN()、AVG()等),也包括其他函数,如row_number(),rank(),precent()等
OVER子句:开窗函数(over()),为分析函数指明一个查询结果集,在SELECT子句之中使用
PARTITION BY子句: 将一个简单的结果集分为N组(或称为分区),而后按照不同的组(分区)对数据进行统计
ORDER BY 子句: 明确数据在每个组内的排列顺序(分析函数的结果与排列顺序有关),NULLS FIRST | NULLS LAST:表示返回数据行中包含NULL值是出现在排序序列前还是尾;
WINDOWING 子句:给出在定义变化的固定的数据窗口方法,分析函数按此数据进行统计。
*不使用PARTITION进行分区,直接利用OVER子句操作——如果没有分区就会把所有的数据当成一个区,然后进行统计
WINDOWING子句:
分窗子句主要是用于定义一个变化或固定的数据窗口方法,主要用于定义分析函数在操作行的集合,分窗子句有两种实现方式:
值域窗(RANGE WINDOW),逻辑偏移 :当前分区之中当前行的前N行到当前行的记录集;
行窗(ROWS WINDOW),物理偏移 :以排序的结果顺序计算偏移当前行的起始行记录集。
而如果要想指定RANGE(值域窗)或ROWS(行窗)的偏移量,则可以采用如下的几种排序列:RANGE | ROWS 数字 PRECEDING; RANGE | ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; RANGE | ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING; 以上的几种排列之中包含的概念如下: PRECEDING :主要是设置一个偏移量,这个偏移量可以是用户设置的数字,或者是其他标记; BETWEEN … AND :设置一个偏移量的操作范围; UNBOUNDED PRECEDING :不限制偏移量大小; CURRENT ROW :表示当前行; FOLLOWING : 如果不写此语句表示使用上N行与当前行指定数据比较,如果编写此语句,表示当前行与下N行数据比较;
例子:
RANGE子句:RANGE子句设置的是一个逻辑的偏移量
--在sal上设置偏移量(PRECEDING):按照向上N行的记录偏移:
SELECT deptno ,