快捷導(dǎo)航

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

更新時(shí)間：2023年04月24日 08:51:41 作者：汪汪隊(duì)倒大霉

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

背景

分位值的概念

分位值：分位值（Quartile）是一種用于描述數(shù)據(jù)分布的統(tǒng)計(jì)概念，在統(tǒng)計(jì)學(xué)和數(shù)據(jù)分析中經(jīng)常被用到。一般情況下，分位值分成四個(gè)等份，分別為第一分位數(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)計(jì)學(xué)中，第一分位數(shù)是指將一組數(shù)據(jù)按照大小順序排列后，處于整個(gè)數(shù)列中最靠前的25%位置的數(shù)；第二分位數(shù)是指一組數(shù)據(jù)按大小順序排列后，處于中間位置的那個(gè)數(shù)；而第三分位數(shù)是指將一組數(shù)據(jù)按照大小順序排列后，處于整個(gè)數(shù)列中最靠后的25%位置的數(shù)。中位數(shù)是第二分位數(shù)。在數(shù)據(jù)分析中，分位值可以幫助我們了解數(shù)據(jù)分布情況以及通過(guò)分位值來(lái)判斷數(shù)據(jù)是否偏向一側(cè)或者分散程度等問(wèn)題。當(dāng)數(shù)據(jù)分配不均勻的時(shí)候，分位值可以更準(zhǔn)確的表現(xiàn)數(shù)據(jù)的差異。

業(yè)務(wù)背景

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

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

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

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

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

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

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

思考

MySQL實(shí)現(xiàn)排序

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

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

MySQL實(shí)現(xiàn)topN

SELECT * FROM sales ORDER BY amount DESC LIMIT 10;

很明顯，這種topN方式并不能實(shí)現(xiàn)按分鐘排序，取前N%。那我們換個(gè)思路，因?yàn)槲覀円戎揽偭浚拍苤繬%有多大，所以我們需要先計(jì)算出每分鐘總量。然后再乘以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

然后，我們?cè)侔呀y(tǒng)計(jì)結(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

這樣就可以通過(guò)比較cn(計(jì)算分位值所需要的數(shù)據(jù)量)和orderNum(當(dāng)前券按面額大小排序所在順序)的大小來(lái)獲取得到前N%的數(shù)據(jù)，然后對(duì)這部分?jǐn)?shù)據(jù)做avg處理，就能得到分位值數(shù)據(jù)。

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

以上就是我在業(yè)務(wù)上遇到計(jì)算分位值指標(biāo)，然后通過(guò)MySQL實(shí)現(xiàn)計(jì)算的實(shí)現(xiàn)過(guò)程。

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