主页 / dba / 问题 / 653
Accepted
Sathyajith Bhat
Asked: 2011-01-18 06:21:58 +0800 CST

计算一行占总和的百分比

  • 772

抱歉标题不好，我不确定什么是好的标题。

这是我正在使用的当前（简化视图）数据

Agent    |  Commission     
---------|------------
Smith    |    100
Neo      |    200
Morpheus |    300

我需要计算佣金总额的百分比，每个代理负责。

因此，对于特工史密斯，百分比将计算为(Agent Smith's commission / Sum(commission)*100

所以，我的预期数据是

Agent    |  Commission   |  % Commission    
---------|---------------|---------------
Smith    |    100        |     17
Neo      |    200        |     33
Morpheus |    300        |     50

我有一个函数返回每个代理的佣金。我有另一个函数将百分比返回为(Commission/Sum(Commission))*100. 问题是Sum(commission)对每一行都进行计算，并且鉴于此查询将在数据仓库上运行，数据集将相当大（目前，它不到 2000 条记录）而且老实说，这是一种糟糕的方法（IMO ）。

有没有一种方法可以Sum(Commission)不计算要获取的每一行？

我在考虑两部分查询的内容，第一部分会将其提取sum(commission)到包变量/类型中，第二部分将引用这个预先计算的值，但我不确定如何实现这一点。

我仅限于使用 SQL，并且我在 Oracle 10g R2 上运行。

aggregate oracle-10g-r2

4 个回答

  1. Best Answer

    您正在寻找analytical function ratio_to_report

    select 
  agent,
  round(ratio_to_report(commission) over ()*100) "% Comm."
from  
  commissions;
    • 24

  2. 要返回所有代理及其佣金和佣金百分比，请使用不带分析子句的分析函数，以便分区覆盖整个表：

    SELECT Agent, commission, 100* commission / (SUM(commission) OVER ()) "% Commission" 
FROM commissions;

    正如我从 René Nyffenegger (+1) 那里了解到的， ratio_to_report 函数收紧了这种语法。

    使用包存储佣金 SUM 将涉及 PL/SQL，您通过表明您需要 SQL 解决方案来明确排除它，但由于您已经在使用函数，我假设您的意图不是排除 PL/SQL。如果是这种情况，那么包解决方案可能会有所帮助，但这取决于您的应用程序的工作方式。

    当您的会话首次创建并调用包中的函数以获取佣金时，会隐式调用包构造函数，该构造函数可以获取总和并将其存储。然后你可以在你的获取佣金函数中引用存储的总和，它只需要计算一次总和。当然，一旦您从不同的会话中调用该函数，就会再次计算总和。此外，如果您的应用程序可以以这种方式设计，那么为每个代理调用该函数的效率将大大低于为所有代理调用一条 SQL 语句的效率。

    您可能需要考虑将您的函数转换为为上述查询返回游标的过程，或者可能有一个函数将查询结果作为流水线结果集返回。

    样本数据：

    create table commissions (Agent Varchar2(100), Commission Number(3));
insert into commissions values ('Smith',100);
insert into commissions values ('Neo',200);
insert into commissions values ('Morpheus',300);
    • 9

  3. 您可以尝试以下查询， sum(commission) 只会计算一次：

    WITH TOTAL_COMMISSION AS 
(SELECT SUM(COMMISSION) AS TOTAL FROM AGENTS)
SELECT A.AGENT_NAME, A.COMMISSION, ((A.COMMISSION/T.TOTAL)*100) AS "% COMMISSION"
FROM AGENTS A, TOTAL_COMMISSION T;
    • 5 
  4.   select 
  Agent, Commission,
  (
      ROUND(
       (Commission *100) / 
          (
            (SELECT SUM(Commission)
             FROM commissions AS A)
          )
       ) 
  ) AS Porcentaje
  from  
  commissions
    • 0

相关问题