Oracle分析函数学习

最新推荐文章于 2021-10-28 12:14:47 发布

jxzkin

最新推荐文章于 2021-10-28 12:14:47 发布

阅读量679

点赞数

分类专栏： ORACLE Management

ORACLE Management 专栏收录该内容

78 篇文章 0 订阅

订阅专栏

http://f.dataguru.cn/thread-41808-1-1.html

row_number() over ([partition by col1] order by col2) )

1.

SQL> SELECT deptno 部门,
  ename 姓名,
  SAL 薪水,
  SUM(SAL) OVER (ORDER BY ENAME) 累加,
  SUM(SAL) OVER () 总和,
  100*ROUND(SAL/SUM(SAL) OVER (),4) "比率(%)"
FROM emp;


      部门 姓名 		      薪水	 累加	    总和    比率(%)
---------- -------------------- ---------- ---------- ---------- ----------
	20 ADAMS		      1100	 1100	   29025       3.79
	30 ALLEN		      1600	 2700	   29025       5.51
	30 BLAKE		      2850	 5550	   29025       9.82
	10 CLARK		      2450	 8000	   29025       8.44
	20 FORD 		      3000	11000	   29025      10.34
	30 JAMES		       950	11950	   29025       3.27
	20 JONES		      2975	14925	   29025      10.25
	10 KING 		      5000	19925	   29025      17.23
	30 MARTIN		      1250	21175	   29025       4.31
	10 MILLER		      1300	22475	   29025       4.48
	20 SCOTT		      3000	25475	   29025      10.34
	20 SMITH		       800	26275	   29025       2.76
	30 TURNER		      1500	27775	   29025       5.17
	30 WARD 		      1250	29025	   29025       4.31


14 rows selected.

select deptno,ename,sal,
sum(sal) over (partition by deptno order by ename) 部门连续求和,--partition by deptno先按部门分组，再按姓名排序，并将薪水逐个累加
sum(sal) over (partition by deptno) 部门总和,   -- 每个部门的薪水总和
100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",--每个员工在各自部门的薪水比例
sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
sum(sal) over () 总和,   -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
100*round(sal/sum(sal) over (),4) "总份额(%)" --求每个人的薪水占总额的比例
from emp；


    DEPTNO ENAME		       SAL 部门连续求和   部门总和 部门份额(%)	 连续求和	总和  总份额(%)
---------- -------------------- ---------- ------------ ---------- ----------- ---------- ---------- ----------
	10 CLARK		      2450	   2450       8750	    28	     2450      29025	   8.44
	10 KING 		      5000	   7450       8750	 57.14	     7450      29025	  17.23
	10 MILLER		      1300	   8750       8750	 14.86	     8750      29025	   4.48
	20 ADAMS		      1100	   1100      10875	 10.11	     9850      29025	   3.79
	20 FORD 		      3000	   4100      10875	 27.59	    12850      29025	  10.34
	20 JONES		      2975	   7075      10875	 27.36	    15825      29025	  10.25
	20 SCOTT		      3000	  10075      10875	 27.59	    18825      29025	  10.34
	20 SMITH		       800	  10875      10875	  7.36	    19625      29025	   2.76
	30 ALLEN		      1600	   1600       9400	 17.02	    21225      29025	   5.51
	30 BLAKE		      2850	   4450       9400	 30.32	    24075      29025	   9.82
	30 JAMES		       950	   5400       9400	 10.11	    25025      29025	   3.27
	30 MARTIN		      1250	   6650       9400	  13.3	    26275      29025	   4.31
	30 TURNER		      1500	   8150       9400	 15.96	    27775      29025	   5.17
	30 WARD 		      1250	   9400       9400	  13.3	    29025      29025	   4.31


14 rows selected.

select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,--根据部门分组，再按部门内的个人薪水排序，逐个累加。
sum(sal) over (order by deptno,sal) sum --按部门排序，将薪水逐个累加。
from emp;


    DEPTNO ENAME		       SAL   DEPT_SUM	     SUM
---------- -------------------- ---------- ---------- ----------
	10 MILLER		      1300	 1300	    1300
	10 CLARK		      2450	 3750	    3750
	10 KING 		      5000	 8750	    8750
	20 SMITH		       800	  800	    9550
	20 ADAMS		      1100	 1900	   10650
	20 JONES		      2975	 4875	   13625
	20 SCOTT		      3000	10875	   19625
	20 FORD 		      3000	10875	   19625
	30 JAMES		       950	  950	   20575
	30 MARTIN		      1250	 3450	   23075
	30 WARD 		      1250	 3450	   23075
	30 TURNER		      1500	 4950	   24575
	30 ALLEN		      1600	 6550	   26175
	30 BLAKE		      2850	 9400	   29025


14 rows selected.

4.部门从大到小排列，部门里各员工的薪水从高到低排列

select deptno,ename,sal,
   sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,--按部门分组后，按部门和薪水降序排
   sum(sal) over (order by deptno desc,sal desc) sum --按部门和薪水降序排
   from emp;


    DEPTNO ENAME		       SAL   DEPT_SUM	     SUM
---------- -------------------- ---------- ---------- ----------
	30 BLAKE		      2850	 2850	    2850
	30 ALLEN		      1600	 4450	    4450
	30 TURNER		      1500	 5950	    5950
	30 WARD 		      1250	 8450	    8450
	30 MARTIN		      1250	 8450	    8450
	30 JAMES		       950	 9400	    9400
	20 FORD 		      3000	 6000	   15400
	20 SCOTT		      3000	 6000	   15400
	20 JONES		      2975	 8975	   18375
	20 ADAMS		      1100	10075	   19475
	20 SMITH		       800	10875	   20275
	10 KING 		      5000	 5000	   25275
	10 CLARK		      2450	 7450	   27725
	10 MILLER		      1300	 8750	   29025


14 rows selected.

5.将各部门的员工按薪水排序

select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名 --先按部门分组，再在部门中按薪水降序排名
from scott.emp;
ENAME		     JOB		    DEPTNO	  SAL	    排名
-------------------- ------------------ ---------- ---------- ----------
KING		     PRESIDENT			10	 5000	       1
CLARK		     MANAGER			10	 2450	       2
MILLER		     CLERK			10	 1300	       3
SCOTT		     ANALYST			20	 3000	       1
FORD		     ANALYST			20	 3000	       2
JONES		     MANAGER			20	 2975	       3
ADAMS		     CLERK			20	 1100	       4
SMITH		     CLERK			20	  800	       5
BLAKE		     MANAGER			30	 2850	       1
ALLEN		     SALESMAN			30	 1600	       2
TURNER		     SALESMAN			30	 1500	       3
MARTIN		     SALESMAN			30	 1250	       4
WARD		     SALESMAN			30	 1250	       5
JAMES		     CLERK			30	  950	       6


14 rows selected.

6.查找各部门中薪水最高的前2位

select ename,job,deptno,sal,排名 from
(select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名 
from scott.emp                   --先将各部门的员工按薪水排序，再在结果中取出需要的部分
) where 排名<=2;


ENAME		     JOB		    DEPTNO	  SAL	    排名
-------------------- ------------------ ---------- ---------- ----------
KING		     PRESIDENT			10	 5000	       1
CLARK		     MANAGER			10	 2450	       2
SCOTT		     ANALYST			20	 3000	       1
FORD		     ANALYST			20	 3000	       2
BLAKE		     MANAGER			30	 2850	       1
ALLEN		     SALESMAN			30	 1600	       2


6 rows selected.