sql 中 dense_rank 用法_sql 中 dense_rank 密集排名教程

dense_rank 是 sql 中用于分配连续排名的窗口函数，1. 它在并列情况下给予相同排名且后续排名不跳过；2. 语法为 dense_rank() over ([partition by] order by)，其中 partition by 可选，order by 必须；3. 相比 rank()，dense_rank 排名更密集，不会因并列而跳过数字；4. 常用于业务场景如商品销量排名、用户活跃度分析；5. 使用时需注意排序方向、分组逻辑、性能优化及与 row_number 的区别。

DENSE_RANK 在 SQL 中是用来给数据集中的行分配排名的一种窗口函数，它的特点是，如果存在并列的情况，它们会获得相同的排名，并且下一个非并列的排名是紧接着上一个排名的，中间不会出现空缺。简单来说，它提供的是一个“密集”的排名序列，确保排名的连续性。

解决方案

谈到 DENSE_RANK，我个人觉得它在处理排名问题时，比 RANK() 函数在某些场景下更符合我们直觉中的“名次”概念。当你需要对一组数据进行排序，并且希望并列的项拥有相同的排名，同时后续的排名是连续的，DENSE_RANK 就是你的首选。它不会像 RANK() 那样，因为并列而跳过某些排名。

想象一下，你有一张员工销售业绩表，你想要找出每个部门销售额前三的员工，而且如果有人销售额相同，他们应该并列，但下一名员工的排名不能跳过。这时候 DENSE_RANK 就派上用场了。

它的基本语法是：DENSE_RANK() OVER ([PARTITION BY column_list] ORDER BY column_list [ASC|DESC])。

这里面，PARTITION BY 是可选的，它定义了分组的依据。如果你不指定 PARTITION BY，那么整个数据集会被视为一个组进行排名。而 ORDER BY 则是必需的，它定义了排名时数据的排序规则，决定了谁在前谁在后。

举个例子：

SELECT
    employee_id,
    sales_amount,
    department,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) as dept_sales_rank
FROM
    employee_sales;

这段代码的意思是，对于每个部门（PARTITION BY department），根据员工的销售额从高到低（ORDER BY sales_amount DESC）进行密集排名。这样，即使有多个员工销售额相同，他们会得到相同的排名，而下一个销售额不同的员工会得到紧接着的下一个排名，不会出现跳过。

DENSE_RANK 和 RANK 有什么区别？

这是个老生常谈的问题，但确实是理解 DENSE_RANK 绕不开的关键点。说实话，刚接触窗口函数的时候，这俩名字就容易让人犯迷糊。最核心的区别就在于“空缺”与否。

让我用一个简单的表格来直观地展示：

假设我们有这样一份成绩数据：

现在我们分别用 RANK() 和 DENSE_RANK() 来给这些学生排名（按成绩降序）：

使用 RANK():

SELECT
    student_id,
    score,
    RANK() OVER (ORDER BY score DESC) as rank_score
FROM
    student_scores;

结果可能是这样：

使用 DENSE_RANK():

SELECT
    student_id,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_score
FROM
    student_scores;

结果会是这样：

你看，RANK() 在有两个并列第一（成绩90）之后，下一个成绩（85）的排名直接跳到了3。而 DENSE_RANK() 在两个并列第一之后，下一个成绩（85）的排名是2，紧密相连。这在很多场景下，比如评选“前N名”时，DENSE_RANK 显得更合理，因为它不会因为并列而“浪费”排名。

DENSE_RANK 在实际业务场景中的应用案例

DENSE_RANK 的实用性远不止于简单的排名展示，它在很多业务分析中都扮演着关键角色。我曾经在处理用户行为数据时，就大量用到了它。

百度文心百中

百度大模型语义搜索体验中心

22

查看详情

案例一：找出每个商品类别中销量前三的商品

假设你是一个电商平台的数据分析师，你想知道每个商品类别里，哪些商品最受欢迎（销量最高），并且你想看到前三名，如果销量相同，它们应该并列，并且不跳过排名。

SELECT
    category_name,
    product_name,
    sales_volume,
    DENSE_RANK() OVER (PARTITION BY category_name ORDER BY sales_volume DESC) as product_rank_in_category
FROM
    product_sales_data
WHERE
    DENSE_RANK() OVER (PARTITION BY category_name ORDER BY sales_volume DESC) <= 3;

