Lila, miembro de nuestro equipo, nos ha hecho el favor de escribir este post ya que conoce a la perfección dicho tema.
Solución:
estas buscando el analytical functionratio_to_report
select
agent,
round(ratio_to_report(commission) over ()*100) "% Comm."
from
commissions;
Para devolver todos los agentes con sus comisiones y porcentajes de comisión, use una función analítica sin cláusula analítica para que la partición sea sobre toda la tabla:
SELECT Agent, commission, 100* commission / (SUM(commission) OVER ()) "% Commission"
FROM commissions;
Como aprendí de René Nyffenegger (+1), la función ratio_to_report ajusta esta sintaxis.
El uso de un paquete para almacenar la SUM de la Comisión implicaría PL/SQL, que excluyó específicamente al indicar que desea una solución SQL, pero dado que ya está utilizando funciones, asumo que su intención no era excluir PL/SQL. Si este es el caso, entonces la solución del paquete puede ayudar, pero depende de cómo funcione su aplicación.
Cuando su sesión se crea por primera vez y llama a la función en el paquete para obtener la comisión, hay una llamada implícita al constructor de paquetes que podría obtener la suma y almacenarla. Luego, podría hacer referencia a la suma almacenada en su función de obtener comisión y solo tendría que hacer la suma una vez. Por supuesto, tan pronto como llame a la función desde una sesión diferente, la suma se calculará nuevamente. Además, llamar a la función para cada agente sería considerablemente menos eficiente que llamar a una instrucción SQL para todos los agentes si su aplicación puede diseñarse de esa manera.
Es posible que desee considerar convertir su función en un procedimiento que devuelva un cursor para la consulta anterior o tal vez tener una función que devuelva los resultados de la consulta como un conjunto de resultados canalizados.
Data de muestra:
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);
Puede intentar la siguiente consulta, la suma (comisión) solo se calculará una 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;
Reseñas y puntuaciones del artículo
Si aceptas, tienes la libertad de dejar un artículo acerca de qué le añadirías a este tutorial.