mysql - with - t sql select max for each group




SQL seleziona solo le righe con valore massimo su una colonna (20)

Ho questa tabella per i documenti (versione semplificata qui):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Come seleziono una riga per ID e solo il massimo numero di giri?
Con i dati sopra riportati, il risultato dovrebbe contenere due righe: [1, 3, ...] e [2, 1, ..] . Sto usando MySQL .

Attualmente utilizzo i controlli del ciclo while per rilevare e sovrascrivere i vecchi giri dal set di risultati. Ma è questo l'unico metodo per raggiungere il risultato? Non c'è una soluzione SQL ?

Aggiornare
Come suggeriscono le risposte, c'è una soluzione SQL, e qui una demo di sqlfiddle .

Aggiornamento 2
Ho notato che dopo aver aggiunto lo sqlfiddle sopra, la velocità con cui la domanda è upvoted ha superato la percentuale di upvote delle risposte. Questa non è stata l'intenzione! Il violino si basa sulle risposte, in particolare sulla risposta accettata.


A prima vista...

Tutto ciò di cui hai bisogno è una clausola GROUP BY con la funzione di aggregazione MAX :

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Non è mai così semplice, vero?

Ho appena notato che hai bisogno anche della colonna del content .

Questa è una domanda molto comune in SQL: trovare l'intero dato per la riga con un valore massimo in una colonna per un identificatore di gruppo. L'ho sentito molto durante la mia carriera. In realtà, è stata una delle domande a cui ho risposto nell'intervista tecnica del mio attuale lavoro.

In realtà, è così comune che la community di ha creato un singolo tag solo per rispondere a domande del genere: greatest-n-per-group .

Fondamentalmente, hai due approcci per risolvere questo problema:

Partecipare con group-identifier, max-value-in-group semplice group-identifier, max-value-in-group

In questo approccio, per prima cosa trovi l' group-identifier, max-value-in-group (già risolto sopra) in una sottoquery. Quindi aggiungi la tua tabella alla sottoquery con uguaglianza su entrambi group-identifier e max-value-in-group :

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Sinistra Unirsi con se stessi, modificare condizioni e filtri

In questo approccio, hai lasciato aderire al tavolo con se stesso. L'uguaglianza, ovviamente, va group-identifier . Quindi, 2 mosse intelligenti:

  1. La seconda condizione di join ha il valore del lato sinistro inferiore al valore corretto
  2. Quando esegui il passaggio 1, la riga (s) che effettivamente ha il valore massimo avrà NULL nella parte destra (è un LEFT JOIN , ricorda?). Quindi, filtriamo il risultato unito, mostrando solo le righe in cui il lato destro è NULL .

Quindi finisci con:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Conclusione

Entrambi gli approcci portano esattamente lo stesso risultato.

Se hai due righe con il max-value-in-group per group-identifier , entrambe le righe saranno nel risultato in entrambi gli approcci.

Entrambi gli approcci sono compatibili ANSI SQL, quindi funzioneranno con il tuo RDBMS preferito, indipendentemente dal suo "sapore".

Entrambi gli approcci sono anche ottimizzati per le prestazioni, tuttavia il tuo chilometraggio può variare (RDBMS, struttura DB, indici, ecc.). Quindi quando scegli un approccio rispetto all'altro, benchmark . E assicurati di scegliere quello che ha più senso per te.


Cosa ne pensi di questo:

select all_fields.*  
from  (select id, MAX(rev) from yourtable group by id) as max_recs  
left outer join yourtable as all_fields  
on max_recs.id = all_fields.id

Ecco un bel modo per farlo

Usa il seguente codice:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)

Ecco un'altra soluzione per recuperare i record solo con un campo che ha il valore massimo per quel campo. Questo funziona per SQL400, che è la piattaforma su cui lavoro. In questo esempio, i record con il valore massimo nel campo FIELD5 verranno recuperati dalla seguente istruzione SQL.

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)

La mia preferenza è usare il minor numero possibile di codice ...

Puoi farlo usando IN prova questo:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

a mio avviso è meno complicato ... più facile da leggere e mantenere.


Lo userei:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

La sottoquery SELECT non è forse troppo efficace, ma nella clausola JOIN sembra essere utilizzabile. Non sono un esperto nell'ottimizzare le query, ma ho provato a MySQL, PostgreSQL, FireBird e funziona molto bene.

È possibile utilizzare questo schema in più join e con la clausola WHERE. È il mio esempio di lavoro (risolvere identico al tuo problema con la tabella "fermo"):

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

Viene chiesto ai tavoli con ragazzi e ai record, e richiede meno di 0,01 secondi su una macchina veramente non troppo forte.

