1 over()分析函数
说明:聚合函数(如sum()
、max()
等)可以计算基于组的某种聚合值,但是聚合函数对于某个组只能返回一行记录。若想对于某组返回多行记录,则需要使用分析函数。
1.1 rank()/dense_rank()
1.1.1 基础
rank()/dense_rank over(partition by ... order by ...)
说明:
over()
在什么条件之上;partition by
按哪个字段划分组(如果要分组必须,有此关键字partition
);order by
按哪个字段排序;
注意:
- 使用
rank()/dense_rank()
时,必须要带order by
否则非法 rank()/dense_rank()
分级的区别
rank()
: 跳跃排序,如果有两个第一级时,接下来就是第三级。
dense_rank()
:连续排序,如果有两个第一级时,接下来仍然是第二级。
1.1.2 示例
示例:查询每个部门工资最高的员工信息
一般的写法:
select e.ename, e.job, e.sal, e.deptno from scott.emp e, (select e.deptno, max(e.sal) sal from scott.emp e group by e.deptno) me where e.deptno = me.deptno and e.sal = me.sal;
使用over()
函数:
方法一:
select e.ename, e.job, e.sal, e.deptno from (select e.ename, e.job, e.sal, e.deptno, rank() over(partition by e.deptno order by e.sal desc) rank --在按部门划分的基础上,工资从高到低分级,级别rank从1开始依次递增 from emp e) e where e.rank = 1 ;
方法二:
select e.ename, e.job, e.sal, e.deptno from (select e.ename, e.job, e.sal, e.deptno, dense_rank() over(partition by e.deptno order by e.sal desc) rank from emp e) e where e.rank = 1;
对比查询结果:左边的是用一般的方法查询结果,右边的是分析函数查询结果(两种方法结果相同)
1.2 min()/max()
min()/max() over(partition by ...)
1.2.1 示例
查询员工信息的同时,查询员工工资与所在部门最低、最高工资的差额
一般的写法:
select e.ename, e.job, e.sal, e.deptno, e.sal - me.min_sal diff_min_sal, me.max_sal - e.sal diff_max_sal from scott.emp e, (select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal from scott.emp e group by e.deptno) me where e.deptno = me.deptno order by e.deptno, e.sal;
使用分析函数:
select e.ename, e.job, e.sal, e.deptno, min(e.sal) over(partition by e.deptno) min_sal, max(e.sal) over(partition by e.deptno) max_sal, nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal, nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal from emp e;
注:这里没有排序条件,若加上order by
排序条件
max() over(partition by .. order by .. desc)
排序规则只能为desc
,否则不起作用,将查询到目前为止排序值最高字段的对应值min() over(partition by .. order by .. asc )
排序规则只能为asc
,否则不起作用,将查询到目前为止排序值最低的字段的对应值
如下:
select e.ename, e.job, e.sal, e.deptno, min(e.sal) over(partition by e.deptno) min_sal01, max(e.sal) over(partition by e.deptno) max_sal01, min(e.sal) over(partition by e.deptno order by e.sal) min_sal02, max(e.sal) over(partition by e.deptno order by e.sal) max_sal02, --不起作用 min(e.sal) over(partition by e.deptno order by e.sal desc) min_sal03, --不起作用 max(e.sal) over(partition by e.deptno order by e.sal desc) max_sal03, min(e.sal) over(partition by e.deptno order by e.sal asc) min_sal04, max(e.sal) over(partition by e.deptno order by e.sal asc) max_sal04, --不起作用 nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal, nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal from emp e;
结果如下:
1.3 lead()/lag()
1.3.1 基础
lead()/lag() over(partition by ... order by ...)
取前面/后面第n
行记录说明:
lead(列名,n,m):
当前记录后面第n
行记录的列名的值,没有则默认值为m
;如果不带参数n,m
,则查找当前记录后面第一行的记录列名的值,没有则默认值为null
。lag(列名,n,m):
当前记录前面第n
行记录的列名的值,没有则默认值为m
;如果不带参数n,m
,则查找当前记录前面第一行的记录列名的值,没有则默认值为null
1.3.2 示例
查询个人工资与比自己高一位、低一位的工资的差额
使用分析函数:
select e.ename, e.job, e.sal, e.deptno, lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal, --记录后面第n行记录 lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal, --记录前面第n行记录 nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal, 0) diff_lead_sal, nvl(e.sal - lead(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal from emp e;
查询结果:
1.4 first_value/last_value()
first_value/last_value() over(partition by ...)
取首尾记录示例:
select e.empno, e.ename, e.job, e.mgr, e.sal, e.deptno, first_value(e.sal) over(partition by e.deptno) first_sal, last_value(e.sal) over(partition by e.deptno) last_sal from emp e;
查询结果:
1.5 row_number()
row_number() over(partition by.. order by ..)
排序(应用:分页)
示例:
select e.ename, e.job, e.sal, e.deptno,e.row_num from (select e.ename, e.job, e.sal, e.deptno, row_number() over(partition by e.deptno order by e.sal) row_num from emp e) e where e.row_num > 3;
查询结果:
补充:类似分页的操作还可以用rownum
、fetch
(oracle12c
后的新特性)实现
1.6 sum/avg/count()
sum/avg/count() over(partition by ..)
示例1:
select e.ename, e.job, e.sal, e.deptno, sum(e.sal) over(partition by e.deptno) sum_sal, --统计某组中的总计值 avg(e.sal) over(partition by e.deptno) avg_sal, --统计某组中的平均值 count(e.sal) over(partition by e.deptno) count_sal --按某列分组,并统计该组中记录数量 from emp e;
查询结果:
示例2(全统计):为数据集统计部门销售总和,全公司销售总和,部门销售均值,全公司销售均值
select a.dept_id, a.sale_date, a.goods_type, a.sale_cnt, sum(a.sale_cnt) over(partition by a.dept_id) dept_total, --部门销售总和 sum(a.sale_cnt) over() cmp_total, --公司销售总额 avg(a.sale_cnt) over(partition by a.dept_id) dept_avg, --部门销售均值 avg(a.sale_cnt) over() cmp_avg --公司销售均值 from learn_fun_keep a;
1.7 rows/range
rows/range between … preceding and … following
1.7.1 基础
上下范围内求值说明:
unbounded
:不受控制的,无限的preceding
:在…之前following
:在…之后
rows between … preceding and … following
示例1:
显示各部门员工的工资,并附带显示该部门的最高工资
写法一:
select e.deptno, e.empno, e.ename, e.sal, last_value(e.sal) over(partition by e.deptno order by e.sal rows /*max(e.sal) over(partition by e.deptno order by e.sal rows*/ --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录 --unbounded:不受控制的,无限的 --preceding:在...之前 --following:在...之后 between unbounded preceding and unbounded following) max_sal from emp e;
结果如下:
写法二:
select e.deptno, e.empno, e.ename, e.sal, max(e.sal) over(partition by e.deptno /*order by e.sal*/) max_sal from emp e;
示例2:
对各部门进行分组,并附带显示第一行至当前行的汇总
select empno, ename, deptno, sal, --注意rows between unbounded preceding and current row 是指第一行至当前行的汇总 sum(sal) over(partition by deptno order by ename rows between unbounded preceding and current row) max_sal from scott.emp;
结果如下:
示例3:
当前行至最后一行的汇总
select empno, ename, deptno, sal, --注意rows between current row and unbounded following 指当前行到最后一行的汇总 sum(sal) over(partition by deptno order by ename rows between current row and unbounded following) max_sal from scott.emp;
结果如下:
示例4:
当前行的上一行(rownum-1
)到当前行的汇总
select empno, ename, deptno, sal, --注意rows between 1 preceding and current row 是指当前行的上一行(rownum-1)到当前行的汇总 sum(sal) over(partition by deptno order by ename rows between 1 preceding and current row) max_sal from scott.emp;
结果如下:
示例5:
当前行的上一行(rownum-1
)到当前行的下两行(rownum+2
)的汇总
select empno, ename, deptno, sal, --注意rows between 1 preceding and 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总 sum(sal) over(partition by deptno order by ename rows between 1 preceding and 2 following) max_sal from scott.emp;
结果如下:
2 其他
2.1 nulls first/last
将空值字段记录放到最前或最后显示
说明:通过rank()
、dense_rank()
、row_number()
对记录进行全排列、分组排列取值,但有时候,会遇到空值的情况,空值会影响得到的结果的正确性
nulls first/last
可以帮助我们在处理含有空值的排序排列中,将空值字段记录放到最前或最后显示,帮助我们得到期望的结果
select e.ename, e.job, e.sal, e.deptno, rank() over(partition by e.deptno order by e.sal nulls last) from emp e;
结果如下:
2.2 ntile(n)
有时会有这样的需求:如果数据排序后分为三部分,业务人员只关心其中的一部分,如何将这中间的三分之一数据拿出来呢?
这时比较好的选择,就是使用ntile函数
示例:
select e.ename, e.job, e.sal, e.deptno, ntile(3) over(order by e.sal desc nulls last) all_cmp, --若只取前三分之一,all_cmp=1即可,若只取中间三分之一,all_cmp=2即可 ntile(3) over(partition by e.deptno order by e.sal desc nulls last) all_dept --每个部门的分成三部分 from emp e
结果如下:
2.3 keep(dense_rank first/last)
说明:
keep
字面意思就是保持,也就是说保存满足keep()
括号内条件的记录,这里可以想象到,会有多条记录的情况,即存在多个last
或first
的情况
dense_rank
是排序策略first/last
是筛选策略
示例:
查看部门 d02
内,销售记录时间最早,销售量最小的记录
select a.dept_id, min(a.sale_cnt) keep(dense_rank first order by a.sale_date) min_early_date from learn_fun_keep a where a.dept_id = 'd02' group by a.dept_id;
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。