由 John Doe 一月 14, 2025
摘要:在本文中,您将学习如何使用 PostgreSQL 中的窗口函数,对查询结果进行分页。
目录
介绍
窗口函数提供了另一种在 SQL 中实现分页的方法。这是一种灵活的方法,最重要的是,它是符合 SQL 标准的。但是,PostgreSQL 只有在版本 15 以后,才可以将它们用于流水线化的 Top-N 查询。
示例
以下示例使用窗口函数ROW_NUMBER
进行分页查询:
SELECT *
FROM ( SELECT sales.*
, ROW_NUMBER() OVER (ORDER BY sale_date DESC
, sale_id DESC) rn
FROM sales
) tmp
WHERE rn between 11 and 20
ORDER BY sale_date DESC, sale_id DESC
ROW_NUMBER
函数根据OVER
子句中定义的排序顺序列举行。外部的WHERE
子句使用这种列举,将结果限制为第二页(第 11 行到第 20 行)。
怎么做到的?
PostgreSQL 数据库识别出中止的条件,并使用在SALE_DATE
和SALE_ID
上的索引,来产生流水线化的 Top-N 行为:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Subquery Scan on tmp (cost=0.42..141724.98 rows=334751 width=249) (actual time=0.040..0.052 rows=10 loops=1)
Filter: (tmp.rn >= 11)
Rows Removed by Filter: 10
Buffers: shared hit=5
-> WindowAgg (cost=0.42..129171.80 rows=1004254 width=249) (actual time=0.028..0.049 rows=20 loops=1)
Run Condition: (row_number() OVER (?) <= 20)
Buffers: shared hit=5
-> Index Scan Backward using sl_dtid on sales (cost=0.42..111597.36 rows=1004254 width=241) (actual time=0.018..0.025 rows=22 loops=1)
Buffers: shared hit=5
注意:从版本 15 开始,执行计划会显示 “Run Condition”,这可能会中止下游的执行。
这里没有用到排序操作,并且数据库在达到阈值上限时中止了执行。考虑到中止的操作是以流水线方式执行的,这意味着该查询可以同OFFSET
的方法一样高效。
还有更多
虽然这种优化在概念上可以适用于任何单调函数,但在各种分析函数中,显然聚集的是ROW_NUMBER
函数。
使用PARTITION BY
时需要小心:即使WHERE
子句将行限制为单个分区,PARTITION BY
的存在在其他数据库中也会禁用此优化,如 Oracle。如果分区化窗口函数是一个视图的一部分,但外部查询将视图限制为单个分区,则可能会发生这种情况。
然而,窗口函数的优势不在于分页,而是分析计算。如果您以前从未使用过窗口函数,那么您绝对应该花几个小时学习下相应的文档。