Créer un « moteur de recommandation » personnalisé en 10 lignes de SQL

Les moteurs de recommandation ne reposent pas toujours sur l'IA ou l'apprentissage automatique. Dans de nombreux cas, quelques requêtes SQL bien écrites suffisent pour découvrir les produits que les clients achètent fréquemment ensemble. Dans cet article, vous allez créer un moteur de recommandation simple en environ 10 lignes de SQL à l'aide d'auto-joints et d'agrégation.

Lorsque vous entendez « moteur de recommandation », vous pensez probablement à l'apprentissage automatique, aux notebooks Python et aux modèles d'IA complexes. Cette association est devenue si courante que nous oublions souvent combien de recommandations dans les systèmes réels reposent sur des bases beaucoup plus simples. Dans la pratique, une grande partie des recommandations en matière de commerce électronique et de marketing commence par du SQL et une question analytique claire.

L'une des approches les plus courantes est basée sur la cooccurrence. Si deux produits apparaissent ensemble dans la même transaction suffisamment souvent, cette relation est significative d'un point de vue commercial. Cette logique sous-tend des modèles familiers tels que les sections « Les clients ont également acheté », les offres groupées de produits, les suggestions de ventes croisées et même certaines analyses de segmentation marketing. Il n'y a ici aucune prédiction au sens de l'apprentissage automatique, mais simplement un comptage, une comparaison et un classement de ce qui s'est réellement passé.

SQL est particulièrement bien adapté à ce type de travail. Avec JOIN et GROUP BY, vous pouvez analyser les relations au sein des données transactionnelles et transformer l'historique des achats bruts en informations exploitables. Si vous maîtrisez ces concepts, vous disposez déjà de la plupart des éléments nécessaires pour créer un moteur de recommandation de base.

Dans cet article, vous allez créer un moteur de recommandation basique en utilisant une seule idée : les clients qui ont acheté ceci ont également acheté cela.

Nous produirons de véritables recommandations en utilisant uniquement le SQL analytique, sans Python ni bibliothèques d'IA. Ce type de réflexion est exactement ce que la pratique intermédiaire et avancée du SQL sur LearnSQL.fr vise à développer.

La question commerciale

Concrétisons le problème.

Imaginez que vous travaillez pour un magasin d'électronique en ligne. L'un de vos articles les plus vendus est un casque sans fil. Vous souhaitez améliorer la page produit en montrant aux clients quels autres produits sont le plus souvent achetés avec ce casque.

La question devient alors :

Pour le produit « Écouteurs sans fil », quels sont les trois autres produits les plus fréquemment achetés lors de la même transaction ?

Il s'agit d'une question simple, mais très pratique. La réponse peut être utilisée directement sur une page produit, dans des widgets de vente croisée ou par une équipe marketing qui planifie des offres groupées et des promotions. Pour un responsable e-commerce ou un analyste produit, ce type d'information est immédiatement exploitable.

Le modèle de données

Pour que l'exemple reste réaliste, supposons que vous travaillez avec une base de données e-commerce classique. Les commandes, les produits et les articles achetés sont stockés séparément, ce qui rend les requêtes analytiques à la fois flexibles et évolutives.

Nous utiliserons trois tableaux.

transactions — Ce tableau stocke des informations sur chaque commande terminée.

Chaque ligne représente une seule transaction effectuée par un client. Dans les systèmes réels, ce tableau comprend souvent le statut du paiement, le mode d'expédition ou la devise, mais ces détails ne sont pas pertinents pour cette analyse.

products — Ce tableau stocke le catalogue de produits.

Cette table nous fournit des noms de produits lisibles et permet un filtrage supplémentaire par catégorie ou par prix si nécessaire.

transaction_items — Ce tableau relie les produits aux transactions et constitue le cœur de notre analyse.

Chaque ligne représente un produit inclus dans une transaction. Si un client achète plusieurs produits dans une seule commande, la transaction apparaît dans plusieurs lignes.

La conception de ce tableau est extrêmement courante dans les bases de données de production et correspond également à la structure utilisée dans de nombreux exercices pratiques de LearnSQL.fr. Si vous souhaitez vous exercer concrètement avec ce type de données de magasin (commandes, produits et articles de transaction), le cours « Basic La pratique du SQL: Store » sur LearnSQL.fr est la suite logique.

L'idée centrale : l'auto-jointure

