hive中按照一个字段的区间进行统计

1. question

给定一个hive表tmp，表中有一个字段result(double型)；给定一个区间分类个数m，统计每个区间内的count(*)的数量。

2. answer

在linux终端下，

# step1 设定区间分类个数，此处设置为10m=10# step2 求解字段 result 的最大值和最小值section=`hive -e "select max(result) as max_num,       min(result) as min_numfrom  tmp"`max_num=`echo -e "${section}" | cut -f1`min_num=`echo -e "${section}" | cut -f2`# step3 求解区间的长度len_section=`hive -e "select (${max_num} - ${min_num}) / ${m}from   dual"`# step4 统计每个区间的个数hive -e "select ${min_num} + floor((result - ${min_num}) / ${len_section}) * ${len_section} as section_flag,       count(*) as num  from   tmp"

最后的运行结果

0.0     11970.1     15870.2     14950.3     9810.4     10740.5     9470.6     4240.7     1730.8     1140.9     591.0     2