Selecione o máximo de linhas de subgrupos classificados

Aug 18 2020

Usando o PostgreSQL 11, eu tenho uma tabela contendo uma entrada DAY e MONTH_TO_DAY para cada dia de cada mês. Gostaria de selecionar a entrada MONTH_TO_DAY mais recente para cada conta. Minha mesa é:

+------+------------+--------------+------------+--------------------------+
|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 ajudar, as entradas estão sempre em ordem decrescente no tempo.

Em uma consulta solicitando todas as contas, uma vez que o dia 31 é o último dia de 08 e o primeiro é a entrada mais recente de 09, minha saída esperada seria

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

Eu estava pensando em agrupar entradas por mês (truncar dd / hh / ss) e, em seguida, selecionar a linha com o carimbo de data / hora máximo em cada grupo. Posso obter as linhas certas com isso, mas não consigo descobrir como obter qualquer um dos outros campos.

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

Eu também pensei que poderia usar distintos em algo como o abaixo, mas não estou muito familiarizado com distintos em ou date_trunc e não consigo descobrir como usá-los juntos.

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

Respostas

MikeOrganek Aug 18 2020 at 07:03

Você quer distinct on, mas deseja aplicá-lo ao account:

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

Se você quiser o mais recente accountaté month, isso deve 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;