SQL 计算百分比|极客教程

SQL 计算百分比，你想知道某一列的值占总和的百分比。例如，你希望知道 DEPTNO 等于 10 的部门的工资占全体员工工资的百分比。

SQL 计算百分比问题

你想知道某一列的值占总和的百分比。例如，你希望知道 DEPTNO 等于 10 的部门的工资占全体员工工资的百分比。

SQL 计算百分比解决方案

总体而言，使用 SQL 计算百分比和在纸上手算没什么不同。只需先做除法，再做乘法即可。在本例中，要计算 EMP 表中 DEPTNO 等于 10 的工资额占总体的百分比。先算出 DEPTNO 等于 10 的工资总额，然后再除以表中全部工资的总额，最后乘以 100 以得到一个代表百分比的值。
MySQL 和 PostgreSQL
DEPTNO 等于 10 的工资总额除以全体工资总额。

1 select (sum(
2          case when deptno = 10 then sal end)/sum(sal)
3         )*100 as pct
4   from emp

DB2、Oracle 和 SQL Server
使用内嵌视图和窗口函数 SUM OVER 来得到全体工资总额以及 DEPTNO 等于 10 的工资总额。然后，在外层查询中执行除法和乘法。

1  select distinct (d10/total)*100 as pct
2    from (
3  select deptno,
4         sum(sal)over() total,
5         sum(sal)over(partition by deptno) d10
6    from emp
7         ) x
8   where deptno=10

SQL 计算百分比扩展知识

MySQL 和 PostgreSQL
CASE 语句很容易筛选出 DEPTNO 等于 10 的工资值，把这些数加起来就可以得到工资和，然后除以工资总和。因为聚合函数忽略 Null，所以不需要在 CASE 语句的后面加 ELSE 子句。为了清楚地看到除数和被除数，不妨先去掉除法运算，并执行如下的查询语句。

select sum(case when deptno = 10 then sal end) as d10,
       sum(sal)
  from emp
　
 D10  SUM(SAL)
---- ---------
8750     29025

执行除法运算时可能需要加入显式的类型转换操作，这取决于 SAL 列的类型。例如，对于 DB2、SQL Server 和 PostgreSQL，如果 SAL 列的类型为整型，可以将其转换为十进制小数，以便于得到正确的计算结果，如下所示。

select (cast(
         sum(case when deptno = 10 then sal end)
             as decimal)/sum(sal)
        )*100 as pct
  from emp

DB2、Oracle 和 SQL Server
DB2、Oracle 和 SQL Server 与上述传统的做法不同，下面的解决方案使用窗口函数来计算百分比。对于 DB2 和 SQL Server，如果 SAL 列的类型为整型，需要在进行除法运算前做类型转换。

select distinct
       cast(d10 as decimal)/total*100 as pct
  from (
select deptno,
      sum(sal)over() total,
      sum(sal)over(partition by deptno) d10
 from emp
      ) x
where deptno=10

始终要记住的一点是，WHERE 子句评估完成之后才会执行窗口函数。因而，不能将过滤 DEPTNO 的操作放到内嵌视图 X 里。试想没有 DEPTNO 过滤条件和有该过滤条件，内嵌视图 X 的查询结果集有何异同。首先来看看没有该过滤条件的结果集。

select deptno,
       sum(sal)over() total,
       sum(sal)over(partition by deptno) d10
  from emp
　
 DEPTNO     TOTAL       D10
------- --------- ---------
     10     29025      8750
     10     29025      8750
     10     29025      8750
     20     29025     10875
     20     29025     10875
     20     29025     10875
     20     29025     10875
     20     29025     10875
     30     29025      9400
     30     29025      9400
     30     29025      9400
     30     29025      9400
     30     29025      9400
     30     29025      9400

下面是有过滤条件的结果集。

select deptno,
       sum(sal)over() total,
       sum(sal)over(partition by deptno) d10
  from emp
 where deptno=10
　
DEPTNO     TOTAL       D10
------ --------- ---------
    10      8750      8750
    10      8750      8750
    10      8750      8750

因为要先评估 WHERE 子句再执行窗口函数，所以此处 TOTAL 的计算结果实际上只是 DEPTNO 等于 10 的员工的工资和。但是，我们希望 TOTAL 等于全体员工的工资和。这就是为什么 DEPTNO 的过滤条件要放在内嵌视图 X 之外。