Oracle分析函数
01.Oracle分析函数
--运行总计:比如逐行显示在某些部门中的累计汇总工资，每行包含前面各行工资之和，
--查找一组内的百分数：比如显示在某些部门中付给个人的总工资百分数，将他们的工资与该部门的工资总和相除
--前n个查询:查询指定条件的前N个记录
--移动平均值计算：将当前行的值与前N行的值 加一起求平均值
--执行等级查询:比如显示一个部门内某个员工工资的相关等级
select ename,
deptno,
sal,
sum(sal) over(order by deptno, ename) 运行总计,
sum(sal) over(partition by deptno order by ename) 分部门运行统计,
row_number() over(partition by deptno order by ename) 序列
from emp
where deptno in (10, 20)
order by deptno, ename;
SQL> select ename,
2 deptno,
3 sal,
4 sum(sal) over(order by deptno, ename) 运行总计,
5 sum(sal) over(partition by deptno order by ename) 分部门运行统计,
6 row_number() over(partition by deptno order by ename) 序列
7 from emp
8 where deptno in (10, 20)
9 order by deptno, ename;
ENAME DEPTNO SAL 运行总计 分部门运行统计 序列
---------- ---------- ---------- ---------- -------------- ----------
CLARK 10 2450 2450 2450 1
KING 10 5000 7450 7450 2
MILLER 10 1300 8750 8750 3
ADAMS 20 1100 9850 1100 1
FORD 20 3000 12850 4100 2
JONES 20 2975 15825 7075 3
SCOTT 20 3000 18825 10075 4
SMITH 20 800 19625 10875 5
已选择8行。
02.Oracle分析函数_不指定排序规则
指定排序规则
SQL> select ename, deptno, sal, sum(sal) over(order by ename) 运行总计
2 from emp
3 where deptno in (10, 20);
ENAME DEPTNO SAL 运行总计
---------- ---------- ---------- ----------
ADAMS 20 1100 1100
CLARK 10 2450 3550
FORD 20 3000 6550
JONES 20 2975 9525
KING 10 5000 14525
MILLER 10 1300 15825
SCOTT 20 3000 18825
SMITH 20 800 19625
已选择8行。
--注意 sum要进行运行总计计算，如果不指定排序规则，在运行总计栏只能看到相同的结果,如下
SQL> select ename, deptno, sal, sum(sal) over() 运行总计
2 from emp
3 where deptno in (10, 20);
ENAME DEPTNO SAL 运行总计
---------- ---------- ---------- ----------
SMITH 20 800 19625
JONES 20 2975 19625
CLARK 10 2450 19625
SCOTT 20 3000 19625
KING 10 5000 19625
ADAMS 20 1100 19625
FORD 20 3000 19625
MILLER 10 1300 19625
已选择8行。
03.统计部门中各部门中不同职位的薪资总计，同时显示不同职位的薪资的运行总计
SQL> select o.deptno,
2 o.job,
3 sum(o.sal) 部门职称汇总,
4 sum(sum(o.sal)) over(partition by o.deptno order by o.job) 部门薪资
运行汇总
5 from emp o
6 where deptno in (10, 20, 30)
7 and job is not null
8 group by o.deptno, o.job
9 order by o.deptno;
DEPTNO JOB 部门职称汇总 部门薪资运行汇总
---------- --------- ------------ ----------------
10 CLERK 1300 1300
10 MANAGER 2450 3750
10 PRESIDENT 5000 8750
20 ANALYST 6000 6000
20 CLERK 1900 7900
20 MANAGER 2975 10875
30 CLERK 950 950
30 MANAGER 2850 3800
30 SALESMAN 5600 9400
已选择9行。
04_等级函数.
SQL> select o.deptno,
2 --o.job,
3 sum(o.sal) 部门职称汇总,
4 rank() over(order by sum(o.sal) desc) 薪资等级,
5 dense_rank() over(order by sum(o.sal) desc) dense_rank排名,
6 row_number() over(partition by o.deptno order by sum(o.sal) desc) 分
组行号,
7 sum(sum(o.sal)) over(partition by o.deptno order by o.job) 部门薪资
运行总计
8 from emp o
9 where o.deptno in (10, 20, 30)
10 and job is not null
11 group by o.deptno, o.job;
DEPTNO 部门职称汇总 薪资等级 DENSE_RANK排名 分组行号 部门薪资运行总计
---------- ------------ ---------- -------------- ---------- ----------------
20 6000 1 1 1 6000
30 5600 2 2 1 9400
10 5000 3 3 1 8750
20 2975 4 4 2 10875
30 2850 5 5 2 3800
10 2450 6 6 2 3750
20 1900 7 7 3 7900
10 1300 8 8 3 1300
30 950 9 9 3 950
已选择9行。
SQL>
05.分区子句
--使用row_number进行行号统计时使用分区
--注意 row_number 函数总是从1开始计数
SQL> select deptno,
2 empno,
3 ename,
4 row_number() over(partition by deptno order by empno) 分组
5 from emp
6 where deptno in (10, 20, 30);
DEPTNO EMPNO ENAME 分组行号
---------- ---------- ---------- ----------
10 7782 CLARK 1
10 7839 KING 2
10 7934 MILLER 3
20 7369 SMITH 1
20 7566 JONES 2
20 7788 SCOTT 3
20 7876 ADAMS 4
20 7902 FORD 5
30 7499 ALLEN 1
30 7521 WARD 2
30 7654 MARTIN 3
DEPTNO EMPNO ENAME 分组行号
---------- ---------- ---------- ----------
30 7698 BLAKE 4
30 7844 TURNER 5
30 7900 JAMES 6
已选择14行。
06.排序子句
--order by 子句用于指定分组中数据的排序方式，排序方式会明显地影响任何分析函数的结果，举例来说，在进行AVG
--运算时，如果不指定排序，将会看到所有的结果都相同
--排序子句SQL> select ename, sal, avg(sal) over() from emp where rownum <= 3;
ENAME SAL AVG(SAL)OVER()
---------- ---------- --------------
SMITH 800 1216.66667
ALLEN 1600 1216.66667
WARD 1250 1216.66667
--如果指定按ename排序，其结果又大不一样
SQL> select ename, sal, avg(sal) over( order by ename) from emp where rownum <=
3;
ENAME SAL AVG(SAL)OVER(ORDERBYENAME)
---------- ---------- --------------------------
ALLEN 1600 1600
SMITH 800 1200
WARD 1250 1216.66667
07.开窗子句
--开窗子句
--从当前记录开始直至某个部分的最后一条记录结束记录
--从统计时可以统计分组以外的记录
--在当前行的前几行或后几行进行滚动计算
SQL> select deptno,
2 sum(sal) 部门薪资小计,
3 sum(sum(sal)) over(order by deptno rows between unbounded preceding
and unbounded following) 部门总计
4 from emp
5 group by deptno;
DEPTNO 部门薪资小计 部门总计
---------- ------------ ----------
10 8750 29025
20 10875 29025
30 9400 29025
08.分析函数列表 count函数
SQL> select empno,
2 ename,
3 count(*) over(partition by deptno order by empno) 条数统计
4 from emp;
EMPNO ENAME 条数统计
---------- ---------- ----------
7782 CLARK 1
7839 KING 2
7934 MILLER 3
7369 SMITH 1
7566 JONES 2
7788 SCOTT 3
7876 ADAMS 4
7902 FORD 5
7499 ALLEN 1
7521 WARD 2
7654 MARTIN 3
EMPNO ENAME 条数统计
---------- ---------- ----------
7698 BLAKE 4
7844 TURNER 5
7900 JAMES 6
已选择14行。
--通过range between来判断sal值是否在50-150之间
SQL> select empno,
2 ename,
3 sal,
4 count(*) over(order by sal range between 50 preceding and 150 follow
ing) 薪水差异个数
5 from emp;
EMPNO ENAME SAL 薪水差异个数
---------- ---------- ---------- ------------
7369 SMITH 800 2
7900 JAMES 950 2
7876 ADAMS 1100 3
7521 WARD 1250 3
7654 MARTIN 1250 3
7934 MILLER 1300 3
7844 TURNER 1500 2
7499 ALLEN 1600 1
7782 CLARK 2450 1
7698 BLAKE 2850 4
7566 JONES 2975 3
EMPNO ENAME SAL 薪水差异个数
---------- ---------- ---------- ------------
7788 SCOTT 3000 3
7902 FORD 3000 3
7839 KING 5000 1
已选择14行。
--09.分析函数列表 AVG
SQL> select deptno,
2 empno,
3 ename,
4 sal,
5 avg(sal) over(partition by deptno order by deptno) avg_sal
6 from emp;
DEPTNO EMPNO ENAME SAL AVG_SAL
---------- ---------- ---------- ---------- ----------
10 7782 CLARK 2450 2916.66667
10 7839 KING 5000 2916.66667
10 7934 MILLER 1300 2916.66667
20 7566 JONES 2975 2175
20 7902 FORD 3000 2175
20 7876 ADAMS 1100 2175
20 7369 SMITH 800 2175
20 7788 SCOTT 3000 2175
30 7521 WARD 1250 1566.66667
30 7844 TURNER 1500 1566.66667
30 7499 ALLEN 1600 1566.66667
DEPTNO EMPNO ENAME SAL AVG_SAL
---------- ---------- ---------- ---------- ----------
30 7900 JAMES 950 1566.66667
30 7698 BLAKE 2850 1566.66667
30 7654 MARTIN 1250 1566.66667
已选择14行。
10_分析函数列表min_max函数
SQL> select deptno,
2 empno,
3 ename,
4 hiredate,
5 sal,
6 min(sal) over(partition by deptno order by hiredate range unbounded
preceding) 最低薪水,
7 max(sal) over(partition by deptno order by hiredate range unbounded
preceding) 最高薪水
8 from emp;
DEPTNO EMPNO ENAME HIREDATE SAL 最低薪水 最高薪水
---------- ---------- ---------- -------------- ---------- ---------- ----------
10 7782 CLARK 09-6月 -81 2450 2450 2450
10 7839 KING 17-11月-81 5000 2450 5000
10 7934 MILLER 23-1月 -82 1300 1300 5000
20 7369 SMITH 17-12月-80 800 800 800
20 7566 JONES 02-4月 -81 2975 800 2975
20 7902 FORD 03-12月-81 3000 800 3000
20 7788 SCOTT 19-4月 -87 3000 800 3000
20 7876 ADAMS 23-5月 -87 1100 800 3000
30 7499 ALLEN 20-2月 -81 1600 1600 1600
30 7521 WARD 22-2月 -81 1250 1250 1600
30 7698 BLAKE 01-5月 -81 2850 1250 2850
DEPTNO EMPNO ENAME HIREDATE SAL 最低薪水 最高薪水
---------- ---------- ---------- -------------- ---------- ---------- ----------
30 7844 TURNER 08-9月 -81 1500 1250 2850
30 7654 MARTIN 28-9月 -81 1250 1250 2850
30 7900 JAMES 03-12月-81 950 950 2850
已选择14行。
11.--分析函数列表rank,dense_rank和row_number函数
SQL> select deptno,
2 ename,
3 sal,
4 --mgr,
5 rank() over(order by deptno) rank结果， dense_rank() over(order by d
eptno) dense_rank结果,
6 row_number() over(order by deptno) row_number结果
7 from emp
8 where deptno in (10, 20, 30);
DEPTNO ENAME SAL RANK结果 DENSE_RANK结果 ROW_NUMBER结果
---------- ---------- ---------- ---------- -------------- --------------
10 CLARK 2450 1 1 1
10 KING 5000 1 1 2
10 MILLER 1300 1 1 3
20 JONES 2975 4 2 4
20 FORD 3000 4 2 5
20 ADAMS 1100 4 2 6
20 SMITH 800 4 2 7
20 SCOTT 3000 4 2 8
30 WARD 1250 9 3 9
30 TURNER 1500 9 3 10
30 ALLEN 1600 9 3 11
DEPTNO ENAME SAL RANK结果 DENSE_RANK结果 ROW_NUMBER结果
---------- ---------- ---------- ---------- -------------- --------------
30 JAMES 950 9 3 12
30 BLAKE 2850 9 3 13
30 MARTIN 1250 9 3 14
已选择14行。
12_分析函数列表first和last函数
SQL> select deptno,
2 min(sal) keep(dense_rank first order by comm) 最低提成薪水,
3 max(sal) keep(dense_rank last order by comm) 最高提成薪水
4 from emp
5 group by deptno;
DEPTNO 最低提成薪水 最高提成薪水
---------- ------------ ------------
10 1300 5000
20 800 3000
30 1500 2850
--13.first_value和last_value函数
SQL> select deptno,
2 empno,
3 sal,
4 first_value(sal) over(partition by deptno order by empno) "第一个值
,
5 last_value(sal) over(partition by deptno order by empno) "最后一个值
"
6 from emp;
DEPTNO EMPNO SAL 第一个值 最后一个值
---------- ---------- ---------- ---------- ----------
10 7782 2450 2450 2450
10 7839 5000 2450 5000
10 7934 1300 2450 1300
20 7369 800 800 800
20 7566 2975 800 2975
20 7788 3000 800 3000
20 7876 1100 800 1100
20 7902 3000 800 3000
30 7499 1600 1600 1600
30 7521 1250 1600 1250
30 7654 1250 1600 1250
DEPTNO EMPNO SAL 第一个值 最后一个值
---------- ---------- ---------- ---------- ----------
30 7698 2850 1600 2850
30 7844 1500 1600 1500
30 7900 950 1600 950
已选择14行。
14.--lag和lead函数
--lag的功能是返回指定列col前n1行的值(如果前n1行已经超出比照范围，则返回n2,如果不指定n2则默认返回null),
--如果不指定n1，其默认值为1,lead函数与此相反，返回指定列col1后面的n1行的
--使用lag和lead函数查找当前雇员的前一个雇员的薪水后和后一个雇员薪水
SQL> select ename,
2 hiredate,
3 sal,
4 deptno,
5 lag(sal, 1, 0) over(order by hiredate) as "前一个雇员薪水",
6 lead(sal, 1, 0) over(order by hiredate) as "后一个雇员薪水"
7 from emp
8 where deptno = 30;
ENAME HIREDATE SAL DEPTNO 前一个雇员薪水 后一个雇员薪水
---------- -------------- ---------- ---------- -------------- --------------
ALLEN 20-2月 -81 1600 30 0 1250
WARD 22-2月 -81 1250 30 1600 2850
BLAKE 01-5月 -81 2850 30 1250 1500
TURNER 08-9月 -81 1500 30 2850 1250
MARTIN 28-9月 -81 1250 30 1500 950
JAMES 03-12月-81 950 30 1250 0
已选择6行。
--15.分析函数使用示例 记录排名
SQL> select deptno,
2 -- empno,
3 ename,
4 sum(sal) dept_sales,
5 rank() over(partition by deptno order by sum(sal) desc nulls last)
薪资排名_跳号,
6 dense_rank() over(partition by deptno order by sum(sal) desc nulls l
ast) 薪资排名_同级同号,
7 row_number() over(partition by deptno order by sum(sal) desc nulls l
ast) 薪资排名_不跳号
8 from emp
9 group by deptno, empno, ename;
DEPTNO ENAME DEPT_SALES 薪资排名_跳号 薪资排名_同级同号 薪资排名_不跳号
---------- ---------- ---------- ------------- ----------------- ---------------
10 KING 5000 1 1 1
10 CLARK 2450 2 2 2
10 MILLER 1300 3 3 3
20 FORD 3000 1 1 1
20 SCOTT 3000 1 1 2
20 JONES 2975 3 2 3
20 ADAMS 1100 4 3 4
20 SMITH 800 5 4 5
30 BLAKE 2850 1 1 1
30 ALLEN 1600 2 2 2
30 TURNER 1500 3 3 3
DEPTNO ENAME DEPT_SALES 薪资排名_跳号 薪资排名_同级同号 薪资排名_不跳号
---------- ---------- ---------- ------------- ----------------- ---------------
30 MARTIN 1250 4 4 4
30 WARD 1250 4 4 5
30 JAMES 950 6 5 6
已选择14行。
16.--分析函数使用 首尾记录查询
SQL> select min(empno) keep(dense_rank first order by sum(sal) desc nulls last)
薪资排名首位， max(empno) keep(dense_rank last order by sum(sal) desc nulls last
) 薪资排名尾位
2 from emp
3 where sal is not null
4 and deptno is not null
5 group by empno;
薪资排名首位 薪资排名尾位
------------ ------------
7839 7369
--17.分析函数使用 前后排名查询
SQL> select min(empno) keep(dense_rank first order by sum(sal) desc nulls last)
薪资排名首位， max(empno) keep(dense_rank last order by sum(sal) desc nulls last
) 薪资排名尾位
2 from emp
3 where sal is not null
4 and deptno is not null
5 group by empno;
薪资排名首位 薪资排名尾位
------------ ------------
7839 7369
--18.分析函数使用示例 层次查询
SQL> select *
2 from (select deptno,
3 empno,
4 ename,
5 sum(sal) dept_sales,
6 ntile(3) over(partition by deptno order by sum(sal) nulls la
st) rank_ration
7 from emp
8 where deptno is not null
9 group by deptno, empno, ename)
10 where rank_ration = 1;
DEPTNO EMPNO ENAME DEPT_SALES RANK_RATION
---------- ---------- ---------- ---------- -----------
10 7934 MILLER 1300 1
20 7369 SMITH 800 1
20 7876 ADAMS 1100 1
30 7900 JAMES 950 1
30 7654 MARTIN 1250 1
19.--范围统计查询
--范围查询是分析函数发挥其功能的重点，范围查询是指查询当前记录的前面或后面的记录进行统计，比如想知道
--员工史密斯的雇佣日期前10天和后10天新进员工的最高薪资，可以使用分析函数来实现这个功能
SQL> select empno,
2 --ename,
3 hiredate,
4 sal,
5 max(sal) over(order by hiredate rows between 10 preceding and curren
t row) "前10天入职最高薪资",
6 max(sal) over(order by hiredate rows between current row and 10 foll
owing) "后10天入职最高薪资"
7 from emp
8 where sal is not null;
EMPNO HIREDATE SAL 前10天入职最高薪资 后10天入职最高薪资
---------- -------------- ---------- ------------------ ------------------
7369 17-12月-80 800 800 5000
7499 20-2月 -81 1600 1600 5000
7521 22-2月 -81 1250 1600 5000
7566 02-4月 -81 2975 2975 5000
7698 01-5月 -81 2850 2975 5000
7782 09-6月 -81 2450 2975 5000
7844 08-9月 -81 1500 2975 5000
7654 28-9月 -81 1250 2975 5000
7839 17-11月-81 5000 5000 5000
7900 03-12月-81 950 5000 3000
7902 03-12月-81 3000 5000 3000
EMPNO HIREDATE SAL 前10天入职最高薪资 后10天入职最高薪资
---------- -------------- ---------- ------------------ ------------------
7934 23-1月 -82 1300 5000 3000
7788 19-4月 -87 3000 5000 3000
7876 23-5月 -87 1100 5000 1100
已选择14行。
--20.相邻记录比较
SQL> select ename,
2 hiredate,
3 deptno,
4 sal,
5 sal - prev_sal "与前面的差异"，sal - next_sal "与后面的差异"
6 from (select ename,
7 hiredate,
8 sal,
9 deptno,
10 lag(sal, 1, 0) over(order by hiredate) as prev_sal,
11 lead(sal, 1, 0) over(order by hiredate) as next_sal
12 from emp
13 where deptno is not null
14 and sal is not null);
ENAME HIREDATE DEPTNO SAL 与前面的差异 与后面的差异
---------- -------------- ---------- ---------- ------------ ------------
SMITH 17-12月-80 20 800 800 -800
ALLEN 20-2月 -81 30 1600 800 350
WARD 22-2月 -81 30 1250 -350 -1725
JONES 02-4月 -81 20 2975 1725 125
BLAKE 01-5月 -81 30 2850 -125 400
CLARK 09-6月 -81 10 2450 -400 950
TURNER 08-9月 -81 30 1500 -950 250
MARTIN 28-9月 -81 30 1250 -250 -3750
KING 17-11月-81 10 5000 3750 4050
JAMES 03-12月-81 30 950 -4050 -2050
FORD 03-12月-81 20 3000 2050 1700
ENAME HIREDATE DEPTNO SAL 与前面的差异 与后面的差异
---------- -------------- ---------- ---------- ------------ ------------
MILLER 23-1月 -82 10 1300 -1700 -1700
SCOTT 19-4月 -87 20 3000 1700 1900
ADAMS 23-5月 -87 20 1100 -1900 1100
已选择14行。
21.--抑制重复
SQL> select *
2 from (select empno,
3 ename,
4 sal,
5 hiredate,
6 row_number() over(partition by extract(year from hiredate)
rder by empno) rn
7 from emp
8 where hiredate is not null
9 and extract(year from hiredate) in (1981, 19872, 1983))
10 where rn = 1;
EMPNO ENAME SAL HIREDATE RN
---------- ---------- ---------- -------------- ----------
7499 ALLEN 1600 20-2月 -81 1
22.--行列转换
SQL> select job,
2 empno,
3 ename,
4 row_number() over(partition by job order by ename) rb
5 from emp
6 where job is not null;
JOB EMPNO ENAME RB
--------- ---------- ---------- ----------
ANALYST 7902 FORD 1
ANALYST 7788 SCOTT 2
CLERK 7876 ADAMS 1
CLERK 7900 JAMES 2
CLERK 7934 MILLER 3
CLERK 7369 SMITH 4
MANAGER 7698 BLAKE 1
MANAGER 7782 CLARK 2
MANAGER 7566 JONES 3
PRESIDENT 7839 KING 1
SALESMAN 7499 ALLEN 1
JOB EMPNO ENAME RB
--------- ---------- ---------- ----------
SALESMAN 7654 MARTIN 2
SALESMAN 7844 TURNER 3
SALESMAN 7521 WARD 4
已选择14行。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。