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 'David', 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 | | David | 80 | 4 | 4 | 3 | | Eve | 80 | 5 | 4 | 3 | | Frank | 75 | 6 | 6 | 4 |

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

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

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

法语写作助手

法语助手旗下的AI智能写作平台，支持语法、拼写自动纠错，一键改写、润色你的法语作文。

31

查看详情

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来筛选出每个部门的前三名（包含并列）。

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(...)、AVG() 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 = '2023-10-26' -- 假设分析某一天的表现
   )
   SELECT
       portfolio_id,
       stock_symbol,
       daily_return
   FROM
       StockPerformance
   WHERE
       stock_rank <= 5;

   登录后复制

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

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

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