Выберите максимальное количество строк отсортированных подгрупп

Aug 18 2020

Используя PostgreSQL 11, у меня есть таблица, содержащая записи DAY и MONTH_TO_DAY для каждого дня каждого месяца. Я хотел бы выбрать самую последнюю запись MONTH_TO_DAY для каждой учетной записи. Моя таблица:

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

Если это помогает, записи всегда располагаются по убыванию по времени.

В запросе, запрашивающем все учетные записи, поскольку 31-е число - это последний день 08 года, а 1-е - самая последняя запись 09, мой ожидаемый результат будет следующим:

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

Я думал, что хотел бы сгруппировать записи по месяцам (обрезать dd / hh / ss), а затем выбрать строку с максимальной временной меткой в ​​каждой группе. Я могу получить правильные строки с этим, но я не могу понять, как получить любое из других полей.

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

Я также подумал, что могу использовать strict для чего-то вроде приведенного ниже, но я не слишком знаком с отличным on или date_trunc и не могу понять, как использовать их вместе.

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

Ответы

MikeOrganek Aug 18 2020 at 07:03

Вы действительно хотите distinct on, но хотите применить его к account:

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

Если вы хотите получить последнюю версию accountдо month, тогда это должно сработать:

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