hive聚合函数多行合并

最新推荐文章于 2024-10-17 21:57:20 发布

长孙刚

最新推荐文章于 2024-10-17 21:57:20 发布

阅读量26

点赞数

文章标签： hive hadoop 数据仓库大数据

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;