PostgreSQL 13 新特性之 FETCH FIRST WITH TIES

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

文章目錄

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

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

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

如果覺得文章有用，歡迎關(guān)注??、評論??、點贊??
測試數(shù)據(jù)

首先，我們創(chuàng)建一個簡單的測試表 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|



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

PostgreSQL 12 以及之前的版本實現(xiàn)了 SQL 標準中的 FETCH 子句：

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



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

例如，以下語句返回了分數(shù)最高的 2 個學生：

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

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



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

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

SELECT *
FROM t
FETCH FIRST 2 ROWS ONLY;

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



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

PostgreSQL 13 的增強功能

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

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



其中，WITH TIES 選項表示返回排名和最后一行相同的額外數(shù)據(jù)行，而且必須同時指定 ORDER BY 子句。例如，以下語句返回了分數(shù)排名前 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 選項時需要注意一點，F(xiàn)ETCH FIRST 語句返回的結(jié)果數(shù)量不再是確定值。例如在上面的語句中，雖然指定了 count 為 2，但實際返回了 4 行數(shù)據(jù)。如果我們基于這個功能實現(xiàn)分頁查詢，需要記錄每次實際返回的行數(shù)；對應(yīng)上面的示例，第一頁返回 4 行數(shù)據(jù)，查詢第二頁時的語句如下：

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



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

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

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 中，其他分數(shù)為 80 的學生不再被認為是排名相同的數(shù)據(jù)，除非他們的姓名也叫做“孫七”。為了解決這個問題，可以增加一個子查詢或者使用通用表表達式（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 用于分數(shù)排名，第二個 ORDER BY 用于最終的排序顯示。

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

總結(jié)

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