SQL-Floor函数代替CASE WHEN实现指定步长区间分组统计

背景

数据分析中会遇到的一个场景是分析支付金额在不同分组区间的订单数，比如支付金额200以内的每5元是一个区间，200-100的以100元为一个区间，计算不同分组区间的订单数有多少。

技术方案

方案1

可能最先想到的是用case when，将所有可能的区间枚举出来，如下

select 
			case when pay_amt < 20 then '[0,20)'
                when pay_amt < 25 then '[20,25)'
                when pay_amt < 30 then '[25,30)'
                when pay_amt < 35 then '[30,35)'
                when pay_amt < 40 then '[35,40)'
                when pay_amt < 45 then '[40,45)'
                when pay_amt < 50 then '[45,50)'
                when pay_amt < 55 then '[50,55)'
                when pay_amt < 60 then '[55,60)'
                when pay_amt < 65 then '[60,65)'
                when pay_amt < 70 then '[65,70)'
                when pay_amt < 75 then '[70,75)'
                when pay_amt < 80 then '[75,80)'
                when pay_amt < 85 then '[80,85)'
                when pay_amt < 90 then '[85,90)'
                when pay_amt < 95 then '[90,95)'
                when pay_amt < 100 then '[95,100)'
                when pay_amt < 105 then '[100,105)'
                when pay_amt < 110 then '[105,110)'
                when pay_amt < 115 then '[110,115)'
                when pay_amt < 120 then '[115,120)'
                when pay_amt < 125 then '[120,125)'
                when pay_amt < 130 then '[125,130)'
                when pay_amt < 135 then '[130,135)'
                when pay_amt < 140 then '[135,140)'
                when pay_amt < 145 then '[140,145)'
                when pay_amt < 150 then '[145,150)'
                when pay_amt < 155 then '[150,155)'
                when pay_amt < 160 then '[155,160)'
                when pay_amt < 165 then '[160,165)'
                when pay_amt < 170 then '[165,170)'
                when pay_amt < 175 then '[170,175)'
                when pay_amt < 180 then '[175,180)'
                when pay_amt < 185 then '[180,185)'
                when pay_amt < 190 then '[185,190)'
                when pay_amt < 195 then '[190,195)'
                when pay_amt < 200 then '[195,200)'
                when pay_amt < 300 then '[200,300)'
                when pay_amt < 400 then '[300,400)'
                when pay_amt < 500 then '[400,500)'
                when pay_amt < 600 then '[500,600)'
                when pay_amt < 700 then '[600,700)'
                when pay_amt < 800 then '[700,800)'
                when pay_amt < 900 then '[800,900)'
                when pay_amt < 1000 then '[900,1000)'
            else '[1000,10000)' 
            end
            ,count(1) as order_cnt
from ord
group by 
			case when pay_amt < 20 then '[0,20)'
                when pay_amt < 25 then '[20,25)'
                when pay_amt < 30 then '[25,30)'
                when pay_amt < 35 then '[30,35)'
                when pay_amt < 40 then '[35,40)'
                when pay_amt < 45 then '[40,45)'
                when pay_amt < 50 then '[45,50)'
                when pay_amt < 55 then '[50,55)'
                when pay_amt < 60 then '[55,60)'
                when pay_amt < 65 then '[60,65)'
                when pay_amt < 70 then '[65,70)'
                when pay_amt < 75 then '[70,75)'
                when pay_amt < 80 then '[75,80)'
                when pay_amt < 85 then '[80,85)'
                when pay_amt < 90 then '[85,90)'
                when pay_amt < 95 then '[90,95)'
                when pay_amt < 100 then '[95,100)'
                when pay_amt < 105 then '[100,105)'
                when pay_amt < 110 then '[105,110)'
                when pay_amt < 115 then '[110,115)'
                when pay_amt < 120 then '[115,120)'
                when pay_amt < 125 then '[120,125)'
                when pay_amt < 130 then '[125,130)'
                when pay_amt < 135 then '[130,135)'
                when pay_amt < 140 then '[135,140)'
                when pay_amt < 145 then '[140,145)'
                when pay_amt < 150 then '[145,150)'
                when pay_amt < 155 then '[150,155)'
                when pay_amt < 160 then '[155,160)'
                when pay_amt < 165 then '[160,165)'
                when pay_amt < 170 then '[165,170)'
                when pay_amt < 175 then '[170,175)'
                when pay_amt < 180 then '[175,180)'
                when pay_amt < 185 then '[180,185)'
                when pay_amt < 190 then '[185,190)'
                when pay_amt < 195 then '[190,195)'
                when pay_amt < 200 then '[195,200)'
                when pay_amt < 300 then '[200,300)'
                when pay_amt < 400 then '[300,400)'
                when pay_amt < 500 then '[400,500)'
                when pay_amt < 600 then '[500,600)'
                when pay_amt < 700 then '[600,700)'
                when pay_amt < 800 then '[700,800)'
                when pay_amt < 900 then '[800,900)'
                when pay_amt < 1000 then '[900,1000)'
            else '[1000,10000)' 
            end
     

但如果枚举值增加到更多呢？
而且，这么多枚举很容易一不注意就写出BUG~

方案二

使用Floor函数。
Floor函数是向下取整，返回不大于number的最大整数值

如果步长为5，我们就可以使用Floor(pay_amt/5)*5，这样得出来的就是区间的左范围，同理步长是几就除以几，这样只需要几行代码就可以实现我们想要的分组区间。

上面的代码就可以替代为：

select 	
	   CASE WHEN pay_amt<20 THEN '[0,20)'
	      	WHEN pay_amt<200 then concat('[',floor(pay_amt/5)*5,',',floor(pay_amt/5)*5+5,')')
	      	WHEN pay_amt<1000 THEN concat('[',floor(pay_amt/100)*100,',',floor(pay_amt/100)*100+100,')')
		ELSE '[1000,10000)'
		END AS amt_range
from ord
group by 
		 CASE WHEN pay_amt<20 THEN '[0,20)'
	          WHEN pay_amt<200 then concat('[',floor(pay_amt/5)*5,',',floor(pay_amt/5)*5+5,')')
	         WHEN pay_amt<1000 THEN concat('[',floor(pay_amt/100)*100,',',floor(pay_amt/100)*100+100,')')
	     ELSE '[1000,10000)'
	     END