1. ROW_NUMBER()
函数与 OVER()
的使用
1.1 什么是 ROW_NUMBER()
函数?
ROW_NUMBER()
是 Oracle 中的一个分析函数,用于为结果集中的每一行分配唯一的序号。这个序号根据指定的排序规则递增,可以通过 OVER()
子句中的 ORDER BY
子句来指定排序条件。与 Oracle 的 ROWNUM
类似,但 ROW_NUMBER()
提供了更多的灵活性,例如可以在每个分区内重新开始编号。
1.2 ROW_NUMBER()
与 OVER()
的基本语法
ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name)
- PARTITION BY:可选项,用于指定分组依据。每个分区内的行会从
1
开始编号。 - ORDER BY:必选项,用于指定在每个分区内的排序顺序。该排序决定了
ROW_NUMBER()
的编号顺序。
2. 示例:ROW_NUMBER()
在实际中的应用
为了更好地理解 ROW_NUMBER()
的使用,以下是一个创建表并插入数据的示例。假设我们有一个名为 test_7
的表,包含以下数据:
DROP TABLE test_7 CASCADE CONSTRAINTS PURGE;
CREATE TABLE test_7 (
type VARCHAR2(5),
qty VARCHAR2(5)
);
INSERT INTO test_7 VALUES('1', '6');
INSERT INTO test_7 VALUES('2', '9');
INSERT INTO test_7 VALUES('3', '5');
INSERT INTO test_7 VALUES('1', '6');
INSERT INTO test_7 VALUES('1', '7');
INSERT INTO test_7 VALUES('2', '7');
INSERT INTO test_7 VALUES('3', '7');
INSERT INTO test_7 VALUES('1', '9');
2.1 使用 ROW_NUMBER()
对数据进行编号
假设我们希望为每个 type
分区内的数据按 qty
的大小进行排序,并为每一行分配一个递增的序号。SQL 语句如下:
SELECT
type,
qty,
TO_CHAR(ROW_NUMBER() OVER (PARTITION BY type ORDER BY qty)) || '/' ||
TO_CHAR(COUNT(*) OVER (PARTITION BY type)) AS cnt2
FROM
test_7;
- ROW_NUMBER() OVER (PARTITION BY type ORDER BY qty): 为每个
type
分区内的数据根据qty
列的排序结果生成递增的序号。例如,对于type
值为1
的行,按qty
排序后,如果qty
有 6、7、9 三个值,则编号依次为 1、2、3。 - COUNT(*) OVER (PARTITION BY type): 统计每个
type
分区内的行数。输出结果为序号和总行数的组合,形如1/3
,2/3
,3/3
等。
2.2 运行结果
test_7
表的数据如下:
TYPE QTY
---- ----
1 6
1 7
1 9
2 9
2 7
3 5
3 7
3 7
运行上述 SQL 语句后,结果可能如下:
TYPE QTY CNT2
---- ---- ------
1 6 1/3
1 7 2/3
1 9 3/3
2 7 1/2
2 9 2/2
3 5 1/3
3 7 2/3
3 7 3/3
在这个结果中,每个 type
分区内的行按 qty
排序,ROW_NUMBER()
对每行数据分配一个递增的序号,而 COUNT(*)
则统计了每个分区内的总行数。
3. ROW_NUMBER()
与其他排名函数的对比
3.1 RANK()
函数
- 功能:
RANK()
返回分区内数据的排名值。如果遇到相同的值,它们将获得相同的排名,但接下来的排名将跳过。例如,如果有两个并列第二名,下一名将是第四名。 - 示例:
SELECT type, qty, RANK() OVER (PARTITION BY type ORDER BY qty) AS rank_value FROM test_7;
3.2 DENSE_RANK()
函数
- 功能:
DENSE_RANK()
与RANK()
类似,但不同之处在于,如果有两个并列第二名,下一名将是第三名,而不是第四名。 - 示例:
SELECT type, qty, DENSE_RANK() OVER (PARTITION BY type ORDER BY qty) AS dense_rank_value FROM test_7;
3.3 ROW_NUMBER()
函数
- 功能:
ROW_NUMBER()
返回分区内数据的唯一序号,不考虑数据是否相同。即使值相同,编号也会递增。
通过这些函数,可以灵活地为分区内的数据进行排序和编号,满足不同的业务需求。
4. ROW_NUMBER()
的实战应用
假设我们在一个员工表 emp
中想要统计每个部门(deptno
)的员工薪资排名,同时为薪资相同的员工保留相同的排名次序:
SELECT
empno,
deptno,
sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank_value,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dense_rank_value,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_number_value
FROM
emp;
- RANK() 会跳过排名。例如,如果有两个员工的薪资并列第二,下一名员工的排名将为第四。
- DENSE_RANK() 不会跳过排名。如果有两个员工的薪资并列第二,下一名员工的排名将为第三。
- ROW_NUMBER() 无论薪资是否相同,都会给每个员工分配唯一的编号。
ROW_NUMBER()
是 Oracle 中一个功能强大的分析函数,尤其在与 OVER()
子句结合使用时,可以灵活地为结果集中的每一行生成唯一编号。这种编号方式在排序和分区处理数据时尤为有用。通过与其他排名函数的对比,你可以选择最适合你业务需求的排名方式。
非常感谢您读到这里!如果您觉得这篇文章对您有帮助,可以关注一下博主。关注后,您将第一时间获得最新的AI、云计算、运维(Linux、数据库,容器等)技术,以及更多实用的技能干货。
学习成功人士的经验,提供全面的学习资源和社群支持,多种副业选择,总有一个适合你。