sql - ROW_NUMBER 条查询数据是否需要获取整个结果集？

Question

我正在尝试编写不使用偏移量的查询(因为我刚刚了解到偏移量会获取所有导致性能开销的数据)。带有 ROW_NUMBER 窗口函数。例如:

SELECT id
FROM(
SELECT id, ROW_NUMBER() over (order by id) rn
FROM users) sq
WHERE rn > 1000

它是否需要像使用 offset 1000 那样获取所有行？我的意思是，使用这样的查询而不是

SELECT if
FROM users
OFFSET 1000

?我是否在处理大量数据时获得了性能提升？

Answer 1

查看 window function文档。窗口函数在获取之后对结果集进行操作:

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.