这里，我们首先对每个 category_name 进行分组，然后在每个组内按 sales_volume 降序排名。最后，通过外部的 WHERE 子句筛选出排名在前三的商品。这比你手动去筛选或者写复杂的子查询要高效和优雅得多。

案例二：分析用户在不同月份的活跃度排名

如果你想追踪用户在每个月的活跃度（例如，登录次数）排名，并且想知道哪些用户是持续的“高活跃用户”，DENSE_RANK 可以帮你识别出每个月排名靠前的用户群体。

SELECT
    user_id,
    EXTRACT(YEAR FROM activity_date) as activity_year,
    EXTRACT(MONTH FROM activity_date) as activity_month,
    COUNT(DISTINCT activity_date) as login_days,
    DENSE_RANK() OVER (PARTITION BY EXTRACT(YEAR FROM activity_date), EXTRACT(MONTH FROM activity_date) ORDER BY COUNT(DISTINCT activity_date) DESC) as monthly_active_rank
FROM
    user_activities
GROUP BY
    user_id,
    EXTRACT(YEAR FROM activity_date),
    EXTRACT(MONTH FROM activity_date);

这个查询会计算每个用户在每个月的登录天数，然后根据登录天数在当月进行密集排名。这能让你快速发现哪些用户是每个月的“头号玩家”或“核心用户”，即使他们登录天数相同，也能得到连续的排名。

使用 DENSE_RANK 时需要注意的常见陷阱和性能考量

虽然 DENSE_RANK 很好用，但它也不是万能的，使用时有些“坑”和性能上的考量是需要留意的。我个人就曾因为没注意这些细节，导致查询效率低下，甚至结果不符合预期。

1. ORDER BY 的重要性与方向

这是最基础也最容易出错的地方。如果你忘记了 ORDER BY，或者 ORDER BY 的方向（ASC 或 DESC）搞错了，那么排名结果会完全偏离你的预期。比如，你想找销量最高的，结果排出了销量最低的。这种低级错误，在调试复杂SQL时，往往会让你抓狂。务必仔细检查 ORDER BY 子句，确保它反映了你想要的排序逻辑。

2. PARTITION BY 的理解与使用

PARTITION BY 是分组的关键。如果你想在整个数据集上排名，那就不要写 PARTITION BY。但如果你想在每个分组内独立排名（比如每个部门内部排名），那就必须正确指定 PARTITION BY 的列。我见过不少新手，想在部门内排名，结果忘了加 PARTITION BY department，导致所有部门的员工混在一起排名，结果当然是错的。这就像你把所有班级的学生成绩混在一起排名，而不是在各自班级里排名一样。

3. 大数据量下的性能问题

窗口函数，包括 DENSE_RANK，在处理大数据量时可能会带来性能开销。这是因为数据库需要对数据进行排序和分区操作，这通常是比较耗费资源的。

• 索引： 对 PARTITION BY 和 ORDER BY 中涉及的列建立合适的索引，可以显著提升性能。例如，如果你按 department 分区，按 sales_amount 排序，那么在 department 和 sales_amount 上建立组合索引或单独索引会很有帮助。
• 数据量： 如果你的表有上亿行数据，并且你没有适当的索引，或者 PARTITION BY 和 ORDER BY 的组合导致无法有效利用索引，那么查询可能会非常慢。这时候你可能需要考虑数据分片、物化视图或者将部分计算逻辑转移到ETL阶段进行预处理。
• 内存与磁盘： 当需要排序的数据量超过可用内存时，数据库会将数据写入临时磁盘文件进行排序，这会大大降低性能。所以，优化 ORDER BY 涉及的列，减少需要排序的数据量，或者增加数据库服务器的内存，都是可以考虑的方向。

4. 结果集的唯一性与排名

DENSE_RANK 的特性是并列值得到相同排名。如果你需要为每个行都分配一个唯一的排名，即使它们的值相同，那么 ROW_NUMBER() 才是你需要的。不要混淆了这两种需求，否则结果会不符合预期。

总而言之，DENSE_RANK 是一个非常强大的工具，但要用好它，需要对它的工作原理有清晰的理解，并在实际操作中注意这些细节。

以上就是sql 中 dense_rank 用法_sql 中 dense_rank 密集排名教程的详细内容，更多请关注php中文网其它相关文章！