Creazione di un "motore di raccomandazione" personalizzato in 10 righe di SQL

I motori di raccomandazione non sempre iniziano con l'AI o l'apprendimento automatico. In molti casi, bastano poche query SQL ben scritte per scoprire i prodotti che i clienti acquistano spesso insieme. In questo articolo, creerai un semplice motore di raccomandazione in circa 10 righe di SQL utilizzando self-join e aggregazione.

Quando senti parlare di "motore di raccomandazione", probabilmente pensi all'apprendimento automatico, ai notebook Python e a complessi modelli di intelligenza artificiale. Questa associazione è diventata così comune che spesso dimentichiamo quante raccomandazioni nei sistemi reali siano costruite su basi molto più semplici. In pratica, gran parte delle raccomandazioni di e-commerce e marketing inizia con SQL e una chiara domanda analitica.

Uno degli approcci più comuni si basa sulla co-occorrenza. Se due prodotti compaiono insieme nella stessa transazione con sufficiente frequenza, tale relazione è significativa dal punto di vista commerciale. Questa logica è alla base di modelli familiari come le sezioni "I clienti hanno acquistato anche", i pacchetti di prodotti, i suggerimenti di cross-selling e persino alcune analisi di segmentazione di marketing. Qui non c'è alcuna previsione nel senso dell'apprendimento automatico, ma solo il conteggio, il confronto e la classificazione di ciò che è realmente accaduto.

SQL è particolarmente adatto a questo tipo di lavoro. Con JOIN e GROUP BY, è possibile analizzare le relazioni all'interno dei dati transazionali e trasformare la cronologia degli acquisti grezza in informazioni utili. Se avete familiarità con questi concetti, avete già la maggior parte di ciò che serve per costruire un motore di raccomandazione di base.

In questo articolo, creerete un motore di raccomandazione di base utilizzando un'idea: i clienti che hanno acquistato questo hanno acquistato anche quello.

Produciamo raccomandazioni reali utilizzando solo SQL analitico, senza Python o librerie AI. Questo tipo di ragionamento è esattamente ciò che la pratica SQL intermedia e avanzata su LearnSQL.it è progettata per sviluppare.

La domanda di business

Rendiamo il problema più concreto.

Immagina di lavorare per un negozio di elettronica online. Uno dei tuoi articoli più venduti sono le cuffie wireless. Vuoi migliorare la pagina del prodotto mostrando ai clienti quali altri prodotti vengono acquistati più spesso insieme a queste cuffie.

La domanda diventa:

Per il prodotto Cuffie wireless, quali altri tre prodotti vengono acquistati più frequentemente nella stessa transazione?

Si tratta di una domanda semplice, ma molto pratica. La risposta può essere utilizzata direttamente su una pagina di prodotto, nei widget di cross-selling o dal team di marketing che pianifica pacchetti e promozioni. Per un responsabile dell'e-commerce o un analista di prodotto, questo tipo di informazione è immediatamente utilizzabile.

Il modello di dati

Per mantenere l'esempio realistico, supponiamo di lavorare con un tipico database di e-commerce. Gli ordini, i prodotti e gli articoli acquistati sono memorizzati separatamente, il che rende le query analitiche flessibili e scalabili.

Useremo tre tabelle.

transactions — Questa tabella memorizza le informazioni relative a ogni ordine completato.

Ogni riga rappresenta una singola transazione effettuata da un cliente. Nei sistemi reali, questa tabella include spesso lo stato del pagamento, il metodo di spedizione o la valuta, ma questi dettagli non sono rilevanti per questa analisi.

products — Questa tabella memorizza il catalogo dei prodotti.

Questa tabella ci fornisce nomi di prodotti leggibili e consente un ulteriore filtraggio per categoria o prezzo, se necessario.

transaction_items — Questa tabella collega i prodotti alle transazioni ed è il fulcro della nostra analisi.

Ogni riga rappresenta un prodotto incluso in una transazione. Se un cliente acquista più prodotti in un unico ordine, la transazione appare in più righe.

Il design di questa tabella è estremamente comune nei database di produzione ed è anche la struttura utilizzata in molti esercizi pratici di LearnSQL.it. Se desideri fare pratica con questo tipo di dati di negozio, lavorando con ordini, prodotti e articoli di transazione, il corso Basic Pratica su SQL: Store su LearnSQL.it è il passo successivo naturale.

L'idea di base: l'auto-join

