PostgreSQL 13 新特性之 FETCH FIRST WITH TIES

原文地址

目录

测试数据

PostgreSQL 12 之前的实现

PostgreSQL 13 的增强功能

注意事项

总结

---

PostgreSQL 全球开发组于 2020 年 9 月 24 日宣布 PostgreSQL 13 正式发布。接下来我会通过一系列文章为大家解析这个新版本带来的功能增强，本文先给大家介绍一个与 SQL 开发相关的新特性：FETCH FIRST 子句增加了 WITH TIES 选项，可以用于返回更多排名相同的数据行。

如果觉得文章有用，欢迎关注❤️、评论📝、点赞👍

测试数据

首先，我们创建一个简单的测试表 t 并插入一些数据：

CREATE TABLE t(name varchar(20), score int);
INSERT INTO t VALUES('张三', 80), ('李四', 77), ('王五', 80), ('赵六', 90), ('孙七', 80);

SELECT * FROM t;
name |score|
-----|-----|
张三  |   80|
李四  |   77|
王五  |   80|
赵六  |   90|
孙七  |   80|

该表中只包含姓名和分数 2 个字段，其中有 3 个学生的分数相同。

PostgreSQL 12 之前的实现

PostgreSQL 12 以及之前的版本实现了 SQL 标准中的 FETCH 子句：

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

其中，OFFSET 表示跳过指定的 start 行数， 默认为 0；FETCH 表示最多返回 count 行结果，默认为 1；ROW 和 ROWS 是同义词，FIRST 和 NEXT 是同义词；ONLY 表示不返回更多的数据。

例如，以下语句返回了分数最高的 2 个学生：

SELECT *
FROM t
ORDER BY score DESC
FETCH FIRST 2 ROWS ONLY;

name |score|
-----|-----|
赵六  |   90|
张三  |   80|

虽然的确返回了分数最高的两条记录，但是从表中的数据可以看出分数为 80 的还有两名同学，如果计算排名的话他们也应该是第二名。

另外需要注意，如果没有指定 ORDER BY 子句，查询将会返回随机的 2 行记录：

SELECT *
FROM t
FETCH FIRST 2 ROWS ONLY;

name |score|
-----|-----|
张三  |   80|
李四  |   77|

📝PostgreSQL 还支持另一种限定查询结果数量的子句：LIMIT { count | ALL } OFFSET start，也可以实现相同的功能。

PostgreSQL 13 的增强功能

为了解决返回排名相同的数据问题，PostgreSQL 13 对 FETCH FIRST 子句进行了增强：

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

其中，WITH TIES 选项表示返回排名和最后一行相同的额外数据行，而且必须同时指定 ORDER BY 子句。例如，以下语句返回了分数排名前 2 的所有学生：

SELECT *
FROM t
ORDER BY score DESC
FETCH FIRST 2 ROWS WITH TIES;

name |score|
-----|-----|
赵六  |   90|
张三  |   80|
王五  |   80|
孙七  |   80|

如果使用 WITH TIES 选项时没有指定 ORDER BY 子句，将会返回错误：

SELECT *
FROM t
FETCH FIRST 2 ROWS WITH TIES;
RROR: WITH TIES cannot be specified without ORDER BY clause

注意事项

使用 WITH TIES 选项时需要注意一点，FETCH FIRST 语句返回的结果数量不再是确定值。例如在上面的语句中，虽然指定了 count 为 2，但实际返回了 4 行数据。如果我们基于这个功能实现分页查询，需要记录每次实际返回的行数；对应上面的示例，第一页返回 4 行数据，查询第二页时的语句如下：

SELECT *
FROM t
ORDER BY score
OFFSET 4
FETCH FIRST 2 ROWS WITH TIES;

其中，OFFSET 4 是上一页实际返回的行数。

另外，这种情况下的 ORDER BY 子句中不能出现其他用于排序显示的字段。假如我们想要找出分数前 2 名的所有学生，同时对于分数相同的学生按照姓名拼音进行排序：

SELECT *
FROM t
ORDER BY score DESC, name COLLATE "zh_CN"
FETCH FIRST 2 ROWS WITH TIES;

name |score|
-----|-----|
赵六  |   90|
孙七  |   80|

由于 name 字段出现在 ORDER BY 中，其他分数为 80 的学生不再被认为是排名相同的数据，除非他们的姓名也叫做“孙七”。为了解决这个问题，可以增加一个子查询或者使用通用表表达式（WITH 子句）：

WITH d AS (
  SELECT *
  FROM t
  ORDER BY score DESC
  FETCH FIRST 2 ROWS WITH TIES
)
SELECT *
FROM d
ORDER BY score DESC, name COLLATE "zh_CN";

name |score|
-----|-----|
赵六  |   90|
孙七  |   80|
王五  |   80|
张三  |   80|

其中，第一个 ORDER BY 用于分数排名，第二个 ORDER BY 用于最终的排序显示。

📝按照 SQL 标准，除了可以使用 count 限定返回的行数之外，还可以使用 N PERCENT 按照百分比限定返回的行数。PostgreSQL 目前没有实现这一功能。

总结

作为世界上最先进的开源数据库，PostgreSQL 新版本增加了 FETCH FIRST 子句对于 WITH TIES 的支持，为应用程序开发提供了更多的便利。关于 PostgreSQL 13 新特性的更多内容，可以参考这篇文章。