เลือกแถวสูงสุดของกลุ่มย่อยที่เรียงลำดับ

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

ฉันยังคิดว่าฉันสามารถใช้ความแตกต่างในบางสิ่งเช่นด้านล่างนี้ได้ แต่ฉันไม่ค่อยคุ้นเคยกับความแตกต่างบนหรือ 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;