Prima di applicarlo ai prodotti, vediamo un semplice esempio di cosa fa effettivamente un auto-join. Un auto-join è un join di una tabella con se stessa.

Immaginiamo una tabella chiamata employees con le seguenti colonne: employee_id, employee_name, manager_id. Ogni dipendente ha un manager, e anche il manager è memorizzato nella stessa tabella. Per visualizzare i dipendenti insieme ai nomi dei loro manager, non è necessario eseguire un join con una tabella diversa, ma è sufficiente eseguire un join della tabella employees con se stessa.

Quando si utilizza la stessa tabella due volte in una query, SQL richiede che ogni copia abbia un proprio alias. Senza alias, SQL non saprebbe a quale istanza della tabella ci si riferisce. È meglio scegliere alias che riflettano il ruolo che ogni copia svolge nella query, non solo brevi etichette tecniche.

In questo esempio:

• e rappresenta il dipendente
• m rappresenta il manager

Ecco la query:

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

Vediamo come funziona.

SQL legge la employees tabella due volte. La prima volta, considera le righe come dipendenti (e). La seconda volta, tratta le stesse righe come manager (m). La JOIN condizione collega i due ruoli facendo corrispondere il manager_id a quella del manager employee_id. Di conseguenza, ogni riga dipendente è abbinata alla riga che rappresenta il proprio manager.

Se gli auto-join sembrano poco intuitivi, questo stesso modello è ampiamente utilizzato nel corso SQL JOIN su LearnSQL.it , che include una sezione dedicata agli auto-join.

Applicazione dell'auto-join al problema della raccomandazione

Per trovare i prodotti acquistati insieme, dobbiamo confrontare i prodotti all'interno della stessa transazione. Ciò significa unire la transaction_items tabella a se stessa e assegnare a ciascuna copia un ruolo chiaro. Un'istanza rappresenta il prodotto che stiamo analizzando, mentre l'altra rappresenta gli altri prodotti che compaiono nello stesso ordine. Ciò che li accomuna transaction_id è ciò che li collega.

Concettualmente, appare così:

• la prima istanza rappresenta il prodotto che ci interessa
• la seconda istanza rappresenta gli altri prodotti nella stessa transazione

È qui che un self-join diventa estremamente utile. Consente a SQL di ragionare sulle relazioni all'interno dello stesso set di dati, utilizzando la stessa tabella vista da due prospettive diverse.

Prima di scrivere la query, delineiamo la logica passo dopo passo:

• Prendiamo tutti i prodotti di una transazione
• Abbina ogni prodotto agli altri prodotti della stessa transazione
• Escludere l'abbinamento di un prodotto con se stesso
• Contare la frequenza con cui appare ciascuna coppia di prodotti
• Ordinare i risultati in base alla frequenza
• Restituire i risultati migliori

Questa analisi dettagliata è esattamente il modo in cui dovrebbero essere affrontati i problemi analitici SQL. La sintassi viene per ultima. Il ragionamento viene prima.

La query

Ecco una query completa che esegue tutte le operazioni sopra descritte in circa dieci righe di 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;

Questa query trova i tre prodotti più frequentemente acquistati insieme al prodotto 101.

Analizziamo passo dopo passo cosa succede qui.

La query inizia leggendo la transaction_items tabella due volte. La prima copia, t1, rappresenta il prodotto base che stiamo analizzando. La seconda copia, t2, rappresenta gli altri prodotti che compaiono nella stessa transazione. Assegnare a ciascuna copia un alias chiaro rende espliciti i loro ruoli e mantiene la query leggibile.

La JOIN condizione collega le righe di entrambe le copie utilizzando transaction_id. Questo assicura che stiamo confrontando solo prodotti che sono stati acquistati nello stesso ordine. La condizione aggiuntiva t1.product_id <> t2.product_id impedisce che un prodotto venga abbinato a se stesso, il che aggiungerebbe coppie prive di significato ai risultati.

La WHERE restringe l'analisi a un prodotto specifico. In questo caso, t1.product_id = 101 significa che stiamo chiedendo: quando il prodotto 101 appare in una transazione, quali altri prodotti appaiono insieme ad esso? Questo è ciò che trasforma una query di co-occorrenza generale in un motore di raccomandazione concreto per un singolo prodotto.

Successivamente, la GROUP BY raggruppa i risultati per coppie di prodotti: il prodotto base e il prodotto raccomandato. Per ogni coppia unica, COUNT(*) conta quante volte quella combinazione appare in tutte le transazioni. Questo conteggio rappresenta la forza della relazione tra i due prodotti.

