Pilih baris maksimum dari subkelompok yang diurutkan

Question

Menggunakan PostgreSQL 11, saya memiliki tabel yang berisi entri DAY dan MONTH_TO_DAY untuk setiap hari setiap bulan. Saya ingin memilih entri MONTH_TO_DAY terbaru untuk setiap akun. Meja saya adalah:

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

Jika membantu, entri selalu dalam urutan menurun tepat waktu.

Dalam kueri yang menanyakan semua akun, karena tanggal 31 adalah hari terakhir 08 dan tanggal 1 adalah entri terbaru 09, keluaran yang saya harapkan adalah

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

Saya berpikir saya ingin mengelompokkan entri berdasarkan bulan (memotong dd / hh / ss), dan kemudian memilih baris dengan cap waktu maksimum di setiap grup. Saya bisa mendapatkan baris yang benar dengan ini tetapi saya tidak tahu bagaimana mendapatkan salah satu bidang lainnya.

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

Saya juga berpikir saya dapat menggunakan differensial pada sesuatu seperti di bawah ini, tetapi saya tidak terlalu paham dengan differensial on atau date_trunc dan saya tidak tahu bagaimana menggunakannya bersama-sama.

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

Answer 1

Anda memang menginginkannya distinct on, tetapi Anda ingin menerapkannya ke account:

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

Jika Anda ingin yang terbaru accountoleh month, maka ini harus berfungsi:

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