案例分析：SQL 窗口函數(shù)實(shí)現(xiàn)高效分頁(yè)查詢

作者: 不剪發(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



文章目錄

        傳統(tǒng)方法實(shí)現(xiàn)分頁(yè)查詢
        窗口函數(shù)實(shí)現(xiàn)分頁(yè)查詢
        總結(jié)

    ??不聞不若聞之，聞之不若見之，見之不若知之，知之不若行之。學(xué)至于行之而止矣。——荀子

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

在使用 SQL 語(yǔ)句實(shí)現(xiàn)分頁(yè)查詢時(shí)，我們需要知道一些額外的參數(shù)信息，例如查詢返回的總行數(shù)、當(dāng)前所在的頁(yè)數(shù)、最后一頁(yè)的頁(yè)數(shù)等。在傳統(tǒng)的實(shí)現(xiàn)方法中我們需要執(zhí)行額外的查詢語(yǔ)句獲得這些信息，本文介紹一種只需要一個(gè)查詢語(yǔ)句就可以返回所有數(shù)據(jù)的方法，也就是通過(guò) SQL 窗口函數(shù)實(shí)現(xiàn)高效的分頁(yè)查詢功能。

如果覺得文章有用，歡迎評(píng)論??、點(diǎn)贊??、推薦??

    本文使用的示例表和數(shù)據(jù)可以這里下載。

傳統(tǒng)方法實(shí)現(xiàn)分頁(yè)查詢

在 SQL 中實(shí)現(xiàn)分頁(yè)查詢的傳統(tǒng)方法就是利用標(biāo)準(zhǔn)的 OFFSET … FETCH 語(yǔ)句或者許多數(shù)據(jù)庫(kù)支持的 LIMIT … OFFSET 語(yǔ)句，例如：

-- Oracle、SQL Server、PostgreSQL
SELECT emp_name, sex, email
FROM employee
ORDER BY emp_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL、PostgreSQL、SQLite
SELECT emp_name, sex, email
FROM employee
ORDER BY emp_id
LIMIT 10 OFFSET 10;

    以上語(yǔ)句非常容易理解，返回的是第 2 頁(yè)中的 10 條記錄。但是問(wèn)題在于我們?nèi)绾沃揽偣舶嗌夙?yè)數(shù)據(jù)（或者總的記錄數(shù)），顯然在此之前我們需要執(zhí)行另一個(gè)查詢：

SELECT COUNT(*)
FROM employee;

COUNT(*)|
--------+
      25|

 

有了總的記錄數(shù) 25 之后，我們可以計(jì)算出數(shù)據(jù)總共有 3 頁(yè)，每頁(yè) 10 條。

這種方法要求我們每次進(jìn)行分頁(yè)查詢時(shí)都需要執(zhí)行 2 個(gè)查詢語(yǔ)句，使用起來(lái)不是很方便。下面我們介紹更加高效的窗口函數(shù)分頁(yè)查詢。

    ??關(guān)于分頁(yè)查詢的實(shí)現(xiàn)，OFFSET 分頁(yè)對(duì)于大量數(shù)據(jù)的分頁(yè)可能存在性能問(wèn)題，另一種方法就是采用鍵集分頁(yè)（keyset pagination）。

窗口函數(shù)實(shí)現(xiàn)分頁(yè)查詢

首先讓我們考慮一下使用 OFFSET 分頁(yè)查詢時(shí)需要哪些參數(shù)：

    TOTAL_ROWS，總記錄數(shù)；
    CURRENT_PAGE，當(dāng)前所在頁(yè)碼；
    MAX_PAGE_SIZE，每一頁(yè)最多顯示的記錄數(shù)，例如 10、20、50；
    ACTUAL_PAGE_SIZE，當(dāng)前頁(yè)實(shí)際包含的記錄數(shù)；
    ROW_NBR，每條記錄的實(shí)際偏移量；
    LAST_PAGE，當(dāng)前頁(yè)是否是最后一頁(yè)。

每一頁(yè)最多顯示的記錄數(shù)（MAX_PAGE_SIZE）是我們傳遞給數(shù)據(jù)庫(kù)的參數(shù)，其他則是查詢返回的結(jié)果，我們可以通過(guò)下面的查詢語(yǔ)句實(shí)現(xiàn)所有的功能：

