sql 中 rank 用法_sql 中 rank 函数排名技巧方法

rank函数在sql中用于为结果集中的行分配排名，当多行具有相同排序值时，它们会获得相同的排名，并跳过后续排名。与row_number不同，它允许并列且不唯一；与dense_rank不同，它会在下一个排名上跳过相应数量。1. rank()适用于需要找出前n名且包含所有并列者的情况；2. row_number为每行分配唯一序号，不考虑并列；3. dense_rank为并列行分配相同排名，但不会跳过后续排名。使用场景包括销售排名、绩效评估、用户行为分析等。

SQL中的RANK函数是窗口函数家族中的一员，它的核心作用是为结果集中的每一行分配一个排名，特别之处在于，当多行具有相同的排序值时，它们会获得相同的排名，并且下一个排名会跳过相应数量的数字。这在我们需要找出“并列第一”或“前N名，且包含所有并列者”的场景时非常有用。

解决方案

RANK()函数的基本用法是结合OVER子句，它定义了函数操作的“窗口”或数据分区。它的通用语法是：

RANK() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column_to_rank [ASC|DESC], ... )

这里：

• PARTITION BY 是可选的。如果你指定了它，RANK()函数会为每个独立的分区（即分组）重新开始排名。这就像你把学生按班级分开，然后在每个班级内部进行排名。
• ORDER BY 是必须的。它指定了在每个分区内（或整个结果集，如果没有PARTITION BY）用来确定排名的列和排序顺序。排名是根据这个顺序分配的。

举个例子，假设我们有一个销售表sales_data，包含region（地区）和sales_amount（销售额）。如果我们想找出每个地区销售额最高的员工，并允许并列，RANK()就派上用场了：

SELECT
    employee_name,
    region,
    sales_amount,
    RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) as rank_in_region
FROM
    sales_data;

这段代码会先按region把数据分成不同的组，然后在每个组内，根据sales_amount降序排列，并为每个员工分配一个排名。如果两个员工在同一个地区销售额相同，他们会得到相同的排名，而下一个排名会跳过。比如，如果前两名都是1000，排名会是1, 1, 3, 4...

SQL中RANK函数与ROW_NUMBER、DENSE_RANK有何不同？

这三者都是SQL中常用的排名函数，但它们处理并列关系的方式截然不同，理解它们的差异是掌握排名技巧的关键。我个人在使用这些函数时，一开始也常常会混淆，但一旦你抓住它们处理“并列”的核心逻辑，就豁然开朗了。

• ROW_NUMBER(): 这个函数为结果集中的每一行分配一个唯一的、连续的整数。它不考虑并列，即使有两行的排序值完全相同，它们也会得到不同的、连续的排名。你可以把它想象成给每一行一个“身份证号”，保证唯一性。

  • 示例排名序列（针对并列值）: 1, 2, 3, 4, 5 (即使值相同，排名也不同)

• RANK(): 这是我们当前讨论的重点。它会为并列的行分配相同的排名，但随后的排名会跳过。也就是说，如果有N行并列第一，它们都会得到排名1，而下一行的排名将是N+1。

  • 示例排名序列（针对并列值）: 1, 1, 3, 4, 4, 6 (跳过排名2和5)

• DENSE_RANK(): 这个函数也为并列的行分配相同的排名，但它不会跳过随后的排名。它提供的是一个“紧密”的排名序列，没有间隙。

  • 示例排名序列（针对并列值）: 1, 1, 2, 3, 3, 4 (没有跳过排名)

何时选用？

• 如果你需要为每一行分配一个唯一的序号，无论是否有并列，就用ROW_NUMBER()。比如，当你需要分页显示数据，或者想从多个重复记录中“随机”选择一个时。
• 如果你想找出前N名，并且希望所有并列的记录都被包含，但又不介意排名有跳跃，那么RANK()是你的选择。例如，找出班级前三名，如果并列，所有并列者都算在内，但下一名就直接是第四名了。
• 如果你想要一个连续的排名序列，且并列的记录获得相同排名，那么DENSE_RANK()更合适。比如，你想知道一个班级里有多少个不同的成绩等级，或者找出前三名，并希望“第三名”之后直接就是“第四名”，而不是跳过。

