Oracle 窗口函数

Oracle 窗口函数

Oracle 窗口函数

窗口函数是分析函数的一种，通常可以理解成over()函数

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，与普通的聚合函数不同，聚合函数用group by分组，每个分组返回一个统计值；而分析函数采用partition by 分组，并且每组每行都可以返回一个统计值。

构成格式：函数名①() over(partition by 分组的列名 order by 排序的列名 rows/range.. 子句)

函数名①一般来说有几种：

一种是聚合函数，像是sum、avg、count这种

一种是排序函数（序列函数），像是rank、dense_rank、row_number这种

一种是不好归类的，像是row_number这种行数范围的

1. 准备工作

练习SQL：

CREATE TABLE EMP (
	dept_id NUMBER, -- 部门编号
	emp_id NUMBER, --员工编号
	emp_name varchar2(50), --员工姓名
	sal number --员工薪资
);

INSERT INTO EMP values(100,10211,'zhengjielin','8800');
INSERT INTO EMP values(100,10211,'chenxueyuan','8000');
INSERT INTO EMP values(100,10300,'zhangyuyu','7200');
INSERT INTO EMP values(100,10121,'liruyu','7500');
INSERT INTO EMP values(200,10123,'liyuqiu','11000');
INSERT INTO EMP values(200,10555,'lilijuan','11500');
INSERT INTO EMP values(200,20567,'tanghao','9500');
INSERT INTO EMP values(300,20345,'xiaoyan','9000');
INSERT INTO EMP values(300,20223,'xiaoyuner','8800');
INSERT INTO EMP values(400,30344,'guqingyang','8500');
INSERT INTO EMP values(400,30255,'linzhiwen','10000');
INSERT INTO EMP values(400,30266,'wangbadan','7500');
INSERT INTO EMP values(500,30377,'gelilaowang','18800');

2. 认识 over() 函数

查询每个部门的总薪资，SQL1：

SELECT 
	e.dept_id, e.emp_id, e.emp_name, e.sal,
	sum(e.sal) over(PARTITION BY e.dept_id) dept_sal
FROM 
	EMP e;

结果

2.1 分组、排序、窗口

分析函数包含三个分析子句：分组(partition by)、排序(order by)、窗口(rows)。

分组和排序和 group by、order by 没什么差别，不多说。

窗口子句是在进行排序统计时对数据作出的一些限制，窗口子句中有三个属性值：

UNBOUNDED PRECEDING ：第一行
CURRENT ROW ：当前行
UNBOUNDED FOLLOWING ：最后一行

窗口子句不能单独出现，必须有order by 子句时才能出现，如：
sum(e.sal) over(PARTITION BY e.dept_id ORDER BY e.sal DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
而出现order by 子句的时候，可以忽略不写窗口子句，这时候默认为当前组的第一行到当前行！即默认为：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 。

按部门查询累计薪资，SQL2：

SELECT 
	e.dept_id, e.emp_id, e.emp_name, e.sal,
	sum(e.sal) over(PARTITION BY e.dept_id ORDER BY e.sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) dept_sal
FROM EMP e;

-- 等价于SQL：
SELECT 
	e.dept_id, e.emp_id, e.emp_name, e.sal,
	sum(e.sal) over(PARTITION BY e.dept_id ORDER BY e.sal) dept_sal
FROM EMP e;

结果：

【小总结】

当省略窗口子句时：

如果存在order by, 则默认的窗口是 unbounded preceding and current row.

如果同时省略order by, 则默认的窗口是 unbounded preceding and unbounded following.

如果省略分组，则把全部记录当成一个组：

如果存在order by 则默认窗口是unbounded preceding and current row

如果这时省略order by 则窗口默认为 unbounded preceding and unbounded following

2.2 最值函数：first_value()与last_value()

获取部门内最低和最高薪资：

SELECT 
	e.dept_id, e.emp_id, e.emp_name, e.sal,
	first_value(e.sal) over(PARTITION BY e.dept_id ORDER BY e.sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) min_sal,
	last_value(e.sal) over(PARTITION BY e.dept_id ORDER BY e.sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) max_sal
FROM EMP e;

执行结果：

first_value() 与 last_value() 用于获取第一个值和最后一个值

first_value()倒序排名也可获得 last_value() 的效果

2.3 排序函数：rank()、dense_rank()与row_number()

部门内员工薪资排序：

SELECT 
	e.dept_id, e.emp_id, e.emp_name, e.sal,
	rank() over(PARTITION BY e.dept_id ORDER BY e.sal desc) rank_,
	dense_rank() over(PARTITION BY e.dept_id ORDER BY e.sal desc) dense_rank_,
	row_number() over(PARTITION BY e.dept_id ORDER BY e.sal desc) row_number_
FROM EMP e

结果：

rank()：值相同时排名相同，其后续排名跳跃不连续

dense_rank()：值相同时排名相同，但后续排名连续不跳跃

row_number()：值相同时排名不同

2.4 获取之前/后第N个值：lag() 与 lead()

查询当前行向下偏移n行对应的结果

函数 lag(arg1, arg2, arg3)：

arg1：要获取的行的表达式
arg2：偏移量，默认值为1
arg3：超出了分组的范围时返回的值，默认为null

lead() 与lag() 效果相反。

SELECT 
	e.dept_id, e.emp_id, e.emp_name, e.sal,
	lag(e.sal) over(PARTITION BY e.DEPT_ID ORDER BY e.SAL) lag_,
	lag(e.sal,1,0) over(PARTITION BY e.DEPT_ID ORDER BY e.SAL) lag_1,
	lag(e.sal,2,0) over(PARTITION BY e.DEPT_ID ORDER BY e.SAL) lag_2,
	lead(e.sal,1,0) over(PARTITION BY e.DEPT_ID ORDER BY e.SAL) lead_1
FROM EMP e;

结果为：

应用场景：在进行排名时，想知道前一名的积分，或者是还差多少分可排名上前一名

2.5 百分比：ratio_to_report()

ratio_to_report(a) over(partition by b) ：求按照b分组后a的值在所属分组中总值的占比，a的值必须为数值或数值型字段。

【注意】：禁用order by

获取薪资占有的百分比：

SELECT 
	e.dept_id, e.emp_id, e.emp_name, e.sal,
	ratio_to_report(e.SAL) over() 薪资占总体百分比,
	trunc(RATIO_TO_REPORT(e.SAL) over(PARTITION BY e.DEPT_ID),4) 薪资占部门百分比 --trunc() 限定保留4位小数
FROM EMP e;

结果：

3. 小总结

平时的开发中，不用分析函数也能达到我们想要的效果，只是有了分析函数会方便很多，所以当做一种辅助方式就好。

以上是日常用到较多的函数，后续持续更新。

posted @ 2022-10-12 16:16 乐子不痞阅读(3047) 评论(1) 编辑收藏举报

刷新页面返回顶部

Luler's Blog

Oracle 窗口函数