Mysql區間分組查詢的實現方式

2022-10-14 14:05:22

Mysql區間分組查詢

場景

一張使用者表(user)，有使用者id(id)、餘額(balance)等欄位，要求展示餘額在某個區間內的人數

區間有0-1萬，1-10萬，10-50萬，50-100萬，100萬+，

下面是模擬資料：

使用者id        餘額
1            100    
2            200    
3            3223
4            100001
5            100025
6            512123
7            565656
8            10000001

統計結果應該如下所示：

餘額       人數
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 ;

這樣可以查出來每個範圍對應的人數，但是不盡人意，而且寫的很麻煩…

一番百度之後

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函數實現了效果

interval

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

elt

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

兩個函數結合，再加上group，實現了這種範圍分組的效果

另一種解決辦法

由於使用的是類似mysql語句查詢的一個分析資料庫，它不支援elt函數和interval函數（抄mysql沒有抄全…）

實現這種範圍分組的場景，可以通過建立中間表的形式實現。然後通過使用者表去join

建立如下一個中間表：有下限、上限和區間名三個欄位

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

使用者表就可以通過餘額欄位去join這個表

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

就可以實現範圍分組的效果

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

按區間分組查詢、獲取各區間的總數

資料表如下

需求

tick_count是次數、user_account是使用者標識，user_account可能重複，統計0次，1-3次、4-6次、7-9次、10-12次、13次以上，這幾個區間各有多少個使用者數

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

執行結果

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

Mysql區間分組查詢的實現方式

目錄