Ordine MySQL prima di raggruppare per

Ci sono un sacco di domande simili da trovare qui ma non credo che risponda adeguatamente alla domanda.

Continuerò dall’attuale domanda più popolare e utilizzerò il loro esempio se va bene.

L’attività in questo caso è di ottenere l’ultimo post per ogni autore nel database.

La query di esempio produce risultati inutilizzabili poiché non sempre è il post più recente che viene restituito.

SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY wp_posts.post_author ORDER BY wp_posts.post_date DESC 

L’attuale risposta accettata è

 SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY wp_posts.post_author HAVING wp_posts.post_date = MAX(wp_posts.post_date) <- ONLY THE LAST POST FOR EACH AUTHOR ORDER BY wp_posts.post_date DESC 

Sfortunatamente questa risposta è semplice e semplice e in molti casi produce risultati meno stabili rispetto alla query originale.

La mia soluzione migliore è utilizzare una sottoquery del modulo

 SELECT wp_posts.* FROM ( SELECT * FROM wp_posts ORDER BY wp_posts.post_date DESC ) AS wp_posts WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY wp_posts.post_author 

La mia domanda è semplice allora: Esiste comunque l’ordine di file prima del raggruppamento senza ricorrere a una sottoquery?

Modifica : Questa domanda era una continuazione di un’altra domanda e le specifiche della mia situazione sono leggermente diverse. Puoi (e dovresti) assumere che ci sia anche un wp_posts.id che è un identificatore univoco per quel particolare post.

L’utilizzo di un ORDER BY in una sottoquery non è la soluzione migliore a questo problema.

La soluzione migliore per ottenere il max(post_date) per autore è utilizzare una sottoquery per restituire la data massima e quindi aggiungerla alla tabella sia sul post_author sia sulla data massima.

La soluzione dovrebbe essere:

 SELECT p1.* FROM wp_posts p1 INNER JOIN ( SELECT max(post_date) MaxPostDate, post_author FROM wp_posts WHERE post_status='publish' AND post_type='post' GROUP BY post_author ) p2 ON p1.post_author = p2.post_author AND p1.post_date = p2.MaxPostDate WHERE p1.post_status='publish' AND p1.post_type='post' order by p1.post_date desc 

Se si dispone dei seguenti dati di esempio:

 CREATE TABLE wp_posts (`id` int, `title` varchar(6), `post_date` datetime, `post_author` varchar(3)) ; INSERT INTO wp_posts (`id`, `title`, `post_date`, `post_author`) VALUES (1, 'Title1', '2013-01-01 00:00:00', 'Jim'), (2, 'Title2', '2013-02-01 00:00:00', 'Jim') ; 

La sottoquery restituirà la data massima e l’autore di:

 MaxPostDate | Author 2/1/2013 | Jim 

Quindi, dal momento che ti stai ricongiungendo al tavolo, su entrambi i valori restituirai i dettagli completi di quel post.

Vedi SQL Fiddle con Demo .

Per espandere i miei commenti sull’utilizzo di una sottoquery per restituire dati precisi.

MySQL non ti obbliga a GROUP BY ogni colonna che includi nell’elenco SELECT . Di conseguenza, se si GROUP BY solo GROUP BY una colonna ma si restituiscono 10 colonne in totale, non è ansible garantire che gli altri valori di colonna che appartengono post_author restituito. Se la colonna non è in un GROUP BY MySQL sceglie quale valore deve essere restituito.

L’utilizzo della sottoquery con la funzione di aggregazione garantisce che l’autore e il post corretti vengano restituiti ogni volta.

Come nota a margine, mentre MySQL consente di utilizzare un ORDER BY in una sottoquery e consente di applicare un GROUP BY a non tutte le colonne nell’elenco SELECT questo comportamento non è consentito in altri database, incluso SQL Server.

La tua soluzione utilizza un’estensione della clausola GROUP BY che consente di raggruppare per alcuni campi (in questo caso, solo post_author ):

 GROUP BY wp_posts.post_author 

