目錄

• 背景
• 業務背景
• 思考
• 效果
• 參考

背景

分位值的概念

分位值：分位值（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以內；性能得到極大提升

參考

到此這篇關于MySQL實現查詢分位值的示例代碼的文章就介紹到這了,更多相關MySQL 查詢分位值內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！