我正在尝试使用窗口函数来获取最近的 n 条记录,如下从这里 https://stackoverflow.com/questions/61570170/something-like-select-distinct-on-but-for-n-1/61570644#61570644:
I have:
select
id,
blah,
row_number () over (
partition by blah, my_id
order by datetime) rn,
theme
from documents
where theme = 'cats';
我得到:
id | blah | rn | theme
----+-----+----+-------
1 | 1 | 1 | cats
2 | 1 | 2 | cats
3 | 1 | 3 | cats
4 | 1 | 4 | cats
5 | 1 | 5 | cats
9 | 2 | 1 | cats
8 | 2 | 2 | cats
11 | 3 | 1 | cats
12 | 4 | 1 | cats
13 | 5 | 1 | cats
14 | 6 | 1 | cats
(11 rows)
这太棒了。但我想要不超过2行,rn <= 2
, 例如。我把这想象成这样:
select
id,
blah,
row_number () over (
partition by blah, my_id
order by datetime) rn,
theme
from documents
where theme = 'cats' and
rn <= 2;
但我得到:
ERROR: column "rn" does not exist
LINE 15: rn <= 1;
^
我知道我可以将其设置为像链接问题一样的子查询,但是我必须缺少将 row_number 放入 where 子句中的语法,对吧?它是什么?