Creación de un «motor de recomendaciones» personalizado en 10 líneas de SQL

Los motores de recomendación no siempre comienzan con IA o aprendizaje automático. En muchos casos, unas pocas consultas SQL bien escritas son suficientes para descubrir los productos que los clientes compran juntos con frecuencia. En este artículo, creará un motor de recomendación sencillo en unas 10 líneas de SQL utilizando autojuniones y agregación.

Cuando oye «motor de recomendaciones», probablemente piensa en aprendizaje automático, cuadernos de Python y modelos complejos de IA. Esa asociación se ha vuelto tan común que a menudo olvidamos cuántas recomendaciones en sistemas reales se basan en fundamentos mucho más simples. En la práctica, gran parte de las recomendaciones de comercio electrónico y marketing comienzan con SQL y una pregunta analítica clara.

Uno de los enfoques más comunes se basa en la coocurrencia. Si dos productos aparecen juntos en la misma transacción con suficiente frecuencia, esa relación es significativa desde el punto de vista comercial. Esta lógica subyace a patrones familiares como las secciones «Los clientes también compraron», los paquetes de productos, las sugerencias de venta cruzada e incluso algunos análisis de segmentación de marketing. Aquí no hay predicción en el sentido del aprendizaje automático, solo se cuenta, se compara y se clasifica lo que realmente ha ocurrido.

SQL es especialmente adecuado para este tipo de trabajo. Con JOIN y GROUP BY, puede analizar las relaciones dentro de los datos transaccionales y convertir el historial de compras sin procesar en información útil. Si se siente cómodo con estos conceptos, ya tiene casi todo lo que necesita para crear un motor de recomendaciones básico.

En este artículo, creará un motor de recomendaciones básico utilizando una idea: los clientes que compraron esto también compraron aquello.

Generaremos recomendaciones reales utilizando únicamente SQL analítico, sin Python ni bibliotecas de IA. Este tipo de pensamiento es exactamente lo que se pretende desarrollar con la práctica intermedia y avanzada de SQL en LearnSQL.es.

La pregunta comercial

Concretemos el problema.

Imagina que trabajas para una tienda de electrónica online. Uno de tus artículos más vendidos son los auriculares inalámbricos. Quieres mejorar la página del producto mostrando a los clientes qué otros productos se compran con más frecuencia junto con estos auriculares.

La pregunta es:

Para el producto «Auriculares inalámbricos», ¿qué otros tres productos se compran con más frecuencia en la misma transacción?

Es una pregunta sencilla, pero muy práctica. La respuesta se puede utilizar directamente en la página del producto, en widgets de venta cruzada o por parte del equipo de marketing a la hora de planificar paquetes y promociones. Para un gestor de comercio electrónico o un analista de productos, este tipo de información es inmediatamente útil.

El modelo de datos

Para que el ejemplo sea realista, supongamos que trabajas con una base de datos de comercio electrónico típica. Los pedidos, los productos y los artículos comprados se almacenan por separado, lo que hace que las consultas analíticas sean flexibles y escalables.

Utilizaremos tres tablas.

transactions — Esta tabla almacena información sobre cada pedido completado.

Cada fila representa una única transacción realizada por un cliente. En los sistemas reales, esta tabla suele incluir el estado del pago, el método de envío o la moneda, pero esos detalles no son relevantes para este análisis.

products — Esta tabla almacena el catálogo de productos.

Esta tabla nos proporciona nombres de productos legibles y permite filtrar por categoría o precio si es necesario.

transaction_items : esta tabla vincula los productos con las transacciones y es el núcleo de nuestro análisis.

Cada fila representa un producto incluido en una transacción. Si un cliente compra varios productos en un solo pedido, la transacción aparece en varias filas.

El diseño de esta tabla es muy común en las bases de datos de producción y también es la estructura que se utiliza en muchos ejercicios prácticos de LearnSQL.es. Si desea practicar con este tipo de datos de tienda, trabajando con pedidos, productos y artículos de transacción, el curso «Ejercicio de SQL: Store» (: Tienda) de LearnSQL.es es el siguiente paso natural.

La idea central: la autounión

Antes de aplicarlo a los productos, veamos un ejemplo sencillo de lo que realmente hace una autounión. Una autounión es una unión de una tabla consigo misma.

