MySQL实现查询分位值的示例代码

Summer ·

更新时间:2024-11-01

· 162 次阅读

背景

业务背景

思考

效果

参考

背景

分位值的概念

分位值：分位值（Quartile）是一种用于描述数据分布的统计概念，在统计学和数据分析中经常被用到。一般情况下，分位值分成四个等份，分别为第一分位数（Q1）、第二分位数（Q2）（也就是中位数）、第三分位数（Q3）以及极差（IQR）。其中，1/4的数据小于第一分位数，1/4的数据大于第三分位数，中间50%的数据处于第一分位数和第三分位数之间。在统计学中，第一分位数是指将一组数据按照大小顺序排列后，处于整个数列中最靠前的25%位置的数；第二分位数是指一组数据按大小顺序排列后，处于中间位置的那个数；而第三分位数是指将一组数据按照大小顺序排列后，处于整个数列中最靠后的25%位置的数。中位数是第二分位数。在数据分析中，分位值可以帮助我们了解数据分布情况以及通过分位值来判断数据是否偏向一侧或者分散程度等问题。当数据分配不均匀的时候，分位值可以更准确的表现数据的差异。

业务背景

商家活动发券面额有一个分布区间[1, 20]，每发一张券就都会标记对应券的面额。如何比较准确的控制券的成本，就需要实时对这些券的发放情况做一个比较准确的了解。对券的发放量、发券金额均值、以及发放金额分位值(了解不同区间发放金额均值)进行实时的监控，就可以比较清楚的了解券的发放情况。

目前，业务梳理出如下指标需要数据的同学提供，所有指标均以分钟为统计粒度：

发放量：发券总量

发券金额均值：发放总额/发放总量

发券金额0.1分位均值：每分钟发券金额按照面额大小排序，面额大的在前，面额小的在后，计算每分钟发券金额靠前占比10%的那部分券的均值[如，发券面额排序为：10,9,8,8,6,5,4,4,2,2，那么0.1分位均值就是10]

发券金额0.2分位均值：每分钟发券金额按照面额大小排序，面额大的在前，面额小的在后，计算每分钟发券金额靠前占比20%的那部分券的均值[如，发券面额排序为：10,9,8,8,6,5,4,4,2,2，那么0.2分位均值就是(10+9)/2=9.5]

发放量和发券金额均值这类指标都可以用MySQL实现，那么如何实现使用MySQL查询分位值呢？

思考

MySQL实现排序

row_number() over ( partition by a1.min order by metric_value desc) as orderNum

metric_value表示发券金额，通过以上函数即可实现按照发券金额排序，而且是每分钟的发券数据按照金额排序

MySQL实现topN

SELECT * FROM sales ORDER BY amount DESC LIMIT 10;

很明显，这种topN方式并不能实现按分钟排序，取前N%。那我们换个思路，因为我们要先知道总量，才能知道N%有多大，所以我们需要先计算出每分钟总量。然后再乘以N%，就知道我们需要提取N%有多少数据了。

select hour,min, count(1) as cn 
from table  
where dt=20230423 and hour=11 and min>=0 and min<=30 
group by hour,min

然后，我们再把统计结果乘以N%

select dt,a2.hour,a2.min as min,metric_value, round(cn*N%) as cn, orderNum 
from ( 
select dt,hour,a1.min as min, 
metric_value, row_number() over ( partition by a1.min order by metric_value desc) as orderNum 
from table a1 
where dt=20230423 and hour=11 and min>=0 and min<=30 
) as a2 
inner join ( 
select hour,min , count(1) as cn 
from table c 
where dt=20230423 and hour=11 and min>=0 and min<=30  
group by hour,min ) a3
on a2.hour=a3.hour and a2.min=a3.min

这样就可以通过比较cn(计算分位值所需要的数据量)和orderNum(当前券按面额大小排序所在顺序)的大小来获取得到前N%的数据，然后对这部分数据做avg处理，就能得到分位值数据。

调整计算逻辑融合到一起就可以得出分位值的SQL如下：

select dt,hour,min, round(avg(metric_value)) as metric_value 
from ( 
select dt,a2.hour,a2.min as min,metric_value, round(cn*?) as cn, orderNum 
from ( 
select dt,hour,a1.min as min,
metric_value, row_number() over ( partition by a1.min order by metric_value desc) as orderNum 
from table a1 
where dt=20230423 and hour=11 and min>=0 and min<=30 
) as a2 
inner join ( 
select hour,min, count(1) as cn 
from table a1 
where dt=20230423 and hour=11 and min>=0 and min<=30 
) as a3
on a2.hour=a3.hour and a2.min=a3.min ) as q 
where cn>orderNum 
group by dt,hour,min 
order by dt,hour,min

cn > orderNum 表示这条数据在计算分位值统计范围内。如果要计算0.1分位值，那么需要统计每分钟的前10%的发券数据。按照面额排序，分钟分组后，每条记录都会标记这条记录排在第几。每分钟发券总量再乘以10%得到cnt，这个值就是计算这一分钟0.1分钟均值的所需要数据量，当cnt<orderNum时，代表超出10%的限制，不再纳入统计0.1分位均值的范围。这样就可以实现过滤出计算分位值所需数据，然后再通过avg函数就可以实现计算均值。最终结果也就是分位值的结果了。

以上就是我在业务上遇到计算分位值指标，然后通过MySQL实现计算的实现过程。

说明在使用MySQL实现计算分位值之前，分位值一直都是通过Java程序查询每分钟的发券数据，然后排序计算均值实现。通过程序实现最大的问题是，如果发券量比较大，那么要查询一段时间的分位值指标，这会对程序带来极大的压力。事实上，我们在实际的业务上也确实存在这个问题。每次查询2个小时的分位值数据，就会出现超百万的数据被加载到Java程序中，这对数据查询服务是极为可怕的。为了解决这个问题，我们必须通过MySQL的方式来实现分位值的查询。效果

由程序查询明细数据计算分位值 --> MySQL实现直接查询分位值

性能从>1min --> 15s以内；性能得到极大提升

参考

https://geek-docs.com/sql/sql-examples/sql-to-calculate-the-median.html

到此这篇关于MySQL实现查询分位值的示例代码的文章就介绍到这了,更多相关MySQL 查询分位值内容请搜索软件开发网以前的文章或继续浏览下面的相关文章希望大家以后多多支持软件开发网！

示例 Mysql

1024 个赞