数据分析EPHS(5)-使用Hive SQL计算数列统计值
先来回顾一下数据和对应的统计结果：
这里咱们使用的是iris分类数据集，数据下载地址为：
http://archive.ics.uci.edu/ml/datasets/Iris
下载后转换为xlsx格式的文件，数据如下：
对应的统计结果如下：
接下来先进入实战部分，最后再总结下本地Hive使用过程中的一些坑。
1、使用Hive计算统计值
1.1 最大值、最小值
使用Hive统计最大值直接使用max和min函数就可以：
select
max(feature1) as max_feature1,
max(feature2) as max_feature2,
max(feature3) as max_feature3,
max(feature4) as max_feature4
from
iris;结果输出如下：
select
min(feature1) as min_feature1,
min(feature2) as min_feature2,
min(feature3) as min_feature3,
min(feature4) as min_feature4
from
iris;结果输出如下：
咦，这里很奇怪的一点是，怎么不显示列名呢？这里如果想显示列名的话，需要进行设置：
set hive.cli.print.header=true;此时再执行上面的SQL，就可以啦：
1.2 平均值
平均值的计算使用avg函数：
select
avg(feature1) as avg_feature1,
avg(feature2) as avg_feature2,
avg(feature3) as avg_feature3,
avg(feature4) as avg_feature4
from
iris;输出为：
1.3 样本标准差&总体标准差
Hive中总体标准差的计算有两个函数可以使用，分别是stddev函数和stddev_pop函数：
select
stddev_pop(feature1) as std_feature1,
stddev_pop(feature2) as std_feature2,
stddev(feature3) as std_feature3,
stddev(feature4) as std_feature4
from
iris;输出结果为：
样本标准差使用stddev_samp方法：
select
stddev_samp(feature1) as std_feature1,
stddev_samp(feature2) as std_feature2,
stddev_samp(feature3) as std_feature3,
stddev_samp(feature4) as std_feature4
from
iris;输出结果为：
1.4 中位数
Hive中求中位数和求四分位数使用的是用一个方法，如果你的数据是整数，可以使用percentile方法：
select percentile(feature1,0.5) as median_feature1 from iris;但由于咱的数据不是整数，所以会报如下的错误：
FAILED: NoMatchingMethodException No matching method for class org.apache.hadoop.hive.ql.udf.UDAFPercentile with (double, decimal(1,1)). Possible choices: _FUNC_(bigint, array<double>) _FUNC_(bigint, double)想要处理double类型的数据，可以使用percentile_approx方法：
select percentile_approx(feature1,0.5) as median_feature1 from iris;输出结果为：
稳了么？不稳啊，这个结果和咱们Excel的结果不一样啊，这个函数是等频划分的方法来计算中位数的，什么是等频划分计算的中位数呢，举个简单的例子：一组数据的分布情况如下：
也就是说，按从小到大排序之后，1的累积概率为10%，小于等于2的累积概率是30%，小于等于3的累积概率是60%，如果按我们之前的理解，中位数就是3，那么按照等频划分的话，中位数是恰好累积概率取得50%的时候的那个数，很显然，这个数应该在2和3之间，且离3比较近。
所以，按照距离规则，此时的中位数是(2 * (0.6 - 0.5) + 3 * (0.5 -0.3) ) / (0.6 - 0.3) = 2.67。
而在我们这个数据集中，5.7的累积概率是0.48667、5.8的累积概率是0.5333，所以按照公式，中位数是 (5. 7 * (0.5333 - 0.5) + 5.8 * (0.5 - 0.48667)) / (0.5333 - 0.48667) = 5.72857，与Hive给出的结果一样。
因此，直接使用Hive函数看来是行不通了，得自己对数据进行处理了。
计算中位数也好，计算四分位数也好，无非就是要取得两个位置嘛，假设我们的数据从小到大排，按照1、2、3、.. 、n进行编号，当数量n为奇数时，取编号（n + 1）／2位置的数即可，当n为偶数时，取(int)（n + 1）／2位置和(int)（n + 1）／2 + 1位置的数取平均即可。但二者其实可以统一到一个公式中：
1）假设n = 149 ，(n+1)/2 = 75 ，小数部分为0，那么中位数=75位置的数 * （1 - 0）+ 76位置的数 * （0 - 0） 2）假设n = 150，(n+1)/2 = 75，小数部分为0.5,那么中位数=75位置的数 * （1 - 0.5）+ 76位置的数 * （0.5 - 0）
所以，我们可以把这个过程分解为三个步骤，第一步是给数字进行一个编号，我们使用row_number()函数（该函数的具体用法后续再展开，这里只提供一个简单的例子），第二步是计算(n+1)/2的整数部分和小数部分，第三步就是根据公式计算中位数。
首先使用row_number()给数据进行编号：
select
feature1,row_number() over(order by feature1 asc) as rank
from
iris;输出如下：
接下来是确定中位数的位置，这里我们分别拿到（n + 1）／2的整数部分和小数部分：
select
cast((count(feature1) + 1) / 2 as int) as int_part,
(count(feature1)+1) / 2 % 1 as float_part
from
iris;输出如下：
这里小数部分不为0，意味着我们不仅要拿到rank=75的数，还要拿到rank=76的数，我们最好把其放到一行上，这里使用lead函数，lead函数的作用就是拿到分组排序后，下一个位置或下n个位置的数，咱们在后面的博客中还会细讲，这里也只是抛砖引玉：
select
feature1,
row_number() over(order by feature1 asc) as rank,
lead(feature1,1) over(order by feature1 asc) as next_feature1
from
iris;输出如下：
接下来，join两个表，按公式计算中位数就可以啦：
select
feature1 * (1 - float_part) + next_feature1 * (float_part-0) as median
from
(select
feature1,
row_number() over(order by feature1 asc) as rank,
lead(feature1,1) over(order by feature1 asc) as next_feature1
from
iris
) a
inner join
(
select
cast((count(feature1) + 1) / 2 as int) as int_part,
(count(feature1)+1) / 2 % 1 as float_part
from
iris
) b
on a.rank = b.int_part;输出如下：
1.5 四分位数
先来复习下四分位数的两种解法，n+1方法和n-1方法：
对于n+1方法，如果数据量为n，则四分位数的位置为：
Q1的位置= (n+1) × 0.25 Q2的位置= (n+1) × 0.5 Q3的位置= (n+1) × 0.75
对于n-1方法，如果数据量为n，则四分位数的位置为：
Q1的位置=1+（n-1）x 0.25 Q2的位置=1+（n-1）x 0.5 Q3的位置=1+（n-1）x 0.75
这里的思路和求解中位数是一样的，我们分别实现一下两种方法，首先是n+1方法：
select
feature1 * (1 - float_part) + next_feature1 * (float_part-0) as q1
from
(select
feature1,
row_number() over(order by feature1 asc) as rank,
lead(feature1,1) over(order by feature1 asc) as next_feature1
from
iris
) a
inner join
(
select
cast((count(feature1) + 1) * 0.25 as int) as int_part,
(count(feature1)+1) * 0.25 % 1 as float_part
from
iris
) b
on a.rank = b.int_part;输出为：
接下来是n-1方法：
select
feature1 * (1 - float_part) + next_feature1 * (float_part-0) as q1
from
(select
feature1,
row_number() over(order by feature1 asc) as rank,
lead(feature1,1) over(order by feature1 asc) as next_feature1
from
iris
) a
inner join
(
select
cast((count(feature1) - 1) * 0.25 as int) + 1 as int_part,
(count(feature1) - 1) * 0.25 % 1 as float_part
from
iris
) b
on a.rank = b.int_part;输出为：
2、踩坑总结
这里总结两个本地跑Hive时遇到的两个错误：
2.1 The auxService:mapreduce_shuffle does not exist
当报org.apache.hadoop.yarn.exceptions.InvalidAuxServiceException: The auxService:mapreduce_shuffle does not exist错误时，因为Hive底层执行job是hadoop的MP，如果auxService:mapreduce_shuffle不存在，我们就须要配置。
具体做法是在hadoop的配置文件yarn-site.xml文件中，添加以下内容：
<property>
<name>yarn.nodemanager.aux-services</name>
<value>mapreduce_shuffle</value>
</property>
<property>
<name>yarn.nodemanager.aux-services.mapreduce.shuffle.class</name>
<value>org.apache.hadoop.mapred.ShuffleHandler</value>
</property>2.2 java.lang.OutOfMemoryError: PermGen space
当报错Exception in thread "main" java.lang.OutOfMemoryError: PermGen space时，在hive/conf/hive-env.sh文件中，加入下面的内容，问题得以解决：
export HADOOP_OPTS="$HADOOP_OPTS -XX:PermSize=512M -XX:MaxPermSize=1024M"