e seleziona le colonne non aggregate:

 SELECT wp_posts.* 

che non sono elencati nella clausola group by o che non sono usati in una funzione aggregata (MIN, MAX, COUNT, ecc.).

Uso corretto dell’estensione alla clausola GROUP BY

Questo è utile quando tutti i valori delle colonne non aggregate sono uguali per ogni riga.

Ad esempio, supponiamo di avere un tavolo GardensFlowers ( name del giardino, flower che cresce nel giardino):

 INSERT INTO GardensFlowers VALUES ('Central Park', 'Magnolia'), ('Hyde Park', 'Tulip'), ('Gardens By The Bay', 'Peony'), ('Gardens By The Bay', 'Cherry Blossom'); 

e vuoi estrarre tutti i fiori che crescono in un giardino, dove crescono più fiori. Quindi devi usare una sottoquery, ad esempio potresti usare questo:

 SELECT GardensFlowers.* FROM GardensFlowers WHERE name IN (SELECT name FROM GardensFlowers GROUP BY name HAVING COUNT(DISTINCT flower)>1); 

Se hai bisogno di estrarre tutti i fiori che sono gli unici fiori nel garder, puoi semplicemente cambiare la condizione HAVING COUNT(DISTINCT flower)=1 in HAVING COUNT(DISTINCT flower)=1 , ma MySql ti permette anche di usare questo:

 SELECT GardensFlowers.* FROM GardensFlowers GROUP BY name HAVING COUNT(DISTINCT flower)=1; 

nessuna subquery, non standard SQL, ma più semplice.

Uso errato dell’estensione alla clausola GROUP BY

Ma cosa succede se SELEZIONA colonne non aggregate che non sono uguali per ogni riga? Qual è il valore che MySql sceglie per quella colonna?

Sembra che MySql scelga sempre il PRIMO valore che incontra.

Per assicurarti che il primo valore che incontra sia esattamente il valore che desideri, devi applicare un GROUP BY a una query ordinata, quindi la necessità di utilizzare una sottoquery. Non puoi farlo diversamente.

Partendo dal presupposto che MySql sceglie sempre la prima riga che incontra, stai correggendo correttamente le righe prima di GROUP BY. Ma sfortunatamente, se leggi attentamente la documentazione, noterai che questa ipotesi non è vera.

Quando si selezionano colonne non aggregate che non sono sempre le stesse, MySql è libero di scegliere qualsiasi valore, quindi il valore risultante che effettivamente mostra è indeterminato .

Vedo che questo trucco per ottenere il primo valore di una colonna non aggregata è molto usato, e di solito funziona quasi sempre, lo uso anch’io a volte (a mio rischio). Ma dal momento che non è documentato, non puoi fare affidamento su questo comportamento.

Questo collegamento (grazie a ypercube!) GROUP BY è stato ottimizzato per mostrare una situazione in cui la stessa query restituisce risultati diversi tra MySql e MariaDB, probabilmente a causa di un diverso motore di ottimizzazione.

Quindi, se questo trucco funziona, è solo una questione di fortuna.

La risposta accettata sull’altra domanda mi sembra sbagliata:

 HAVING wp_posts.post_date = MAX(wp_posts.post_date) 

wp_posts.post_date è una colonna non aggregata e il suo valore sarà ufficialmente indeterminato, ma probabilmente sarà il primo post_date incontrato. Ma poiché il trucco GROUP BY viene applicato a una tabella non ordinata, non è sicuro quale sia il primo post_date incontrato.

Probabilmente restituirà post che sono gli unici post di un singolo autore, ma anche questo non è sempre certo.

Una ansible soluzione

Penso che questa potrebbe essere una ansible soluzione:

 SELECT wp_posts.* FROM wp_posts WHERE id IN ( SELECT max(id) FROM wp_posts WHERE (post_author, post_date) = ( SELECT post_author, max(post_date) FROM wp_posts WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY post_author ) AND wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY post_author ) 

