MySQL怎么实现查询分位值-mysql教程-PHP中文网

MySQL怎么实现查询分位值

王林

发布： 2023-05-27 16:36:28

转载

2168 人浏览过

背景

分位值的概念

在统计学和数据分析中，分位数（或四分位数）经常用于描述数据分布的统计特征。一般情况下，分位值分成四个等份，分别为第一分位数（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

登录后复制

This data is within the range of calculating percentile value statistics if cn > orderNum.。为了计算0.1分位值，需要收集每分钟发券数据的前10%。按照面额排序，分钟分组后，每条记录都会标记这条记录排在第几。每分钟发券总量再乘以10%得到cnt，这个值就是计算这一分钟0.1分钟均值的所需要数据量，当cnt

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