PostgreSQL: recupera la riga che ha il valore Max per una colonna

Ho a che fare con una tabella Postgres (chiamata “lives”) che contiene record con colonne per time_stamp, usr_id, transaction_id e lives_remaining. Ho bisogno di una query che mi fornisca le vite più recenti che totalizzano il totale per ogni usr_id

  1. Ci sono più utenti (distinti di usr_id)
  2. time_stamp non è un identificatore univoco: a volte gli eventi utente (uno per riga nella tabella) si verificano con lo stesso time_stamp.
  3. trans_id è unico solo per intervalli di tempo molto piccoli: nel tempo si ripete
  4. remaining_lives (per un determinato utente) può sia aumentare che diminuire nel tempo

esempio:

  time_stamp | lives_remaining | usr_id | trans_id
 -----------------------------------------
   07:00 |  1 |  1 |  1    
   09:00 |  4 |  2 |  2    
   10:00 |  2 |  3 |  3    
   10:00 |  1 |  2 |  4    
   11:00 |  4 |  1 |  5    
   11:00 |  3 |  1 |  6    
   13:00 |  3 |  3 |  1    

Poiché avrò bisogno di accedere ad altre colonne della riga con i dati più recenti per ogni dato usr_id, ho bisogno di una query che dia un risultato simile a questo:

  time_stamp | lives_remaining | usr_id | trans_id
 -----------------------------------------
   11:00 |  3 |  1 |  6    
   10:00 |  1 |  2 |  4    
   13:00 |  3 |  3 |  1    

Come accennato, ogni utente può guadagnare o perdere vite, e talvolta questi eventi con data e ora si verificano così vicini che hanno lo stesso timestamp! Pertanto questa query non funzionerà:

SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM (SELECT usr_id, max(time_stamp) AS max_timestamp FROM lives GROUP BY usr_id ORDER BY usr_id) a JOIN lives b ON a.max_timestamp = b.time_stamp 

Invece, ho bisogno di usare sia time_stamp (prima) che trans_id (secondo) per identificare la riga corretta. Devo anche passare tali informazioni dalla subquery alla query principale che fornirà i dati per le altre colonne delle righe appropriate. Questa è la query compromise che ho ottenuto di lavorare:

 SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM (SELECT usr_id, max(time_stamp || '*' || trans_id) AS max_timestamp_transid FROM lives GROUP BY usr_id ORDER BY usr_id) a JOIN lives b ON a.max_timestamp_transid = b.time_stamp || '*' || b.trans_id ORDER BY b.usr_id 

Ok, quindi funziona, ma non mi piace. Richiede una query all’interno di una query, un self join, e mi sembra che potrebbe essere molto più semplice afferrando la riga che MAX ha trovato per avere il timestamp più grande e trans_id. La tabella “lives” ha decine di milioni di righe da analizzare, quindi vorrei che questa query fosse il più veloce ed efficiente ansible. Sono nuovo di RDBM e Postgres in particolare, quindi so che ho bisogno di fare un uso efficace degli indici appropriati. Sono un po ‘perso su come ottimizzare.

Ho trovato una discussione simile qui . Posso eseguire un tipo di Postgres equivalente a una funzione analitica Oracle?

Qualsiasi consiglio sull’accesso alle informazioni relative alle colonne utilizzate da una funzione di aggregazione (come MAX), la creazione di indici e la creazione di query migliori sarebbe molto apprezzato!

PS È ansible utilizzare il seguente per creare il mio esempio:

 create TABLE lives (time_stamp timestamp, lives_remaining integer, usr_id integer, trans_id integer); insert into lives values ('2000-01-01 07:00', 1, 1, 1); insert into lives values ('2000-01-01 09:00', 4, 2, 2); insert into lives values ('2000-01-01 10:00', 2, 3, 3); insert into lives values ('2000-01-01 10:00', 1, 2, 4); insert into lives values ('2000-01-01 11:00', 4, 1, 5); insert into lives values ('2000-01-01 11:00', 3, 1, 6); insert into lives values ('2000-01-01 13:00', 3, 3, 1); 

Su una tabella con righe pseudo-casuali 158k (usr_id distribuite uniformsmente tra 0 e 10k, trans_id distribuite uniformsmente tra 0 e 30),

