sql - 对于某些 groupid 列的每个唯一值，如何获取具有最后 3 个日期的行？

Question

我有一个包含列的表:FILING_ID、DATE 和 BLAH

我正在尝试为每个 FILING_ID 编写一个查询，返回具有最后三个 日期的行。如果表是:

   FILING_ID   DATE    
     aksjdfj   2/1/2006
           b   2/1/2006
           b   3/1/2006
           b   4/1/2006
           b   5/1/2006

我愿意:

   FILING_ID   DATE    
     aksjdfj   2/1/2006
           b   3/1/2006
           b   4/1/2006
           b   5/1/2006

我正在考虑运行一些查询来找出每个 FILING_ID 的第三大日期，然后进行连接并将截止日期与 DATE 进行比较？

我使用 PostgreSQL。有什么方法可以使用限制吗？

Answer 1

SELECT filing_id, date  -- more columns?
FROM  (
   SELECT *, row_number() OVER (PARTITION BY filing_id ORDER BY date DESC NULLS LAST) AS rn
   FROM   tbl
   ) sub
WHERE  rn < 4
ORDER  BY filing_id, date;  -- optionally order rows

NULLS LAST 仅在 date 实际上可以为 NULL 时才相关。
如果 date 不是唯一的，您可能需要打破平局以获得稳定的结果。

• PostgreSQL sort by datetime asc, null first?
• Select first row in each GROUP BY group?

Is there some way to use limit?

也许吧。如果您有一个包含所有不同 filing_id 的附加表(可能还有一些，它们已被连接删除)，您可以使用CROSS JOIN LATERAL(，LATERAL 是短语法):

SELECT f.filing_id, t.*
FROM   filing f  -- table with distinct filing_id
     , LATERAL (
   SELECT date -- more columns?
   FROM   tbl
   WHERE  filing_id = f.filing_id
   ORDER  BY date DESC NULLS LAST
   LIMIT  3  -- now you can use LIMIT
   ) t
ORDER  BY f.filing_id, t.date;

• What is the difference between LATERAL and a subquery in PostgreSQL?

如果您没有归档 表，您可以创建一个。或者动态推导它:

• Optimize GROUP BY query to retrieve latest record per user