Obliczanie procentu rzędu w stosunku do sumy całkowitej

Przepraszam za zły tytuł, nie byłem pewien, jaki byłby dobry tytuł do tego.

To obecnie (uproszczony widok) danych, z którymi pracuję

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

Muszę obliczyć procent całkowitej prowizji, za którą odpowiedzialny jest każdy agent.

Tak więc, dla Agenta Smitha, Procent zostanie obliczony jako (Agent Smith's commission / Sum(commission)*100

Więc moje oczekiwane dane będą

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

Mam funkcję zwracającą prowizję dla każdego agenta. Mam inną funkcję zwracającą procent jako (Commission/Sum(Commission))*100. Problem polega na tym, że Sum(commission)oblicza się dla każdego wiersza, a biorąc pod uwagę, że to zapytanie byłoby uruchamiane w hurtowni danych, zestaw danych byłby raczej duży (obecnie jest to nieco mniej niż 2000 rekordów) i całkiem szczerze mówiąc, złe podejście (IMO ).

Czy istnieje sposób, aby Sum(Commission)nie obliczać dla każdego pobieranego wiersza?

Myślałem coś w wierszach dwuczęściowego zapytania, pierwsza część pobierałaby sum(commission)zmienną / typ pakietu, a druga część odnosiła się do tej wstępnie obliczonej wartości, ale nie jestem pewien, jak to osiągnąć.

Ograniczam się do używania SQL i korzystam z Oracle 10g R2.

Szukasz analytical function stosunku_do_reportu

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

Aby zwrócić wszystkim agentom ich prowizje i procenty prowizji, użyj funkcji analitycznej bez klauzuli analitycznej, aby partycja znajdowała się nad całą tabelą:

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

Jak dowiedziałem się od René Nyffeneggera (+1), funkcja ratio_to_report zaostrza tę składnię.

Używanie pakietu do przechowywania SUM prowizji wymagałoby PL / SQL, który specjalnie wykluczyłeś wskazując, że chcesz rozwiązania SQL, ale ponieważ już używasz funkcji, zakładam, że nie zamierzałeś wykluczać PL / SQL. W takim przypadku rozwiązanie pakietowe może pomóc, ale zależy to od sposobu działania aplikacji.

Kiedy twoja sesja jest tworzona po raz pierwszy i wywołuje funkcję w pakiecie, aby uzyskać prowizję, następuje niejawne wywołanie konstruktora pakietów, które może pobrać sumę i ją zapisać. Następnie możesz odwołać się do zapisanej sumy w funkcji get prowizji i będzie musiał wykonać sumę tylko raz. Oczywiście, jak tylko wywołasz funkcję z innej sesji, suma zostanie obliczona ponownie. Wywołanie funkcji dla każdego agenta byłoby znacznie mniej wydajne niż wywołanie jednej instrukcji SQL dla wszystkich agentów, jeśli aplikacja może być zaprojektowana w ten sposób.

Możesz rozważyć przekształcenie funkcji w procedurę, która zwraca kursor dla powyższego zapytania, lub być może masz funkcję, która zwraca wyniki zapytania jako zestaw wyników w potoku.

Przykładowe dane:

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);

Możesz wypróbować następujące zapytanie, suma (prowizja) zostanie obliczona tylko raz:

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;

  select 
  Agent, Commission,
  (
      ROUND(
       (Commission *100) / 
          (
            (SELECT SUM(Commission)
             FROM commissions AS A)
          )
       ) 
  ) AS Porcentaje
  from  
  commissions