前言

今天在群里看到了一个小伙伴提的一个SQL需求：

把一列分为10个区间，按最大值和最小值去分区间，然后统计区间数据量。

emmm，感觉和之前的那篇文章很像，但又有些许不同，而且他这个场景应用更频繁，所以总结一波。

本文主要分为：

一般的分段区间统计；

指定步长的分段区间统计；

动态计算步长的分段区间统计

分段区间

一个区间的包含左边界和右边界，比如[0,10),[10,20),.....,[90,100).

如上，是一组左闭右开的区间，步长gap为10。

一般写法如下，可能我们会得到如下SQL：

select 
 count(case when score >=0 and score <10 then 1 else 0 end) as range_0_10,
 count(case when score >=10 and score <20 then 1 else 0 end) as range_10_20,
 count(case when score >=20 and score <30 then 1 else 0 end) as range_20_30,
 count(case when score >=30 and score <40 then 1 else 0 end) as range_30_40,
 ...
from input

以上SQL从结果的正确性来说没有任何问题，但是从维护的角度来说，问题就大了。

同样这种SQL在每一种分组区间都得去判断一次，难以维护，新增区间或者调整区间步长又需要手动修改，时间成本增加。

解决方案

我们分析一下上面那个SQL，无论是case when score >=0 and score <10 then 1 else 0 end 还是case when score >=10 and score <20 then 1 else 0 end,提取公有的属性其实都是:

case when  score >= ？ and score < ? then 1 else 0 end 

我们这么做其实就是为了给数据去分组，那我们换种思路想一下，分组。。首先想到的是不是group by？

所以我们就想一想，如何将原始数据transform为可以使用group by来解决的数据：

现有数据如下：

hive (default)> select * from math_test;
OK
math_test.score
30
50
101
300
456
768
999
130
350
1130
1350
1131
1150
Time taken: 1.193 seconds, Fetched: 13 row(s)

如果我们按照步长为100去分组，那么 30，50 会分在[0,100）的区间，100，130会分在[100,200)的区间

这里我们可以使用floor函数来实现，步长gap为100所以执行如下SQL结果：

select floor(score/100) as tag,score from math_test;
tag score
0   30
0   50
1   101
3   300
4   456
7   768
9   999
1   130
3   350
11  1130
13  1350
11  1131
11  1150
Time taken: 0.119 seconds, Fetched: 13 row(s)

结果大家已经看到了，通过floor函数和步长gap可以实现将对应区间的数据分在一个组里，打上了相同的tag。因为使用了floor函数向下取整，所以我们的区间是左闭有开，如果你使用的ceil函数向上取整，则是左开右闭。

SQL具体实现如下：

select 
    concat('[',cast(tag as string),'--',cast(tag + 100 as string),')') as score_range,
    cnt 
from (
    select 
        floor(score/100) * 100 as tag,
        count(*) as cnt 
    from math_test 
    group by floor(score/100)
) t

score_range cnt
[0--100)    2
[100--200)  2
[300--400)  2
[400--500)  1
[700--800)  1
[900--1000) 1
[1100--1200)    3
[1300--1400)    1

如何动态的确定步长并统计

以上的代码已经很好的将多行的case when转变为了一行，但是和前言我们小伙伴提出的需求还是有差别，把一列分为10个区间，按最大值和最小值去分区间，然后统计区间数据量。所以这个区间是动态的，步长需要去计算得到，而不是固定的步长。

如何计算得到这个步长呢？很简单：

gap = floor(max(score) - min(score) / 分段数量);

例如在我的数据中：最大的值为1350，最小的为30 ，所以 gap = (1350-30)/10 = 120。

下面我放出完整的SQL，大家品一下：

with v as(select (max(score)- min(score)) / 10 as gap from math_test)  <==== (1) 计算出步长gap
select 
concat('[',cast(tag as string),'--',cast(tag + gap as string),')') as score_range,cnt
from (
    select 
        floor(score/gap) * gap as tag, <==== (2) 计算得到分组标识
        gap, 
        count(*) as cnt 
    from (
        select 
            score,
            v.gap 
        from math_test,v 
    ) t1 group by floor(score/gap),gap <==== （3） 使用笛卡尔将gap关联在math_test表
) t2 

这里使用了with as 语法先计算处理步长gap，并利用笛卡尔将gap关联在了源表，因为with as 的结果只要一条数据，所以这里使用笛卡尔不会造成影响。

至此，我们完成了小伙伴提出的需求。

总结

本文根据小伙伴提出的需求，经过一层一层的分析得到了最终的解决方案，这类查询的应用范围还是很广的，同样也利用了数学的方式优化了SQL，让SQL更加灵活切易维护。

-- by 俩只猴