[Oracle]分析函数详述(三)
3
S01 03-4月 -13 G03 200 5 5 4
S02 05-4月 -13 G03 800 1 1 1
S02 05-4月 -13 G00 400 2 2 2
S02 22-4月 -13 G03 300 3 3 3
S02 06-4月 -13 G04 300 4 3 3
已选择10行。
排名分析函数要点
排名分析函数不需要参数
排名分析函数里的order by子句是必须的
partition by 和 order by后均可跟多列
只在没有partition by 的情况下，rownum才能做到和row_number同样的事情
分析函数功能之 - 相邻
业务上需要用到相邻行的场景有：
用于获取相邻行的数据，以便于进行相关计算，例如同比环比
实现重复数据只输出第一个的需求
实现重复数据只输出第一个和最后一个的需求
相邻类分析函数有：
LAG和LEAD用于获取相邻行的数据，以便于进行相关计算
LAG 是取到排序后当前记录之前的记录
LEAD 是取到排序后当前记录之后的记录
例子：查出同部门按字母正序姓名比自己大和小的雇员姓名各是啥？没有比自己姓名小的设为AAA，没有比自己姓名大的设为ZZZ。
[sql]
SQL> select deptno, ename
2 , lag(ename,1,'AAA')over(partition by deptno order by ename) lower_name
3 , lead(ename,1,'ZZZ')over(partition by deptno order by ename) higher_name
4 from emp;
DEPTNO ENAME LOWER_NAME HIGHER_NAM
---------- ---------- ---------- ----------
10 CLARK AAA KING
10 KING CLARK MILLER
10 MILLER KING ZZZ
20 ADAMS AAA FORD
20 FORD ADAMS JONES
20 JONES FORD SCOTT
20 SCOTT JONES SMITH
20 SMITH SCOTT ZZZ
30 ALLEN AAA BLAKE
30 BLAKE ALLEN JAMES
30 JAMES BLAKE MARTIN
30 MARTIN JAMES TURNER
30 TURNER MARTIN WARD
30 WARD TURNER ZZZ
已选择14行。
在前例基础上，部门编号只输出一次：
[sql]
SQL> select (case when deptno= lag(deptno,1,-1)over(partition by deptno order by ename) then null else deptno end) deptno
2 , ename
3 , lag(ename,1,'AAA')over(partition by deptno order by ename) lower_name
4 , lead(ename,1,'ZZZ')over(partition by deptno order by ename) higher_name
5 from emp;
DEPTNO ENAME LOWER_NAME HIGHER_NAM
---------- ---------- ---------- ----------
10 CLARK AAA KING
KING CLARK MILLER
MILLER KING ZZZ
20 ADAMS AAA FORD
FORD ADAMS JONES
JONES FORD SCOTT
SCOTT JONES SMITH
SMITH SCOTT ZZZ
30 ALLEN AAA BLAKE
BLAKE ALLEN JAMES
JAMES BLAKE MARTIN
MARTIN JAMES TURNER
TURNER MARTIN WARD
WARD TURNER ZZZ
相邻类分析函数要点
LAG/LEAD(v, n, dv)里的n表示位移，必须是0或正整数，dv是在没有取到对应值时的默认值。n默认是1，dv默认是null。
相邻类分析函数后面order by子句是必须的
partition by 和 order by后均可跟多列
分析函数功能之 - 统计
业务上需要用到统计分析函数的有：
当年各月的累计销售额
每名销售人员当月的销售额与平均每名销售人员销售额的差值
XX货物每月的最高和最低销售额对应的部门
获取相邻行内最近的一个非空值
相关统计分析函数
SUM
AVG
MAX/MIN
FIRST_VALUE/LAST_VALUE
相关统计分析函数--SUM
例1：求出每个部门按月的累计销售额
[sql]
SQL> with t as
2 (select dept_id, to_char(sale_date,'YYYY-MM') sale_month,
3 sum(sale_cnt) month_sale_cnt from lw_sales
4 group by dept_id, to_char(sale_date,'YYYY-MM')
5 )
6 select dept_id, sale_month, month_sale_cnt,
7 sum(month_sale_cnt)over(partition by dept_id order by sale_month) cum_month_sale_cnt
8 from t;
DEPT_I SALE_MO MONTH_SALE_CNT CUM_MONTH_SALE_CNT
------ ------- -------------- ------------------
S00 2013-03 3330