Mysql區(qū)間分組查詢的實(shí)現(xiàn)方式

更新時(shí)間：2022年10月11日 15:17:15 作者：upupfeng

這篇文章主要介紹了Mysql區(qū)間分組查詢的實(shí)現(xiàn)方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

Mysql區(qū)間分組查詢

場(chǎng)景

一張用戶表(user)，有用戶id(id)、余額(balance)等字段，要求展示余額在某個(gè)區(qū)間內(nèi)的人數(shù)

? 區(qū)間有0-1萬，1-10萬，10-50萬，50-100萬，100萬+，

下面是模擬數(shù)據(jù)：

用戶id?? ??? ?余額
1?? ??? ??? ?100?? ?
2?? ??? ??? ?200?? ?
3?? ??? ??? ?3223
4?? ??? ??? ?100001
5?? ??? ??? ?100025
6?? ??? ??? ?512123
7?? ??? ??? ?565656
8?? ??? ??? ?10000001

統(tǒng)計(jì)結(jié)果應(yīng)該如下所示：

余額       人數(shù)
0-1萬       1
1-10萬       2
10-50萬       1
50-100萬   2
100萬+       1

第一想法

select?
?? ?count(if(balance between 0 and 10000, id , null ) ) as "0-1萬",
?? ?count(if(balance between 10001 and 100000, id , null ) ) as "1-10萬",
?? ?count(if(balance between 100001 and 500000, id , null ) ) as "10-50萬",
?? ?count(if(balance between 500001 and 1000000, id , null ) ) as "50-100萬",
?? ?count(if(balance > 1000000, id , null ) ) as "100萬+"
from user ;

這樣可以查出來每個(gè)范圍對(duì)應(yīng)的人數(shù)，但是不盡人意，而且寫的很麻煩…

一番百度之后

select interval(balance,0,10000,100000,500000,1000000) as i ,count(*)?
from user group by i;

select elt(interval(balance,0,10000,100000,500000,1000000),"0-1萬","1-10萬","10-50萬","50-100萬","100萬+") as region ,count(*)?
from user group by region;

利用了mysql提供的interval和elt函數(shù)實(shí)現(xiàn)了效果

interval

interval(N,N1,N2,N3) ,比較列表中的N值，該函數(shù)如果N<N1返回0，如果N<N2返回1，如果N<N3返回2 等等。

elt

elt(n,str1,str2,str3,…) 如果n=1，則返回str1,如果n=2，則返回str2,依次類推

兩個(gè)函數(shù)結(jié)合，再加上group，實(shí)現(xiàn)了這種范圍分組的效果

另一種解決辦法

由于使用的是類似mysql語句查詢的一個(gè)分析數(shù)據(jù)庫，它不支持elt函數(shù)和interval函數(shù)（抄mysql沒有抄全…）

實(shí)現(xiàn)這種范圍分組的場(chǎng)景，可以通過創(chuàng)建中間表的形式實(shí)現(xiàn)。然后通過用戶表去join

創(chuàng)建如下一個(gè)中間表：有下限、上限和區(qū)間名三個(gè)字段

lower?? ??? ?upper?? ??? ?region
0?? ??? ??? ?10000?? ??? ?0-1萬
10001?? ??? ?100000?? ??? ?1-10萬
100001?? ??? ?500000?? ??? ?10-50萬
500001?? ??? ?1000000?? ??? ?50-100萬
1000000?? ??? ?2000000000?? ?100萬+

用戶表就可以通過余額字段去join這個(gè)表

select region,count(*)
from user?
left join tmp on user.balance between tmp.lower and tmp.upper
group by region?

就可以實(shí)現(xiàn)范圍分組的效果

相比之前兩種，感覺這個(gè)想法很有趣（同事教的）。

按區(qū)間分組查詢、獲取各區(qū)間的總數(shù)

數(shù)據(jù)表如下

需求

tick_count是次數(shù)、user_account是用戶標(biāo)識(shí)，user_account可能重復(fù)，統(tǒng)計(jì)0次，1-3次、4-6次、7-9次、10-12次、13次以上，這幾個(gè)區(qū)間各有多少個(gè)用戶數(shù)

select case
         when tc.stick_count = 0 then
          '0'
         when tc.stick_count > 0 and tc.stick_count <= 3 then
          '1to3'
         when tc.stick_count > 3 and tc.stick_count<= 6 then
          '4to6'
         when tc.stick_count > 6 and tc.stick_count <= 9 then
          '7to9'
	     when tc.stick_count > 9 and tc.stick_count <= 12 then
          '10to12'
	     when tc.stick_count >  13 then
          'more13'
       end stickLevel,
       COUNT(DISTINCT user_account) total
  from t_stick_detail_hourly tc 
 group by case
         when tc.stick_count = 0 then
          '0'
         when tc.stick_count > 0 and tc.stick_count <= 3 then
          '1to3'
         when tc.stick_count > 3 and tc.stick_count<= 6 then
          '4to6'
         when tc.stick_count > 6 and tc.stick_count <= 9 then
          '7to9'
		 when tc.stick_count > 9 and tc.stick_count <= 12 then
          '10to12'
	     when tc.stick_count > 13 then
          'more13'
       end

運(yùn)行結(jié)果