Início / dba / Perguntas / 653
Accepted
Sathyajith Bhat
Asked: 2011-01-18 06:21:58 +0800 CST

Calculando a porcentagem de uma linha sobre a soma total

  • 772

Desculpas pelo título ruim, eu não tinha certeza do que seria um bom título para isso.

Este é atualmente (visualização simplificada dos) dados com os quais estou trabalhando

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

Preciso calcular a porcentagem da comissão total, pela qual cada agente é responsável.

Assim, para o Agente Smith, a Porcentagem seria calculada como(Agent Smith's commission / Sum(commission)*100

Então, meus dados esperados seriam

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

Eu tenho uma função retornando a comissão para cada agente. Eu tenho outra função retornando a porcentagem como (Commission/Sum(Commission))*100. O problema é que Sum(commission)é calculado para cada linha e, como essa consulta seria executada em um Data Warehouse, o conjunto de dados seria bastante grande (atualmente, são pouco menos de 2000 registros) e, honestamente, uma abordagem ruim (IMO ).

Existe uma maneira de Sum(Commission)não calcular para cada linha que está sendo buscada?

Eu estava pensando em algo nas linhas de uma consulta de 2 partes, a primeira parte buscaria sum(commission)em uma variável/tipo de pacote e a segunda parte se referiria a esse valor pré-calculado, mas não tenho certeza de como posso fazer isso.

Estou limitado a usar SQL e estou executando o Oracle 10g R2.

aggregate oracle-10g-r2

4 respostas

  1. Best Answer

    Você está procurando o analytical function ratio_to_report

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

  2. Para retornar todos os agentes com suas comissões e porcentagens de comissão, use uma função analítica sem cláusula analítica para que a partição seja sobre toda a tabela:

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

    Como aprendi com René Nyffenegger (+1), a função ratio_to_report aperta essa sintaxe.

    Usar um pacote para armazenar o Commission SUM envolveria PL/SQL, que você excluiu especificamente indicando que deseja uma solução SQL, mas como você já está usando funções, suponho que sua intenção não era excluir PL/SQL. Se for esse o caso, a solução de pacote pode ajudar, mas depende de como seu aplicativo funciona.

    Quando sua sessão é criada pela primeira vez e chama a função no pacote para obter a comissão, há uma chamada implícita para o construtor de pacotes que pode obter a soma e armazená-la. Então você poderia referenciar a soma armazenada em sua função de obter comissão e ela só teria que fazer a soma uma vez. É claro que assim que você chamar a função de uma sessão diferente, a soma será calculada novamente. Além disso, chamar a função para cada agente seria consideravelmente menos eficiente do que chamar uma instrução SQL para todos os agentes se seu aplicativo puder ser projetado dessa maneira.

    Você pode querer considerar transformar sua função em um procedimento que retorne um cursor para a consulta acima ou talvez tenha uma função que retorne os resultados da consulta como um conjunto de resultados em pipeline.

    Dados de amostra:

    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. Você pode tentar a seguinte consulta, soma (comissão) será calculada apenas uma vez:

    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

relate perguntas