分析函数概述
Oracle中的分析函数和聚合函数相似，但是对于每一组记录，无论多少行，聚合函数只返回一行值，而分析函数对其中每一行记录都返回值。这一组记录，称为分析函数的一个（WINDOW），窗口决定了要处理数据的范围，该范围在物理上可以由指定的行数来确定，或者在逻辑上由相对偏移量来确定。
分析函数总是在除了ORDER BY之外的其他子句运算后才执行的，所以它不能出现在where、group by等子句中，只能出现在select列表和order by子句中。
准备测试数据
在SCOTT用户下执行如下语句，创建测试表和测试数据：
create table lw_sales(dept_id varchar2(6), sale_date date, goods_type varchar2(4), sale_cnt number(10));
COMMENT ON TABLE LW_SALES IS '销售数据测试表。'
/
SET DEFINE OFF;
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/16/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G00', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('04/05/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G00', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/17/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G00', 700);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/13/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G00', 900);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('04/18/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/15/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 30);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/15/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('03/10/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 200);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('03/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 70);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/17/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G02', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/14/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G02', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('03/09/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G02', 900);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/16/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G02', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('04/22/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/21/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 200);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/17/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 600);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('04/05/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 200);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/09/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 30);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/20/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 900);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/21/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/02/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 500);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/10/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 500);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('04/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G05', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('03/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G05', 200);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G05', 500);
COMMIT;
分析函数功能之 - 排名
几种不同的排名需求
- 排名无并列，且每个排名与紧接着的下一个排名都是连续的
- 排名有并列，且并列的排名与紧接着的下一个排名不连续
- 排名有并列，且并列的排名与紧接着的下一个排名连续
如下例，对CNT列排序的结果，从左到右分别符合上述三种需求，结果即为：
用于排名的几个分析函数
- row_number
- rank
- dense_rank
SQL> select dept_id, sale_date, goods_type, sale_cnt,
2 row_number()over(partition by dept_id order by sale_cnt desc) rn,
3 rank()over(partition by dept_id order by sale_cnt desc) rk,
4 dense_rank()over(partition by dept_id order by sale_cnt desc) drk
5 from lw_sales where trunc(sale_date,'MM')=date'2013-04-01';
DEPT_I SALE_DATE GOOD SALE_CNT RN RK DRK
------ -------------- ---- ---------- ---------- ---------- ----------
S00 18-4月 -13 G01 300 1 1 1
S01 20-4月 -13 G04 900 1 1 1
S01 13-4月 -13 G00 900 2 1 1
S01 15-4月 -13 G01 400 3 3 2
S01 07-4月 -13 G02 300 4 4 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> 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> 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> 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 3330
S00 2013-04 300 3630
S01 2013-03 4430 4430
S01 2013-04 2700 7130
S02 2013-03 1370 1370
S02 2013-04 1800 3170
已选择6行。例2：求出每个部门按售出货物类别的累计销售额（按货物类别代码正序排列）以及每个货物按部门的累计销售额（按部门编号正序排列）
SQL> with t as (
2 select dept_id, goods_type, sum(sale_cnt) goods_sale_cnt from lw_sales group by dept_id, goods_type
3 )
4 select dept_id, goods_type, goods_sale_cnt,
5 sum(goods_sale_cnt)over(partition by dept_id order by goods_type) cum_gsc_goods,
6 sum(goods_sale_cnt)over(partition by goods_type order by dept_id) cum_gsc_dept
7 from t;
DEPT_I GOOD GOODS_SALE_CNT CUM_GSC_GOODS CUM_GSC_DEPT
------ ---- -------------- ------------- ------------
S00 G00 400 400 400
S01 G00 1600 1600 2000
S02 G00 400 400 2400
S00 G01 330 730 330
S01 G01 800 2400 1130
S02 G01 270 670 1400
S01 G02 1400 3800 1400
S02 G02 900 1570 2300
S00 G03 1000 1730 1000
S01 G03 800 4600 1800
S02 G03 1100 2670 2900
S00 G04 1000 2730 1000
S01 G04 2530 7130 3530
S02 G04 300 2970 3830
S00 G05 900 3630 900
S02 G05 200 3170 1100
已选择16行。相关统计分析函数--AVG
例1：求出每个部门每种货物的销售额与该货物在公司各部门平均销售额之间的差值
SQL> with t as (
2 select dept_id, goods_type, sum(sale_cnt) goods_sale_cnt
3 from lw_sales group by dept_id, goods_type)
4 select dept_id, goods_type, goods_sale_cnt,
5 round(AVG(goods_sale_cnt)over(partition by goods_type),2) avg_goods_sale_cnt
6 , goods_sale_cnt-round(AVG(goods_sale_cnt) over(partition by goods_type),2) dv_goods_sale_cnt
7 from t;
DEPT_I GOOD GOODS_SALE_CNT AVG_GOODS_SALE_CNT DV_GOODS_SALE_CNT
------ ---- -------------- ------------------ -----------------
S00 G00 400 800 -400
S01 G00 1600 800 800
S02 G00 400 800 -400
S00 G01 330 466.67 -136.67
S01 G01 800 466.67 333.33
S02 G01 270 466.67 -196.67
S01 G02 1400 1150 250
S02 G02 900 1150 -250
S00 G03 1000 966.67 33.33
S01 G03 800 966.67 -166.67
S02 G03 1100 966.67 133.33
S00 G04 1000 1276.67 -276.67
S01 G04 2530 1276.67 1253.33
S02 G04 300 1276.67 -976.67
S00 G05 900 550 350
S02 G05 200 550 -350
已选择16行。相关统计分析函数--MAX/MIN
例1：货物G01每月的最高和最低销售额对应的部门（如有多个部门按部门ID列出最小的一个，如某部门某月无销售额则不做统计）
SQL> with t as (
2 select dept_id , to_char(sale_date,'YYYY-MM') sale_month,
3 sum(sale_cnt) goods_sale_cnt，
4 max(sum(sale_cnt))over(partition by to_char(sale_date,'YYYY-MM')) max_gsc,
5 min(sum(sale_cnt))over(partition by to_char(sale_date,'YYYY-MM')) min_gsc
6 from lw_sales where goods_type='G01' group by dept_id, to_char(sale_date,'YYYY-MM'))
7 select a.sale_month,
8 min(case when goods_sale_cnt=max_gsc then dept_id end) max_dept_id,
9 min(case when goods_sale_cnt=min_gsc then dept_id end) min_dept_id
10 from t a where (goods_sale_cnt=min_gsc or goods_sale_cnt=max_gsc ) group by a.sale_month;
SALE_MO MAX_DE MIN_DE
------- ------ ------
2013-03 S01 S00
2013-04 S01 S00例2：查出货物G03在销售当天及前十二天的最大销售额（按部门和整个公司分别求出）
SQL> select dept_id， sale_date，sale_cnt，
2 max(sale_cnt)over(partition by dept_id order by sale_date range interval '12' day preceding) max_dept_sale_cnt,
3 max(sale_cnt)over(order by sale_date range numtodsinterval(12, 'DAY') preceding) max_sale_cnt
4 from lw_sales where goods_type='G03';
DEPT_I SALE_DATE SALE_CNT MAX_DEPT_SALE_CNT MAX_SALE_CNT
------ -------------- ---------- ----------------- ------------
S00 06-3月 -13 800 800 800
S01 17-3月 -13 600 600 800
S00 21-3月 -13 200 200 600
S01 03-4月 -13 200 200 200
S02 05-4月 -13 800 800 800
S02 22-4月 -13 300 300 300
已选择6行。
相关统计分析函数--FIRST_VALUE和LAST_VALUE
例：查出所有雇员的雇员职位、编号、姓名、薪水以及同职位上薪水最高和最低的雇员的雇员编号（薪水相同的，按雇员编号倒序排列）
SQL> select job, empno, ename, sal,
2 last_value(empno)over(partition by job order by sal, empno desc rows between unbounded preceding and unbounded following) lv,
3 first_value(empno)over(partition by job order by sal, empno desc rows between unbounded preceding and unbounded following) fv
4 from emp;
JOB EMPNO ENAME SAL LV FV
--------- ---------- ---------- ---------- ---------- ----------
ANALYST 7902 FORD 7788 7902
ANALYST 7788 SCOTT 7788 7902
CLERK 7900 JAMES 950 7369 7900
CLERK 7934 MILLER 7369 7900
CLERK 7876 ADAMS 7369 7900
CLERK 7369 SMITH 7369 7900
MANAGER 7698 BLAKE 2850 7566 7698
MANAGER 7782 CLARK 7566 7698
MANAGER 7566 JONES 7566 7698
PRESIDENT 7839 KING 7839 7839
SALESMAN 7654 MARTIN 1250 7499 7654
SALESMAN 7521 WARD 1250 7499 7654
SALESMAN 7844 TURNER 1500 7499 7654
SALESMAN 7499 ALLEN 1600 7499 7654
已选择14行。
统计分析函数要点
- 可以有partition by、order by和range/rows子句（此即windowing子句）
- 以上子句都不是必须出现的，但若出现windowing子句则其前必须出现order by子句
- 没有order by就是分组统计，有order by就是分组累计
- FIRST/LAST这两个分析函数后面只能跟partition by子句
- FIRST_VALUE和LAST_VALUE用于获取一组有序的数据中的第一个和最后一个值，和MAX/MIN以及FIRST/LAST函数很像，但要注意区别
- COUNT也可以作为分析函数，这和SUM/MAX等差不多，另外一个函数叫RATIO_TO_REPORT，在做报表或饼状图的时候很有用！
2547
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
