[mySQL] dubbio sempiterno sul GROUP BY

[Argosoft]

Della serie: ora basta!! è ora di finirla!! :p

Allora, devo togliermi questo dubbio definitivamente sennò non dormo più la notte.

Poniamo un esempio qualsiasi. Io ho una lista di persone in un'azienda:


ID Ruolo Nome

1 Segreteria Andrea Rossi
2 Segreteria Giovanni Rossi
3 Amministrazione Peppe Rossi
4 Impiegato Giulia Rossi
5 Impiegato Ubaldo Rossi
6 Impiegato Gianmarco Rossi
7 Impiegato Francesca Rossi
8 Impiegato Elena Rossi
9 Impiegato Carlo Rossi
10 Pianificazione Paolo Rossi
11 Pulizie Simone Rossi
12 Pulizie Genoveffa Rossi



Se io facessi una select con group by sul Ruolo, avrei un risultato simile:



ID Ruolo Nome

1 Segreteria Andrea Rossi
3 Amministrazione Peppe Rossi
4 Impiegato Giulia Rossi
10 Pianificazione Paolo Rossi
12 Pulizie Genoveffa Rossi


Però... con che criterio???
Voglio dire: ok, i dati vengono raggruppati per ruolo, ma quali righe per ogni gruppo vengono scelte? Le prime incontrate in ordine di presenza nel db? Le ultime? A random? E SOPRATTUTTO:
Posso scegliere quali righe farmi restituire?
E' un problema che ho incontrato un sacco di volte e non ne sono mai venuto a capo. Nell'esempio di prima, se io per ogni gruppo volessi prendere che ne so, l'ultimo elemento in ordine alfabetico? O il primo? O comunque qualcosa in particolare?

Ho guardato anche qui ma non ho avuto speranze lo stesso:
http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html


Qualcuno sa aiutarmi?! :D :help: se mi risolvete questa.... se anche mi dite NON SE PO' FA' io torno a dormire la notte :D


Thanks!!

[john_revelator]

Ciao, vedi se questo link può esserti utile. :)

http://forum.html.it/forum/showthread.php?s=&threadid=1048671&highlight=group+by+max

[Argosoft]

Uhm..... quindi dici che nel singolo group by non c'è possibilità di scelta? Bisogna per forza usare una subquery?

[john_revelator]

Per quanto io sappia non è possibile con una semplice query estrarre il valore maggiore di ciascun gruppo o effettuare operazioni simili. :)

[gugoXX]

Della serie: ora basta!! è ora di finirla!! :p

Allora, devo togliermi questo dubbio definitivamente sennò non dormo più la notte.

Poniamo un esempio qualsiasi. Io ho una lista di persone in un'azienda:

...

Se io facessi una select con group by sul Ruolo, avrei un risultato simile:



ID Ruolo Nome

1 Segreteria Andrea Rossi
3 Amministrazione Peppe Rossi
4 Impiegato Giulia Rossi
10 Pianificazione Paolo Rossi
12 Pulizie Genoveffa Rossi


Però... con che criterio???


QUESTA QUERY e' sbagliata.
quasliasi motore di database serio impedisce l'esecuzioni di query come questa.
MySql evidentemente non e' serio.
Quando si opera con GROUP BY, si possono selezionare solo le colonne messe in GROUP BY e funzioni di gruppo (e costanti e funzioni scalari che accettano parametri scelti tra le colonne in group by)


Voglio dire: ok, i dati vengono raggruppati per ruolo, ma quali righe per ogni gruppo vengono scelte? Le prime incontrate in ordine di presenza nel db? Le ultime? A random? E SOPRATTUTTO:
Posso scegliere quali righe farmi restituire?

esattamente per questo motivo i motori seri impediscono tali query, perche' il risultato e' impredicibile o arbitrariamente scelto dal motore secondo regole non standard.

Quindi... cosa vorresti fare? E troveremo una query.

[kk3z]

Non è vero che è sbagliata, è che non è questo il suo uso. Chi se ne frega di quale venga estratto, l'importante è che non vengono estratte tutte (o quella che uno vuole) e quindi non serve a questo. Ma se uno per esempio vuole contare quanti impiegati ci sono per ogni ruolo, perchè non la si dovrebbe usare?

[gugoXX]

Non è vero che è sbagliata, è che non è questo il suo uso. Chi se ne frega di quale venga estratto, l'importante è che non vengono estratte tutte (o quella che uno vuole) e quindi non serve a questo. Ma se uno per esempio vuole contare quanti impiegati ci sono per ogni ruolo, perchè non la si dovrebbe usare?

Perche' la query per estrarre i dati di cui sopra e'
SELECT Id, Ruolo, Nome
FROM tabella
GROUP BY Id

e da SQL-92 standard (e successivi) non potresti estrarre Ruolo e Nome, dato che non fanno parte della GROUP BY.
E fra l'altro mi risulta possa essere eseguita solo con MySQL.

Per contare quanti impiegati ci sono in ogni ruolo la query e' un'altra
SELECT Ruolo, COUNT(*)
FROM tabella
GROUP BY Ruolo

[Argosoft]

Perche' la query per estrarre i dati di cui sopra e'
SELECT Id, Ruolo, Nome
FROM tabella
GROUP BY Id

e da SQL-92 standard (e successivi) non potresti estrarre Ruolo e Nome, dato che non fanno parte della GROUP BY.
E fra l'altro mi risulta possa essere eseguita solo con MySQL.

Per contare quanti impiegati ci sono in ogni ruolo la query e' un'altra
SELECT Ruolo, COUNT(*)
FROM tabella
GROUP BY Ruolo

Questa non la sapevo, evidentemente mi sono abituato male? :p

