在做报表时,有时需要计算“占比”这个指标。
举个例子,统计每个员工的薪资占部门薪资总和的比重。
在 SQL 中,计算占比有很多种写法,本文将列出常见的几种写法。
就用前面举的例子做演示,刚好,emp 表的数据就能符合我们的要求。
先来看看 emp 表的数据 >>>
empno ename sal deptno ------ ------ ------- -------- 7782 CLARK 2450.00 10 7839 KING 5000.00 10 7934 MILLER 1300.00 10 7369 SMITH 800.00 20 7566 JONES 2975.00 20 7788 SCOTT 3000.00 20 7876 ADAMS 1100.00 20 7902 FORD 3000.00 20 7499 ALLEN 1600.00 30 7521 WARD 1250.00 30 7654 MARTIN 1250.00 30 7698 BLAKE 2850.00 30 7844 TURNER 1500.00 30 7900 JAMES 950.00 30
毋庸置疑,不管使用哪种方法,要计算占比,都要先计算出部门的合计薪资。
统计各个部门的合计薪资,SQL 可以就这么写:
SELECT deptno, SUM(sal) AS sal_dept FROM emp GROUP BY deptno
在 emp 表的基础上,再去关联部门合计薪资的结果集,就能算出每个员工的薪资占部门薪资合计的比重。
SELECT empno, ename, sal, sal / sal_dept AS ratio, a.deptno FROM emp a INNER JOIN (SELECT deptno, SUM(sal) AS sal_dept FROM emp GROUP BY deptno) b ON b.deptno = a.deptno ;
计算结果展示 >>>
empno ename sal ratio deptno ------ ------ ------- -------- -------- 7782 CLARK 2450.00 0.280000 10 7839 KING 5000.00 0.571429 10 7934 MILLER 1300.00 0.148571 10 7369 SMITH 800.00 0.073563 20 7566 JONES 2975.00 0.273563 20 7788 SCOTT 3000.00 0.275862 20 7876 ADAMS 1100.00 0.101149 20 7902 FORD 3000.00 0.275862 20 7499 ALLEN 1600.00 0.170213 30 7521 WARD 1250.00 0.132979 30 7654 MARTIN 1250.00 0.132979 30 7698 BLAKE 2850.00 0.303191 30 7844 TURNER 1500.00 0.159574 30 7900 JAMES 950.00 0.101064 30
有的朋友觉得单独统计每个部门的合计薪资再关联原表的写法太麻烦了,那也可以选择标量子查询的写法。
SELECT empno, ename, sal, sal / (SELECT SUM(sal) FROM emp WHERE deptno = a.deptno) AS ratio, deptno FROM emp a
不关联是否也能实现呢?试试窗口函数。
SELECT empno, ename, sal, sal / SUM(sal) over (PARTITION BY deptno) AS ratio, deptno FROM emp ;