查找每个学生的第二高分数及其学科

本文将通过一个示例，展示如何使用 SQL 中的窗口函数来找出每个学生的第二高分数及其对应的学科。

示例数据

假设我们的 test 表有以下数据：

第一步：为每个学生的分数生成排名

我们首先需要为每个学生的分数生成一个排名，以便后续筛选。可以使用以下 SQL 查询：

SELECT 
    student,
    subject,
    score,
    DENSE_RANK() OVER (PARTITION BY student ORDER BY score DESC) AS rn
FROM 
    test;

结果（子查询结果）：

解析

• DENSE_RANK() 函数为每个学生的分数生成一个排名，分数高的排名小。
• PARTITION BY student 表示我们按照学生进行分区。
• ORDER BY score DESC 指定按照分数降序排列。

第二步：筛选出第二高分数的学科

接下来，我们将通过外层查询筛选出排名为 2 的记录：

SELECT 
    student,
    subject
FROM (
    SELECT 
        student,
        subject,
        score,
        DENSE_RANK() OVER (PARTITION BY student ORDER BY score DESC) AS rn
    FROM 
        test
) AS ranked_scores
WHERE rn = 2;

结果（最终结果）：

解析

• 在外层查询中，我们只保留排名为 2 的记录，这样就得到了每个学生的第二高分数对应的学科。

完整步骤解析

1. 生成分数排名：

   • 查询：为每个学生的分数生成排名，以便后续筛选。
   • 结果表：显示学生的分数、学科及其对应的排名。

2. 筛选第二高分数的学科：

   • 查询：在生成的排名中筛选出排名为 2 的记录。
   • 最终结果：展示符合条件的学生和学科。

拓展

对比四个不同的窗口函数：RANK(), DENSE_RANK(), ROW_NUMBER(), 和 PERCENT_RANK()。

各种窗口函数的查询

我们将为每个学生的分数生成排名，并使用四个不同的窗口函数。

SELECT 
    student,
    subject,
    score,
    RANK() OVER (PARTITION BY student ORDER BY score DESC) AS rn,
    DENSE_RANK() OVER (PARTITION BY student ORDER BY score DESC) AS dense_rn,
    ROW_NUMBER() OVER (PARTITION BY student ORDER BY score DESC) AS row_rn,
    PERCENT_RANK() OVER (PARTITION BY student ORDER BY score DESC) AS percent_rn
FROM 
    test;

各窗口函数结果

结果解析

1. RANK()：

   • 相同分数的学生会获得相同的排名，但接下来的排名会跳过。
   • 例如，Bob 在 Math 和 Science 中都获得了相同的分数 80，因此他们的排名都是 1，接下来的排名是 3。

2. DENSE_RANK()：

   • 与 RANK() 相似，但不会跳过排名。
   • Bob 在 Math 和 Science 中的排名都是 1，接下来的排名是 2。

3. ROW_NUMBER()：

   • 为每一行分配一个唯一的序号，即使分数相同。
   • Bob 在 Math 和 Science 中的排名是 1 和 2，尽管他们的分数相同。

4. PERCENT_RANK()：

   • 计算相对排名，值在 0 到 1 之间，表示该行在分区中的位置。
   • 例如，Alice 的 Math 分数位于所有分数的中间，因此她的 percent_rank 是 0.50。