MySQL “Raggruppa per” e “Ordina per”

Voglio essere in grado di selezionare un gruppo di righe da una tabella di e-mail e raggrupparle per il mittente. La mia query è simile a questa:

SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` GROUP BY LOWER(`fromEmail`) ORDER BY `timestamp` DESC 

La query funziona quasi come lo voglio io: seleziona i record raggruppati per e-mail. Il problema è che l’object e il timestamp non corrispondono al record più recente per un particolare indirizzo e-mail.

Ad esempio, potrebbe restituire:

 fromEmail: john@example.com, subject: hello fromEmail: mark@example.com, subject: welcome 

Quando i record nel database sono:

 fromEmail: john@example.com, subject: hello fromEmail: john@example.com, subject: programming question fromEmail: mark@example.com, subject: welcome 

Se l’argomento “domanda di programmazione” è il più recente, come posso ottenere che MySQL selezioni quel record quando raggruppa le e-mail?

Una soluzione semplice è racchiudere la query in una sottoselezione con l’istruzione ORDER prima e applicare GROUP BY in seguito :

 SELECT * FROM ( SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` ORDER BY `timestamp` DESC ) AS tmp_table GROUP BY LOWER(`fromEmail`) 

Questo è simile all’utilizzo del join ma sembra molto più bello.

L’utilizzo di colonne non aggregate in un SELECT con una clausola GROUP BY non è standard. MySQL generalmente restituirà i valori della prima riga trovata e scarterà il resto. Qualsiasi clausola ORDER BY si applicherà solo al valore della colonna restituita, non a quelli scartati.

AGGIORNAMENTO IMPORTANTE Selezionare le colonne non aggregate utilizzate per funzionare nella pratica ma non dovrebbe essere invocato. Secondo la documentazione di MySQL “questo è utile soprattutto quando tutti i valori in ogni colonna non aggregata non nominata in GROUP BY sono gli stessi per ogni gruppo.Il server è libero di scegliere qualsiasi valore da ciascun gruppo, quindi a meno che non siano gli stessi, i valori scelti sono indeterminati . ”

A partire dal 5.6.21 ho notato problemi con GROUP BY sulla tabella temporanea che ripristina l’ordinamento ORDER BY.

A partire da 5.7.5 ONLY_FULL_GROUP_BY è abilitato di default, cioè è imansible utilizzare colonne non aggregate.

Vedi http://www.cafewebmaster.com/mysql-order-sort-group https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html https: //dev.mysql .com / doc / refman / 5.7 / it / group-by-handling.html

Ecco un approccio:

 SELECT cur.textID, cur.fromEmail, cur.subject, cur.timestamp, cur.read FROM incomingEmails cur LEFT JOIN incomingEmails next on cur.fromEmail = next.fromEmail and cur.timestamp < next.timestamp WHERE next.timestamp is null and cur.toUserID = '$userID' ORDER BY LOWER(cur.fromEmail) 

Fondamentalmente, ti unisci al tavolo su se stesso, cercando le righe successive. Nella clausola where dichiari che non ci possono essere righe successive. Questo ti dà solo l'ultima riga.

Se possono esserci più e-mail con lo stesso timestamp, questa query dovrebbe essere perfezionata. Se c'è una colonna ID incrementale nella tabella email, cambia il JOIN come:

 LEFT JOIN incomingEmails next on cur.fromEmail = next.fromEmail and cur.id < next.id 

Esegui un GROUP BY dopo l’ORDINE BY avvolgendo la tua query con GROUP BY in questo modo:

 SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from 

Secondo lo standard SQL non è ansible utilizzare colonne non aggregate nell’elenco di selezione. MySQL consente tale utilizzo (modalità SOLO ONLY_FULL_GROUP_BY utilizzata) ma il risultato non è prevedibile.

ONLY_FULL_GROUP_BY

Per prima cosa devi selezionare Email, MIN (leggi), quindi, con seconda query (o sottoquery) – Oggetto.

Come già indicato in una risposta, la risposta corrente è errata, poiché GROUP BY seleziona arbitrariamente il record dalla finestra.

Se si utilizza MySQL 5.6 o MySQL 5.7 con ONLY_FULL_GROUP_BY , la query corretta (deterministica) è:

 SELECT incomingEmails.* FROM ( SELECT fromEmail, MAX(timestamp) `timestamp` FROM incomingEmails GROUP BY fromEmail ) filtered_incomingEmails JOIN incomingEmails USING (fromEmail, timestamp) GROUP BY fromEmail, timestamp 

Affinché la query possa essere eseguita in modo efficiente, è necessaria un’indicizzazione corretta.

Si noti che a fini di semplificazione, ho rimosso il LOWER() , che nella maggior parte dei casi, non verrà utilizzato.

Ho lottato con entrambi questi approcci per query più complesse di quelle mostrate, perché l’approccio delle subquery era orribilmente inefficiente, indipendentemente dagli indici che ho inserito, e perché non riuscivo a ottenere l’auto join esterno tramite Hibernate

Il modo migliore (e più semplice) per farlo è raggruppare per qualcosa che è costruito per contenere una concatenazione dei campi richiesti e quindi per estrarli usando espressioni nella clausola SELECT. Se hai bisogno di fare un MAX () assicurati che il campo che vuoi MAX () sia sempre alla fine più significativa dell’entity framework concatenata.

La chiave per capire questo è che la query può avere senso solo se questi altri campi sono invarianti per qualsiasi quadro che soddisfa Max (), quindi in termini di tipo gli altri pezzi della concatenazione possono essere ignorati. Spiega come farlo nella parte inferiore di questo link. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Se puoi ottenere un evento insert / update (come un trigger) per pre-calcolare la concatenazione dei campi puoi indicizzarlo e la query sarà veloce come se il gruppo fosse finito solo il campo che volevi effettivamente MAX ( ). Puoi persino usarlo per ottenere il massimo da più campi. Lo uso per fare interrogazioni su alberi multi-dimensionali espressi come insiemi nidificati.