Ordinare i risultati di una query per rilevanza
Data: 18 Ottobre 2005Autore: Maurizio Pelizzone
Per un lavoro che sto seguendo mi è stato chiesto di preparare un motore di ricerca dove i risultati della query devono essere ordinati in base alla rilevanza che questi hanno con le parole cercate.
il problema è che la rilevanza dei singoli record varia a seconda del campo nel quale vengono trovate e dal numero di parole cercate.
immagine una tabella con la seguente struttura:
il motore di ricerca deve cercare all’interno dei campi titolo, descrizione e keywords con il criterio “like ‘%parola%’ ” ma deve visualizzare prima i risultati dove il match è positivo per titolo , poi per keywords ed infine per descrizione.
il discorso di complica se le parole cercate sono più di una in quanto la rilavenza di un match completo su tutte le parola vale di più di uno parziale.
quello che segue è la mia soluzione al problema …
nell’ottica di ottimizzare la ricerca in maniera che sia il più performante possibile ho cercato di utilizzare un’unica query preparata da una pre-elebarazione via codice.
quello che segue è un esempio di una query ( ottimizzata per mysql ) dove l’utente ha cercato due parole ( parolauno e paroladue )
select
( if ( instr( titolo , ‘parola_uno‘) > 0 , 5 , 0 )
+ if ( instr( titolo , ‘parola_due‘ ) > 0 , 5 , 0 )
+ if ( instr( keywords, ‘parola_uno‘) ‘ ) > 0 , 3 , 0 )
+ if ( instr( keywords, ‘parola_due‘) ‘ ) > 0 , 3 , 0 )
+ if ( instr( descrizione, ‘parola_uno‘) ‘ ) > 0 , 1 , 0 )
+ if ( instr( descrizione, ‘parola_due’) ‘ ) > 0 , 1 , 0 )
) as relevance ,
titolo ,
descrizione ,
keywords
from nometabella
where
concat_ws(’ ‘ , titolo , descrizione , keywords) like ‘%parola_uno%’
and concat_ws(’ ‘ , titolo , descrizione , keywords) like ‘%parola_due%’
order by relevance desc
in pratica ho fatto creare a mysql, durante l’elaborazione della query, un campo ( relevance ) ricavato dalla somma dei valori ricavati a loro volta dalla combinazione di una funzione if ed instr.
dopo aver essegnato in modo arbitrario un valore all’importanza del campo ( 5 per titolo , 3 per keywords e 1 per descrizione ) testo con un if se instr restituisce un valore maggiore di 0 in caso di math positivo restituisco il valore assegnato all’importanza del campo mentre in caso di math negativo restituisco 0 ( zero )
creando un loop da codice che cicla per il numero di parole cercate, avremo ( nell’esempio ) la somma di 6 valori che nel caso di rilevanza massima assumera il valore 18 ( 5 + 5 + 3 + 3 + 1 + 1) e nel caso di rilevanza minima 1 ( 0 + 0 + 0 + 0 + 0 + 1)
avendo a questo punto valorizzato il singolo record , è stato sufficente creare un ordinamento decrescente ( order by relevance desc )
consapevole che quanto detto è molto lontano dall’essere considerato una trattazione completa sull’argomento, spero possa essere per voi un punto di partenza in caso di problematiche analoghe o uno stimolo per una riflessione sull’argomento.
Technorati: mysql, database
BlogBabel: mysql, database
Questo articolo è stato pubblicato il 18 Ottobre 2005 alle 10:22 pm ed è stato letto 13473 volte
Archiviato sotto sviluppo, sql, database, mysql.
Se questo post ti è piaciuto puoi iscriverti al mio
Feed Rss
e ricevere direttamente gli aggiornamenti di questo blog sul tuo FeedReader oppure puoi decidere
di offrirmi un caffè.
Se ti va puoi lasciare un commento e leggere quelli degli altri,
ma se preferisci puoi passare direttamente al
post precedente o a quello successivo.
Forse collegati ..
- Come sei diventato bloggers ?
Questa volta sono proprio stato precettato, e non posso esimermi... * Chi o cosa ti ha spinto a creare un Blog? Diciamo - L'autorevolezza dei commenti
A volte l'autorevolezza dei Blog di cui abbiamo letto una moltitudine di volte, può arrivare ad assumere curiosi eccessi, ma - Sviluppare con ASP.NET 2
Da un paio ho incominciato a giocare seriamente con ASP.Net 2 e finalmente ho potuto sfruttare tutte quelle belle cose - Qual' è la dimensione di queste sottodirectory ?
Nei giorni passati ho avuto la necessità di sapere in modo ricorsivo la dimensione di una directory a delle sue - Brainbench: Certificazioni professionali gratuite
Rileggendo una mail di un amico ho trovato questa segnalazione a cui a suo tempo non avevo dato importanza. Vi riporto
19 Ottobre 2005
questo è un trackback manuale, perchè quello automatico non mi funzia
http://www.mucignat.com/blog/archives/165-Il-motore-di-ricerca-migliore.html
7 Novembre 2005
Grazie del suggerimento, stavo cercando qualcosa di simile per la mia tesi… oltre che nella bibliografia ti metto anche nei ringraziamenti!
4 Settembre 2006
Ciao ho letto questo articolo che mi ha colpito per la sua efficacia semplice per un problema complesso come la gestione della rilevanza. Ottimo lavoro!
Io ti espongo un problema in cui mi sono imbattuto e che credo abbia una soluzione semplice ma non alla mia portata (sono un infermiere che si diletta nella gestione di un sito di fotografia per un amico).
Gradirei una risposta al problema ma solo se credi che possa essere di interesse per molti. Dovrei ricavare da una tabella in cui sono presenti un campo idimmagine , descrizione , voto(numerico) il risultato della somma dei voti di ogni record relativo allo stesso idimmagine ordinati in modo decrescente.
Es. se l’immagine idimmagine 1 ha ottenuto tre volte un voto (una volta 1, laseconda 5, la terza 5) venga fuori che idimmagine 1 Totale_Voti 11
e a seguire le altre immagini che hanno ottenuto totali sempre minori.
Spero di essere stato chiaro e di suscitare in te la curiosità di risolvere il problema.
Se non ti ho stimolato non importa complimenti lo stesso per il tuo sito.
Saluti Dionisio.
5 Settembre 2006
questo tipo di operazioni vengono fatte con la clausola “group by” che ti permette di raggruppare secondo un valorore e nel tuo caso sommarne un altro.
un esempio potrebbe essere:
select descrizione , sum(voto) from tabelle group by descrizione;
Questi sono un paio di link per una spiegazione più approfondita, ma ti suggerisco di procurarti un bel manualino su sql …
http://www-db.deis.unibo.it/courses/SIL-A/PDF/SQLb-gruppi.pdf
http://www.vbsimple.net/database/db0808.htm
6 Settembre 2006
Grazie ero sicuro che fosse semplice !
6 Maggio 2008
Salve, sto lavorando da autodidatta con asp ed access e nel realizzare una query fra due tabelle in relazione tra loro, ad es. utenti e commenti, non capisco come devo fare per non far ripetere lo stesso utente ogni volta che ha inserito un commento, bensì avere per ogni utente l’elenco di tutti i commenti e poi di seguito gli altri. Ho provato con “group by” ma da errore.
Se mi potesse aiutare le sarei grato.