核心内容摘要
如何用RISC-V DV构建可靠的处理器验证环境
在数据分析场景中我们经常需要计算分组数据中排名前N的记录的合计值。
本文将详细介绍在MySQL中实现这一需求的几种方法并对比它们的性能差异。
基础需求场景假设我们有一个销售数据表sales_data结构如下CREATETABLEsales_data(idINTAUTO_INCREMENTPRIMARYKEY,product_nameVARCHAR(
,categoryVARCHAR(
,sales_amountDECIMAL(12,
,sale_dateDATE);需求计算每个产品类别中销售额前5名的合计销售额
传统解决方案UNION ALL最常见的实现方式是使用UNION ALL组合两个查询-- 查询前5名明细SELECTcategory,product_name,sales_amountFROMsales_dataWHERE(category,sales_amount)IN(SELECTcategory,sales_amountFROMsales_dataWHEREsale_dateBETWEEN
AND
ORDERBYcategory,sales_amountDESCLIMIT
UNIONALL-- 查询前5名合计SELECTcategory,TOP5_TOTALASproduct_name,SUM(sales_amount)ASsales_amountFROMsales_dataWHERE(category,sales_amount)IN(SELECTcategory,sales_amountFROMsales_dataWHEREsale_dateBETWEEN
AND
ORDERBYcategory,sales_amountDESCLIMIT
GROUPBYcategoryORDERBYcategory,sales_amountDESC;问题分析重复扫描表数据两次子查询执行效率低当数据量大时性能急剧下降
优化方案1窗口函数条件聚合MySQL
0MySQL
0及以上版本支持窗口函数可以更高效地实现WITHranked_salesAS(SELECTcategory,product_name,sales_amount,ROW_NUMBER()OVER(PARTITIONBYcategoryORDERBYsales_amountDESC)ASrnFROMsales_dataWHEREsale_dateBETWEEN
AND
-
SELECTcategory,product_name,sales_amount,CASEWHENproduct_nameTOP5_TOTALTHENNULLELSErnENDASrank_positionFROM(-- 前5名明细SELECTcategory,product_name,sales_amount,rnFROMranked_salesWHERErn5UNIONALL-- 前5名合计SELECTcategory,TOP5_TOTALASproduct_name,SUM(sales_amount)ASsales_amount,NULLASrnFROMranked_salesWHERErn5GROUPBYcategory)combinedORDERBYcategory,IFNULL(rn,
,sales_amountDESC;优势只需扫描表一次利用窗口函数高效排序结果集排序更灵活
优化方案2用户变量模拟MySQL
7及以下对于不支持窗口函数的旧版本可以使用用户变量模拟SELECTfinal_data.*FROM(-- 前5名明细SELECTcategory,product_name,sales_amount,rn:IF(current_categorycategory,rn1,
ASrn,current_category:categoryASdummyFROMsales_data,(SELECTrn:0,current_category:)ASvarsWHEREsale_dateBETWEEN
AND
ORDERBYcategory,sales_amountDESCUNIONALL-- 前5名合计SELECTt.category,TOP5_TOTALASproduct_name,SUM(t.sales_amount)ASsales_amount,NULLASrn,NULLASdummyFROM(SELECTcategory,product_name,sales_amount,rn2:IF(current_category2category,rn21,
ASrn2,current_category2:categoryASdummy2FROMsales_data,(SELECTrn2:0,current_category2:)ASvars2WHEREsale_dateBETWEEN
AND
ORDERBYcategory,sales_amountDESC)tWHEREt.rn25GROUPBYt.category)final_dataWHERE(product_name!TOP5_TOTALANDrn
OR(product_nameTOP5_TOTAL)ORDERBYcategory,IFNULL(rn,
,sales_amountDESC;注意用户变量在复杂查询中可能不稳定需要确保变量初始化正确建议在测试环境验证结果
最佳实践方案推荐结合性能与可维护性推荐以下实现方式-- 创建临时表存储排名数据CREATETEMPORARYTABLEtemp_ranked_salesASSELECTcategory,product_name,sales_amount,ROW_NUMBER()OVER(PARTITIONBYcategoryORDERBYsales_amountDESC)ASrnFROMsales_dataWHEREsale_dateBETWEEN
AND
;-- 创建索引加速查询CREATEINDEXidx_temp_rankONtemp_ranked_sales(category,rn);-- 最终查询(-- 前5名明细SELECTcategory,product_name,sales_amount,rnASrank_positionFROMtemp_ranked_salesWHERErn
UNIONALL(-- 前5名合计SELECTcategory,TOP5_TOTALASproduct_name,SUM(sales_amount)ASsales_amount,NULLASrank_positionFROMtemp_ranked_salesWHERErn5GROUPBYcategory)ORDERBYcategory,IFNULL(rank_position,
,sales_amountDESC;-- 清理临时表DROPTEMPORARYTABLEtemp_ranked_sales;性能优化点使用临时表避免重复计算添加适当索引加速查询分开执行明细和合计查询明确的排序控制
性能对比测试在100万条测试数据上对比三种方案方案执行时间扫描行数备注传统UNION ALL
1
5s2,100,000重复扫描表窗口函数方案
8s1,000,000单次扫描临时表方案
5s1,000,000带索引优化
扩展应用场景动态N值将LIMIT 5改为参数化多维度排名在PARTITION BY中添加更多字段百分比排名使用PERCENT_RANK()函数分组内其他计算如平均值、最大值等
八、