Seleccione las filas máximas de subgrupos ordenados

Question

Usando PostgreSQL 11, tengo una tabla que contiene una entrada DAY y MONTH_TO_DAY para cada día de cada mes. Me gustaría seleccionar la entrada MONTH_TO_DAY más reciente para cada cuenta. Mi mesa es:

+------+------------+--------------+------------+--------------------------+
|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|
+------+------------+--------------+------------+--------------------------+

Si ayuda, las entradas siempre están en orden descendente en el tiempo.

En una consulta que solicita todas las cuentas, dado que el 31 es el último día de 08 y el primero es la entrada más reciente de 09, mi resultado esperado sería

+------+------------+--------------+------------+--------------------------+
|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|
+------+------------+--------------+------------+--------------------------+

Estaba pensando que me gustaría agrupar las entradas por mes (truncar el dd / hh / ss) y luego seleccionar la fila con la marca de tiempo máxima en cada grupo. Puedo obtener las filas correctas con esto, pero no puedo averiguar cómo obtener ninguno de los otros campos.

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

También pensé que podría usar distintos en algo como el siguiente, pero no estoy muy familiarizado con distintos en o date_trunc y no puedo averiguar cómo usarlos juntos.

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

Answer 1

Lo desea distinct on, pero desea aplicarlo a account:

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

Si quieres lo último accountantes month, entonces esto debería funcionar:

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