pandas 实现 sql 中的row_number,dense_rank,rank

本文详细介绍了如何使用 Pandas 中的排名函数对数据进行排序,并提供了 row_number、rank 和 dense_rank 函数的具体应用实例,同时展示了如何通过 groupby 进行分组排序。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

  1. data
df=pd.DataFrame({'Year':[2012,2013,2013,2014],'Value':[10,20,25,30]})

---------------------------------
df

   Value  Year
0     10  2012
1     20  2013
2     25  2013
3     30  2014

2.实现row_number的排名

df['sort_num']=df['Year'].rank(ascending=0,method='first').astype(int)--ascending 正序-1,逆序-0
df

   Value  Year  sort_num
0     10  2012         4
1     20  2013         2
2     25  2013         3
3     30  2014         1

3.实现 rank 的排名

df['sort_num']=df['Year'].rank(ascending=0,method='min')

df
   Value  Year  sort_num
0     10  2012         4
1     20  2013         2
2     25  2013         2
3     30  2014         1

4.实现dense_rank的排名

%timeit df['sort_num']=df['Year'].rank(ascending=1,method='dense')--rank 的方法
%timeit df['sort_num'] = df.Year.astype('category').cat.codes + 1--category类型的特性(默认正序)
%timeit df['sort_num'] = pd.factorize(df.Year)[0] + 1--变成因子(默认正序)
3个方法 执行时间上还是会有小差异的,数据量小,不做示范
---------------------
df
   Value  Year  sort_num
0     10  2012       3.0
1     20  2013       2.0
2     25  2013       2.0
3     30  2014       1.0

5.分组排序

这块没有什么好说的,无非就是 groupby 之后 加上各种rank
例子:
df['sort_num']=df.groupby['Year'].Value.rank(ascending=1,method='dense')
----------------------------
df
   Value  Year  sort_num
