对于某些groupid列的每个唯一值，如何获取最近3个日期的行？

I have a table with columns: FILING_ID, DATE, and BLAH

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

I'm trying to write a query that for each FILING_ID, returns the rows with the last three dates. If table is:

我正在尝试编写一个查询,对于每个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

I would like:

我想要:

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

I was thinking of maybe running some query to figure out the 3rd highest date for each FILING_ID then doing a join and comparing the cutoff date with the DATE?

我想可能运行一些查询来确定每个FILING_ID的第三个最高日期,然后进行连接并将截止日期与DATE进行比较?

I use PostgreSQL. Is there some way to use limit?

我使用PostgreSQL。有没有办法使用限制?

1 个解决方案

#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 is only relevant if date can actually be NULL.
If date is not unique, you may need to break ties to get stable results.

NULLS LAST仅在日期实际为NULL时才相关。如果日期不是唯一的,您可能需要断开关系才能获得稳定的结果。

PostgreSQL sort by datetime asc, null first?

PostgreSQL按日期时间asc排序,先是null吗?

Select first row in each GROUP BY group?

选择每个GROUP BY组中的第一行?

Is there some way to use limit?

有没有办法使用限制?

Maybe. If you have an additional table holding all distinct filing_id (and possibly a few more, which are removed by the join), you can use CROSS JOIN LATERAL (, LATERAL is short syntax):

也许。如果你有一个额外的表,包含所有不同的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?

LATERAL和PostgreSQL中的子查询有什么区别?

If you don't have a filing table, you can create one. Or derive it on the fly:

如果您没有归档表,则可以创建一个归档表。或者即时推导:

Optimize GROUP BY query to retrieve latest record per user

优化GROUP BY查询以检索每个用户的最新记录

#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