Avant de l'appliquer aux produits, examinons un exemple simple de ce que fait réellement une auto-jointure. Une auto-jointure est une jointure d'une table avec elle-même.

Imaginez une table appelée employees avec les colonnes suivantes : employee_id, employee_name, manager_id. Chaque employé a un responsable, et ce responsable est également stocké dans la même table. Pour afficher les employés avec les noms de leurs responsables, vous ne joignez pas une table différente, vous joignez la table employees à elle-même.

Lorsque vous utilisez deux fois la même table dans une requête, SQL exige que chaque copie ait son propre alias. Sans alias, SQL ne saurait pas à quelle instance de la table vous faites référence. Il est préférable de choisir des alias qui reflètent le rôle que joue chaque copie dans la requête, et pas seulement des étiquettes techniques courtes.

Dans cet exemple :

• e représente l'employé
• m représente le responsable

Voici la requête :

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

Voyons comment cela fonctionne.

SQL lit la employees table deux fois. La première fois, il traite les lignes comme des employés (e). La deuxième fois, il traite les mêmes lignes comme des managers (m). La JOIN condition relie les deux rôles en faisant correspondre le manager_id à celle du manager employee_id. Ainsi, chaque ligne d'employé est associée à la ligne qui représente son responsable.

Si les auto-jointures vous semblent peu intuitives, ce modèle précis est largement utilisé dans le cours Les jointures en SQL sur LearnSQL.fr , qui comprend une section dédiée aux auto-jointures.

Application de l'auto-jointure au problème de recommandation

Pour trouver les produits achetés ensemble, nous devons comparer les produits au sein d'une même transaction. Cela signifie qu'il faut joindre la transaction_items table à elle-même et de donner à chaque copie un rôle clair. Une instance représente le produit que nous analysons, et l'autre représente les autres produits qui apparaissent dans la même commande. Le point commun transaction_id est ce qui les relie.

Conceptuellement, cela ressemble à ceci :

• la première instance représente le produit qui nous intéresse
• la deuxième instance représente les autres produits de la même transaction

C'est là qu'une auto-jointure devient extrêmement utile. Elle permet à SQL de raisonner sur les relations au sein du même ensemble de données, en utilisant la même table vue sous deux angles différents.

Avant d'écrire la requête, décrivons la logique étape par étape :

• Prendre tous les produits d'une transaction
• Faites correspondre chaque produit avec les autres produits de la même transaction
• Exclure la mise en correspondance d'un produit avec lui-même
• Compter le nombre d'occurrences de chaque paire de produits
• Trier les résultats par fréquence
• Renvoyer les meilleurs résultats

Cette décomposition étape par étape correspond exactement à la manière dont les problèmes SQL analytiques doivent être abordés. La syntaxe vient en dernier. La réflexion vient en premier.

La requête

Voici une requête complète qui effectue toutes les opérations ci-dessus en une dizaine de lignes 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;

Cette requête trouve les trois produits les plus fréquemment achetés avec le produit 101.

Décomposons étape par étape ce qui se passe ici.

La requête commence par lire la transaction_items table deux fois. La première copie, t1, représente le produit de base que nous analysons. La deuxième copie, t2, représente les autres produits qui apparaissent dans la même transaction. Donner à chaque copie un alias clair rend leur rôle explicite et permet de conserver la lisibilité de la requête.

La JOIN condition relie les lignes des deux copies à l'aide de transaction_id. Cela garantit que nous ne comparons que les produits qui ont été achetés dans la même commande. La condition supplémentaire t1.product_id <> t2.product_id empêche un produit d'être associé à lui-même, ce qui ajouterait des paires sans signification aux résultats.

La WHERE restreint l'analyse à un produit spécifique. Dans ce cas, t1.product_id = 101 signifie que nous demandons : lorsqu'un produit 101 apparaît dans une transaction, quels autres produits apparaissent à ses côtés ? C'est ce qui transforme une requête de cooccurrence générale en un moteur de recommandation concret pour un seul produit.

Ensuite, la GROUP BY regroupe les résultats par paires de produits : le produit de base et le produit recommandé. Pour chaque paire unique, COUNT(*) compte le nombre de fois où cette combinaison apparaît dans toutes les transactions. Ce nombre représente la force de la relation entre les deux produits.

La ORDER BY clause trie ces paires de la plus fréquente à la moins fréquente, et la LIMIT conserve uniquement les trois premiers résultats. Ce sont les trois produits les plus souvent achetés avec le produit 101.

