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 ( parola_uno e parola_due )
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.
[tags]mysql,database[/tags]
questo è un trackback manuale, perchè quello automatico non mi funzia 😉
http://www.mucignat.com/blog/archives/165-Il-motore-di-ricerca-migliore.html
Grazie del suggerimento, stavo cercando qualcosa di simile per la mia tesi… oltre che nella bibliografia ti metto anche nei ringraziamenti! 😀
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 id_immagine , descrizione , voto(numerico) il risultato della somma dei voti di ogni record relativo allo stesso id_immagine ordinati in modo decrescente.
Es. se l’immagine id_immagine 1 ha ottenuto tre volte un voto (una volta 1, laseconda 5, la terza 5) venga fuori che id_immagine 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.
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/db_08_08.htm
Grazie ero sicuro che fosse semplice !
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.