Non è che io abbia un obiettivo in particolare, non cerco una query precisa, mi interessava appunto sapere come venivano estratti i risultati dentro a un group by, cosa che da quanto ho capito da questo passaggio

esattamente per questo motivo i motori seri impediscono tali query, perche' il risultato e' impredicibile o arbitrariamente scelto dal motore secondo regole non standard.

non mi dà molte possibilità :cry:


Anzi no, un esempio ce l'ho.
Mettiamo il caso di una fotogallery, che ha delle categorie e delle foto. Con una query (senza subquery magari) vorrei tirare fuori la lista delle categorie con inclusa l'ultima foto inserita in ognuna (indicazione che prenderei dalla data di inserimento della foto).
Se potessi, farei un group by ordinando i risultati del gruppo delle foto e scegliendone appunto una in particolare, cioè la maggiore in data.

Tutto questo in una sola query. E' possibile?

Thanks!

[john_revelator]

Per fare ciò che chiedi è necessario l'uso delle subquery, per lo meno in mysql. Il link che ti ho indicato mi sembra faccia proprio al caso tuo. :)

[gugoXX]

Anzi no, un esempio ce l'ho.
Mettiamo il caso di una fotogallery, che ha delle categorie e delle foto. Con una query (senza subquery magari) vorrei tirare fuori la lista delle categorie con inclusa l'ultima foto inserita in ognuna (indicazione che prenderei dalla data di inserimento della foto).
Se potessi, farei un group by ordinando i risultati del gruppo delle foto e scegliendone appunto una in particolare, cioè la maggiore in data.

Tutto questo in una sola query. E' possibile?

Thanks!

E' possibile in piu' modi. Il piu' semplice che mi viene in mente e' usando una INLINE query, che e' una SELECT che restitusice uno scalare, e che puo' dipendere da altre colonne di ciascun record.
Non conosco alcun motore SQL che non le permetta (access forse?)

Innanzitutto per risolvere la tua domanda devi essere SICURO a priori che la foto scattata per ultima sia una sola, ovvero che non ci siano 2 foto scattate dalla stessa persona nello stesso momento, pena ECCEZIONE durante l'esecuzione.
Se questo e' verificato sarebbe anche bene rinforzarlo a livello database con un constraint UNIQUE (user_id, datafoto)

Sarebbe qualcosa tipo:


SELECT user_id,
MAX(datafoto) as dataultimafoto,
(SELECT codicefoto
FROM tabella tint
WHERE tint.user_id=text.user_id AND tint.datafoto=MAX(text.datafoto)
) AS codiceultimafoto
FROM tabella text
GROUP BY user_id


Dove al posto di codicefoto puoi mettere uno qualunque dei campi che ti servono dell'ultima foto (filename?)
La parte dentro la parentesi e' una INLINE query, che serve di fatto a calcolare il valore della colonna "codiceultimafoto" per ciascuna delle righe della master table, che per costrutto sara' una riga per ciascun utente.

Se i campi che ti servono per l'ultima di ciascuno fossero 2 o piu' di 2, la INLINE QUERY e' sconsigliata, dato che dovresti scrivere una costrutto analogo per ciascuna delle colonne interessanti.
Si procederebbe con una JOIN con una subquery, con un costrutto abbastanza diverso.


SELECT * FROM (
SELECT user_id,MAX(datafoto) dataultimafoto
FROM tabella
GROUP BY user_id
) AS master JOIN tabella detail
ON (master.user_id=tabella.user_id
AND master.dataultimafoto=tabella.datafoto)


Anche qui devi essere SICURO a priori che la foto scattata per ultima sia una sola, ovvero che non ci siano 2 foto scattate dalla stessa persona nello stesso momento, pena DUPLICAZIONE dati durante l'esecuzione.

Qualsiasi scelta tu faccia, accederai 2 volte alla tabella, una volta per cercare qual e' l'ultima foto, un'altra volta per andarne a pescare i dati.
Per questo motivo nell'SQL sono state introdotte le ANALYTIC function, che permettono di effettuare operazioni di correlazione intrariga.
Se MySql le supporta (ma non sono tanto standard, quasi tutti ce le hanno oggi, ma magari con nomi diversi)
puoi fare qualcosa analogo al seguente, codice per Oracle.


SELECT DISTINCT user_id,
MAX(datafoto) OVER (PARTITION BY user_id) dataultimafoto,
FIRST_VALUE(nomefile) OVER (PARTITION BY user_id ORDER BY datafoto desc) nomefileultimafoto,
FIRST_VALUE(attributoX) OVER (PARTITION BY user_id ORDER BY datafoto desc) attributoXultimafoto
FROM tabella


Dove la tabella viene letta e smastruzzata una volta sola, e dove puoi avere tutti gli attributi che vuoi relativamente all'ultima foto.
E dove non devi neppure avere il constraint di unicita' della data dell'ultima foto. In questo caso la query funzionera', ma dove a parita' di ultima data ti restituierebbe uno a caso degli attributi di una delle N ultime foto (non necessariamente appartenenti tutti alla stessa delle N foto).

Ultimo caso, che serve precisamente a risolvere questo problema, ma dove ho trovato solo Oracle in grado di risolverlo, e' quello di usare funzioni analitiche di gruppo.


SELECT user_id,
MAX(datafoto) dataultimafoto,
MAX(nomefile) KEEP (DENSE_RANK LAST ORDER BY datafoto) nomefileultimafoto,
MAX(attributoX) KEEP (DENSE_RANK LAST ORDER BY datafoto) attributoXultimafoto
FROM tabella
GROUP BY user_id


La cui spiegazione aprirei solo agli interessati.
e che in Oracle darebbe direi il piano di esecuzione migliore.

[john_revelator]

@gugoxx: leggo sempre con molto interesse le tue dettagliate spiegazioni sull'SQL. Ciao e complimenti. :)