类似于SELECT DISTINCT ON，但对于N> 1个 [英] Something like SELECT DISTINCT ON, but for N > 1

问题描述

如果我想获取postgres字段中的第一行或最近的单行，在上选择不同的似乎很棒，查看此答案。

If I want to get the first row, or the most recent single row on a field in postgres, select distinct on seems great, see this answer.

直接打开是只需要1个条目的语法。但是，如果我要N个最近的条目怎么办？我将如何转换：

DISTINCT ON is a syntax for wanting exactly 1 entry. But what if I want the N most recent entries? How would I transform this:

CREATE VIEW your_view AS
SELECT DISTINCT ON (id) *
FROM your_table a
ORDER BY id, date DESC;

但是对于

每个ID最近有n = 2个条目，而不是每个ID是否选择最近的n = 1个条目？ ？

"Select the most recent n=2 entries per id" rather than "select the most recent n=1 entries per id?" ?

我认为它是一个子查询分组，但是我不太清楚。

I assume it's an group by subquery, but I'm not quite seeing it.

推荐答案

对于每组最多n个查询，通常使用窗口函数：

For a greatest-n-per-group query with n > 1 you typically use a window function:

select *
from (
  select *, row_number() over (partition by id order by "date" desc" as rn
  from the_table
) x
where rn <= 2;

这篇关于类似于SELECT DISTINCT ON，但对于N> 1个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！