Non userei la clausola IN (come sopra menzionata sopra). IN viene dato per l'uso con brevi elenchi di costanti e non per essere il filtro di query creato sulla sottoquery. È perché subquery in IN viene eseguita per ogni record scansionato che può rendere la query prendendo molto tempo moooolto.


Mi piace utilizzare una soluzione basata su NOT EXIST per questo problema:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

Nessuna di queste risposte ha funzionato per me.

Questo è ciò che ha funzionato per me.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

Ordinato il campo di rev in ordine inverso e quindi raggruppato per id che ha dato la prima riga di ogni raggruppamento che è quella con il valore di giri più alto.

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

Testato su http://sqlfiddle.com/ con i seguenti dati

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

Ciò ha dato il seguente risultato in MySql 5.5 e 5.6

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two

Penso che questa sia la soluzione più semplice:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELEZIONA *: restituisce tutti i campi.
  • FROM Employee: Table cercato su.
  • (SELEZIONA * ...) sottoquery: restituisce tutte le persone, ordinate per Salario.
  • GROUP BY employeeub.Salary:: impone la riga stipendio top-ordinata di ciascun dipendente come risultato restituito.

Se ti capita di aver bisogno solo di una riga, è ancora più semplice:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

Penso anche che sia il più semplice da abbattere, capire e modificare per altri scopi:

  • ORDINA PER Dipendente. DESCario formale: ordina i risultati in base allo stipendio, con gli stipendi più alti in primo luogo.
  • LIMITE 1: restituire solo un risultato.

Comprendere questo approccio, risolvere uno di questi problemi simili diventa banale: ottenere dipendenti con il salario più basso (cambiare DESC in ASC), ottenere i primi dieci dipendenti (modificare il LIMITE 1 al LIMIT 10), ordinare per mezzo di un altro campo (modificare ORDINA PER Dipendente. Ordine a ORDINE da parte di Employee.Commission), ecc.


Qualcosa come questo?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

Questa soluzione effettua solo una selezione da YourTable, quindi è più veloce. Funziona solo per MySQL e SQLite (per SQLite rimuovi DESC) in base al test su sqlfiddle.com. Forse può essere ottimizzato per lavorare su altre lingue che non conosco.

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id

SELECT * FROM Employee where Employee.Salary in (selezionare max (salary) dal gruppo Employee da Employe_id) ORDER BY Employee.Salary


Se hai molti campi nell'istruzione select e vuoi l'ultimo valore per tutti questi campi attraverso il codice ottimizzato:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

Sono sbalordito che nessuna risposta ha offerto la soluzione per le finestre SQL:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

Aggiunto in SQL standard ANSI / ISO SQL standard: 2003 e successivamente esteso con ANSI / ISO Standard SQL: 2008, le funzioni di finestra (o finestra) sono ora disponibili con tutti i principali fornitori. Esistono più tipi di funzioni di classifica disponibili per gestire un problema di parità: RANK, DENSE_RANK, PERSENT_RANK .


Un altro modo per eseguire il lavoro consiste nell'utilizzare la funzione analitica MAX () nella clausola OVER PARTITION

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

L'altra soluzione OVER PARTITION già documentata in questo post è

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
      FROM YourTable
    ) t
  WHERE t.rank = 1 

Questo SELECT 2 funziona bene su Oracle 10g.


Una terza soluzione che non vedo quasi mai menzionata è MySQL e assomiglia a questo:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

Sì, sembra orribile (conversione in stringa e ritorno, ecc.) Ma nella mia esperienza è solitamente più veloce delle altre soluzioni. Forse è solo per i miei casi d'uso, ma l'ho usato su tabelle con milioni di record e molti ID univoci. Forse è perché MySQL è piuttosto brutto nell'ottimizzare le altre soluzioni (almeno nei 5.0 giorni in cui ho trovato questa soluzione).

Una cosa importante è che GROUP_CONCAT ha una lunghezza massima per la stringa che può accumulare. Probabilmente vuoi aumentare questo limite impostando la variabile group_concat_max_len . E tieni presente che questo sarà un limite al ridimensionamento se hai un numero elevato di righe.

Ad ogni modo, quanto sopra non funziona direttamente se il tuo campo di contenuto è già testo. In tal caso probabilmente vorrai usare un separatore diverso, come \ 0 forse. Inoltre, potrai eseguire il limite di group_concat_max_len più rapidamente.


ecco un'altra soluzione spero che possa aiutare qualcuno

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

select * from yourtable
group by id
having rev=max(rev);






greatest-n-per-group