MySQL實現(xiàn)查詢分位值的示例代碼

更新時間：2023年04月24日 08:51:41 作者：汪汪隊倒大霉

本文主要介紹了MySQL實現(xiàn)查詢分位值的示例代碼，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

背景

分位值的概念

分位值：分位值（Quartile）是一種用于描述數(shù)據(jù)分布的統(tǒng)計概念，在統(tǒng)計學和數(shù)據(jù)分析中經(jīng)常被用到。一般情況下，分位值分成四個等份，分別為第一分位數(shù)（Q1）、第二分位數(shù)（Q2）（也就是中位數(shù)）、第三分位數(shù)（Q3）以及極差（IQR）。其中，1/4的數(shù)據(jù)小于第一分位數(shù)，1/4的數(shù)據(jù)大于第三分位數(shù)，中間50%的數(shù)據(jù)處于第一分位數(shù)和第三分位數(shù)之間。在統(tǒng)計學中，第一分位數(shù)是指將一組數(shù)據(jù)按照大小順序排列后，處于整個數(shù)列中最靠前的25%位置的數(shù)；第二分位數(shù)是指一組數(shù)據(jù)按大小順序排列后，處于中間位置的那個數(shù)；而第三分位數(shù)是指將一組數(shù)據(jù)按照大小順序排列后，處于整個數(shù)列中最靠后的25%位置的數(shù)。中位數(shù)是第二分位數(shù)。在數(shù)據(jù)分析中，分位值可以幫助我們了解數(shù)據(jù)分布情況以及通過分位值來判斷數(shù)據(jù)是否偏向一側(cè)或者分散程度等問題。當數(shù)據(jù)分配不均勻的時候，分位值可以更準確的表現(xiàn)數(shù)據(jù)的差異。

業(yè)務背景

商家活動發(fā)券面額有一個分布區(qū)間[1, 20]，每發(fā)一張券就都會標記對應券的面額。如何比較準確的控制券的成本，就需要實時對這些券的發(fā)放情況做一個比較準確的了解。對券的發(fā)放量、發(fā)券金額均值、以及發(fā)放金額分位值(了解不同區(qū)間發(fā)放金額均值)進行實時的監(jiān)控，就可以比較清楚的了解券的發(fā)放情況。

目前，業(yè)務梳理出如下指標需要數(shù)據(jù)的同學提供，所有指標均以分鐘為統(tǒng)計粒度：

發(fā)放量：發(fā)券總量

發(fā)券金額均值：發(fā)放總額/發(fā)放總量

發(fā)券金額0.1分位均值：每分鐘發(fā)券金額按照面額大小排序，面額大的在前，面額小的在后，計算每分鐘發(fā)券金額靠前占比10%的那部分券的均值[如，發(fā)券面額排序為：10,9,8,8,6,5,4,4,2,2，那么0.1分位均值就是10]

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

發(fā)放量和發(fā)券金額均值這類指標都可以用MySQL實現(xiàn)，那么如何實現(xiàn)使用MySQL查詢分位值呢？

思考

MySQL實現(xiàn)排序

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

metric_value表示發(fā)券金額，通過以上函數(shù)即可實現(xiàn)按照發(fā)券金額排序，而且是每分鐘的發(fā)券數(shù)據(jù)按照金額排序

MySQL實現(xiàn)topN

SELECT * FROM sales ORDER BY amount DESC LIMIT 10;

很明顯，這種topN方式并不能實現(xiàn)按分鐘排序，取前N%。那我們換個思路，因為我們要先知道總量，才能知道N%有多大，所以我們需要先計算出每分鐘總量。然后再乘以N%，就知道我們需要提取N%有多少數(shù)據(jù)了。

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

然后，我們再把統(tǒng)計結(jié)果乘以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(計算分位值所需要的數(shù)據(jù)量)和orderNum(當前券按面額大小排序所在順序)的大小來獲取得到前N%的數(shù)據(jù)，然后對這部分數(shù)據(jù)做avg處理，就能得到分位值數(shù)據(jù)。

調(diào)整計算邏輯融合到一起就可以得出分位值的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 表示這條數(shù)據(jù)在計算分位值統(tǒng)計范圍內(nèi)。如果要計算0.1分位值，那么需要統(tǒng)計每分鐘的前10%的發(fā)券數(shù)據(jù)。按照面額排序，分鐘分組后，每條記錄都會標記這條記錄排在第幾。每分鐘發(fā)券總量再乘以10%得到cnt，這個值就是計算這一分鐘0.1分鐘均值的所需要數(shù)據(jù)量，當cnt<orderNum時，代表超出10%的限制，不再納入統(tǒng)計0.1分位均值的范圍。這樣就可以實現(xiàn)過濾出計算分位值所需數(shù)據(jù)，然后再通過avg函數(shù)就可以實現(xiàn)計算均值。最終結(jié)果也就是分位值的結(jié)果了。

以上就是我在業(yè)務上遇到計算分位值指標，然后通過MySQL實現(xiàn)計算的實現(xiàn)過程。

說明在使用MySQL實現(xiàn)計算分位值之前，分位值一直都是通過Java程序查詢每分鐘的發(fā)券數(shù)據(jù)，然后排序計算均值實現(xiàn)。通過程序?qū)崿F(xiàn)最大的問題是，如果發(fā)券量比較大，那么要查詢一段時間的分位值指標，這會對程序帶來極大的壓力。事實上，我們在實際的業(yè)務上也確實存在這個問題。每次查詢2個小時的分位值數(shù)據(jù)，就會出現(xiàn)超百萬的數(shù)據(jù)被加載到Java程序中，這對數(shù)據(jù)查詢服務是極為可怕的。為了解決這個問題，我們必須通過MySQL的方式來實現(xiàn)分位值的查詢。