La ORDER BY clausola ordina queste coppie dalla più frequente alla meno frequente, e la LIMIT mantiene solo i primi tre risultati. Questi sono i tre prodotti più spesso acquistati insieme al prodotto 101.

Il risultato è semplice ma potente: un elenco classificato di prodotti correlati basato interamente sul comportamento di acquisto reale. Nessuna previsione, nessun modello: solo SQL che esprime una chiara idea analitica. Questo è il tuo motore di raccomandazione.

Come leggere il risultato

L'output della query è simile a questo:

Ogni riga rappresenta una raccomandazione per il prodotto di base.

In questo esempio, il prodotto 101 (cuffie wireless) è stato acquistato più spesso insieme al prodotto 102 (custodia per telefono). Il valore in times_bought_together indica quante transazioni contenevano entrambi i prodotti. Più alto è il numero, più forte è la relazione tra i due prodotti.

Questo risultato può essere utilizzato direttamente nei dashboard, nei report o nella logica delle applicazioni, ad esempio per alimentare la sezione "I clienti hanno acquistato anche" in una pagina di prodotto o per aiutare un team di marketing a decidere quali prodotti abbinare.

Come estendere questo concetto con più SQL

Una volta compreso questo modello, estenderlo diventa una questione di porre domande migliori, non di imparare nuovi strumenti. La logica di base rimane la stessa; è sufficiente perfezionare le transazioni da esaminare e il modo in cui interpretare i risultati.

Un primo passo comune è filtrare le transazioni in base al tempo. Potreste volere consigli basati solo sugli ultimi 30 giorni o sulla stagione corrente, invece che su tutti i dati storici. L'aggiunta di una condizione di data consente di adattare i consigli al comportamento di acquisto recente, che spesso è più rilevante per le decisioni di marketing e merchandising.

È anche possibile limitare l'analisi a segmenti di clientela specifici. Ad esempio, potresti voler vedere quali prodotti vengono comunemente acquistati insieme dai clienti abituali, dai clienti di alto valore o dai clienti di una particolare regione. Questo trasforma una raccomandazione generica in qualcosa di più mirato, senza modificare la struttura complessiva della query.

La classificazione è un'altra estensione naturale. Invece di affidarsi solo al ORDER BY e LIMIT, è possibile utilizzare le funzioni finestra per classificare i consigli per prodotto o per segmento. Ciò è particolarmente utile quando si desidera generare consigli per molti prodotti contemporaneamente, piuttosto che analizzare un prodotto alla volta.

Infine, spesso vale la pena escludere il rumore a basso volume. I prodotti che sono stati acquistati insieme solo una o due volte potrebbero non rappresentare una relazione significativa. L'aggiunta di una soglia minima aiuta a garantire che i consigli siano basati su modelli coerenti piuttosto che su coincidenze casuali.

Tutte queste estensioni si basano su tecniche SQL analitiche: JOIN, aggregazioni, filtraggi e funzioni finestra. Queste sono esattamente le competenze sviluppate nei corsi intermedi e avanzati dell'LearnSQL.it, dove modelli simili ricorrono continuamente in scenari aziendali realistici.

SQL è spesso il primo motore di raccomandazione

Prima di ricorrere a Python o agli strumenti di intelligenza artificiale, vale la pena fermarsi e porsi una domanda più semplice: SQL è già in grado di risolvere questo problema? Come dimostra questo esempio, in molti scenari aziendali reali la risposta è sì. Con una domanda chiara e una solida comprensione delle JOIN e delle aggregazioni, SQL è sufficiente per trasformare i dati transazionali grezzi in raccomandazioni pratiche.

Ecco perché è importante avere solide basi di SQL. Se sei in grado di ragionare sulle relazioni tra i dati ed esprimere chiaramente questa logica in SQL, puoi creare soluzioni che offrono un valore aziendale reale senza inutili complessità. Sviluppare questo tipo di sicurezza richiede pratica in molti scenari realistici, non solo in una singola query.

Se volete andare oltre, il piano SQL dell'Completo per sempre su LearnSQL.it vi dà accesso a tutti i corsi SQL attuali e a tutti i nuovi corsi che verranno aggiunti in futuro. È un modo per costruire e ampliare continuamente le competenze specifiche utilizzate in questo articolo: pensiero analitico, lavoro con set di dati reali e trasformazione di domande aziendali in query SQL chiare ed efficaci.