hive Group By 语法

最新推荐文章于 2022-07-22 18:37:03 发布

Rainmt_水战

最新推荐文章于 2022-07-22 18:37:03 发布

阅读量1.2k

点赞数

分类专栏： hive

hive 专栏收录该内容

14 篇文章

订阅专栏

本文深入探讨了Hive中GROUP BY语句的用法，包括其基本概念、简单例子、高级特性如多组别插入、Map端聚合等。详细解释了如何根据性别计算去重用户数，以及在同一个查询中进行多个聚合操作的限制。同时，通过实例展示了如何在SQL查询中使用GROUP BY与聚合函数，确保了查询的有效性和准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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看起来像：

如果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