背景
最近有接触到一个统计的需求,要求输出数值各个范围的计数。举个例子,一个班有N个人,要求输出60以下,60-70,70-80,80,100各个分段的人数。像这种范围比较少的情况,可以使用如下的第一种方式即case。在MySQL和Hive中都是支持的。但是如果所要统计的范围很多,或者说不是确定的,如果使用case的方式,可以想见,将非常繁琐。这时候,就可以用到下面的第二种方式,巧妙利用计算来完成对应的统计。
实践
使用预定义case
select
case when cash between 0 and 100 then '[0,1]'
when cash between 101 and 200 then '[1,2]'
when cash between 201 and 300 then '[2,3]'
when cash between 5001 and 10000 then '[50,100]' end as gap
, count(id)
from test
group by case when cash between 0 and 100 then '[0,1]'
when cash between 101 and 200 then '[1,2]'
when cash between 201 and 300 then '[2,3]'
when cash between 5001 and 10000 then '[50,100]' end
可以很明显看到是比较繁琐的。
使用动态计算
select floor(cash/500) * 500 , count(id) from my_account_table group by floor(cash/500) * 500
如上,通过floor
函数,先除以500再乘以500,这样就将数据按照500做了分割,再将这个范围计数使用group by
聚合,完成了实际的统计。 因此得到的数据0,即表示0-499, 1表示500-999,范围可以随实际数据而变化,比较方便简洁。当然,如果需要的范围不是分散,需要定制的,在少量的情况下,case between
可能更适合些。
如上的SQL都可在hive和MySQL执行
总结
以上就是本期的内容,作为一篇操作备忘。