PostgreSQL 13 新特性之 FETCH FIRST WITH TIES

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        測(cè)試數(shù)據(jù)
        PostgreSQL 12 之前的實(shí)現(xiàn)
        PostgreSQL 13 的增強(qiáng)功能
            注意事項(xiàng)
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。

PostgreSQL 全球開發(fā)組于 2020 年 9 月 24 日宣布 PostgreSQL 13 正式發(fā)布。接下來我會(huì)通過一系列文章為大家解析這個(gè)新版本帶來的功能增強(qiáng)，本文先給大家介紹一個(gè)與 SQL 開發(fā)相關(guān)的新特性：FETCH FIRST 子句增加了 WITH TIES 選項(xiàng)，可以用于返回更多排名相同的數(shù)據(jù)行。

測(cè)試數(shù)據(jù)

首先，我們創(chuàng)建一個(gè)簡(jiǎn)單的測(cè)試表 t 并插入一些數(shù)據(jù)：

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|

該表中只包含姓名和分?jǐn)?shù) 2 個(gè)字段，其中有 3 個(gè)學(xué)生的分?jǐn)?shù)相同。
PostgreSQL 12 之前的實(shí)現(xiàn)

PostgreSQL 12 以及之前的版本實(shí)現(xiàn)了 SQL 標(biāo)準(zhǔn)中的 FETCH 子句：

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

其中，OFFSET 表示跳過指定的 start 行數(shù)， 默認(rèn)為 0；FETCH 表示最多返回 count 行結(jié)果，默認(rèn)為 1；ROW 和 ROWS 是同義詞，F(xiàn)IRST 和 NEXT 是同義詞；ONLY 表示不返回更多的數(shù)據(jù)。

例如，以下語句返回了分?jǐn)?shù)最高的 2 個(gè)學(xué)生：

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

name |score|
-----|-----|
趙六  |   90|
張三  |   80|

雖然的確返回了分?jǐn)?shù)最高的兩條記錄，但是從表中的數(shù)據(jù)可以看出分?jǐn)?shù)為 80 的還有兩名同學(xué)，如果計(jì)算排名的話他們也應(yīng)該是第二名。

另外需要注意，如果沒有指定 ORDER BY 子句，查詢將會(huì)返回隨機(jī)的 2 行記錄：

SELECT *
FROM t
FETCH FIRST 2 ROWS ONLY;

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

    ??PostgreSQL 還支持另一種限定查詢結(jié)果數(shù)量的子句：LIMIT { count | ALL } OFFSET start，也可以實(shí)現(xiàn)相同的功能。

PostgreSQL 13 的增強(qiáng)功能

為了解決返回排名相同的數(shù)據(jù)問題，PostgreSQL 13 對(duì) FETCH FIRST 子句進(jìn)行了增強(qiáng)：

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

其中，WITH TIES 選項(xiàng)表示返回排名和最后一行相同的額外數(shù)據(jù)行，而且必須同時(shí)指定 ORDER BY 子句。例如，以下語句返回了分?jǐn)?shù)排名前 2 的所有學(xué)生：

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

name |score|
-----|-----|
趙六  |   90|
張三  |   80|
王五  |   80|
孫七  |   80|

如果使用 WITH TIES 選項(xiàng)時(shí)沒有指定 ORDER BY 子句，將會(huì)返回錯(cuò)誤：

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

注意事項(xiàng)

使用 WITH TIES 選項(xiàng)時(shí)需要注意一點(diǎn)，F(xiàn)ETCH FIRST 語句返回的結(jié)果數(shù)量不再是確定值。例如在上面的語句中，雖然指定了 count 為 2，但實(shí)際返回了 4 行數(shù)據(jù)。如果我們基于這個(gè)功能實(shí)現(xiàn)分頁(yè)查詢，需要記錄每次實(shí)際返回的行數(shù)；對(duì)應(yīng)上面的示例，第一頁(yè)返回 4 行數(shù)據(jù)，查詢第二頁(yè)時(shí)的語句如下：

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

其中，OFFSET 4 是上一頁(yè)實(shí)際返回的行數(shù)。

另外，這種情況下的 ORDER BY 子句中不能出現(xiàn)其他用于排序顯示的字段。假如我們想要找出分?jǐn)?shù)前 2 名的所有學(xué)生，同時(shí)對(duì)于分?jǐn)?shù)相同的學(xué)生按照姓名拼音進(jìn)行排序：

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

name |score|
-----|-----|
趙六  |   90|
孫七  |   80|

由于 name 字段出現(xiàn)在 ORDER BY 中，其他分?jǐn)?shù)為 80 的學(xué)生不再被認(rèn)為是排名相同的數(shù)據(jù)，除非他們的姓名也叫做“孫七”。為了解決這個(gè)問題，可以增加一個(gè)子查詢或者使用通用表表達(dá)式（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|

其中，第一個(gè) ORDER BY 用于分?jǐn)?shù)排名，第二個(gè) ORDER BY 用于最終的排序顯示。

    ??按照 SQL 標(biāo)準(zhǔn)，除了可以使用 count 限定返回的行數(shù)之外，還可以使用 N PERCENT 按照百分比限定返回的行數(shù)。PostgreSQL 目前沒有實(shí)現(xiàn)這一功能。

總結(jié)

作為世界上最先進(jìn)的開源數(shù)據(jù)庫(kù)，PostgreSQL 新版本增加了 FETCH FIRST 子句對(duì)于 WITH TIES 的支持，為應(yīng)用程序開發(fā)提供了更多的便利。關(guān)于 PostgreSQL 13 新特性的更多內(nèi)容，可以參考這篇文章。