Percentiles y conjuntos de datos

A veces es necesario organizar un determinado conjunto de datos en percentiles (o en n-tiles). Por ejemplo, podría ser necesario organizar la información de ventas a clientes de forma que pudiésemos hacer una clasificación ABC de los mismos. En este post vamos a suponer que hemos decidido que el trabajo de proceso de la información va a tener lugar en un servidor SQL Server de Microsoft (durante mucho tiempo el que escribe ha sido un firme partidario de que la lógica de las aplicaciones estuviese únicamente en el software y de que las bases de datos fuesen poco más que meros repositorios de información, pero los tiempos cambian y hoy en día responsabilizar de ciertos procesos al servidor de base de datos proporciona múltiples ventajas que no podemos obviar).

Si efectuamos una aproximación superficial a los percentiles en SQL Server podríamos pensar que una llamada a la función NTILE() de Transact-SQL pasándole un 100 como parámetro resolvería nuestro problema. Pero desgraciadamente la función NTILE() organiza la información según los registros y no según los valores de un campo numérico de esos registros (v. esta entrada en la MSDN) y, por lo tanto, no sirve para nuestro propósito. En el ejemplo que estamos manejando, si tenemos 320 clientes, el primer percentil que devuelva la función NTILE() estaría ocupado por los 3 ó 4 primeros clientes (realmente por los primeros 3,20 clientes), independientemente de la cifra de ventas que éstos tuvieran.

Vamos, pues, a tratar de desarrollar un procedimiento que nos devuelva un conjunto de datos con información acerca del percentil ocupado por cada registro. Supongamos que partimos de una tabla (o de una vista, o del resultado de una consulta, de un procedimiento almacenado o de una función) denominada VENTAS que tiene únicamente dos campos: un identificador de cliente (IdCliente) y las ventas acumuladas a ese cliente para el período de que se trate (ImporteVentas).

Lo primero que tenemos que hacer es ordenar dicha tabla de menor a mayor importe acumulado de las ventas, guardando la posición de cada registro (después la vamos a necesitar). Además, vamos a trabajar con una tabla temporal para la que ya en este primer paso crearemos todos los campos necesarios:

/* Borramos la tabla temporal si existe */
if object_id('tempdb..#tmp') is not null
drop table #tmp

select row_number() over (order by q.ImporteVentas) as Fila,
q.IdCliente,
q.ImporteVentas,
cast(0 as money) as SaldoAcumulado,
cast(0 as smallmoney) as Percentil,
' ' as ABC
into #tmp
from Ventas q
order by row_number() over (order by q.ImporteVentas)

A continuación debemos acumular el saldo, lo cual puede ser hecho con la siguiente actualización de la tabla temporal:

update #tmp set SaldoAcumulado =
(select sum(abs(q.ImporteVentas))
from #tmp q
where q.fila <= #tmp.fila)

Fijémonos en que estamos acumulando el valor absoluto del saldo, lo cual hace que el proceso funcione bien incluso si hay clientes con ventas netas negativas.

Para calcular el importe total de las ventas, declaramos la variable correspondiente y efectuamos el cálculo:

declare @suma money
select @suma = sum(abs(#tmp.ImporteVentas))
from #tmp

Ya podemos calcular el percentil correspondiente y asignar cada cliente al grupo (A, B o C) correspondiente en función de dichos percentiles (aquí asumo que pertenecen al grupo C los clientes situados hasta el percentil 80, éste incluido; no habría problema en suponer que el percentil 80 pertenece al grupo B; el mismo comentario puede efectuarse para los clientes situados en el percentil 95 con respecto a los grupos B y A):

update #tmp set percentil = 100 * isnull(SaldoAcumulado, 0) / @suma
update #tmp set abc =
case
when percentil <= 80 then 'C'
when percentil <= 95 then 'B'
else 'A'
end

Llegado a este punto, podemos dar por resuelto el problema ya que en la tabla #tmp tenemos el percentil que corresponde a cada cliente. Espero que pueda seros de utilidad.

Advertisements

One Comment on “Percentiles y conjuntos de datos”

  1. Jeb says:

    Me ha resultado muy util el planteamiento de cálculo del ABC mediante una tabla temporal.
    Muchas gracias por el artículo y felicidades por este fantástico blog.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s