【SQL】分组再聚合&ratio_to_report函数

最新推荐文章于 2024-05-27 17:06:53 发布

q3dxdx

最新推荐文章于 2024-05-27 17:06:53 发布

阅读量1.8k

点赞数

分类专栏： Oracle数据库-SQL

本文链接：https://blog.csdn.net/q3dxdx/article/details/49226525

版权

Oracle数据库-SQL 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

如下表记录：

select empno, ename, deptno, sal from emp_copy order by deptno asc;

结果如下：

我们可以很轻松地，使用sum()函数求出薪水的总和，再结合group by按部门进行分组：

SQL> select deptno, sum(sal) from emp_copy group by deptno;
DEPTNO SUM(SAL)
------ ----------
30 9500
20 11115
10 8750

SQL>

如果我们要求得每个员工的薪水在部门内薪水总和的占比，那么可以再次关联原表。可以这样写：

SQL> select t1.empno,
  2         t1.ename,
  3         t1.deptno,
  4         t1.sal,
  5         t2.sal_sum,
  6         t1.sal / t2.sal_sum as ratio
  7    from emp_copy t1,
  8         (select deptno, sum(sal) as sal_sum from emp_copy group by deptno) t2
  9   where t1.deptno = t2.deptno
 10   order by deptno;
EMPNO ENAME DEPTNO       SAL    SAL_SUM      RATIO
----- ---------- ------ --------- ---------- ----------
 7782 CLARK     10   2450.00       8750       0.28
 7839 KING       10   5000.00       8750 0.57142857
 7934 MILLER     10   1300.00       8750 0.14857142
 7369 SMITH     20   1040.00      11115 0.09356725
 7566 JONES     20   2975.00      11115 0.26765632
 7788 SCOTT     20   3000.00      11115 0.26990553
 7876 ADAMS     20   1100.00      11115 0.09896536
 7902 FORD       20   3000.00      11115 0.26990553
 7499 ALLEN     30   1600.00       9500 0.16842105
 7521 WARD     30   1250.00       9500 0.13157894
 7654 MARTIN     30   1250.00       9500 0.13157894
 7698 BLAKE      30   2850.00       9500        0.3
 7844 TURNER     30   1600.00       9500 0.16842105
 7900 JAMES     30    950.00       9500        0.1
14 rows selected

SQL>

其实，更简便的方法，我们可以这样写：

SQL> select deptno,
  2         empno,
  3         ename,
  4         sal,
  5         sum(sal) over(partition by deptno) as sum_sal,
  6         sal / (sum(sal) over(partition by deptno)) as ratio
  7    from emp_copy;
DEPTNO EMPNO ENAME       SAL    SUM_SAL      RATIO
------ ----- ---------- --------- ---------- ----------
    10  7782 CLARK   2450.00       8750       0.28
    10  7839 KING     5000.00       8750 0.57142857
    10  7934 MILLER   1300.00       8750 0.14857142
    20  7566 JONES   2975.00      11115 0.26765632
    20  7902 FORD     3000.00      11115 0.26990553
    20  7876 ADAMS   1100.00      11115 0.09896536
    20  7369 SMITH   1040.00      11115 0.09356725
    20  7788 SCOTT   3000.00      11115 0.26990553
    30  7521 WARD   1250.00       9500 0.13157894
    30  7844 TURNER   1600.00       9500 0.16842105
    30  7499 ALLEN   1600.00       9500 0.16842105
    30  7900 JAMES    950.00       9500        0.1
    30  7698 BLAKE    2850.00       9500        0.3
    30  7654 MARTIN   1250.00       9500 0.13157894
14 rows selected

SQL>

更有甚者，还可以使用ratio_to_report函数：

SQL> select deptno,
  2         empno,
  3         ename,
  4         sal,
  5         sum(sal) over(partition by deptno) as sum_sal,
  6         ratio_to_report(sal) over(partition by deptno) as ratio
  7    from emp_copy;
DEPTNO EMPNO ENAME       SAL    SUM_SAL      RATIO
------ ----- ---------- --------- ---------- ----------
    10  7782 CLARK   2450.00       8750       0.28
    10  7839 KING     5000.00       8750 0.57142857
    10  7934 MILLER   1300.00       8750 0.14857142
    20  7566 JONES   2975.00      11115 0.26765632
    20  7902 FORD     3000.00      11115 0.26990553
    20  7876 ADAMS   1100.00      11115 0.09896536
    20  7369 SMITH   1040.00      11115 0.09356725
    20  7788 SCOTT   3000.00      11115 0.26990553
    30  7521 WARD   1250.00       9500 0.13157894
    30  7844 TURNER   1600.00       9500 0.16842105
    30  7499 ALLEN   1600.00       9500 0.16842105
    30  7900 JAMES    950.00       9500        0.1
    30  7698 BLAKE    2850.00       9500        0.3
    30  7654 MARTIN   1250.00       9500 0.13157894
14 rows selected

SQL>

这个是分组内求sum，同理可以求avg、max以及min。