1. Hive 聚合函数的基本概念

聚合函数用于将多个值汇总为一个值，通常与 GROUP BY 子句一起使用。每个聚合函数处理的方式略有不同：

  • COUNT: 计算行数或非空值的数量。
  • SUM: 对数值列的总和。
  • AVG: 计算数值列的平均值。
  • COLLECT_LIST: 将一组值合并为一个数组，可能包含重复值。
  • COLLECT_SET: 将一组值合并为一个数组，只保留唯一值。

2. 示例数据

我们使用的表和数据示例如下，做一个更详细的步骤：

创建表和插入数据

CREATE TABLE IF NOT EXISTS example_table (
    id INT,
    name STRING
);

-- 插入数据
INSERT INTO example_table VALUES 
(1, 'Alice'),
(1, 'Bob'),
(2, 'Charlie'),
(2, 'David'),
(2, 'Eve'),
(3, 'Alice'),
(3, 'Bob');

3. 聚合函数的使用

3.1 使用 COUNT

我们可以用 COUNT 统计每个 ID 的出现次数：

SELECT 
    id, 
    COUNT(name) AS name_count
FROM 
    example_table
GROUP BY 
    id;

结果：

id

name_count

1

2

2

3

3

2

3.2 使用 SUM

如果我们有一个包含数值列的表格，我们可以计算总和。假设我们添加了一个 score 列：

CREATE TABLE IF NOT EXISTS score_table (
    id INT,
    score INT
);

INSERT INTO score_table VALUES 
(1, 10),
(1, 20),
(2, 30),
(2, 10),
(3, 15);

使用 SUM 计算每个 ID 的总分：

SELECT 
    id, 
    SUM(score) AS total_score
FROM 
    score_table
GROUP BY 
    id;

结果：

id

total_score

1

30

2

40

3

15

4. 组合多个聚合函数

你可以一次使用多个聚合函数：

SELECT 
    id, 
    COUNT(name) AS name_count, 
    COLLECT_LIST(name) AS names
FROM 
    example_table
GROUP BY 
    id;

结果：

id

name_count

names

1

2

["Alice", "Bob"]

2

3

["Charlie", "David", "Eve"]

3

2

["Alice", "Bob"]

5. 使用 HAVING 子句

HAVING 用于过滤聚合后的结果，而 WHERE 用于过滤原始行。

例如，选出 name_count 大于 1 的 ID：

SELECT 
    id, 
    COUNT(name) AS name_count
FROM 
    example_table
GROUP BY 
    id
HAVING 
    name_count > 1;

结果：

id

name_count

1

2

3

2

6. PARTITION BY 和 RANKING 函数

在大数据处理时，有时你可能需要对数据进行分区处理。例如，使用 RANK() 函数来为每个分组添加排名。

SELECT 
    id, 
    name, 
    RANK() OVER (PARTITION BY id ORDER BY name) AS name_rank
FROM 
    example_table;

这会为每个 ID 下的名字按字母顺序排名。

id

name

name_rank

1

Alice

1

1

Bob

2

2

Charlie

1

2

David

2

2

Eve

3

3

Alice

1

3

Bob

2

7. 使用窗口函数

窗口函数与聚合函数相似，但保留了每一行的详细信息。例如，计算每个 ID 的平均分：

SELECT 
    id, 
    name, 
    AVG(score) OVER (PARTITION BY id) AS avg_score
FROM 
    score_table;

8. 代码示例的完整性

把所有内容整合成一个完整的执行脚本：

-- 创建表
CREATE TABLE IF NOT EXISTS example_table (
    id INT,
    name STRING
);

CREATE TABLE IF NOT EXISTS score_table (
    id INT,
    score INT
);

-- 插入数据
INSERT INTO example_table VALUES 
(1, 'Alice'),
(1, 'Bob'),
(2, 'Charlie'),
(2, 'David'),
(2, 'Eve'),
(3, 'Alice'),
(3, 'Bob');

INSERT INTO score_table VALUES 
(1, 10),
(1, 20),
(2, 30),
(2, 10),
(3, 15);

-- 聚合查询示例
SELECT 
    id, 
    COUNT(name) AS name_count, 
    COLLECT_LIST(name) AS names
FROM 
    example_table
GROUP BY 
    id;

9. 总结

通过以上细化的内容，你可以掌握如何在 Hive 中使用聚合函数进行复杂的数据分析