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

最近有接触到一个统计的需求，要求输出数值各个范围的计数。举个例子，一个班有N个人，要求输出60以下，60-70，70-80，80，100各个分段的人数。像这种范围比较少的情况，可以使用如下的第一种方式即case。在MySQL和Hive中都是支持的。但是如果所要统计的范围很多，或者说不是确定的，如果使用case的方式，可以想见，将非常繁琐。这时候，就可以用到下面的第二种方式，巧妙利用计算来完成对应的统计。

可以很明显看到是比较繁琐的。

如上，通过 floor 函数，先除以500再乘以500，这样就将数据按照500做了分割，再将这个范围计数使用 group by 聚合，完成了实际的统计。 因此得到的数据0，即表示0-499， 1表示500-999，范围可以随实际数据而变化，比较方便简洁。当然，如果需要的范围不是分散，需要定制的，在少量的情况下， case between 可能更适合些。

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

1. Hive SQL 分区间统计问题

时间段统计，可以采用 hour(subscribe_time) 取出小时然后分层。思路：

select

uid

,CASE WHEN HOUR(subscribe_time) BETWEEN 0 AND 1 THEN '00:00:00'

WHEN HOUR(subscribe_time) BETWEEN 2 AND 3 THEN '02:00:00'

...

ELSE '23:00:00' END -- 生成时间分层部分，insert前外层sql加上日期后作为唯一的时间值

,COUNT(*)

FROM yht_fans WHERE subscribe_time>=UNIX_TIMESTAMP(CURDATE()) and subscribe_time<=UNIX_TIMESTAMP(CURDATE())+86400

GROUP BY 1,2

SQL好写，但是要说得明白估计很费劲，关于函数就不说明了，百度查；

思路是，将24小时内分为8个区间，分别以0~7表示，0表示3小时内、1表示第6到第三小时内……

以下SQL是统计区间的数量，请参考：

SELECT

FLOOR((TIMESTAMPDIFF(SECOND,acquisition_time,NOW()) - 1)/10800) AS section,

COUNT(1)

FROM

co_data_history

WHERE

1 = 1

AND acquisition_time >= DATE_SUB(NOW(),INTERVAL 1 DAY)

AND acquisition_time < NOW()

GROUP BY section

---