下面是一个对比三者行为的SQL示例：

WITH Scores AS (
    SELECT 'Alice' as name, 90 as score UNION ALL
    SELECT 'Bob', 85 UNION ALL
    SELECT 'Charlie', 85 UNION ALL
    SELECT 'D*id', 80 UNION ALL
    SELECT 'Eve', 80 UNION ALL
    SELECT 'Frank', 75
)
SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) as row_num_rank,
    RANK() OVER (ORDER BY score DESC) as rank_rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_rank
FROM
    Scores;

运行结果你会看到： | name | score | row_num_rank | rank_rank | dense_rank_rank | | :------ | :---- | :----------- | :-------- | :-------------- | | Alice | 90 | 1 | 1 | 1 | | Bob | 85 | 2 | 2 | 2 | | Charlie | 85 | 3 | 2 | 2 | | D*id | 80 | 4 | 4 | 3 | | Eve | 80 | 5 | 4 | 3 | | Frank | 75 | 6 | 6 | 4 |

从这个结果就能清晰地看出它们之间的差异了。

如何利用RANK函数处理复杂排名需求？

RANK函数虽然看似简单，但在实际的业务场景中，通过巧妙地结合PARTITION BY和ORDER BY子句，以及与子查询或CTE（Common Table Expression）的配合，可以解决很多复杂的排名问题。

Phidata

Phidata是一个开源框架，可以快速构建和部署AI智能体应用

173 查看详情

1. 多条件排序的排名： 有时候，你不仅仅需要根据一个字段来排名，而是要根据多个字段的优先级来决定排名。比如，你可能想先按总销售额降序排名，如果销售额相同，再按客户数量降序排名。

   SELECT
       product_id,
       total_sales,
       customer_count,
       RANK() OVER (ORDER BY total_sales DESC, customer_count DESC) as product_rank
   FROM
       product_performance;

   这里，ORDER BY子句中的顺序决定了排名的优先级。

2. 分组内排名与过滤： 这是RANK最常见的用途之一。比如，找出每个部门工资最高的前三名员工。这需要先按部门分组，然后在每个部门内部进行排名，最后再筛选出排名靠前的员工。

   WITH EmployeeRank AS (
       SELECT
           employee_id,
           employee_name,
           department_id,
           salary,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
       FROM
           employees
   )
   SELECT
       employee_id,
       employee_name,
       department_id,
       salary
   FROM
       EmployeeRank
   WHERE
       salary_rank <= 3;

   这里，PARTITION BY department_id确保了排名是在每个部门内部独立进行的。然后，我们通过外部查询的WHERE salary_rank 来筛选出每个部门的前三名（包含并列）。

3. 处理NULL值在排名中的位置： 在ORDER BY子句中，你可以明确指定NULLS FIRST或NULLS LAST来控制NULL值在排名中的位置。这在数据不完整时非常有用。

   SELECT
       item_id,
       value,
       RANK() OVER (ORDER BY value DESC NULLS LAST) as item_rank_nulls_last,
       RANK() OVER (ORDER BY value DESC NULLS FIRST) as item_rank_nulls_first
   FROM
       items_with_nulls;

   默认情况下，不同的数据库系统对NULL值的处理方式可能不同，显式指定可以避免潜在的排名混乱。

4. 结合其他窗口函数或聚合函数：RANK函数可以与其他窗口函数（如SUM() OVER(...)、*G() OVER(...)）或聚合函数结合使用，以实现更复杂的分析。例如，在计算排名的同时，也计算该组的总和或平均值。

   SELECT
       category,
       product,
       sales,
       RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_in_category,
       SUM(sales) OVER (PARTITION BY category) as total_sales_in_category
   FROM
       product_sales;

   这展示了如何在一个查询中同时进行分组排名和分组聚合。

这些技巧的核心在于灵活运用PARTITION BY来定义分组边界，以及ORDER BY来确定排序逻辑。而将带有RANK函数的结果作为CTE或子查询，再进行进一步的过滤或连接，则是处理复杂排名需求的常用模式。

