Oracle 高级函数学习——窗口函数

一、什么是窗口函数

窗口函数是分析函数的一种

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

1.准备工作

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

INSERT INTO TEST_EMPvalues(100,10211,'zhengjielin','8800');

INSERT INTO TEST_EMPvalues(100,10211,'chenxueyuan','8000');

INSERT INTO TEST_EMPvalues(100,10300,'zhangyuyu','7200');

INSERT INTO TEST_EMPvalues(100,10121,'liruyu','7500');

INSERT INTO TEST_EMPvalues(200,10123,'liyuqiu','11000');

INSERT INTO TEST_EMPvalues(200,10555,'lilijuan','11500');

INSERT INTO TEST_EMPvalues(200,20567,'tanghao','9500');

INSERT INTO TEST_EMPvalues(300,20345,'xiaoyan','9000');

INSERT INTO TEST_EMPvalues(300,20223,'xiaoyuner','8800');

INSERT INTO TEST_EMPvalues(400,30344,'guqingyang','8500');

INSERT INTO TEST_EMPvalues(400,30255,'linzhiwen','10000');

INSERT INTO TEST_EMPvalues(400,30266,'wangbadan','7500');

INSERT INTO TEST_EMPvalues(500,30377,'gelilaowang','18800');

2.查询一下数据

 3.group by 或者 distinct 分组效果

 4.分析函数row_number() 分组效果

 row_number()函数单独作为一列，PARTITION BY 字段A:根据字段A 进行分组，ORDER BY e.SAL desc  根据e.SAL进行排序 desc倒序。

由此可以看出分析分析函数作为单独一列，会显示出每个分组的每条记录，并给每条记录按照order by 的字段进行组内编号，而聚合函数只会一组显示一条数据。

二、窗口函数介绍以及应用

1.分组、排序、窗口

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

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

1. UNBOUNDED PRECEDING ：第一行
2. CURRENT ROW ：当前行
3. 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;

小总结：

当省略窗口子句时：

1. 如果存在order by, 则默认的窗口是 unbounded preceding and current row.
2. 如果同时省略order by, 则默认的窗口是 unbounded preceding and unbounded following.

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

1. 如果存在order by 则默认窗口是unbounded preceding and current row
2. 如果这时省略order by 则窗口默认为 unbounded preceding and unbounded following

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 TEST_EMP e;

 

 

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

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

也就是说使用正序，first_value()取最小值；

使用倒序，first_value()取最大值。

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 TEST_EMP e

 

 

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

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

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

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 TEST_EMP e;

 应用场景：在进行排名时，想知道前一名的积分，或者是还差多少分可排名上前一名等，也可以做差值直接体现。

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 TEST_EMP e;

三、总结

     分析函数的意义就是面对复杂的统筹场景应用，在平常的开发中能使用聚合函数查出的，不必须使用聚合函数。