Docs4dev
Docs
apache-hive / 3.1.1 / reference / LanguageManual_GroupBy.html

LanguageManual GroupBy

按语法分组

groupByClause: GROUP BY groupByExpression (, groupByExpression)*

groupByExpression: expression

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

在groupByExpression中，列是通过名称而不是位置编号指定的。但是，在Hive 0.11.0及更高版本中，按以下方式配置时可以按位置指定列：

Simple Examples

为了计算表中的行数：

SELECT COUNT(*) FROM table2;

请注意，对于不包含HIVE-287的 Hive 版本，您需要使用 COUNT(1)代替 COUNT(*)。

为了按性别计算不同用户的数量，可以编写以下查询：

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

可以同时进行多个聚合，但是任何两个聚合都不能具有不同的 DISTINCT 列。例如，由于 count(DISTINCT)和 sum(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;

请注意，对于不包含HIVE-287的 Hive 版本，您需要使用 COUNT(1)代替 COUNT(*)。

但是，不允许以下查询。我们不允许在同一查询中使用多个 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;

选择语句并按子句分组

使用 group by 子句时，select 语句只能包含 group by 子句中包含的列。当然，您也可以在 select 语句中具有尽可能多的聚合函数(例如count)。
让我们举一个简单的例子

CREATE TABLE t1(a INTEGER, b INTGER);

上表中的查询分组可能类似于：

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

上面的查询之所以有效，是因为 select 子句包含a(按键分组)和聚合函数(sum(b))。

但是，下面的查询“不起作用”有效：

SELECT
   a,
   b
FROM
   t1
GROUP BY
   a;

这是因为 select 子句还有一个附加列(b)，它不包含在 group by 子句中(并且它也不是聚合函数)。这是因为，如果表t1看起来像：

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

由于仅在a上进行分组，因此 Hive 应该为a=100组显示b的哪个值？有人可能会说它应该是第一个值或最低值，但是我们都同意有多种可能的选择。 Hive 通过使 select 子句中未包含在 group by 子句中的列变为无效 SQL(准确地说是 HQL)来消除这种猜测。

Advanced Features

Multi-Group-By Inserts

聚合或简单选择的输出可以进一步发送到多个表中，甚至可以发送到 hadoop dfs 文件(然后可以使用 hdfsUtil 对其进行操作)。例如如果连同性别细分一起，需要按年龄查找唯一页面浏览的细分，则可以通过以下查询来完成：

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;

分组依据的 Map 端聚合

  • hive.map.aggr *控制我们如何进行汇总。默认为 false。如果将其设置为 true，则 Hive 将直接在 map 任务中进行一级聚合。
    通常，这可以提高效率，但是可能需要更多内存才能成功运行。
set hive.map.aggr=true;
  SELECT COUNT(*) FROM table2;

请注意，对于不包含HIVE-287的 Hive 版本，您需要使用 COUNT(1)代替 COUNT(*)。

分组集，多维数据集，汇总和 GROUPING__ID 函数

Version

Hive 版本 0.10.0 中添加了分组集，CUBE 和 ROLLUP 运算符以及 GROUPING__ID 函数。

有关这些聚合运算符的信息，请参见增强的聚合，多维数据集，分组和汇总。

另请参见 JIRA：

Hive 0.11.0 版中的新功能：

  • HIVE-3552 HIVE-3552 有效的方式，用于为大量分组设置键执行多维数据集/汇总/分组设置