Imaginemos una tabla llamada «employees» con las siguientes columnas: employee_id, employee_name, manager_id. Cada empleado tiene un gerente, y ese gerente también se almacena en la misma tabla. Para mostrar a los empleados junto con los nombres de sus gerentes, no se une a una tabla diferente, sino que se une la tabla employees a sí misma.

Cuando se utiliza la misma tabla dos veces en una consulta, SQL requiere que cada copia tenga su propio alias. Sin alias, SQL no sabría a qué instancia de la tabla se refiere. Es mejor elegir alias que reflejen la función que desempeña cada copia en la consulta, y no solo etiquetas técnicas cortas.

En este ejemplo:

• e representa al empleado
• m representa al gerente

Esta es la consulta:

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id;

Veamos cómo funciona.

SQL lee la employees tabla dos veces. La primera vez, trata las filas como empleados (e). La segunda vez, trata las mismas filas como gerentes (m). La JOIN condición conecta los dos roles haciendo coincidir el manager_id con la del gerente employee_id. Como resultado, cada fila de empleado se empareja con la fila que representa a su gerente.

Si las autojuniones le parecen poco intuitivas, este patrón exacto se practica ampliamente en el curso «Tipos de JOIN en SQL» (Programación de bases de datos) en LearnSQL.es , que incluye una sección dedicada a las autojuniones.

Aplicación de la autojunción al problema de las recomendaciones

Para encontrar productos comprados juntos, necesitamos comparar productos dentro de la misma transacción. Eso significa unir la transaction_items tabla consigo misma y dar a cada copia una función clara. Una instancia representa el producto que estamos analizando y la otra representa los demás productos que aparecen en el mismo pedido. Lo que las une es lo que comparten transaction_id es lo que los une.

Conceptualmente, esto se ve así:

• la primera instancia representa el producto que nos interesa
• la segunda instancia representa los demás productos de la misma transacción

Aquí es donde una autounión resulta extremadamente útil. Permite a SQL razonar sobre las relaciones dentro del mismo conjunto de datos, utilizando la misma tabla vista desde dos perspectivas diferentes.

Antes de escribir la consulta, describamos la lógica paso a paso:

• Tomar todos los productos de una transacción
• Emparejar cada producto con otros productos de la misma transacción.
• Excluir la coincidencia de un producto consigo mismo
• Contar la frecuencia con la que aparece cada par de productos
• Ordenar los resultados por frecuencia
• Devolver los resultados principales

Este desglose paso a paso es exactamente cómo se deben abordar los problemas analíticos de SQL. La sintaxis es lo último. Lo primero es pensar.

La consulta

Aquí hay una consulta completa que hace todo lo anterior en aproximadamente diez líneas de SQL:

SELECT
    t1.product_id AS base_product,
    t2.product_id AS recommended_product,
    COUNT(*) AS times_bought_together
FROM transaction_items t1
JOIN transaction_items t2
    ON t1.transaction_id = t2.transaction_id
   AND t1.product_id <> t2.product_id
WHERE t1.product_id = 101
GROUP BY t1.product_id, t2.product_id
ORDER BY times_bought_together DESC
LIMIT 3;

Esta consulta encuentra los tres productos que se compran con más frecuencia junto con el producto 101.

Analicemos paso a paso lo que ocurre aquí.

La consulta comienza leyendo la transaction_items tabla dos veces. La primera copia, t1, representa el producto base que estamos analizando. La segunda copia, t2, representa los otros productos que aparecen en la misma transacción. Dar a cada copia un alias claro hace que sus funciones sean explícitas y mantiene la legibilidad de la consulta.

La JOIN condición conecta las filas de ambas copias utilizando transaction_id. Esto garantiza que solo comparamos productos que se compraron en el mismo pedido. La condición adicional t1.product_id <> t2.product_id evita que un producto se empareje consigo mismo, lo que añadiría pares sin sentido a los resultados.

La WHERE restringe el análisis a un producto específico. En este caso, t1.product_id = 101 significa que estamos preguntando: cuando el producto 101 aparece en una transacción, ¿qué otros productos aparecen junto a él? Esto es lo que convierte una consulta de coocurrencia general en un motor de recomendación concreto para un solo producto.

A continuación, la GROUP BY agrupa los resultados por pares de productos: el producto base y el producto recomendado. Para cada par único, COUNT(*) cuenta cuántas veces aparece esa combinación en todas las transacciones. Este recuento representa la fuerza de la relación entre los dos productos.