0     10  2012       1.0
1     20  2013       1.0
2     25  2013       2.0
3     30  2014       1.0
<think>嗯,用户这次的问题是问在SQL中如何实现基于平均值的排序功能,参考了之前讨论的斯皮尔曼相关系数中处理并列排名的方法。看来用户正在处理数据库查询中的排序需求,特别是遇到相同数值时需要平均秩的情况。用户提供的四个引用中,[1]和[2]特别有价值。[1]提到MySQL没有rank()函数但需要处理相同成绩的排名,[2]则对比了SQLServer中RANK()和DENSE_RANK()的区别。这两个引用正好能解决用户的问题。用户可能正在编写学生成绩排名、销售数据统计这类报表,需要专业但易懂的SQL解决方案。从ta特意引用"平均排序"这个表述来看,ta应该已经了解基本排序函数,但需要更精准的并列处理方案。接下来需要重点说明三点:第一是基础排序函数的区别(RANK/DENSE_ROW_NUMBER),第二是具体实现平均秩的SQL语法(尤其是MySQL的变量法),第三要提醒注意事项如性能影响。用户引用[4]提到的归并排序算法虽然相关,但当前问题用窗口函数更直接,暂时不需要深入讨论底层实现。考虑到用户引用过Python示例,在回答时可以补充说明:这种排序逻辑其实和pandasrank(method='average')机制一致,帮助ta建立跨语言理解。不过主要还是要聚焦在SQL实现上。最后要注意的是,用户之前讨论斯皮尔曼系数时很重视数学准确性,所以SQL示例中的计算逻辑必须严格对应平均秩公式$(\text{起始排名}+\text{结束排名})/2$,这在学生成绩并列时特别重要。</think> 在 SQL实现基于平均值的排序(即处理并列排名的平均秩分配),主要通过**窗口函数**实现,以下是具体方法和示例: --- ### **核心方法:窗口函数结合 `RANK()` 或 `DENSE_RANK()`** #### **1. 分配平均秩的基本原理** 当数据存在并列时: - 将并列值的排名替换为平均排名(起始排名 + 结束排名)/2。 - **例如**:值 `[85, 85, 90]`中: - 两个 `85` 占据第 1、2 名 → 平均秩 = (1+2)/2 = **1.5** - `90` 排名为 **3** #### **2. SQL 实现步骤** ##### **(1) 计算并列组的起止排名** ```sql SELECT score, start_rank = RANK() OVER(ORDER BY score DESC), -- 起始排名 end_rank = RANK() OVER(ORDER BY score ASC) -- 结束排名(逆序) FROM Scores; ``` ##### **(2) 计算平均秩并排序** ```sql WITH RankedData AS ( SELECT student_id, score, start_rank = RANK() OVER(ORDER BY score DESC), end_rank = (SELECT COUNT(*) FROM Scores) - RANK() OVER(ORDER BY score ASC) + 1 FROM Scores ) SELECT student_id, score, average_rank = (start_rank + end_rank) * 1.0 / 2 -- 显式转换为浮点数 FROM RankedData ORDER BY average_rank; -- 按平均秩升序排列 ``` --- ### **不同数据库的优化方案** #### **1. MySQL(无原生窗口函数)** 使用会话变量计算: ```sql SET @rank = 0, @prev_score = NULL, @group_size = 1; SELECT student_id, score, average_rank = (start_rank + start_rank + count_in_group - 1) / 2 FROM ( SELECT student_id, score, @rank := IF(score = @prev_score, @rank, @rank + @group_size) AS start_rank, @group_size := IF(score = @prev_score, @group_size + 1, 1), @prev_score := score, (SELECT COUNT(*) FROM Scores s2 WHERE s2.score = s1.score) AS count_in_group FROM Scores s1 ORDER BY score DESC ) tmp ORDER BY average_rank; ``` > **说明**:通过变量跟踪连续相同值的数量 `count_in_group`,动态计算平均秩[^1]。 #### **2. SQL Server / PostgreSQL** 直接使用 `DENSE_RANK()` 和聚合: ```sql SELECT student_id, score, average_rank = AVG(DENSE_RANK() OVER(ORDER BY score DESC)) OVER(PARTITION BY score) -- 按分数分区计算均值 FROM Scores ORDER BY average_rank; ``` --- ### **注意事项** 1. **性能影响**: - 窗口函数在大型数据集上可能较慢,建议对排序字段加索引。 - 避免在 `WHERE` 子句中使用平均秩计算(可先用子查询存储结果)。 2. **浮点数精度**: - 显式转换整数为浮点数(如 `*1.0`),避免整数除法截断。 3. **并列分组逻辑**: - 若需严格分组(如成绩并列时),优先用 `PARTITION BY score` 而非变量法[^2]。 --- ### **应用场景示例** **学生成绩排名**: | student_id | score | average_rank | |------------|-------|--------------| | 101 | 90 | 1.0 | | 102 | 85 | 2.5 | ← 与103并列 | 103 | 85 | 2.5 | | 104 | 80 | 4.0 | > **结果说明**:85分二人共享第2-3名,平均秩为 **2.5**[^2]. --- ### 相关问题: 1. **如何处理并列排名时的百分比(如百分位排名)计算?** ```sql SELECT score, percentile = PERCENT_RANK() OVER(ORDER BY score) FROM Scores; ``` 2. **如何在 MySQL 中模拟 SQL Server 的 `NTILE()` 函数(分桶排序)?** ```sql SET @bucket_size = 4; -- 分为4个桶 SELECT score, NTILE = FLOOR((RANK() OVER(ORDER BY score) - 1) * @bucket_size / COUNT(*) OVER() + 1) FROM Scores; ``` 3. **大数据集下优化平均秩计算的策略是什么?** - **预处理**:先对排序字段去重计算分组范围,再关联回原表[^4]。 - **分片归并**:分批排序后合并结果(类 MapReduce 逻辑)[^4]。 [引用说明] - 示例中的变量法适用于无窗口函数的数据库(如 MySQL 5.x)[^1]。 - 窗口函数实现更简洁但需数据库支持(如 PostgreSQL/SQL Server)[^2]。 - 归并分片策略适用于超大数据集,可参考排序算法优化实现[^4]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值