Hive group by详解

一、 group by的概念

group by是SQL中非常重要的一个查询方式，它可以将表中的数据根据指定的列进行分组，然后对每个组进行聚合操作，例如count、sum、avg、max、min等聚合操作，最后返回每个分组的结果。它可以帮助我们方便地对数据进行分类、汇总、筛选等操作，从而更好地了解数据的分布、趋势等信息。

二、 Hive语法中的group by

Hive是一个基于Hadoop的数据仓库工具，它支持类SQL语言，允许我们使用SQL语句查询Hadoop上存储的大数据，其中也包含了group by的操作。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

三、 Hive group by操作的实战应用

1、 示例数据

为了更好地说明group by的使用，我们先导入一个简单的示例数据，包含学生信息和成绩信息。

CREATE TABLE student_scores (
    id INT,
    name STRING,
    age INT,
    gender STRING,
    subject STRING,
    score INT
);

INSERT INTO student_scores VALUES (1,'Tom',18,'male','math',90);
INSERT INTO student_scores VALUES (2,'Jack',17,'male','math',80);
INSERT INTO student_scores VALUES (3,'Lucy',18,'female','math',95);
INSERT INTO student_scores VALUES (4,'Lily',17,'female','math',85);
INSERT INTO student_scores VALUES (5,'Tom',18,'male','english',70);
INSERT INTO student_scores VALUES (6,'Jack',17,'male','english',82);
INSERT INTO student_scores VALUES (7,'Lucy',18,'female','english',90);
INSERT INTO student_scores VALUES (8,'Lily',17,'female','english',88);

2、 基本的group by查询操作

我们首先来看一个基本的group by查询，它可以根据学生姓名分组，计算每个学生的平均成绩。

SELECT name, AVG(score) as avg_score
FROM student_scores
GROUP BY name;

执行这个查询后，得到的结果如下：

name    avg_score
------------------
Jack    81.0
Lily    86.5
Lucy    92.5
Tom     80.0

3、 group by的多列查询

如果我们需要同时按照多个列进行分组，只需要在group by后面加上多个列名即可。

SELECT subject, name, AVG(score) as avg_score
FROM student_scores
GROUP BY subject, name;

执行这个查询后，得到的结果如下：

subject name   avg_score
------------------------
english Jack   82.0
english Lily   88.0
english Lucy   90.0
english Tom    70.0
math    Jack   80.0
math    Lily   85.0
math    Lucy   95.0
math    Tom    90.0

4、 group by的过滤操作

我们还可以在group by操作之前加上where语句进行过滤，例如只查询数学相关的信息。

SELECT name, AVG(score) as avg_score
FROM student_scores
WHERE subject='math'
GROUP BY name;

执行这个查询后，得到的结果如下：

name    avg_score
------------------
Jack    80.0
Lily    85.0
Lucy    95.0
Tom     90.0

5、 group by的聚合操作

除了平均值之外，还可以使用其他的聚合操作，例如sum、count、min、max等。

SELECT name, COUNT(*) as count, MIN(score) as min_score, MAX(score) as max_score
FROM student_scores
GROUP BY name;

执行这个查询后，得到的结果如下：

name    count   min_score   max_score
--------------------------------------
Jack    2       80          82
Lily    2       85          88
Lucy    2       90          95
Tom     2       70          90

结论

Hive group by操作是数据分析和统计的基础之一，在实际的数据处理中非常常见。我们可以利用它对数据进行分类、汇总、筛选等操作，从而更好地了解数据的特点和趋势，为后续的数据分析和挖掘提供基础。