Consultas SQL con la utilidad OVER(PARTITION BY)

Se han preguntado cómo puedo obtener el porcentaje de una fila con relación al gran total?

Bueno, para esto podemos utilizar varias técnicas como realizar sub-consultas, realizar un proceso que llene una temporal y realice los cálculos, pero existe una forma muy sencilla y no compleja de usar que es el comando OVER.

Con el comando OVER podemos obtener resultados de funciones de grupo de una determinada partición de datos.

Como podemos usar el comando OVER?

Para contestar esta pregunta mejor veamos un ejemplo:

EJEMPLO USANDO EL COMANDO OVER

Tenemos información de almacenes vs ventas y deseamos obtener la siguiente información: Almacen, ventas, cantidades y % de Ventas con relación al total.

ALMACEN_ID	VALOR_VENTAS	CANTIDAD_VENTAS	% VENTAS
ALM_30	150.000	15	33,33%
ALM_10	100.000	10	22,22%
ALM_20	200.000	20	44,44%
TOTAL	450.000	45	100,00%

Ahora veamos cómo podemos obtener esta información desde una consulta SQL.

• Creamos la tabla
  
  Create Table Almacenes_Ventas
  (
  Almacen_IdVarchar2 (10),
  Valor_Ventas Number,
  Cantidad_Ventas Number
  )
  
• Insertamos los datos
  
  Insert Into Almacenes_Ventas
  (Almacen_Id,Valor_Ventas, Cantidad_Ventas)
  Values ('ALM_30', 150000, 15);
  Insert Into Almacenes_Ventas
  (Almacen_Id, Valor_Ventas, Cantidad_Ventas)
  Values('ALM_10', 100000, 10);
  Insert Into Almacenes_Ventas
  (Almacen_Id,Valor_Ventas, Cantidad_Ventas)
  Values ('ALM_20',200000, 20);
  Commit;
  
• Realizamos la consulta
  
  Select v.*,
  v.valor_ventas/sum(valor_ventas)over()*100"% Ventas",
  sum(valor_ventas)over()gran_total
  
  From Almacenes_Ventas V
  

    --Resultado

ALMACEN_ID	VALOR_VENTAS	CANTIDAD_VENTAS	% Ventas	GRAN_TOTAL
ALM_30	150.000	15	33,33333333	450.000
ALM_10	100.000	10	22,22222222	450.000
ALM_20	200.000	20	44,44444444	450.000

Vemos que la columna GRAN_TOTAL de la consulta, es el mismo valor en las tres filas ya que indicamos por medio del OVER que es la suma del total de registros en el campo VALOR_VENTAS. Ya con lo definido en con el OVER podemos obtener el campo "% Ventas".

Fíjense que aunque usamos la función de grupo SUM no estamos usando declaración GROUP BY en nuestra consulta SQL.

EJEMPLO USAANDO EL COMANDO OVER (PARTITION BY)

Al ejemplo anterior agreguemos un campo periodo para tener las ventas de los almacenes por mes.

PERIODO	ALMACEN_ID	VALOR_VTA	% VTA PERIODO	% VTA
201101	ALM_10	10.000	22,22%	9,52%
201101	ALM_20	15.000	33,33%	14,29%
201101	ALM_30	20.000	44,44%	19,05%
Total 201101		45.000	100,00%	42,86%
201102	ALM_10	20.000	33,33%	19,05%
201102	ALM_20	40.000	66,67%	38,10%
Total 201102		60.000	100,00%	57,14%
GRAN TOTAL		105.000		100,00%

Ahora vemos que la información que necesitamos mostrar es más compleja. "% VTA Periodo" es el porcentaje de ventas con relación al total del periodo y "% VTA" es el % del total de las ventas.

Ahora veamos como lo podemos resolver con una consulta SQL.

• Creamos la tabla
  
  Create Table Almacenes_Ventas_Periodo
  (
  Periodo varchar2(6),
  Almacen_Id Varchar2 (10),
  Valor_Ventas Number,
  Cantidad_Ventas Number
  )
  
• Insertamos los registros
  
  Insert into ALMACENES_VENTAS_PERIODO
  (PERIODO,ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201101', 'ALM_30', 20000, 30);
  Insert into  ALMACENES_VENTAS_PERIODO
  (PERIODO, ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201101','ALM_10', 10000, 10);
  Insert into ALMACENES_VENTAS_PERIODO
  (PERIODO, ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201101','ALM_20', 15000,20);
  Insert into ALMACENES_VENTAS_PERIODO
  (PERIODO, ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201102','ALM_10', 20000, 20);
  Insert into ALMACENES_VENTAS_PERIODO
  (PERIODO, ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201102','ALM_20', 40000, 40);
  COMMIT;
  
• Realizamos la consulta
  
  SelectV.*,
  Round(Valor_Ventas/Sum(Valor_Ventas) Over (Partition By Periodo) * 100, 2)
  "%Periodo",
  Round(Valor_Ventas/Sum(Valor_Ventas) Over() * 100, 2) "%Total",
  Sum(Valor_Ventas) Over (Partition By Periodo) Periodo_Total,
  Sum(Valor_Ventas) Over () Gran_Total
  From Almacenes_Ventas_Periodo V
  Order By Periodo, Almacen_Id
  
  --Resultado
  

PERIODO	ALMACEN_ID	VALOR_VENTAS	%Periodo	%Total	PERIODO_TOTAL	GRAN_TOTAL
201101	ALM_10	10000	22,22	9,52	45.000	105.000
201101	ALM_20	15000	33,33	14,29	45.000	105.000
201101	ALM_30	20000	44,44	19,05	45.000	105.000
201102	ALM_10	20000	33,33	19,05	60.000	105.000
201102	ALM_20	40000	66,67	38,1	60.000	105.000

 El campo GRAN_TOTAL es el total de todos los registros del campo VALOR_VENTAS y PERIODO_TOTAL es el total del campo VALOR_VENTAS pero de la agrupación PERIODO. Aquí vemos que agregamos al OVER la clausula PARTITION BY que no indica sobre que partición de datos deseamos realizar la operación de grupo (SUM, AVG, MAX, MIN, ETC.).

Para obtener la fila "%Periodo" debemos dividir VALOR_VENTAS para el total de ventas del grupo periodo "Sum(Valor_Ventas) Over (Partition By Periodo)" y oara obtener el "%Total" debemos dividir VALOR_VENTAS para el gran total "Sum (Valor_Ventas)Over ()".

CONCLUSION:

Es claro que la utilidad OVER nos ayuda a simplificar la generación de consultas complejas y elaborar mejor nuestros cubos de datos. Solo hay que ponerle cabeza para utilizar de la mejor manera estas clausulas.