In base al costo della query, di seguito, mi riferisco alla stima dei costi dell’ottimizzatore basata sui costi di Postgres (con i valori xxx_cost predefiniti di xxx_cost ), che è una stima della funzione ponderata delle risorse I / O e CPU richieste; è ansible ottenere questo accendendo PgAdminIII ed eseguendo “Query / Explain (F7)” sulla query con “Opzioni di Query / Explain” impostato su “Analizza”

  • La query di Quassnoy ha una stima di costo di 745k (!), E completa in 1,3 secondi (dato un indice composto su ( usr_id , trans_id , time_stamp ))
  • La query di Bill ha una stima del costo di 93k e termina in 2,9 secondi (dato un indice composto su ( usr_id , trans_id ))
  • La query numero 1 in basso ha una stima di costo di 16k e completa in 800 ms (dato un indice composto su ( usr_id , trans_id , time_stamp ))
  • La query n. 2 in basso ha una stima di costo di 14k e completa in 800 ms (dato un indice di funzione composta su ( usr_id , EXTRACT(EPOCH FROM time_stamp) , trans_id ))
    • questo è specifico di Postgres
  • La query n. 3 (Postgres 8.4+) ha una stima dei costi e un tempo di completamento paragonabili alla (o migliore) query n. 2 (dato un indice composto su ( usr_id , time_stamp , trans_id )); ha il vantaggio di scansionare la tabella delle lives solo una volta e, se si aumenta temporaneamente (se necessario) work_mem per adattarsi all’ordinamento in memoria, sarà di gran lunga la più veloce di tutte le query.

Tutte le volte sopra includono il recupero del set di risultati completo di 10 righe.

Il tuo objective è la stima dei costi minima e il tempo minimo di esecuzione delle query, con un’enfasi sul costo stimato. L’esecuzione della query può dipendere in modo significativo dalle condizioni di esecuzione (ad esempio se le righe pertinenti sono già completamente memorizzate nella memoria o meno), mentre la stima dei costi non lo è. D’altra parte, tieni presente che la stima dei costi è esattamente quella, una stima.

Il tempo di esecuzione della query migliore si ottiene eseguendo su un database dedicato senza carico (ad es. Giocando con pgAdminIII su un PC di sviluppo). Il tempo di interrogazione varierà in produzione in base al carico effettivo della macchina / diffusione dell’accesso ai dati. Quando una query appare leggermente più veloce (<20%) rispetto all'altra ma ha un costo molto più elevato, sarà generalmente più saggio scegliere quella con tempi di esecuzione più elevati ma costi inferiori.

Quando ci si aspetta che non ci sia competizione per la memoria sulla macchina di produzione nel momento in cui viene eseguita la query (ad esempio, la cache RDBMS e la cache del filesystem non saranno troncate da query simultanee e / o attività del filesystem) allora il tempo di query ottenuto in modalità standalone (ad es. pgAdminIII su un PC di sviluppo) la modalità sarà rappresentativa. Se c’è un conflitto sul sistema di produzione, il tempo di interrogazione si ridurrà proporzionalmente al rapporto di costo stimato, poiché la query con il costo inferiore non dipende tanto dalla cache, mentre la query con un costo più elevato rivisiterà gli stessi dati più e più volte (triggerszione I / O aggiuntivo in assenza di una cache stabile), ad esempio:

  cost | time (dedicated machine) | time (under load) | -------------------+--------------------------+-----------------------+ some query A: 5k | (all data cached) 900ms | (less i/o) 1000ms | some query B: 50k | (all data cached) 900ms | (lots of i/o) 10000ms | 

Non dimenticare di eseguire ANALYZE lives una volta dopo aver creato gli indici necessari.


Query # 1

 -- incrementally narrow down the result set via inner joins -- the CBO may elect to perform one full index scan combined -- with cascading index lookups, or as hash aggregates terminated -- by one nested index lookup into lives - on my machine -- the latter query plan was selected given my memory settings and -- histogram SELECT l1.* FROM lives AS l1 INNER JOIN ( SELECT usr_id, MAX(time_stamp) AS time_stamp_max FROM lives GROUP BY usr_id ) AS l2 ON l1.usr_id = l2.usr_id AND l1.time_stamp = l2.time_stamp_max INNER JOIN ( SELECT usr_id, time_stamp, MAX(trans_id) AS trans_max FROM lives GROUP BY usr_id, time_stamp ) AS l3 ON l1.usr_id = l3.usr_id AND l1.time_stamp = l3.time_stamp AND l1.trans_id = l3.trans_max 

Query # 2

 -- cheat to obtain a max of the (time_stamp, trans_id) tuple in one pass -- this results in a single table scan and one nested index lookup into lives, -- by far the least I/O intensive operation even in case of great scarcity -- of memory (least reliant on cache for the best performance) SELECT l1.* FROM lives AS l1 INNER JOIN ( SELECT usr_id, MAX(ARRAY[EXTRACT(EPOCH FROM time_stamp),trans_id]) AS compound_time_stamp FROM lives GROUP BY usr_id ) AS l2 ON l1.usr_id = l2.usr_id AND EXTRACT(EPOCH FROM l1.time_stamp) = l2.compound_time_stamp[1] AND l1.trans_id = l2.compound_time_stamp[2] 

Aggiornamento 2013/01/29

Infine, a partire dalla versione 8.4, Postgres supporta Window Function, il che significa che puoi scrivere qualcosa di semplice ed efficace come:

