hive GroupBy操作(翻译自Hive wiki)

Group By 语法

groupByClause: GROUP BY groupByExpression (, groupByExpression)*

groupByExpression: expression

groupByQuery: SELECT expression (, expression)* FROM src groupByClause?

 

简单例子

为了计算表的行数:

  SELECT COUNT(*) FROM table2;

 

根据性别，计算去重用户数，可以写下面的查询

  INSERT OVERWRITE TABLE pv_gender_sum
  SELECT pv_users.gender, count (DISTINCT pv_users.userid)
  FROM pv_users
  GROUP BY pv_users.gender;

同时可以做多个聚合操作，但是，不能有两个聚合操作有不同的DISTINCT列。下面的语句是合法的：

  INSERT OVERWRITE TABLE pv_gender_agg
  SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid)
  FROM pv_users
  GROUP BY pv_users.gender;

但是，下面的查询是不允许的。不允许在同一个查询，有多个DISTINCT表达式

  INSERT OVERWRITE TABLE pv_gender_agg
  SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip)
  FROM pv_users
  GROUP BY pv_users.gender;

 

Select语句和group by字句

当使用group by字句，select语句，只能包含group by包含的列。当然，在select语句，可以有多个聚合函数(例如count)。看一下例子：

CREATE TABLE t1(a INTEGER, b INTGER);

一个关于上表的group by的查询，可以是这样：

SELECT
   a,
   sum(b)
FROM
   t1
GROUP BY
   a;

上面的查询可以工作，因为select字句包含a(group by表达式)和一个聚合函数(sum(b)).

但是，下面的查询无法工作：

SELECT
   a,
   b
FROM
   t1
GROUP BY
   a;

select语句有多余的列(b),它不包含在group by字句(而且它也不是聚合函数)。这是因为，如果表t1看起来像：

a    b
------
100  1
100  2
100  3

如果group的key是a，那么当group a=100时，b的值在hive中应该显示多少？一种说法，它应该是第一个值或最低值，但我们都同意，有多种可能的选项。hive摒弃了这种猜测无效的SQL（HQL，要准确）：有一列在select子句中，却不包含在GROUP BY子句中。

 

高级特性

Multi-Group-By Inserts

简单查询或者聚合的输出，可以发送到多个表甚至是hadoop的dfs文件(可以使用hdfs工具管理)。例子，如果伴随着性别的细分，按年龄细分，人们需要根据这些，找到每个页面的访问量，使用下面的查询，可以做到这一点：

  FROM pv_users 
  INSERT OVERWRITE TABLE pv_gender_sum
    SELECT pv_users.gender, count(DISTINCT pv_users.userid) 
    GROUP BY pv_users.gender 
  INSERT OVERWRITE DIRECTORY '/user/facebook/tmp/pv_age_sum'
    SELECT pv_users.age, count(DISTINCT pv_users.userid) 
    GROUP BY pv_users.age; 

 

 

GroupBy的Map端聚合

hive.map.aggr控制如何聚合，默认是false，如果设置为true，Hive将会在map端做第一级的聚合。这通常提供更好的效果，但是要求更多的内存才能运行成功。

  set hive.map.aggr=true;
  SELECT COUNT(*) FROM table2;

翻译自 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+GroupBy