Nella query interna restituisco la data massima di post per ogni autore. Prenderò in considerazione il fatto che lo stesso autore potrebbe teoricamente avere due post contemporaneamente, quindi ricevo solo l’ID massimo. E poi sto restituendo tutte le righe che hanno quegli ID massimi. Potrebbe essere reso più veloce usando i join anziché la clausola IN.

(Se sei sicuro che l’ ID sta solo aumentando, e se ID1 > ID2 significa anche post_date1 > post_date2 , allora la query potrebbe essere resa molto più semplice, ma non sono sicuro che sia così).

Quello che leggerete è piuttosto hacky, quindi non provatelo a casa!

In SQL, in generale, la risposta alla tua domanda è NO , ma a causa della modalità rilassata di GROUP BY (menzionata da @bluefeet ), la risposta è SI in MySQL.

Supponiamo che tu abbia un indice BTREE su (post_status, post_type, post_author, post_date). Come appare l’indice sotto il cofano?

(post_status = ‘publish’, post_type = ‘post’, post_author = ‘utente A’, post_date = ‘2012-12-01’) (post_status = ‘publish’, post_type = ‘post’, post_author = ‘utente A’, post_date = ‘2012-12-31’) (post_status = ‘pubblica’, post_type = ‘post’, post_author = ‘utente B’, post_date = ‘2012-10-01’) (post_status = ‘pubblica’, post_type = ‘ post ‘, post_author =’ utente B ‘, post_date =’ 2012-12-01 ‘)

Questo è il dato ordinato per tutti quei campi in ordine crescente.

Quando si esegue un GROUP BY per impostazione predefinita, ordina i dati in base al campo di raggruppamento ( post_author , nel nostro caso, post_status, post_type sono richiesti dalla clausola WHERE ) e se esiste un indice corrispondente, prende i dati per ogni primo record in ordine crescente ordine. Quella è la query recupererà il seguente (il primo post per ogni utente):

(post_status = ‘publish’, post_type = ‘post’, post_author = ‘utente A’, post_date = ‘2012-12-01’) (post_status = ‘pubblica’, post_type = ‘post’, post_autore = ‘utente B’, POST_DATE = ‘2012-10-01’)

Ma GROUP BY in MySQL ti consente di specificare l’ordine in modo esplicito. E quando richiedi post_user in ordine decrescente, esso passerà attraverso il nostro indice nell’ordine opposto, prendendo comunque il primo record per ogni gruppo che è effettivamente l’ultimo.

Questo è

 ... WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY wp_posts.post_author DESC 

ci darà

(post_status = ‘publish’, post_type = ‘post’, post_author = ‘utente B’, post_date = ‘2012-12-01’) (post_status = ‘pubblica’, post_type = ‘post’, post_author = ‘utente A’, POST_DATE = ‘2012-12-31’)

Ora, quando ordini i risultati del raggruppamento per post_date, ottieni i dati che desideri.

 SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY wp_posts.post_author DESC ORDER BY wp_posts.post_date DESC; 

NB :

Questo non è quello che consiglierei per questa particolare query. In questo caso, vorrei usare una versione leggermente modificata di ciò che suggerisce @bluefeet . Ma questa tecnica potrebbe essere molto utile. Dai un’occhiata alla mia risposta qui: Recupero dell’ultimo record in ogni gruppo

Insidie : gli svantaggi dell’approccio è questo

  • il risultato della query dipende dall’indice, che è contro lo spirito dell’SQL (gli indici dovrebbero solo velocizzare le query);
  • l’indice non sa nulla della sua influenza sulla query (tu o qualcun altro in futuro potresti trovare l’indice troppo dispendioso in termini di risorse e modificarlo in qualche modo, interrompendo i risultati della query, non solo le sue prestazioni)
  • se non capisci come funziona la query, molto probabilmente dimenticherai la spiegazione in un mese e la query confonderà te e i tuoi colleghi.

