在數(shù)據(jù)分析和處理的過程中，尤其是在使用 SQL 進(jìn)行查詢時，排名函數(shù)（Ranking Functions）是一個非常重要的工具。Apache Hive 和其他數(shù)據(jù)庫系統(tǒng)都提供了一些排名函數(shù)，常見的包括 ROW_NUMBER()、RANK() 和 DENSE_RANK()。雖然這三個函數(shù)都可以用于為結(jié)果集中的行分配一個排名，但它們的工作原理和返回結(jié)果卻各不相同。本文將深入探討這三個函數(shù)的區(qū)別、使用場景以及實(shí)例演示。

ROW_NUMBER()、RANK() 和 DENSE_RANK()是排名函數(shù)，也叫分組排序函數(shù)。即可以對查詢結(jié)果集進(jìn)行分組后進(jìn)行排序，對結(jié)果集的每一行分配一個編號。例如：對考試成績按科目進(jìn)行分組，然后按分?jǐn)?shù)排序，獲取前5名。

ROW_NUMBER()、RANK() 和 DENSE_RANK()這三個函數(shù)在mysql8.0、hive、oracle都是支持的

一、函數(shù)定義

1.1、ROW_NUMBER()

ROW_NUMBER() 函數(shù)用于為結(jié)果集中的每一行分配一個唯一的序號。無論是否存在重復(fù)值，ROW_NUMBER() 返回的序號都是連續(xù)的。這個函數(shù)常用于需要唯一行號的場景。

基本語法：

ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)

• PARTITION BY：指定如何將結(jié)果集分組。
• ORDER BY：指定每個分組內(nèi)的排序規(guī)則。

1.2、RANK()

RANK() 函數(shù)與 ROW_NUMBER() 類似，也用于對結(jié)果集中的行進(jìn)行排名，但在處理重復(fù)值時表現(xiàn)不同。RANK() 會為相同的值分配相同的排名，并在隨后排名中跳過相應(yīng)的名次。

基本語法：

RANK() OVER (PARTITION BY column1 ORDER BY column2)

1.3、DENSE_RANK()

DENSE_RANK() 函數(shù)也是用于排名的，其與 RANK() 的主要區(qū)別在于處理重復(fù)值時的行為。DENSE_RANK() 為相同的值分配相同的排名，但不會跳過名次。

基本語法：

DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2)

1.4、row_number()、rank() 和 dense_rank() 的區(qū)別

• ROW_NUMBER()：為每一行分配一個唯一的行號。即使有重復(fù)值，返回的行號也是唯一且連續(xù)的。
• RANK()：為相同的值分配相同的排名，但在后續(xù)排名中會跳過相應(yīng)的名次。例如，如果有兩個并列第一的記錄，則下一個記錄的排名為第三。
• DENSE_RANK()：與 RANK() 類似，給相同的值分配相同的排名，但后續(xù)排名不會跳過。相同的值后面的排名是緊接著的下一個值。

二、使用示例

結(jié)合示例來看一下三者之間的區(qū)別，以下sql基于MySql8.0進(jìn)行講解。

建表語句：

create table test(
       id varchar(10) NOT NULL,
       `name` varchar(10) NULL,
       age varchar(10) NULL,
       salary int NULL
);
-- 數(shù)據(jù)是每個人不同年齡段的薪資數(shù)據(jù)
insert into test(id,`name`,age,salary) values(1,'張三',24,15000);
insert into test(id,`name`,age,salary) values(2,'李四',22,8000);
insert into test(id,`name`,age,salary) values(3,'王五',20,6500);
insert into test(id,`name`,age,salary) values(4,'趙六',23,15000);
insert into test(id,`name`,age,salary) values(5,'孫七',22,8000);
insert into test(id,`name`,age,salary) values(6,'周八',21,7500);

表數(shù)據(jù)：

以下是使用這三個函數(shù)的 SQL 查詢示例：

SELECT id, name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK() OVER (ORDER BY salary DESC) AS `rank`,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS `dense_rank`
FROM test;

注意：以上sql只使用了order by進(jìn)行排序，并沒有使用partition by進(jìn)行分組，所以默認(rèn)是同一組，然后組內(nèi)進(jìn)行排名。

從上表可以看出：

• ROW_NUMBER()： 會為每一行數(shù)據(jù)分配唯一連續(xù)的編號，不會因?yàn)榕琶嗤峙湎嗤木幪枴?/li>
• RANK()： 若排名相同則分配相同的編號，并在隨后排名中跳過相應(yīng)的名次。
• DENSE_RANK()： 若排名相同則分配相同的編號，并在隨后排名中不跳過相應(yīng)的名次。

三、總結(jié)

在數(shù)據(jù)分析中，ROW_NUMBER()、RANK() 和 DENSE_RANK() 是非常有用的工具。它們可以幫助用戶快速對數(shù)據(jù)進(jìn)行排名和分類分析。雖然這三種函數(shù)的作用相似，但因其在處理重復(fù)值時的行為不同，所以在使用時需要根據(jù)具體需求進(jìn)行選擇。

3.1、row_number()、rank() 和 dense_rank() 的區(qū)別

• ROW_NUMBER()：為每一行分配唯一的行號，適合唯一標(biāo)識需求。
• RANK()：為重復(fù)值分配相同的排名，并在后續(xù)排名中跳過名次，適合需要處理排名的場景。
• DENSE_RANK()：為重復(fù)值分配相同的排名，但不跳過名次，適合希望連續(xù)排名的場景。

下面表格總結(jié)了這三個函數(shù)的主要區(qū)別：

具體請參考《row_number() over (partition by 分組列 order by 排序列 desc)、row_number() 函數(shù)、分組排序函數(shù)》、《數(shù)據(jù)庫rank()分組排序函數(shù)詳解》、《數(shù)據(jù)庫dense_rank() 函數(shù)的使用、MySQL之dense_rank()、Hive之dense_rank()函數(shù)》

到此這篇關(guān)于數(shù)據(jù)庫中row_number()、rank() 和 dense_rank() 的區(qū)別的文章就介紹到這了,更多相關(guān)row_number() rank()  dense_rank()內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論