Seleziona il numero massimo di righe di sottogruppi ordinati

Question

Utilizzando PostgreSQL 11, ho una tabella contenente una voce DAY e MONTH_TO_DAY per ogni giorno di ogni mese. Vorrei selezionare la voce MONTH_TO_DAY più recente per ogni account. Il mio tavolo è:

+------+------------+--------------+------------+--------------------------+
|id    |account     |code          |interval    |timestamp                 |
+------+------------+--------------+------------+--------------------------+
|387276|ALPBls6EsP  |52            |MONTH_TO_DAY|2020-09-01 01:05:00.000000|
|387275|ALPBls6EsP  |52            |DAY         |2020-09-01 01:05:00.000000|
|387272|YkON8lk8A8  |25            |MONTH_TO_DAY|2020-09-01 01:05:00.000000|
|387271|YkON8lk8A8  |25            |DAY         |2020-08-01 01:05:00.000000|
|387273|ALPBls6EsP  |32            |MONTH_TO_DAY|2020-08-31 01:05:00.000000|
|387274|ALPBls6EsP  |32            |DAY         |2020-08-31 01:05:00.000000|
|387272|ALPBls6EsP  |27            |MONTH_TO_DAY|2020-08-30 01:05:00.000000|
|387271|ALPBls6EsP  |27            |DAY         |2020-08-30 01:05:00.000000|
+------+------------+--------------+------------+--------------------------+

Se aiuta, le voci sono sempre in ordine decrescente nel tempo.

In una query che richiede tutti gli account, poiché il 31 è l'ultimo giorno di 08 e il primo è la voce più recente di 09, il mio output previsto sarebbe

+------+------------+--------------+------------+--------------------------+
|id    |account     |code          |interval    |timestamp                 |
+------+------------+--------------+------------+--------------------------+
|387276|ALPBls6EsP  |52            |MONTH_TO_DAY|2020-09-01 01:05:00.000000|
|387272|YkON8lk8A8  |25            |MONTH_TO_DAY|2020-09-01 01:05:00.000000|
|387273|ALPBls6EsP  |32            |MONTH_TO_DAY|2020-08-31 01:05:00.000000|
+------+------------+--------------+------------+--------------------------+

Stavo pensando che mi piacerebbe raggruppare le voci per mese (troncare il dd / hh / ss), quindi selezionare la riga con il timestamp massimo in ciascun gruppo. Posso ottenere le righe giuste con questo ma non riesco a capire come ottenere nessuno degli altri campi.

SELECT max(timestamp) 
FROM mytable 
GROUP BY date_trunc('month', mytable.timestamp);

Ho anche pensato di poter usare distinti su qualcosa come il seguente, ma non ho molta familiarità con i distinti su o date_trunc e non riesco a capire come usarli insieme.

SELECT distinct on (timestamp)
    *
FROM mytable
ORDER BY date_trunc('month', mytable.timestamp)

Answer 1

Vuoi distinct on, ma vuoi applicarlo a account:

select distinct on (account) *
  from mytable
 where interval = 'MONTH_TO_DAY'
 order by account, timestamp desc;

Se vuoi l'ultimo accountentro month, allora dovrebbe funzionare:

select distinct on (date_trunc('month', timestamp), account) *
  from mytable
 where interval = 'MONTH_TO_DAY'
 order by date_trunc('month', timestamp), account, timestamp desc;