Agrupando sumas mediante particiones en SQL
Cuando el cliente te pide una funcionalidad relacionada con la generación de informes, sabes que vas a tener que enfrentarte a un concepto que, a menudo, provoca verdaderos dolores de cabeza cuando se trata de realizar consultas SQL: los valores agrupados.
No entraremos en detalle en el concepto de agrupación, ya que cualquier manual de SQL le dedica secciones enteras a este concepto. Trataremos un caso particular de agrupación: la agrupación de sumas.
Imaginemos el siguiente caso: una empresa de reprografía tiene una serie de clientes. Esos clientes realizan copias, y la empresa cobra a los clientes en función de las copias que haga. Hasta aquí sencillo, ¿verdad?
Ricemos un poco el rizo. Imaginemos que algunos de esos clientes dependen de otros, es decir, existen unos clientes que dependen de otros. Esto podría modelarse (para el ejemplo, al menos) mediante dos tablas: una tabla “clientes” y otra “impresiones”.
La importancia del ejemplo se encuentra en la tabla “Impresiones”. Vemos que la tabla tiene un campo “CodigoCliente” y un campo “CodigoClienteResponsable”. Ambos campos son claves ajenas a la tabla “Cliente”, específicamente a su clave primaria, “IdCliente”.
La cuestión es simple: queremos obtener los siguientes datos: las impresiones que realiza cada cliente, las impresiones que realizan todos los clientes que están al cargo de cada cliente y (optativamente) la suma del total de ambas.
El camino fácil sería utilizar subconsultas. Sería una opción perfectamente válida, pero penalizaría el rendimiento en consultas en las que se maneje un gran volumen de datos. Y no es ese el objetivo del presente artículo, sino mostrar las bondades de la agrupación de sumas mediante particiones. Por lo tanto, comencemos con un primer acercamiento a la solución:
select C.Nombre,
C.Apellido1,
C.Apellido2,
SUM(ICliente.NumeroImpresiones) as ImpresionesCliente,
SUM(IResponsable.NumeroImpresiones) as ImpresionesResponsable
from Cliente C
left join Impresion ICliente
on ICliente.CodigoCliente = C.IdCliente
left join Impresion IResponsable
on IResponsable.CodigoClienteResponsable = C.IdCliente
group by C.Nombre, C.Apellido1, C.Apellido2
Esta consulta daría el siguiente resultado:
Como vemos, al agrupar por nombre y apellidos, el campo “ImpresionesCliente” se suma una vez por cada registro que tenga asociado, multiplicando el valor original por el número de registros repetidos. Por lo tanto, lo que haríamos a continuación sería agrupar también por el campo que nos está fastidiando: ”ImpresionesResponsable”:
select C.Nombre, C.Apellido1, C.Apellido2, SUM(ICliente.NumeroImpresiones) as ImpresionesCliente, SUM(IResponsable.NumeroImpresiones) as ImpresionesResponsable from Cliente C left join Impresion ICliente on ICliente.CodigoCliente = C.IdCliente left join Impresion IResponsable on IResponsable.CodigoClienteResponsable = C.IdCliente group by C.Nombre, C.Apellido1, C.Apellido2, IResponsable.NumeroImpresiones
El resultado tiene mejor pinta, pero tampoco nos sirve: ahora nos ha separado en dos líneas distintas los resultados, ya que dependerá de la agrupación.
Es en este punto donde utilizaremos (por fin) la agrupación sobre una partición. En lugar de obtener la suma de ImpresionesCliente, obtendremos la suma de todos sus valores particionados por un campo que sea único, como el NIF del cliente o su ID. La sintaxis de esta operación es
SUM(SUM(CampoASumar)) over (partition by CampoPorElQueParticionar)
El campo por el que se particiona deberá, además, colocarse en el Group By de la cláusula.
select C.IdCliente,
C.Nombre,
C.Apellido1,
C.Apellido2,
SUM(ICliente.NumeroImpresiones) as ImpresionesCliente,
SUM(SUM(IResponsable.NumeroImpresiones)) over (partition by C.IdCliente) as ImpresionesResponsable,
SUM(ICliente.NumeroImpresiones) +
SUM(SUM(case when IResponsable.NumeroImpresiones is null then 0
else IResponsable.NumeroImpresiones end))
over (partition by C.IdCliente) as TotalImpresiones
from Cliente C
left join Impresion ICliente
on ICliente.CodigoCliente = C.IdCliente
left join Impresion IResponsable
on IResponsable.CodigoClienteResponsable = C.IdCliente
group by C.IdCliente, C.Nombre, C.Apellido1, C.Apellido2, IResponsable.NumeroImpresiones
El resultado final será el siguiente:
Esto se acerca ya bastante a lo que buscábamos. Ahora sólo necesitamos eliminar las filas sobrantes. Sin embargo, dado que ahora todas las sumas agrupadas son iguales, lo haremos de una forma tan sencilla como añadiendo un distinct a un campo que sea clave candidata.
select distinct C.IdCliente,
C.Nombre,
C.Apellido1,
C.Apellido2,
SUM(ICliente.NumeroImpresiones) as ImpresionesCliente,
SUM(SUM(IResponsable.NumeroImpresiones)) over (partition by C.IdCliente) as ImpresionesResponsable,
SUM(ICliente.NumeroImpresiones) +
SUM(SUM(case when IResponsable.NumeroImpresiones is null then 0
else IResponsable.NumeroImpresiones end))
over (partition by C.IdCliente) as TotalImpresiones
from Cliente C
left join Impresion ICliente
on ICliente.CodigoCliente = C.IdCliente
left join Impresion IResponsable
on IResponsable.CodigoClienteResponsable = C.IdCliente
group by C.IdCliente, C.Nombre, C.Apellido1, C.Apellido2, IResponsable.NumeroImpresiones
Leave a Reply