query difficile

[stirkland]

Ciao gente ho un problemone con una query... praticamente devo selezionare i 5 "moduli" (adesso spiego cosa sono) con i voti più alti:

Un modulo non è altro che una cartella contenente dei file. E' memorizzato nella tabella MODULES con i campi ID,TITLE,AUTHOR ecc... (ID è intero autoincrementato)

I voti al modulo (mediante click oppure voto esplicito da 1 a 7) sono nella tabella POPULARITY con i campi ID_MODULE,VOTE,CLICK,USERNAME.

USERNAME serve a definire una relazione utente-modulo, in modo che un utente non possa votare più di una volta lo stesso modulo; CLICK è la misura della popolarità implicita (quante volte gli utenti selezionano il modulo per vederlo) e VOTE è il voto esplicito (da "meraviglioso" a "un catarro" )

Io devo selezionare i titoli dei 5 moduli più votati, quindi pensavo di selezionare, prima i moduli con CLICK maggiore e poi tra questi i moduli con VOTE maggiore...boooohhhh...e infine fare una join tra questo e la tabella MODULES per ottenere i titoli corrispondenti!

Aiutino?

[Alex'87]

Penso che la sezione sia sbagliata... Qui si parla di Java, non di SQL :master:
E da come hai spiegato non si capisce bene la struttura delle tue tabelle. Dovresti postare lo schema logico, così capiamo qualcosa :master:

[R@ve M@ster]

Innanzitutto occorre sapere che database stai utilizzando, perchè a seconda del database ci sono modi diversi per limitare i risultati a 5 record.

Poi non è molto chiaro se vuoi selezionare i 5 più cliccati o i 5 col campo vote più alto.

Ad ogni modo questa è la query base, alla quale va aggiunto il necessario per limitare a 5 record:

SELECT title FROM modules,popularity
WHERE popularity.id_module = modules.id
ORDER BY click desc,vote desc

nel caso di Mysql alla query va aggiunto

LIMIT 5

Per oracle alla clausola WHERE va aggiunto

AND rownum<=5

[stirkland]

Intanto ringrazio per le risposte!!

Dunque...quale DB uso? Erhm....utilizzo la libreria hsqldb.jar e francamente non so a cosa corrisponda...jdbc??

E poi ho ho dovuto fare una modifica alle tabelle..perchè mi sono accorto di un errore veramente scemo: ora le tabelle sono così:

USERS: name, surname, username,.....
MODULES: id, title, author....
POPULARITY: _id_module, summation, counter, click (_id_module è chiave esterna, con vincolo di unicità)
VOTERS: _id_module, _username

_id_module è chiave esterna che si riferisce a id di MODULES; _username in VOTERS idem per username in USERS

summation e counter contengono rispettivamente la somma di tutti i voti di tutti gli utenti (da 1 a 7) e il numero totale di volte che il modulo è stato votato: così posso fare la media (sommatoria/N con N il numero totale dei voti)

click contiene il numero di volte che il modulo è stato solamente cliccato (quindi voto implicito)

e la tabella VOTERS mi serve a mantenere il legame modulo votato-utente votante; vincolo di unicità per la coppia (_id_modules,_username)

Io voglio selezionare i titoli dei 5 moduli più popolari, quindi quelli con voto (summation/counter) più alto TRA QUELLI CON CLICK PIU' ALTO!! Almeno....erhm...mi sembra una misura di gradimento non troppo banale...

[stirkland]

Ragazzi scusate....continuo a far modifiche al DB...

le tabelle sono USERS, MODULES e POPULARITY:

POPULARITY: _id_module,vote,click,_username (vincolo di unicità per la coppia _id_module-_username)

A parte questo vale tutto quello che ho detto prima, cioè voglio sapere i 5 moduli più popolari: quelli con voto più alto tra quelli con click più alto; il conteggio totale dei click lo dovrò fare con la parola chiave COUNT, il voto invece sarà calcolato con AVG (media dei voti di tutti gli utenti votanti)

[stirkland]

Erhm....duh..
no COUNT...volevo dire SUM

[stirkland]

Ok ho risolto!!!!!

Ho creato una vista MOST_POPULAR così fatta:
create view most_popular (_id_module,vote,click) as
select _id_module,sum(click),avg(vote) from popularity group by (_id_module) order by(sum(click)) desc

Poi la utilizzo per estrarre gli _id_module che mi interessano e successivamente i relativi titoli:
select title from modules inner join most_popular on id=_id_module where _id_module in (select top 5 _id_module from most_popular where vote>=5 order by (vote) desc)