SQL操作实践之范围分区间统计的使用

背景

最近有接触到一个统计的需求,要求输出数值各个范围的计数。举个例子,一个班有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执行

总结

以上就是本期的内容,作为一篇操作备忘。

参考资源

1.Hive SQL 分区间统计问题

推荐阅读更多精彩内容