Hola a todos, como bien dice el título de esta entrada, hoy estaremos hablando sobre la utilidad de definir un índice de popularidad y su posterior implementación y actualización.
En muchos casos necesitamos ordenar artículos en función de su relevancia o popularidad, conceptos que en tema de ventas, pueden definirse como el nivel de aceptación del producto.
La aceptación normalmente depende de dos valores, total de ventas y evaluación posterior del producto. En nuestro caso haremos uso solamente del total de ventas, aunque un modelo más aceptado debería hacer uso de las críticas o evaluaciones de los clientes sobre el producto.
Las ventas normalmente varían en función del tiempo, y en muchos casos tienen un comportamiento periódico que se repite cada año, mostrándose valores similares en iguales meses o períodos, como puede pasar con productos relacionados con las estaciones (verano, invierno) o con fechas de celebraciones. Por tal motivo nuestro índice de popularidad no es valor estático, sino que es dependiente del tiempo y de la fecha actual, mes, estación o período.
Al igual que en las entradas anteriores utilizaremos el modelo de base de datos definido, aunque incluiremos un nuevo campo de tipo numeric a la tabla productos, al cual llamaremos popularidad.
Para su implementación comencemos primeramente por definir una consulta que nos devuelva el total de ventas para cada producto por año y mes. En postgres sería algo como:
SELECT EXTRACT(YEAR FROM o.fecha) AS anno,
EXTRACT(MONTH FROM o.fecha) AS mes,
a.producto,
SUM(a.cantidad) AS total
FROM ordenes o INNER JOIN articulos a ON (o.id_orden = a.orden)
GROUP BY anno, mes, producto
Este índice debe tener en cuenta los totales de los meses anteriores, pero está claro que mientras más alejado estén los totales en tiempo, menos peso deben tener en el índice. Para poder trabajar de manera cómoda con los años y meses, propongo utilizar un recurso o truco que he utilizado en varias ocasiones con resultados satisfactorios. Se trata de multiplicar el año por 12 y sumar el valor del mes, de manera que a cada dupla (año, mes) se le hace corresponder un número entero único que cumple con dos propiedades: mantiene el orden cronológico y cada mes difiere solo en uno respecto al mes anterior. Para lograr reducir el peso de los totales a medida que nos alejamos en tiempo se pueden utilizar varios recursos, particularmente utilizaré uno muy sencillo, dividir el total del mes entre la distancia de la dupla (año actual, mes actual) con la del (año, mes) asociado a ese total.
Primero veamos cómo obtener los valores numéricos asociados al mes y año.
SELECT EXTRACT(YEAR FROM current_date)*12
+ EXTRACT(MONTH FROM current_date) AS base,
t.anno*12 + t.mes AS indice,
t.producto,
t.total
FROM (
SELECT EXTRACT(YEAR FROM o.fecha) AS anno,
EXTRACT(MONTH FROM o.fecha) AS mes,
a.producto,
SUM(a.cantidad) AS total
FROM ordenes o INNER JOIN articulos a ON (o.id_orden = a.orden)
GROUP BY anno, mes, producto
) t
Note que los resultados anteriores pueden obtenerse sin necesidad de utilizar una subconsulta, pero por motivos puramente didácticos la mantendré así.
En esa consulta obtenemos un valor base, asociado a la fecha actual, y un valor índice asociado a cada una de los años y meses obtenidos en la consulta de agrupamiento utilizada para obtener los totales por productos.
Ahora solo resta definir el índice de popularidad:
SELECT producto, SUM(total/(base-indice)) AS popularidad
FROM (
SELECT EXTRACT(YEAR FROM current_date)*12
+ EXTRACT(MONTH FROM current_date) AS base,
t.anno*12 + t.mes AS indice,
t.producto,
t.total
FROM (
SELECT EXTRACT(YEAR FROM o.fecha) AS anno,
EXTRACT(MONTH FROM o.fecha) AS mes,
a.producto,
SUM(a.cantidad) AS total
FROM ordenes o INNER JOIN articulos a ON (o.id_orden = a.orden)
GROUP BY anno, mes, producto
) t
) b
WHERE indice>=(base - 60) AND indice<base GROUP BY producto
Nótese que se utilizan los valores de los 5 años anteriores (indice>=(base-60)) y no se utiliza el mes actual (indice<base), de hacerlo habría que modificar la fórmula en el SELECT, pues la división nos daría un error al intentar dividir por cero.
Básicamente se suman todos los totales de los últimos 5 años, pero en cada mes se reduce el peso del valor total, dividiendo por la diferencia entre el valor base y el valor indice. Otros modelos, que dependerán del nivel de dependencia del índice con el tiempo, pueden ser utilizados, como:
SUM(total/power(2,base-indice))
en el que se divide por la potencias de 2, de manera que el peso en cada mes será la mitad del peso del mes posterior.
Si queremos además dar un peso adicional a los totales correspondientes a meses del mismo periodo que el actual, pero de años anteriores, podemos utilizar una expresión similar a la siguiente, en la que se da un peso adicional a los totales de meses iguales a los 3 meses anteriores al actual, reduciendo su peso a medida que se alejan.
SUM(total*60/(base-indice)+total*CAST(mod(CAST((base-indice+9) AS INT),12) > 8 AS INT)*30/(base-indice))
Al igual que comentaba anteriormente, son solo propuestas que pueden ser modificadas y adecuadas al caso que se analice. En este caso se utiliza la función mod que devuelve el resto de la división de un número por otro, y se compara su resultado para saber si el resultado corresponde a uno de los meses que buscamos. Al convertir la comparación a un valor entero obtendremos 0 o 1, que permite adicionar el peso adicional si es uno de los meses correspondientes. El uso de 60 y 30, es para diferenciar los pesos, la decisión de estos valores depende, como he dicho anteriormente, del problema que se analice.
Finalmente actualizamos el campo popularidad de la tabla productos, utilizando una consulta de actualización.
UPDATE productos p SET popularidad = c.popularidad
FROM (
SELECT producto, SUM(total*60/(base-indice)
+ total*CAST( mod(CAST((base-indice+9) AS INT),12)>8 AS INT)*30/(base-indice)) AS popularidad
FROM (
SELECT EXTRACT(YEAR FROM current_date)*12
+ EXTRACT(MONTH FROM current_date) AS base,
t.anno*12 + t.mes AS indice,
t.producto,
t.total
FROM (
SELECT EXTRACT(YEAR FROM o.fecha) AS anno,
EXTRACT(MONTH FROM o.fecha) AS mes,
a.producto,
SUM(a.cantidad) AS total
FROM ordenes o INNER JOIN articulos a ON (o.id_orden = a.orden)
GROUP BY anno, mes, producto
) t
) b
WHERE indice>=(base - 60) AND indice<base
GROUP BY producto
) c
WHERE p.id_producto = c.producto;
