정렬 된 하위 그룹의 최대 행 선택

Question

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);

나는 또한 아래와 같은 것에 distinct를 사용할 수 있다고 생각했지만 distinct on 또는 date_trunc에 너무 익숙하지 않아 함께 사용하는 방법을 알 수 없습니다.

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

Answer 1

당신이 원하는 않는 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;