SQL Fundamentals: 子查询 || 分析函数(PARTITION BY,ORDER BY, WINDOWING)
SQL Fundamentals || Oracle SQL语言
七、分析函数
- 分析函数语法；
- 分窗操作的；
- 使用分析函数可以进行更为复杂的查询报表显示。
- 在分析函数中可以使用若干统计函数.
传统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，而这三种子句的组合顺序有如下几种：
|
组合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()； |
基本查询语句中是不能出现字段和统计函数同时出现的.（如下语法是错误的）
SELECT deptno , ename, sal ,
SUM(sal)
FROM emp ;
1、PARTITION子句和ORDER BY子句的使用
|
使用PARTITION子句 按照部门范畴进行统计；每行数据之后都会有统计的结果出现. 同一部门的雇员的SUM（sal）的值相同，并且值等于同一部门雇员sal的相加. |
|
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 ;
|
|
通过PARTITION设置多个分区字段——分析部门编号和职位进行分区 这里的范畴：先按照部门分区，再按照职位分区,返回的结果是：每个部门中职位的总工资 |
|
SELECT deptno , ename, sal , job , SUM(sal) OVER (PARTITION BY deptno , job) sum FROM emp ;
|
|
观察ORDER BY子句 按照部门编号分区，按照工资降序排列（两个数据相同并列为1，后面一个数据为3） |
|
SELECT deptno , ename, sal , RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rk FROM emp ;
|
|
设置多个排序字段（sal和hiredate） |
|
SELECT deptno , ename, sal , hiredate , RANK() OVER (PARTITION BY deptno ORDER BY sal , hiredate DESC) rk FROM emp ;
|
|
直接利用ORDER BY排序所有数据——如果不写分区操作，就表示所有的数据进行排序 先将所有数据变为一组，而后按照姓名进行排序； 排序后，第一个数据的sum(sal)值为其sal本身，第二个数据的sum(sal)值为第一个数据和第二个数据的总和，以此类推. |
|
SELECT deptno , ename, sal , hiredate , SUM(sal) OVER (ORDER BY ename DESC) SUM FROM emp ;
|
2、WINDOWING子句
分窗子句主要是用于定义一个变化或固定的数据窗口方法，主要用于定义分析函数在操作行的集合，分窗子句有两种实现方式：
|
值域窗（RANGE WINDOW），逻辑偏移 |
当前分区之中当前行的前N行到当前行的记录集； |
|
行窗（ROWS WINDOW），物理偏移 |
以排序的结果顺序计算偏移当前行的起始行记录集。 |
而如果要想指定RANGE（值域窗）或ROWS（行窗）的偏移量，则可以采用如下的几种排序列：
|
1 |
RANGE | ROWS 数字 PRECEDING； |
|
2 |
RANGE | ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW； |
|
3 |
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 , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 300 PRECEDING) sum FROM emp ;
|
|
设置偏移量为300，采用下匹配方式处理 向下匹配方式，显示结果向下N行的记录偏移 |
|
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND 300 FOLLOWING) sum FROM emp ;
|
|
匹配当前行数据 |
|
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND CURRENT ROW) sum FROM emp ;
|
|
使用UNBOUNDED不设置边界 现在的结果是在一个区域内进行逐行的操作. |
|
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEENUNBOUNDED PRECEDING AND CURRENT ROW) sum FROM emp ;
|
ROWS子句：ROWS子句设置的是一个物理的偏移量
|
设置2行物理偏移 按照部门编号分组，而后采用当前行和前2行数据进行计算. |
|
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING) sum FROM emp ;
|
|
设置查询行范围 此时和按照部门分区进行求和道理相同. |
|
SELECT deptno , ename, sal , SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum FROM emp ;
|
分析函数：
1、数据统计函数
2、等级函数
3、报表函数
1、数据统计函数
|
函数名称 |
描述 |
|
SUM([DISTINCT | ALL] 表达式) |
计算分区（分组）中的数据累加和 |
|
MIN([DISTINCT | ALL] 表达式) |
查找分区（分组）中的最小值 |
|
MAX([DISTINCT | ALL] 表达式) |
查找分区（分组）中的最大值 |
|
AVG([DISTINCT | ALL] 表达式) |
计算分区（分组）中的数据平均值 |
|
COUNT(* | [DISTINCT | ALL] 表达式) |
计算分区（分组）中的数据量 |
|
查询雇员编号是7369的雇员姓名、职位、基本工资、部门编号、部门的人数、平均工资、最高工资、最低工资、总工资 |
|
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 ; 法二： WITH t AS( 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 ) SELECT * FROM t WHERE t.empno=7369 ; |
|
查询每个雇员的编号、姓名、基本工资、所在部门的名称、部门位置以及此部门的平均工资、最高和最低工资 |
|
确定所需要的数据表：dept表：部门信息，名称，位置 emp表：雇员姓名，统计信息 确定已知的关联字段： 雇员和部门：emp.deptno=dept.deptno 步骤一：进行多表关联 步骤二：加入统计信息（部门的平均工资、最高和最低工资：使用分析函数，进行分窗） |
|
SELECT e.empno , e.ename , e.sal , d.dname , d.loc , ROUND(AVG(sal) OVER (PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) avg_salary , MAX(sal) OVER (PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) max_salary , MIN(sal) OVER (PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) min_salary FROM emp e,dept d WHERE e.deptno=d.deptno ; |
2、等级函数
为数据进行排序编号
|
函数名称 |
描述 |
|
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()函数 |
|
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()函数 针对所有数据，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 ; |
|
KEEP语句
分组函数 () KEEP (DENSE_RANK FIRST | LAST ORDER BY 表达式 [ASC | DESC] [NULLS [FIRST | LAST]] , ...) [OVER () 分区查询] ; |
|
查询每个部门的最高及最低工资 |
|
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 ; |
|
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()与LEAD()函数 相邻记录的比较 |
|
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 ;
|
3、报表函数
|
函数名称 |
描述 |
|
CUME_DIST() |
计算一行在分区（分组）中的相对位置，如果分区有5条记录，记录会按照1、0.8、0.6、0.4、0.2的方式进行划分 |
|
NTILE(数字) |
将一个分区（分组）分为“表达式”的散列表示 针对数据分区中的有序结果集进行划分 |
|
RATIO_TO_REPORT(表达式) |
该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比 |
|
验证CUME_DIST()函数 |
|
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; |
