sql - Postgres 窗口函数语法

Question

为什么会出现以下查询:

select ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY time DESC) as rownum FROM users where rownum < 20;

产生以下错误？

ERROR: column "rownum" does not exist
LINE 1: ...d ORDER BY time DESC) as rownum FROM users where rownum < 2...

如何构造此查询，以便获得窗口函数定义的前 20 个项目？

user_id和 time都是在 users 上定义的列.

Answer 1

它会像这样工作:

SELECT *
FROM  (
   SELECT ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY time DESC) AS rownum
   FROM   users
   ) x
WHERE  rownum < 20;

这里的重点是事件的顺序。窗口函数在 WHERE 子句之后应用。因此 rownum 目前还不可见。您必须将其放入子查询或 CTE 中，并在下一个查询级别的 rownum 上应用 WHERE 子句。

Per documentation :

Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. This is because they logically execute after the processing of those clauses. Also, window functions execute after regular aggregate functions. This means it is valid to include an aggregate function call in the arguments of a window function, but not vice versa.