SQL 窗口函數(shù)實(shí)現(xiàn)高效分頁查詢的案例分析
🍺不聞不若聞之，聞之不若見之，見之不若知之，知之不若行之。學(xué)至于行之而止矣。——荀子
大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。
在使用 SQL 語句實(shí)現(xiàn)分頁查詢時(shí)，我們需要知道一些額外的參數(shù)信息，例如查詢返回的總行數(shù)、當(dāng)前所在的頁數(shù)、最后一頁的頁數(shù)等。在傳統(tǒng)的實(shí)現(xiàn)方法中我們需要執(zhí)行額外的查詢語句獲得這些信息，本文介紹一種只需要一個(gè)查詢語句就可以返回所有數(shù)據(jù)的方法，也就是通過 SQL 窗口函數(shù)實(shí)現(xiàn)高效的分頁查詢功能。
本文使用的示例表和數(shù)據(jù)可以這里下載。
傳統(tǒng)方法實(shí)現(xiàn)分頁查詢
在 SQL 中實(shí)現(xiàn)分頁查詢的傳統(tǒng)方法就是利用標(biāo)準(zhǔn)的 OFFSET … FETCH 語句或者許多數(shù)據(jù)庫支持的 LIMIT … OFFSET 語句，例如：
-- 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;
以上語句非常容易理解，返回的是第 2 頁中的 10 條記錄。但是問題在于我們?nèi)绾沃揽偣舶嗌夙摂?shù)據(jù)（或者總的記錄數(shù)），顯然在此之前我們需要執(zhí)行另一個(gè)查詢：
SELECT COUNT(*)
FROM employee;
COUNT(*)|
--------+
25|
有了總的記錄數(shù) 25 之后，我們可以計(jì)算出數(shù)據(jù)總共有 3 頁，每頁 10 條。
這種方法要求我們每次進(jìn)行分頁查詢時(shí)都需要執(zhí)行 2 個(gè)查詢語句，使用起來不是很方便。下面我們介紹更加高效的窗口函數(shù)分頁查詢。
📝關(guān)于分頁查詢的實(shí)現(xiàn)，OFFSET 分頁對(duì)于大量數(shù)據(jù)的分頁可能存在性能問題，另一種方法就是采用鍵集分頁（keyset pagination）。
窗口函數(shù)實(shí)現(xiàn)分頁查詢
首先讓我們考慮一下使用 OFFSET 分頁查詢時(shí)需要哪些參數(shù)：
- TOTAL_ROWS，總記錄數(shù)；
- CURRENT_PAGE，當(dāng)前所在頁碼；
- MAX_PAGE_SIZE，每一頁最多顯示的記錄數(shù)，例如 10、20、50；
- ACTUAL_PAGE_SIZE，當(dāng)前頁實(shí)際包含的記錄數(shù)；
- ROW_NBR，每條記錄的實(shí)際偏移量；
- LAST_PAGE，當(dāng)前頁是否是最后一頁。
每一頁最多顯示的記錄數(shù)（MAX_PAGE_SIZE）是我們傳遞給數(shù)據(jù)庫的參數(shù)，其他則是查詢返回的結(jié)果，我們可以通過下面的查詢語句實(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 -- 分頁
FETCH NEXT 10 ROWS ONLY
)
SELECT
emp_id, emp_name, sex, email,
COUNT(*) OVER () AS actual_page_size, -- 當(dāng)前頁實(shí)際記錄數(shù)
CASE MAX(row_nbr) OVER ()
WHEN total_rows THEN 'Y'
ELSE 'N'
END AS last_page, -- 是否最后一頁
total_rows, -- 總記錄數(shù)
row_nbr, -- 每一條數(shù)據(jù)的偏移量
((row_nbr - 1) / 10) + 1 AS current_page -- 當(dāng)前所在頁碼
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 -- 分頁
)
SELECT
emp_id, emp_name, sex, email,
COUNT(*) OVER () AS actual_page_size, -- 當(dāng)前頁實(shí)際記錄數(shù)
CASE MAX(row_nbr) OVER ()
WHEN total_rows THEN 'Y'
ELSE 'N'
END AS last_page, -- 是否最后一頁
total_rows, -- 總記錄數(shù)
row_nbr, -- 每一條數(shù)據(jù)的偏移量
((row_nbr - 1) / 10) + 1 AS current_page -- 當(dāng)前所在頁碼
FROM t
ORDER BY emp_id;
首先，我們定義了通用表表達(dá)式 e，它是返回?cái)?shù)據(jù)的初始查詢，可以增加其他的過濾條件。
然后，我們基于 e 定義了另一個(gè)通用表表達(dá)式 t，在定義中進(jìn)行了排序和分頁，并且利用窗口函數(shù) COUNT(*) 計(jì)算總的記錄數(shù)，利用窗口函數(shù) ROW_NUMBER () 計(jì)算每條數(shù)據(jù)的偏移量（行號(hào)）。
接下來，我們基于 t 返回了更多的參數(shù)，利用窗口函數(shù) COUNT(*) 返回了當(dāng)前頁的實(shí)際記錄數(shù)，通過窗口函數(shù) MAX(row_nbr) 返回的當(dāng)前頁最大偏移量和總記錄數(shù)的比較判斷是否最后一頁，以及當(dāng)前所在的頁碼。
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|周倉 |男 |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è)語句中返回分頁查詢的結(jié)果和所需的全部參數(shù)，這種方法比傳統(tǒng)的分頁查詢實(shí)現(xiàn)更加簡潔高效。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信