ソートされたサブグループの最大行を選択します

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

また、以下のようなものでdistinctを使用できると思いましたが、distinct 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;

byaccountまで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;