Le résultat est simple mais puissant : une liste classée de produits connexes basée entièrement sur le comportement d'achat réel. Pas de prédictions, pas de modèles, juste du SQL exprimant une idée analytique claire. C'est votre moteur de recommandation.

Comment lire le résultat

Le résultat de la requête se présente comme suit :

Chaque ligne représente une recommandation pour le produit de base.

Dans cet exemple, le produit 101 (casque sans fil) a été le plus souvent acheté avec le produit 102 (étui de téléphone). La valeur dans times_bought_together indique le nombre de transactions contenant les deux produits. Plus le nombre est élevé, plus la relation entre les deux produits est forte.

Ce résultat peut être utilisé directement dans des tableaux de bord, des rapports ou la logique d'application, par exemple pour alimenter une section « Les clients ont également acheté » sur une page produit ou pour aider une équipe marketing à décider quels produits regrouper.

Comment étendre cela avec plus de SQL

Une fois que vous comprenez ce modèle, l'étendre revient simplement à poser de meilleures questions, et non à apprendre de nouveaux outils. La logique de base reste la même ; vous affinez simplement les transactions que vous examinez et la manière dont vous interprétez les résultats.

Une première étape courante consiste à filtrer les transactions par date. Vous pouvez souhaiter obtenir des recommandations basées uniquement sur les 30 derniers jours ou la saison en cours, plutôt que sur toutes les données historiques. L'ajout d'une condition de date vous permet d'adapter les recommandations au comportement d'achat récent, qui est souvent plus pertinent pour les décisions de marketing et de merchandising.

Vous pouvez également limiter l'analyse à des segments de clientèle spécifiques. Par exemple, vous pouvez vouloir voir quels produits sont couramment achetés ensemble par les clients fidèles, les clients à forte valeur ajoutée ou les clients d'une région particulière. Cela transforme une recommandation générique en une recommandation plus ciblée, sans modifier la structure globale de la requête.

Le classement est une autre extension naturelle. Au lieu de se fier uniquement au ORDER BY et LIMIT, vous pouvez utiliser des fonctions de fenêtre pour classer les recommandations par produit ou par segment. Cela est particulièrement utile lorsque vous souhaitez générer des recommandations pour plusieurs produits à la fois, plutôt que d'analyser un produit à la fois.

Enfin, il est souvent utile d'exclure les bruits de fond à faible volume. Les produits qui n'ont été achetés ensemble qu'une ou deux fois ne représentent peut-être pas une relation significative. L'ajout d'un seuil minimum permet de s'assurer que les recommandations sont basées sur des modèles cohérents plutôt que sur des coïncidences aléatoires.

Toutes ces extensions s'appuient sur des techniques SQL analytiques : JOIN, agrégations, filtrage et fonctions de fenêtre. Ce sont exactement les compétences développées dans les cours intermédiaires et avancés d'LearnSQL.fr, où des modèles similaires apparaissent à maintes reprises dans des scénarios commerciaux réalistes.

SQL est souvent le premier moteur de recommandation

Avant de se tourner vers Python ou les outils d'IA, il vaut la peine de s'arrêter et de se poser une question plus simple : le SQL peut-il déjà résoudre ce problème ? Comme le montre cet exemple, dans de nombreux scénarios commerciaux réels, la réponse est oui. Avec une question claire et une solide compréhension des JOIN et des agrégations, SQL suffit pour transformer des données transactionnelles brutes en recommandations pratiques.

C'est pourquoi il est important d'avoir de solides bases en SQL. Si vous êtes capable de raisonner sur les relations entre les données et d'exprimer clairement cette logique en SQL, vous pouvez créer des solutions qui apportent une réelle valeur commerciale sans complexité inutile. Pour acquérir ce type de confiance, il faut s'entraîner à travers de nombreux scénarios réalistes, et pas seulement une seule requête.

Si vous souhaitez aller plus loin, le plan SQL de Tout à vie sur LearnSQL.fr vous donne accès à tous les cours SQL actuels et à tous les nouveaux cours qui seront ajoutés à l'avenir. C'est un moyen de développer et d'élargir en permanence les compétences exactes utilisées dans cet article : la pensée analytique, le travail avec des ensembles de données réels et la transformation de questions commerciales en requêtes SQL claires et efficaces.