SQL 计算比重|极客教程

SQL 计算比重，报表中有一组数字值，你想同时显示每个值占总数的百分比。例如，你使用的是 Oracle 数据库，你希望返回一个按照 JOB 维度计算出来的工资分布情况，这样就能判断出哪些 JOB 耗费了公司最多的钱。你也希望把从事每种 JOB 的员工人数包括进来，以避免最终结果集中出现的百分比产生误导。

SQL 计算比重问题描述

报表中有一组数字值，你想同时显示每个值占总数的百分比。例如，你使用的是 Oracle 数据库，你希望返回一个按照 JOB 维度计算出来的工资分布情况，这样就能判断出哪些 JOB 耗费了公司最多的钱。你也希望把从事每种 JOB 的员工人数包括进来，以避免最终结果集中出现的百分比产生误导。你希望得到如下所示的报表。

JOB         NUM_EMPS PCT_OF_ALL_SALARIES
--------- ---------- -------------------
CLERK              4                  14
ANALYST            2                  20
MANAGER            3                  28
SALESMAN           4                  19
PRESIDENT          1                  17

如上所述，如果报表中没有包含员工人数，看起来似乎总经理的工资比例并不高。加入员工人数后，我们才清楚地看到总经理一个人的工资竟然占了总数的 17%。

SQL 计算比重解决方案

对于本问题而言，只有 Oracle 提供了一个合适的解决方案，因为 Oracle 支持内置函数 RATIO_TO_REPORT。对于其他数据库，为了计算比重，不妨使用除法，请参考 7.11 节。

1  select job,num_emps,sum(round(pct)) pct_of_all_salaries
2    from (
3  select job,
4         count(*)over(partition by job) num_emps,
5         ratio_to_report(sal)over()*100 pct
6    from emp
7          )
8   group by job,num_emps

SQL 计算比重扩展知识

首先使用窗口函数 COUNT OVER 计算每个 JOB 对应的员工人数。然后使用 RATIO_TO_REPORT 计算每个员工的工资占总数的百分比（该值以小数形式返回）。

select job,
       count(*)over(partition by job) num_emps,
       ratio_to_report(sal)over()*100 pct
  from emp
　
JOB         NUM_EMPS        PCT
--------- ---------- ----------
ANALYST            2 10.3359173
ANALYST            2 10.3359173
CLERK              4 2.75624462
CLERK              4 3.78983635
CLERK              4  4.4788975
CLERK              4 3.27304048
MANAGER            3 10.2497847
MANAGER            3 8.44099914
MANAGER            3 9.81912145
PRESIDENT          1 17.2265289
SALESMAN           4 5.51248923
SALESMAN           4 4.30663221
SALESMAN           4 5.16795866
SALESMAN           4 4.30663221

最后，使用聚合函数 SUM 计算 RATIO_TO_REPORT 函数的返回值的合计值。不要忘记根据 JOB 和 NUM_EMPS 分组。另外，要乘以 100 才能得到一个代表百分比的整数（例如，对于 25% 来说，应该返回的是 25 而不是 0.25）：

select job,num_emps,sum(round(pct)) pct_of_all_salaries
  from (
select job,
       count(*)over(partition by job) num_emps,
       ratio_to_report(sal)over()*100 pct
  from emp
       )
 group by job,num_emps
　
JOB         NUM_EMPS PCT_OF_ALL_SALARIES
--------- ---------- -------------------
CLERK              4                  14
ANALYST            2                  20
MANAGER            3                  28
SALESMAN           4                  19
PRESIDENT          1                  17