Il vantaggio sono le prestazioni nei casi difficili. In questo caso, la prestazione della query dovrebbe essere la stessa della query di @ bluefeet, a causa della quantità di dati coinvolti nell’ordinamento (tutti i dati vengono caricati in una tabella temporanea e quindi ordinati; btw, la sua query richiede (post_status, post_type, post_author, post_date) indice).

Cosa vorrei suggerire :

Come ho detto, queste query rendono MySQL una perdita di tempo nell’ordinamento di enormi quantità di dati in una tabella temporanea. Nel caso in cui sia necessario il paging (vale a dire LIMIT), la maggior parte dei dati viene addirittura eliminata. Quello che farei è minimizzare la quantità di dati ordinati: questo è ordinamento e limita un minimo di dati nella sottoquery e poi si ricongiunge all’intera tabella.

 SELECT * FROM wp_posts INNER JOIN ( SELECT max(post_date) post_date, post_author FROM wp_posts WHERE post_status='publish' AND post_type='post' GROUP BY post_author ORDER BY post_date DESC -- LIMIT GOES HERE ) p2 USING (post_author, post_date) WHERE post_status='publish' AND post_type='post'; 

La stessa query utilizzando l’approccio sopra descritto:

 SELECT * FROM ( SELECT post_id FROM wp_posts WHERE post_status='publish' AND post_type='post' GROUP BY post_author DESC ORDER BY post_date DESC -- LIMIT GOES HERE ) as ids JOIN wp_posts USING (post_id); 

Tutte quelle domande con i loro piani di esecuzione su SQLFiddle .

Prova questo. Basta avere l’elenco delle ultime date di post di ogni autore . Questo è tutto

 SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' AND wp_posts.post_date IN(SELECT MAX(wp_posts.post_date) FROM wp_posts GROUP BY wp_posts.post_author) 

No. Non ha senso ordinare i record prima del raggruppamento, poiché il raggruppamento sta per mutare il set di risultati. La via subquery è il modo preferito. Se questo sta andando troppo lento, dovresti cambiare il design della tua tabella, ad esempio memorizzando l’id dell’ultimo post per ogni autore in una tabella separata, o introdurre una colonna booleana che indica per ogni autore quale dei suoi post è l’ultimo uno.

Solo per ricapitolare, la soluzione standard utilizza una subquery non correlata e si presenta così:

 SELECT x.* FROM my_table x JOIN (SELECT grouping_criteria,MAX(ranking_criterion) max_n FROM my_table GROUP BY grouping_criteria) y ON y.grouping_criteria = x.grouping_criteria AND y.max_n = x.ranking_criterion; 

Se stai utilizzando una versione antica di MySQL o un set di dati abbastanza piccolo, puoi utilizzare il seguente metodo:

 SELECT x.* FROM my_table x LEFT JOIN my_table y ON y.joining_criteria = x.joining_criteria AND y.ranking_criteria < x.ranking_criteria WHERE y.some_non_null_column IS NULL; 

Basta usare la funzione max e la funzione di gruppo

  select max(taskhistory.id) as id from taskhistory group by taskhistory.taskid order by taskhistory.datum desc 

** Le query secondarie possono avere un impatto negativo sulle prestazioni quando vengono utilizzate con set di dati di grandi dimensioni **

Query originale

 SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'post' GROUP BY wp_posts.post_author ORDER BY wp_posts.post_date DESC; 

Query modificata

 SELECT p.post_status, p.post_type, Max(p.post_date), p.post_author FROM wp_posts P WHERE p.post_status = "publish" AND p.post_type = "post" GROUP BY p.post_author ORDER BY p.post_date; 

perché sto usando max nella select clause ==> max(p.post_date) è ansible evitare query sub-select e ordinare per la colonna max dopo il gruppo by.

Per prima cosa, non usare * in select, influenza le loro prestazioni e ostacola l’uso del gruppo e ordina per. Prova questa query:

 SELECT wp_posts.post_author, wp_posts.post_date as pdate FROM wp_posts WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY wp_posts.post_author ORDER BY pdate DESC 

Quando non si specifica la tabella in ORDER BY, solo l’alias, ordineranno il risultato della selezione.