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 新特性的更多内容,可以参考这篇文章

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES,现在已经更名为PostgreSQL. PostgreSQL支持大部分SQL标准并且提供了许多其它现代特性:复杂查询、外键、触发器、视图、事务完整性等。 PostgreSQL 是一个免费的对象-关系数据库服务器(数据库管理系统),它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如 MySQL 和 Firebird),和专有系统(比如 Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server)之外的另一种选择。 事实上, PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99,首先,它包括了可以说是目前世界上最丰富的数据类型的支持,其中有些数据类型可以说连商业数据库都不具备, 比如 IP 类型和几何类型等;其次,PostgreSQL 是全功能的自由软件数据库,很长时间以来,PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统(MVCC)、数据完整性检查等特性的唯一的一种自由软件的数据库管理系统。 Inprise 的 InterBase 以及SAP等厂商将其原先专有软件开放为自由软件之后才打破了这个唯一。最后,PostgreSQL拥有一支非常活跃的开发队伍,而且在许多黑客的努力下,PostgreSQL 的质量日益提高。从技术角度来讲,PostgreSQL 采用的是比较经典的C/S(client/server)结构,也就是一个客户端对应一个服务器端守护进程的模式,这个守护进程分析客户端来的查询请求,生成规划树,进行数据检索并最终把结果格式化输出后返回给客户端。为了便于客户端的程序的编写,由数据库服务器提供了统一的客户端 C 接口。而不同的客户端接口都是源自这个 C 接口,比如ODBC,JDBC,Python,Perl,Tcl,C/C++,ESQL等, 同时也要指出的是,PostgreSQL 对接口的支持也是非常丰富的,几乎支持所有类型的数据库客户端接口。这一点也可以说是 PostgreSQL 一大优点。 本课程作为PostgreSQL数据库管理一,主要讲解以下内容:1.     PostgreSQL安装和环境准备2.     PostgreSQL数据查询3.     PostgreSQL 数据过滤4.     PostgreSQL 多表的联接5.     PostgreSQL数据的分组6.     PostgreSQL合集的操作7.   PostgreSQL 合集的分组

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值