-- Oracle、SQL Server、PostgreSQL
WITH e AS ( -- 初始查詢
  SELECT emp_id, emp_name, sex, email
  FROM employee
),
t AS (
  SELECT emp_id, emp_name, sex, email,
         COUNT(*) OVER () AS total_rows, -- 總記錄數(shù)
         ROW_NUMBER () OVER (ORDER BY e.emp_id) AS row_nbr -- 偏移量，ORDER BY和初始查詢相同
  FROM e
  ORDER BY e.emp_id -- 排序
  OFFSET 10 ROWS -- 分頁(yè)
  FETCH NEXT 10 ROWS ONLY
)
SELECT
  emp_id, emp_name, sex, email,
  COUNT(*) OVER () AS actual_page_size, -- 當(dāng)前頁(yè)實(shí)際記錄數(shù)
  CASE MAX(row_nbr) OVER ()
    WHEN total_rows THEN 'Y'
    ELSE 'N'
  END AS last_page, -- 是否最后一頁(yè)
  total_rows, -- 總記錄數(shù)
  row_nbr, -- 每一條數(shù)據(jù)的偏移量
  ((row_nbr - 1) / 10) + 1 AS current_page -- 當(dāng)前所在頁(yè)碼
FROM t
ORDER BY emp_id;


-- MySQL、PostgreSQL、SQLite
WITH e AS ( -- 初始查詢
  SELECT emp_id, emp_name, sex, email
  FROM employee
),
t AS (
  SELECT emp_id, emp_name, sex, email,
         COUNT(*) OVER () AS total_rows, -- 總記錄數(shù)
         ROW_NUMBER () OVER (ORDER BY e.emp_id) AS row_nbr -- 偏移量，ORDER BY和初始查詢相同
  FROM e
  ORDER BY e.emp_id -- 排序
  LIMIT 10
  OFFSET 10 ROWS -- 分頁(yè)
)
SELECT
  emp_id, emp_name, sex, email,
  COUNT(*) OVER () AS actual_page_size, -- 當(dāng)前頁(yè)實(shí)際記錄數(shù)
  CASE MAX(row_nbr) OVER ()
    WHEN total_rows THEN 'Y'
    ELSE 'N'
  END AS last_page, -- 是否最后一頁(yè)
  total_rows, -- 總記錄數(shù)
  row_nbr, -- 每一條數(shù)據(jù)的偏移量
  ((row_nbr - 1) / 10) + 1 AS current_page -- 當(dāng)前所在頁(yè)碼
FROM t
ORDER BY emp_id;



首先，我們定義了通用表表達(dá)式 e，它是返回?cái)?shù)據(jù)的初始查詢，可以增加其他的過(guò)濾條件。

然后，我們基于 e 定義了另一個(gè)通用表表達(dá)式 t，在定義中進(jìn)行了排序和分頁(yè)，并且利用窗口函數(shù) COUNT(*) 計(jì)算總的記錄數(shù)，利用窗口函數(shù) ROW_NUMBER () 計(jì)算每條數(shù)據(jù)的偏移量（行號(hào)）。

接下來(lái)，我們基于 t 返回了更多的參數(shù)，利用窗口函數(shù) COUNT(*) 返回了當(dāng)前頁(yè)的實(shí)際記錄數(shù)，通過(guò)窗口函數(shù) MAX(row_nbr) 返回的當(dāng)前頁(yè)最大偏移量和總記錄數(shù)的比較判斷是否最后一頁(yè)，以及當(dāng)前所在的頁(yè)碼。

emp_id|emp_name|sex|email              |actual_page_size|last_page|total_rows|row_nbr|current_page|
------+--------+---+-------------------+----------------+---------+----------+-------+------------+
    11|關(guān)平    |男 |guanping@shuguo.com|              10|N        |        27|     11|           2|
    12|趙氏    |女 |zhaoshi@shuguo.com |              10|N        |        27|     12|           2|
    13|關(guān)興    |男 |guanxing@shuguo.com|              10|N        |        27|     13|           2|
    14|張苞    |男 |zhangbao@shuguo.com|              10|N        |        27|     14|           2|
    15|趙統(tǒng)    |男 |zhaotong@shuguo.com|              10|N        |        27|     15|           2|
    16|周倉(cāng)    |男 |zhoucang@shuguo.com|              10|N        |        27|     16|           2|
    17|馬岱    |男 |madai@shuguo.com   |              10|N        |        27|     17|           2|
    18|法正    |男 |fazheng@shuguo.com |              10|N        |        27|     18|           2|
    19|龐統(tǒng)    |男 |pangtong@shuguo.com|              10|N        |        27|     19|           2|
    20|蔣琬    |男 |jiangwan@shuguo.com|              10|N        |        27|     20|           2|


    ??關(guān)于窗口函數(shù)的介紹可以參考這篇文章。

總結(jié)

本文介紹了如何利用窗口函數(shù)在一個(gè)語(yǔ)句中返回分頁(yè)查詢的結(jié)果和所需的全部參數(shù)，這種方法比傳統(tǒng)的分頁(yè)查詢實(shí)現(xiàn)更加簡(jiǎn)潔高效。

如果你覺得文章有用，歡迎評(píng)論??、點(diǎn)贊??、推薦??