La ORDER BY cláusula ordena estos pares de más frecuentes a menos frecuentes, y la LIMIT cláusula mantiene solo los tres resultados principales. Estos son los tres productos que se compran con más frecuencia junto con el producto 101.

El resultado es sencillo pero potente: una lista clasificada de productos relacionados basada íntegramente en el comportamiento real de compra. Sin predicciones, sin modelos, solo SQL expresando una idea analítica clara. Ese es su motor de recomendaciones.

Cómo leer el resultado

El resultado de la consulta tiene este aspecto:

Cada fila representa una recomendación para el producto base.

En este ejemplo, el producto 101 (auriculares inalámbricos) se compró con mayor frecuencia junto con el producto 102 (funda para teléfono). El valor en times_bought_together muestra cuántas transacciones contenían ambos productos. Cuanto mayor sea el número, más fuerte será la relación entre los dos productos.

Este resultado se puede utilizar directamente en paneles, informes o lógica de aplicaciones, por ejemplo, para alimentar una sección «Los clientes también compraron» en una página de productos o para ayudar al equipo de marketing a decidir qué productos agrupar.

Cómo ampliar esto con más SQL

Una vez que se comprende este patrón, ampliarlo es solo cuestión de formular mejores preguntas, no de aprender nuevas herramientas. La lógica básica sigue siendo la misma; simplemente se refinan las transacciones que se analizan y la forma de interpretar los resultados.

Un primer paso habitual es filtrar las transacciones por tiempo. Es posible que desee recomendaciones basadas solo en los últimos 30 días o en la temporada actual, en lugar de en todos los datos históricos. Añadir una condición de fecha le permite adaptar las recomendaciones al comportamiento de compra reciente, que a menudo es más relevante para las decisiones de marketing y comercialización.

También puede limitar el análisis a segmentos de clientes específicos. Por ejemplo, es posible que desee ver qué productos suelen comprar juntos los clientes habituales, los clientes de alto valor o los clientes de una región concreta. Esto convierte una recomendación genérica en algo más específico, sin cambiar la estructura general de la consulta.

La clasificación es otra extensión natural. En lugar de basarse únicamente en ORDER BY y LIMIT, puede utilizar funciones de ventana para clasificar las recomendaciones por producto o por segmento. Esto resulta especialmente útil cuando se desea generar recomendaciones para muchos productos a la vez, en lugar de analizar un producto cada vez.

Por último, a menudo vale la pena excluir el ruido de bajo volumen. Los productos que se compraron juntos solo una o dos veces pueden no representar una relación significativa. Añadir un umbral mínimo ayuda a garantizar que las recomendaciones se basen en patrones consistentes en lugar de coincidencias aleatorias.

Todas estas extensiones se basan en técnicas analíticas de SQL: JOIN, agregaciones, filtrado y funciones de ventana. Estas son precisamente las habilidades que se desarrollan en los cursos intermedios y avanzados de LearnSQL.es, donde patrones similares aparecen una y otra vez en escenarios empresariales realistas.

SQL suele ser el primer motor de recomendaciones

Antes de recurrir a Python o a herramientas de IA, vale la pena detenerse y plantearse una pregunta más sencilla: ¿puede SQL resolver ya este problema? Como muestra este ejemplo, en muchos escenarios empresariales reales la respuesta es sí. Con una pregunta clara y una sólida comprensión de JOIN y las agregaciones, SQL es suficiente para convertir los datos transaccionales sin procesar en recomendaciones prácticas.

Por eso es importante tener unos fundamentos sólidos de SQL. Si puedes razonar sobre las relaciones entre los datos y expresar esa lógica claramente en SQL, podrás crear soluciones que aporten un valor empresarial real sin una complejidad innecesaria. Desarrollar ese tipo de confianza requiere práctica en muchos escenarios realistas, no solo en una consulta.

Si quieres ir más allá, el plan SQL «Todo, para siempre» (Dominar SQL) de LearnSQL.es te da acceso a todos los cursos de SQL actuales y a todos los nuevos cursos que se añadan en el futuro. Es una forma de desarrollar y ampliar continuamente las habilidades exactas que se utilizan en este artículo: pensamiento analítico, trabajo con conjuntos de datos reales y conversión de preguntas empresariales en consultas SQL claras y eficaces.