PostgreSQL中rank()窗口函數(shù)實(shí)用指南與示例
更新時(shí)間：2025年07月11日 11:03:59 作者：夢(mèng)想畫家
在數(shù)據(jù)分析和數(shù)據(jù)庫(kù)管理中,經(jīng)常需要對(duì)數(shù)據(jù)進(jìn)行排名操作,PostgreSQL提供了強(qiáng)大的窗口函數(shù)rank(),可以方便地對(duì)結(jié)果集中的行進(jìn)行排名,本文將詳細(xì)介紹rank()函數(shù)的使用方法,并通過多個(gè)實(shí)用示例展示其在不同場(chǎng)景下的應(yīng)用,需要的朋友可以參考下
一、rank()函數(shù)簡(jiǎn)介
rank()是一個(gè)窗口函數(shù)，用于計(jì)算結(jié)果集中每一行的排名。它的基本語法如下：
rank() OVER ([PARTITION BY partition_expression] ORDER BY order_expression)
- PARTITION BY：可選子句，用于將結(jié)果集劃分為多個(gè)分區(qū)，排名在每個(gè)分區(qū)內(nèi)獨(dú)立計(jì)算。
- ORDER BY：指定排名的順序依據(jù)。
特點(diǎn)：
- 相同值的行會(huì)獲得相同的排名。
- 下一個(gè)排名會(huì)跳過相同值的數(shù)量。例如，如果有兩個(gè)第一名，下一個(gè)排名是第三名。
二、基礎(chǔ)示例：部門內(nèi)員工薪資排名
假設(shè)有一個(gè)employees表，包含員工姓名、部門和薪資信息。我們希望計(jì)算每個(gè)部門內(nèi)員工的薪資排名。
示例數(shù)據(jù)
首先，創(chuàng)建示例數(shù)據(jù)：
WITH sample_data AS (
SELECT * FROM (
VALUES
('Alice', 'Sales', 50000),
('Bob', 'Marketing', 55000),
('Charlie', 'Sales', 52000),
('David', 'IT', 60000),
('Eve', 'Marketing', 55000),
('Frank', 'IT', 62000)
) AS t(employee_name, department, salary)
)
排名查詢
使用rank()函數(shù)按部門分區(qū)，按薪資降序排名：
SELECT
employee_name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM
sample_data
ORDER BY
department, dept_salary_rank;
結(jié)果：
| employee_name | department | salary | dept_salary_rank |
|---|---|---|---|
| Frank | IT | 62000 | 1 |
| David | IT | 60000 | 2 |
| Bob | Marketing | 55000 | 1 |
| Eve | Marketing | 55000 | 1 |
| Charlie | Sales | 52000 | 1 |
| Alice | Sales | 50000 | 2 |
解釋：
- 在IT部門，F(xiàn)rank薪資最高，排名為1；David次之，排名為2。
- 在Marketing部門，Bob和Eve薪資相同，均排名為1。
- 在Sales部門，Charlie薪資最高，排名為1；Alice次之，排名為2。
三、高級(jí)應(yīng)用示例
1. 每組Top N記錄
場(chǎng)景：找出每個(gè)類別中最貴的兩個(gè)產(chǎn)品。
示例數(shù)據(jù)：
WITH products AS (
SELECT * FROM (
VALUES
(1, 'A', 100),
(2, 'A', 80),
(3, 'B', 200),
(4, 'B', 180),
(5, 'B', 150),
(6, 'C', 120)
) AS t(product_id, category, price)
)
查詢：
SELECT *
FROM (
SELECT
product_id,
category,
price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank
FROM
products
) ranked
WHERE rank <= 2;
結(jié)果：
| product_id | category | price | rank |
|---|---|---|---|
| 1 | A | 100 | 1 |
| 2 | A | 80 | 2 |
| 3 | B | 200 | 1 |
| 4 | B | 180 | 2 |
| 6 | C | 120 | 1 |
解釋：
- 每個(gè)類別中，價(jià)格最高的前兩個(gè)產(chǎn)品被篩選出來。
2. 百分位數(shù)計(jì)算
場(chǎng)景：計(jì)算每個(gè)學(xué)生的成績(jī)百分位。
示例數(shù)據(jù)：
WITH scores AS (
SELECT * FROM (
VALUES
('Student 1', 85),
('Student 2', 92),
('Student 3', 78),
('Student 4', 90),
('Student 5', 88)
) AS t(student, score)
)
查詢：
SELECT
student,
score,
RANK() OVER (ORDER BY score) AS rank,
ROUND(100.0 * RANK() OVER (ORDER BY score) / (SELECT COUNT(*) FROM scores), 2) AS percentile
FROM
scores;
結(jié)果：
| student | score | rank | percentile |
|---|---|---|---|
| Student 3 | 78 | 1 | 20.00 |
| Student 1 | 85 | 2 | 40.00 |
| Student 5 | 88 | 3 | 60.00 |
| Student 4 | 90 | 4 | 80.00 |
| Student 2 | 92 | 5 | 100.00 |
解釋：
- 百分位數(shù)通過排名除以總記錄數(shù)并乘以100計(jì)算得出。
四、rank()與其他窗口函數(shù)的比較
PostgreSQL提供了多個(gè)窗口函數(shù)用于排名，各有特點(diǎn)：
| 函數(shù) | 描述 |
|---|---|
| rank() | 相同值的行獲得相同排名，下一個(gè)排名跳過相同值的數(shù)量。 |
| dense_rank() | 相同值的行獲得相同排名，下一個(gè)排名不跳過，保持連續(xù)。 |
| row_number() | 每行分配唯一的序號(hào)，不考慮相同值，即使值相同也會(huì)分配不同序號(hào)。 |
示例：rank() vs dense_rank()
示例數(shù)據(jù)：
WITH scores AS (
SELECT * FROM (
VALUES
('Player 1', 100),
('Player 2', 95),
('Player 3', 95),
('Player 4', 90)
) AS t(player, score)
)
查詢：
SELECT
player,
score,
RANK() OVER (ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM
scores;
結(jié)果：
| player | score | rank | dense_rank |
|---|---|---|---|
| Player 1 | 100 | 1 | 1 |
| Player 2 | 95 | 2 | 2 |
| Player 3 | 95 | 2 | 2 |
| Player 4 | 90 | 4 | 3 |
解釋：
rank()在遇到相同分?jǐn)?shù)時(shí)跳過了排名3。dense_rank()在遇到相同分?jǐn)?shù)時(shí)不跳過排名，保持連續(xù)。
示例：row_number()
場(chǎng)景：為每日的銷售記錄分配唯一序號(hào)，按銷售金額降序排列。
示例數(shù)據(jù)：
WITH sales AS (
SELECT
DATE '2023-01-01' AS sale_date,
1000 AS amount
UNION ALL
SELECT
DATE '2023-01-01',
1500
UNION ALL
SELECT
DATE '2023-01-02',
1200
UNION ALL
SELECT
DATE '2023-01-02',
1200
)
查詢：
SELECT
sale_date,
amount,
ROW_NUMBER() OVER (PARTITION BY sale_date ORDER BY amount DESC) AS row_num
FROM
sales;
結(jié)果：
| sale_date | amount | row_num |
|---|---|---|
| 2023-01-01 | 1500 | 1 |
| 2023-01-01 | 1000 | 2 |
| 2023-01-02 | 1200 | 1 |
| 2023-01-02 | 1200 | 2 |
解釋：
- 即使同一天有相同的銷售金額，
row_number()也會(huì)為每條記錄分配唯一的序號(hào)。
五、性能優(yōu)化建議
使用窗口函數(shù)如rank()時(shí)，可能會(huì)對(duì)查詢性能產(chǎn)生影響，尤其是在處理大數(shù)據(jù)集時(shí)。以下是一些優(yōu)化建議：
- 使用PARTITION BY合理分區(qū)：將數(shù)據(jù)劃分為較小的分區(qū)，可以減少每個(gè)窗口函數(shù)計(jì)算的數(shù)據(jù)量。
- 指定ORDER BY明確排序：確保
ORDER BY子句明確，避免全表排序帶來的性能開銷。 - 創(chuàng)建適當(dāng)?shù)乃饕?/strong>：在
ORDER BY和PARTITION BY涉及的列上創(chuàng)建索引，可以加快排序和分區(qū)操作。 - 限制結(jié)果集：如果只需要前N條記錄，結(jié)合
WHERE rank <= N可以減少計(jì)算量。
六、總結(jié)
PostgreSQL的rank()窗口函數(shù)是一個(gè)強(qiáng)大的工具，適用于各種排名需求，如部門內(nèi)薪資排名、每組Top N記錄、百分位數(shù)計(jì)算等。通過合理使用rank()及其相關(guān)函數(shù)（如dense_rank()和row_number()），可以高效地處理復(fù)雜的數(shù)據(jù)分析任務(wù)。
關(guān)鍵點(diǎn)回顧：
rank()函數(shù)為相同值的行分配相同的排名，并跳過后續(xù)排名。- 結(jié)合
PARTITION BY和ORDER BY，可以實(shí)現(xiàn)多層次的排名需求。 - 與其他窗口函數(shù)（如
dense_rank()和row_number()）相比，rank()在處理并列排名時(shí)有獨(dú)特的行為。 - 通過優(yōu)化查詢和索引，可以提升窗口函數(shù)的性能表現(xiàn)。
希望本文的示例和解釋能幫助你在實(shí)際項(xiàng)目中更好地應(yīng)用rank()函數(shù)，提升數(shù)據(jù)處理的效率和準(zhǔn)確性！
到此這篇關(guān)于PostgreSQL中rank()窗口函數(shù)實(shí)用指南與示例的文章就介紹到這了,更多相關(guān)PostgreSQL rank()窗口函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
PostgreSQL中的NULL處理實(shí)現(xiàn)
PostgreSQL中的NULL值處理功能豐富,可以幫助開發(fā)者更好地管理和查詢數(shù)據(jù),合理地使用NULL值可以提高查詢效率,下面就來詳細(xì)的介紹一下NULL的處理,感興趣的可以了解一下2026-03-03
在使用?PostgreSql時(shí),實(shí)際場(chǎng)景中會(huì)出現(xiàn)某個(gè)字段為空或空字符串,下面這篇文章主要給大家介紹了關(guān)于Postgresql中null值和空字符串的相關(guān)資料,需要的朋友可以參考下2024-02-02
生產(chǎn)環(huán)境中隨著PostgreSQL數(shù)據(jù)庫(kù)表數(shù)據(jù)的不斷產(chǎn)生,數(shù)據(jù)庫(kù)目錄會(huì)不斷增長(zhǎng),當(dāng)磁盤空間不足時(shí)會(huì)有將PostgreSQL數(shù)據(jù)庫(kù)數(shù)據(jù)目錄遷移到其他目錄的需求,下面詳細(xì)介紹目錄遷移過程,需要的朋友可以參考下2024-04-04
postgresql synchronous_commit參數(shù)的用法介紹
這篇文章主要介紹了postgresql synchronous_commit參數(shù)的用法介紹，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-12-12
postgresql 數(shù)據(jù)庫(kù) 與TimescaleDB 時(shí)序庫(kù) join 在一起
這篇文章主要介紹了postgresql 數(shù)據(jù)庫(kù) 與TimescaleDB 時(shí)序庫(kù) join 在一起,需要的朋友可以參考下2020-12-12
PostgreSQL+Pgpool實(shí)現(xiàn)HA主備切換的操作
這篇文章主要介紹了PostgreSQL+Pgpool實(shí)現(xiàn)HA主備切換操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-12-12
postgresql 中的COALESCE()函數(shù)使用小技巧
這篇文章主要介紹了postgresql 中的COALESCE()函數(shù)使用小技巧，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL之連接失敗的問題及解決方案，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-05-05
PostgreSQL 實(shí)現(xiàn)查詢表字段信息SQL腳本
這篇文章主要介紹了PostgreSQL 實(shí)現(xiàn)查詢表字段信息SQL腳本，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
數(shù)據(jù)庫(kù)高可用這件事,做與不做,差別在于：做了一切正常時(shí)可能覺得多余,但出問題的時(shí)候你會(huì)慶幸它還在,PostgreSQL從9.0版本開始原生支持流復(fù)制機(jī)制,本文將摒棄空泛理論,以CentOS/Ubuntu環(huán)境下的PostgreSQL?14為例,手把手帶你完成從零搭建、配置調(diào)優(yōu)到故障演練的完整流程2026-05-05