MySQL怎麼實作查詢分位值

背景

分位值的概念

在統計和資料分析中，分位數（或四分位數）常用來描述資料分佈的統計特徵。一般情況下，分位數值分成四個等份，分別為第一分位數（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的方式來實現分位值的查詢。

效果

由程式查詢明細資料計算分位值--> MySQL實作直接查詢分位值

效能從>1min --> 15s以內；性能大幅提升

以上是MySQL怎麼實作查詢分位值的詳細內容。更多資訊請關注PHP中文網其他相關文章！