Query # 3

 -- use Window Functions -- performs a SINGLE scan of the table SELECT DISTINCT ON (usr_id) last_value(time_stamp) OVER wnd, last_value(lives_remaining) OVER wnd, usr_id, last_value(trans_id) OVER wnd FROM lives WINDOW wnd AS ( PARTITION BY usr_id ORDER BY time_stamp, trans_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ); 

Proporrei una versione pulita basata su DISTINCT ON (vedi documenti ):

 SELECT DISTINCT ON (usr_id) time_stamp, lives_remaining, usr_id, trans_id FROM lives ORDER BY usr_id, time_stamp DESC, trans_id DESC; 

Ecco un altro metodo, che capita di non usare sottoquery o GROUP BY correlati. Non sono esperto nell’ottimizzazione delle prestazioni di PostgreSQL, quindi ti suggerisco di provare sia questa che le soluzioni fornite da altre persone per vedere quale funziona meglio per te.

 SELECT l1.* FROM lives l1 LEFT OUTER JOIN lives l2 ON (l1.usr_id = l2.usr_id AND (l1.time_stamp < l2.time_stamp OR (l1.time_stamp = l2.time_stamp AND l1.trans_id < l2.trans_id))) WHERE l2.usr_id IS NULL ORDER BY l1.usr_id; 

trans_id che trans_id sia unico almeno per un dato valore di time_stamp .

Mi piace lo stile della risposta di Mike Woodhouse sull’altra pagina che hai menzionato. È particolarmente conciso quando la cosa che viene massimizzata è solo una singola colonna, nel qual caso la sottoquery può semplicemente usare MAX(some_col) e GROUP BY le altre colonne, ma nel tuo caso hai una quantità in 2 parti da massimizzare, tu può ancora farlo usando ORDER BY più LIMIT 1 invece (come fatto da Quassnoi):

 SELECT * FROM lives outer WHERE (usr_id, time_stamp, trans_id) IN ( SELECT usr_id, time_stamp, trans_id FROM lives sq WHERE sq.usr_id = outer.usr_id ORDER BY trans_id, time_stamp LIMIT 1 ) 

Trovo che usando la syntax del costruttore di righe WHERE (a, b, c) IN (subquery) bello perché riduce la quantità di verbosità richiesta.

Di sicuro c’è una soluzione hacky per questo problema. Diciamo che vuoi selezionare l’albero più grande di ogni foresta in una regione.

 SELECT (array_agg(tree.id ORDER BY tree_size.size)))[1] FROM tree JOIN forest ON (tree.forest = forest.id) GROUP BY forest.id 

Quando si raggruppano alberi per foreste, vi sarà un elenco di alberi non ordinato e sarà necessario trovare il più grande. La prima cosa da fare è ordinare le righe in base alle loro dimensioni e selezionare il primo della lista. Potrebbe sembrare inefficiente, ma se hai milioni di righe sarà molto più veloce delle soluzioni che includono le condizioni JOIN e WHERE .

BTW, nota che ORDER_BY per array_agg viene introdotto in Postgresql 9.0

 SELECT l.* FROM ( SELECT DISTINCT usr_id FROM lives ) lo, lives l WHERE l.ctid = ( SELECT ctid FROM lives li WHERE li.usr_id = lo.usr_id ORDER BY time_stamp DESC, trans_id DESC LIMIT 1 ) 

La creazione di un indice su (usr_id, time_stamp, trans_id) migliorerà notevolmente questa query.

Dovresti sempre avere sempre una specie di PRIMARY KEY nei tuoi tavoli.

Penso che tu abbia un grosso problema qui: non esiste un “contatore” monotonicamente crescente per garantire che una determinata riga sia avvenuta più avanti nel tempo di un’altra. Prendi questo esempio:

 timestamp lives_remaining user_id trans_id 10:00 4 3 5 10:00 5 3 6 10:00 3 3 1 10:00 2 3 2 

Non è ansible determinare da questi dati che è la voce più recente. È il secondo o l’ultimo? Non esiste una funzione sort o max () applicabile a uno qualsiasi di questi dati per fornire la risposta corretta.

Aumentare la risoluzione del timestamp sarebbe di grande aiuto. Poiché il motore del database serializza le richieste, con una risoluzione sufficiente è ansible garantire che non vi siano due timestamp uguali.

In alternativa, usa un trans_id che non si arrotola per un tempo molto, molto lungo. Avere un trans_id che si sposta su significa che non puoi dire (per lo stesso timestamp) se trans_id 6 è più recente di trans_id 1 a meno che tu non faccia qualche matematica complicata.

C’è una nuova opzione in Postgressql 9.5 chiamata DISTINCT ON

 SELECT DISTINCT ON (location) location, time, report FROM weather_reports ORDER BY location, time DESC; 

Elimina le righe duplicate e lascia solo la prima riga come definito nella mia clausola ORDER BY.

vedere la documentazione ufficiale