RANK函数在实际业务场景中的应用案例解析

RANK函数在各种业务场景中都有着广泛而实用的应用，它能帮助我们从海量数据中快速提取有价值的信息，并进行有效的决策。

1. 产品或服务性能分析： 这是最常见的应用之一。比如，一个电商平台需要找出每个月销售额最高的商品。

   -- 找出每个月销售额最高的前5个商品
   WITH MonthlySalesRank AS (
       SELECT
           EXTRACT(YEAR FROM sale_date) as sales_year,
           EXTRACT(MONTH FROM sale_date) as sales_month,
           product_id,
           SUM(sale_amount) as total_monthly_sales,
           RANK() OVER (PARTITION BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date) ORDER BY SUM(sale_amount) DESC) as rank_in_month
       FROM
           sales_records
       GROUP BY
           sales_year, sales_month, product_id
   )
   SELECT
       sales_year,
       sales_month,
       product_id,
       total_monthly_sales
   FROM
       MonthlySalesRank
   WHERE
       rank_in_month <= 5;

   通过这种方式，运营团队可以快速识别出热销产品，调整库存或营销策略。

2. 员工绩效评估与奖励： 在人力资源管理中，RANK函数可以用于评估员工绩效，比如找出每个区域销售额排名前列的销售人员，以便进行奖励或培训。

   -- 找出每个区域销售业绩前三名的员工
   WITH EmployeePerformance AS (
       SELECT
           e.employee_id,
           e.employee_name,
           e.region,
           SUM(s.sales_amount) as total_sales,
           RANK() OVER (PARTITION BY e.region ORDER BY SUM(s.sales_amount) DESC) as sales_rank
       FROM
           employees e
       JOIN
           sales s ON e.employee_id = s.employee_id
       GROUP BY
           e.employee_id, e.employee_name, e.region
   )
   SELECT
       employee_id,
       employee_name,
       region,
       total_sales
   FROM
       EmployeePerformance
   WHERE
       sales_rank <= 3;

   这提供了一个清晰的绩效排名，有助于管理层做出公正的决策。

3. 用户行为分析： 在分析用户行为时，我们可能需要找出每个用户访问次数最多的页面，或者某个特定行为（如购买、点赞）的排名。

   -- 找出每个用户访问次数最多的页面
   WITH UserPageVisits AS (
       SELECT
           user_id,
           page_id,
           COUNT(*) as visit_count,
           RANK() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) as page_rank
       FROM
           page_views
       GROUP BY
           user_id, page_id
   )
   SELECT
       user_id,
       page_id,
       visit_count
   FROM
       UserPageVisits
   WHERE
       page_rank = 1; -- 只取访问次数最多的页面，如果并列则都取

   这有助于产品经理了解用户最感兴趣的内容，从而优化产品设计。

4. 金融数据分析： 在金融领域，RANK函数可以用于对投资组合、股票表现或基金收益进行排名。

   -- 找出每个投资组合中表现最好的前5只股票
   WITH StockPerformance AS (
       SELECT
           portfolio_id,
           stock_symbol,
           daily_return,
           RANK() OVER (PARTITION BY portfolio_id ORDER BY daily_return DESC) as stock_rank
       FROM
           daily_stock_returns
       WHERE
           return_date = '2025-10-26' -- 假设分析某一天的表现
   )
   SELECT
       portfolio_id,
       stock_symbol,
       daily_return
   FROM
       StockPerformance
   WHERE
       stock_rank <= 5;

   这能帮助投资者快速识别表现优异的资产，进行资产配置调整。

这些例子仅仅是冰山一角。RANK函数作为一个强大的分析工具，其应用场景几乎涵盖了所有需要对数据进行分类、排序和筛选的业务需求。关键在于如何根据具体的业务逻辑，灵活地定义PARTITION BY和ORDER BY的条件。

以上就是sql 中 rank 用法_sql 中 rank 函数排名技巧方法的详细内容，更多请关注其它相关文章！