1. 为什么你需要掌握dense_rank()函数第一次遇到需要处理销售业绩排名时我被需求文档里连续排名且不跳号的要求难住了。比如有两个销售冠军并列第一下一个应该是第二名而不是第三名——这正是dense_rank()的典型应用场景。这个函数在MySQL和Hive中的表现略有不同但核心逻辑都是解决这类密集排名问题。记得去年双十一大促时我们需要实时计算各品类商品的销售排名。当两款手机销量相同时如果用普通rank()函数会导致排名出现断层市场部门坚决要求必须连续编号。这时候dense_rank()就成了救命稻草它能让并列第一的商品之后紧跟着第二名而不是突然跳到第三名。与row_number()和rank()相比dense_rank()的特点是相同值获得相同排名与rank()相同不会跳过后续排名与row_number()不同特别适合需要展示连续名次的业务场景2. dense_rank()基础语法全解析2.1 MySQL中的标准写法在MySQL 8.0及以上版本中dense_rank()的完整语法结构是这样的DENSE_RANK() OVER ( [PARTITION BY 分组字段1, 分组字段2...] ORDER BY 排序字段1 [ASC|DESC], 排序字段2... )这里有个容易踩坑的地方PARTITION BY和GROUP BY是完全不同的概念。我曾经花了三小时调试一个错误就是因为把两者混淆了。PARTITION BY只是逻辑分组不会减少行数而GROUP BY会聚合数据。举个实际例子假设有学生成绩表SELECT student_name, subject, score, DENSE_RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS rank FROM exam_results这个查询会按学科分组在每个学科内按分数降序排名且排名是连续的。2.2 Hive中的特殊注意事项HiveQL的语法看起来和MySQL很像但有几个关键差异Hive对窗口函数的支持更早从0.11版本就开始提供了在Hive中执行时要注意数据倾斜问题特别是当PARTITION BY的字段基数很大时Hive的性能调优参数会影响窗口函数执行效率比如SET hive.exec.paralleltrue; SET hive.exec.parallel.thread.number16;我曾经处理过一个千万级数据的排名需求在Hive中不加优化参数跑了2小时调整后只需15分钟。3. 电商销售排名实战案例3.1 数据准备阶段我们先创建一个模拟电商销售数据的表结构在MySQL和Hive中都可以运行CREATE TABLE sales_records ( order_id VARCHAR(20), seller_id INT, category VARCHAR(50), sale_amount DECIMAL(10,2), sale_date DATE ); -- 插入模拟数据 INSERT INTO sales_records VALUES (ORD1001, 101, 手机, 5999.00, 2023-05-01), (ORD1002, 102, 笔记本, 8999.00, 2023-05-01), (ORD1003, 101, 手机, 5999.00, 2023-05-02), (ORD1004, 103, 平板, 3999.00, 2023-05-02), (ORD1005, 102, 笔记本, 8999.00, 2023-05-03), (ORD1006, 104, 手机, 6999.00, 2023-05-03), (ORD1007, 105, 平板, 4999.00, 2023-05-04), (ORD1008, 103, 平板, 3999.00, 2023-05-05), (ORD1009, 101, 手机, 7999.00, 2023-05-06), (ORD1010, 102, 笔记本, 9999.00, 2023-05-07);3.2 单维度排名查询先看最简单的场景计算所有商家的销售总额排名SELECT seller_id, SUM(sale_amount) AS total_sales, DENSE_RANK() OVER(ORDER BY SUM(sale_amount) DESC) AS sales_rank FROM sales_records GROUP BY seller_id;这个查询会出现一个典型问题如果有商家销售额相同他们会获得相同排名但下一个商家会继续使用后续数字排名不会出现断层。3.3 多维度分组排名更复杂的场景是按商品类目分组排名SELECT seller_id, category, SUM(sale_amount) AS category_sales, DENSE_RANK() OVER( PARTITION BY category ORDER BY SUM(sale_amount) DESC ) AS category_rank FROM sales_records GROUP BY seller_id, category;这里我加了个PARTITION BY相当于在每个商品类目内部单独计算排名。曾经有个同事错误地把GROUP BY字段和PARTITION BY字段混用导致结果完全不对这点要特别注意。4. 性能优化与避坑指南4.1 MySQL性能调优当数据量超过百万行时窗口函数可能变慢。我总结了几条优化经验为ORDER BY和PARTITION BY的字段建立索引减少窗口函数计算范围比如先过滤再排名SELECT * FROM ( SELECT seller_id, sale_date, sale_amount, DENSE_RANK() OVER(PARTITION BY seller_id ORDER BY sale_amount DESC) AS rnk FROM sales_records WHERE sale_date BETWEEN 2023-05-01 AND 2023-05-07 ) t WHERE rnk 3;避免在窗口函数中使用复杂表达式可以先计算好中间结果4.2 Hive特有优化方案在Hive中处理大数据集时这些技巧很管用合理设置reduce任务数量SET mapred.reduce.tasks100;使用DISTRIBUTE BY提前分区SELECT /* MAPJOIN(b) */ a.seller_id, a.category_rank FROM ( SELECT seller_id, category, DENSE_RANK() OVER(PARTITION BY category ORDER BY sales DESC) AS category_rank FROM ( SELECT seller_id, category, SUM(sale_amount) AS sales FROM sales_records GROUP BY seller_id, category ) t ) a JOIN seller_info b ON a.seller_id b.seller_id;对于超大数据集考虑分阶段计算先聚合再排名4.3 常见错误排查错误窗口函数结果不符合预期检查PARTITION BY和ORDER BY的顺序是否正确确认是否真的需要DENSE_RANK()而不是RANK()错误查询执行超时检查是否缺少必要索引考虑缩小数据范围或增加硬件资源错误Hive查询内存不足调整map和reduce的内存设置增加并行度参数5. 三种排名函数的深度对比5.1 通过实例理解差异我们用同一组数据演示三个函数的不同SELECT student_name, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS row_num, RANK() OVER(ORDER BY score DESC) AS rank_val, DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank_val FROM student_scores;假设数据是张三 100分李四 100分王五 95分赵六 90分结果会是student_namescorerow_numrank_valdense_rank_val张三100111李四100211王五95332赵六904435.2 业务场景选择指南根据我的项目经验这三个函数的适用场景分别是ROW_NUMBER()需要绝对唯一序号时分页查询场景抽样数据时RANK()体育比赛排名允许并列且后续名次跳跃需要显示实际位次时DENSE_RANK()销售业绩展示需要连续编号的报表用户体验要求排名不间断的场景5.3 执行效率对比在千万级数据测试中三个函数的性能差异不大但有几个发现ROW_NUMBER()通常最快因为它不需要处理相同值DENSE_RANK()比RANK()略快因为跳号逻辑更简单在Hive中三者性能差异更明显特别是数据倾斜时6. 真实业务场景进阶应用6.1 动态阈值分析在用户分层运营中我们经常需要找出前10%的用户。使用DENSE_RANK()可以这样实现WITH user_ranking AS ( SELECT user_id, purchase_amount, DENSE_RANK() OVER(ORDER BY purchase_amount DESC) AS rank_num, COUNT(*) OVER() AS total_users FROM user_transactions ) SELECT user_id, purchase_amount, rank_num, total_users, (rank_num * 100.0 / total_users) AS percentile FROM user_ranking WHERE (rank_num * 100.0 / total_users) 10;这个查询能准确找出消费金额在前10%的用户而且处理并列情况更合理。6.2 时间序列排名分析用户连续消费行为时可以结合日期函数SELECT user_id, login_date, DENSE_RANK() OVER( PARTITION BY user_id ORDER BY login_date ) AS login_sequence FROM user_logins WHERE login_date DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);这样能识别出用户的连续登录天数对留存分析很有帮助。6.3 多条件复合排名更复杂的业务可能需要对多个指标加权排名SELECT product_id, sales_volume, profit_margin, customer_rating, (sales_volume * 0.5 profit_margin * 0.3 customer_rating * 0.2) AS composite_score, DENSE_RANK() OVER(ORDER BY (sales_volume * 0.5 profit_margin * 0.3 customer_rating * 0.2) DESC) AS overall_rank FROM products;这种复合排名在商品推荐系统中特别有用可以根据业务需要调整权重系数。