Apache Hive 聚合函数与 OVER 窗口函数：从基础到高级应用

🕗 发布于 2025-01-22 05:30 apache hive hadoop

在大数据时代，Apache Hive 是处理和分析海量数据的强大工具。Hive 提供了丰富的聚合函数和强大的 OVER 窗口函数，能够帮助我们高效地进行数据分析。本文将综合介绍 Hive 的聚合函数和 OVER 窗口函数，结合实际使用场景和代码示例，帮助读者深入理解这些功能，尤其是它们在时间序列分析中的应用。

一、Hive 聚合函数基础

聚合函数是 Hive 中用于对一组数据进行计算并返回单个值的函数。它们在数据分析中非常常见，例如计算总和、平均值、最大值、最小值等。以下是一些常用的聚合函数及其使用场景：
sales 表示例数据：

product_id	ad_spend	amount
101	10	100
102	20	150
101	30	200
103	40	250
102	50	300

1.1 常用聚合函数

函数	描述	示例
`COUNT`	统计行数或满足条件的行数。	`SELECT COUNT(*) AS total FROM users;`
`SUM`	计算数值列的总和。	`SELECT SUM(amount) AS total_sales FROM sales;`
`AVG`	计算数值列的平均值。	`SELECT AVG(amount) AS avg_sales FROM sales;`
`MAX`	返回数值列的最大值。	`SELECT MAX(amount) AS max_sales FROM sales;`
`MIN`	返回数值列的最小值。	`SELECT MIN(amount) AS min_sales FROM sales;`
`COUNT(DISTINCT)`	统计某一列中不同值的数量。	`SELECT COUNT(DISTINCT product_id) AS distinct_products FROM sales;`

1.2 高级聚合函数

除了常用的聚合函数，Hive 还提供了一些高级聚合函数，用于更复杂的分析：

函数	描述	示例	结果
`STDDEV`	计算数值列的标准差，用于衡量数据的离散程度。标准差越大，数据越分散；标准差越小，数据越集中。	`SELECT STDDEV(amount) AS stddev_amount FROM sales;`	假设计算结果为约 86.6（表示 `amount` 列数据相对于平均值的离散程度）
`VARIANCE`	计算数值列的方差，用于衡量数据的离散程度。方差是标准差的平方。	`SELECT VARIANCE(amount) AS variance_amount FROM sales;`	约 7500（`amount` 列数据的方差，为标准差 86.6 的平方）
`CORR`	计算两个数值列之间的相关性。相关性系数的范围在 -1 到 1 之间，-1 表示完全负相关，1 表示完全正相关，0 表示无相关性。	`SELECT CORR(amount, id) AS correlation FROM sales;`	假设计算结果为约 0.8（表示 `amount` 列和 `id` 列之间存在较强的正相关关系）

以 STDDEV 为例，具体计算过程是先计算每个数据点与平均值的差的平方，再求这些平方值的平均值，最后对该平均值取平方根。例如，对于上述 sales 表中的 amount 列，平均值为 200，计算每个 amount 值与 200 的差的平方，再求这些平方值的平均值，最后开方得到标准差。

二、OVER 窗口函数基础

OVER 窗口函数是 Hive 中用于在保留数据明细的同时进行分组、排序和聚合计算的强大工具。它通过定义窗口范围，允许我们对数据进行更灵活的分析。以下是 OVER 窗口函数的基本语法和使用场景：

2.1 基本语法

<aggregate_function> OVER (
    [PARTITION BY <col_name>,...]
    [ORDER BY <col_name>,...]
    [ROWS <window_frame>]
)

<aggregate_function>：聚合函数，如 SUM、AVG、COUNT 等。
PARTITION BY：按指定列分组。
ORDER BY：在分组内按指定列排序。
ROWS：定义窗口范围。

2.2 使用场景

2.2.1 全表聚合

SELECT id, amount, SUM(amount) OVER () AS total_sales
FROM sales;

示例数据：

id	amount
1	100
2	200
3	300

结果展示：

id	amount	total_sales
1	100	600
2	200	600
3	300	600

解释：
- 该查询使用 SUM(amount) OVER () 进行全表聚合，计算 amount 列的总和。
- 对于 sales 表中的每一行，都会计算出 amount 列的总和，结果列命名为 total_sales。

2.2.2 分组聚合

SELECT region, amount, SUM(amount) OVER (PARTITION BY region) AS region_total_sales
FROM sales;

示例数据：

region	amount
North	100
North	200
South	300
South	400

结果展示：

region	amount	region_total_sales
North	100	300
North	200	300
South	300	700
South	400	700

解释：
- 使用 PARTITION BY region 将数据按 region 分组。
- 对于每个组，使用 SUM(amount) 计算该组内 amount 列的总和，结果列命名为 region_total_sales。

2.2.3 排序聚合

SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS cumulative_sales
FROM sales;

示例数据：

date	amount
2024-01-01	100
2024-01-02	200
2024-01-03	300

结果展示：

date	amount	cumulative_sales
2024-01-01	100	100
2024-01-02	200	300
2024-01-03	300	600

解释：
- 首先按 date 列对数据进行排序。
- 然后使用 SUM(amount) OVER (ORDER BY date) 计算累积的 amount 总和，对于每一行，计算从第一行到当前行的 amount 总和，结果列命名为 cumulative_sales。

2.2.4 指定窗口范围

SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_with_previous
FROM sales;

示例数据：

date	amount
2024-01-01	100
2024-01-02	200
2024-01-03	300

结果展示：

date	amount	sum_with_previous
2024-01-01	100	100
2024-01-02	200	300
2024-01-03	300	500

解释：
- 按 date 列排序。
- ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 定义了窗口范围，包括当前行和前一行。
- 计算这个窗口内 amount 列的总和，结果列命名为 sum_with_previous。

三、OVER 窗口函数在时间序列分析中的应用

时间序列分析是大数据分析中的常见场景，OVER 窗口函数在处理时间序列数据时表现出色。以下是一些
常见的应用场景和示例：

3.1 累积聚合

累积聚合用于计算某个时间段内的累积值，例如累积销售额或访问量。

示例：计算每日累积销售额

SELECT sales_date, 
       sales_amount, 
       SUM(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM sales_data
ORDER BY sales_date;

示例数据：

sales_date	sales_amount
2024-01-01	100
2024-01-02	200
2024-01-03	300

结果展示：

sales_date	sales_amount	cumulative_sales
2024-01-01	100	100
2024-01-02	200	300
2024-01-03	300	600

解释：
- 按 sales_date 排序。
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示窗口范围从第一行开始到当前行。
- 计算 sales_amount 的累积总和，结果列 cumulative_sales 显示了从第一行到当前行的 sales_amount 累积值。

3.2 滑动窗口聚合

滑动窗口聚合用于计算某个固定时间段内的聚合值，例如过去7天的平均销售额。

示例：计算过去7天的平均销售额

SELECT sales_date, 
       sales_amount, 
       AVG(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg_sales
FROM sales_data
ORDER BY sales_date;

示例数据：

sales_date	sales_amount
2024-01-01	100
2024-01-02	200
2024-01-03	300
2024-01-04	400
2024-01-05	500
2024-01-06	600
2024-01-07	700

结果展示：

sales_date	sales_amount	rolling_avg_sales
2024-01-01	100	100
2024-01-02	200	150
2024-01-03	300	200
2024-01-04	400	250
2024-01-05	500	300
2024-01-06	600	350
2024-01-07	700	400

解释：
- 按 sales_date 排序。
- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 定义窗口范围为当前行及其前 6 行。
- 计算该窗口内 sales_amount 的平均值，结果列 rolling_avg_sales 显示过去 7 天（包括当前行）的平均销售额。

3.3 时间序列的排名分析

排名分析用于了解某个指标在不同时间点的相对位置。

示例：按日期对销售额进行排名

SELECT sales_date, 
       product_id, 
       sales_amount, 
       RANK() OVER (PARTITION BY sales_date ORDER BY sales_amount DESC) AS sales_rank
FROM sales_data
ORDER BY sales_date, sales_rank;

示例数据：

sales_date	product_id	sales_amount
2024-01-01	1	100
2024-01-01	2	200
2024-01-02	1	300
2024-01-02	2	400

结果展示：

sales_date	product_id	sales_amount	sales_rank
2024-01-01	2	200	1
2024-01-01	1	100	2
2024-01-02	2	400	1
2024-01-02	1	300	2

解释：
- PARTITION BY sales_date 将数据按 sales_date 分组。
- ORDER BY sales_amount DESC 在组内按 sales_amount 降序排序。
- RANK() 函数为每个组内的行分配排名，结果列 sales_rank 显示排名。

3.4 时间序列的比较分析

LAG 和 LEAD 函数可以用来访问当前行的前一行或后一行的数据，这在时间序列分析中非常有用，例如计算日增长率。

示例：计算日销售额增长率

SELECT sales_date, 
       sales_amount, 
       LAG(sales_amount, 1) OVER (ORDER BY sales_date) AS previous_sales,
       (sales_amount - LAG(sales_amount, 1) OVER (ORDER BY sales_date)) / LAG(sales_amount, 1) OVER (ORDER BY sales_date) AS growth_rate
FROM sales_data
ORDER BY sales_date;

示例数据：

sales_date	sales_amount
2024-01-01	100
2024-01-02	200
2024-01-03	300

结果展示：

sales_date	sales_amount	previous_sales	growth_rate
2024-01-01	100	NULL	NULL
2024-01-02	200	100	1.0
2024-01-03	300	200	0.5

解释：
- LAG(sales_amount, 1) OVER (ORDER BY sales_date) 获取前一行的 sales_amount。
- 计算当前行 sales_amount 与前一行 sales_amount 的差值，再除以前一行 sales_amount 得到增长率 growth_rate。

3.5 时间序列的分组聚合

按时间段（如月、季度）对数据进行分组聚合是时间序列分析中的常见需求。

示例：按月计算销售额和平均销售额

SELECT year(sales_date) AS year, 
       month(sales_date) AS month, 
       SUM(sales_amount) AS total_sales, 
       AVG(sales_amount) AS avg_sales
FROM sales_data
GROUP BY year(sales_date), month(sales_date)
ORDER BY year, month;

示例数据：

sales_date	sales_amount
2024-01-01	100
2024-01-15	200
2024-02-01	300
2024-02-20	400

结果展示：

year	month	total_sales	avg_sales
2024	1	300	150
2024	2	700	350

解释：
- 使用 year(sales_date) 和 month(sales_date) 提取年份和月份。
- 按年份和月份分组，使用 SUM(sales_amount) 计算每月的总销售额，AVG(sales_amount) 计算每月的平均销售额。

四、优化技巧

在处理大规模数据时，优化查询性能至关重要。以下是一些优化技巧：

合理使用分区和分桶：通过分区和分桶，可以将数据分割成更小的块，提高查询效率。
避免过多的嵌套查询：尽量将复杂的查询逻辑分解成多个简单的查询，减少计算量。
使用物化视图：对于频繁使用的聚合查询，可以创建物化视图，提前计算结果，提高查询速度
。

五、总结

Apache Hive 的聚合函数和 OVER 窗口函数是大数据分析的核心工具。通过本文的介绍，我们详细探讨了这些功能的使用场景和代码示例，尤其是它们在时间序列分析中的强大能力。无论是简单的统计分析，还是复杂的多维度汇总，Hive 都能帮助我们高效地完成任务。

希望本文能够帮助读者更好地掌握 Hive 的聚合函数和 OVER 窗口函数，提升数据分析能力。如果你对 Hive 有更多问题，或者想了解更多高级用法，欢迎在评论区留言，我们一起探讨！

原文地址：https://blog.csdn.net/qq_40025337/article/details/145284386

免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：grafana新增email告警
下一篇：【云岚到家】-day02-我的地址簿(实战)

构建沉浸式汉语学习环境
系统通过API网关与现有教育平台（如Moodle）无缝集成，支持快速部署。持续学习机制确保内容与时俱进，每月自动更新流行语库与文化热点。的技术三角，构建沉浸式汉语学习环境。
阅读更多2025-01-22
弹框上传预览
【代码】弹框上传预览。
阅读更多2025-01-22
Python 字符串处理深度解析：高级操作技巧、性能优化与实用案例全解
在Python中，字符串（str）是用于表示文本数据的不可变序列类型。其定义方式灵活多样，既可以使用单引号'...'、双引号"..."也可借助三引号'''...'''或"
阅读更多2025-01-22
SELF-RAG: 通过自我反思学习检索、生成和批判——图文并茂总结
SELF-RAG 是一种创新的框架，通过按需检索和自我反思来提高 LLM 的生成质量和事实准确性。实验结果表明，SELF-RAG 在多个任务上均优于现有的 LLM 和 RAG 方法。SELF-RAG
阅读更多2025-01-22
【Nginx系列】Nginx配置超时时间
检查配置是否在正确的上下文中设置。确保没有其他与超时相关的指令冲突。确保每次修改后重载配置，并测试是否正确。检查错误日志了解更多超时信息。尝试这些步骤后，如果仍然无法生效，可以分享更详细的配置或错误日
阅读更多2025-01-22
机器学习-核函数（Kernel Function）
核函数（Kernel Function）是一种数学函数，主要用于将数据映射到一个更高维的特征空间，以便于在这个新特征空间中更容易找到数据的结构或模式。核函数的主要作用是在不需要显式计算高维特征空间的情
阅读更多2025-01-22
Android系统开发（二十）：字体活起来，安卓自定义字体改造指南
在安卓系统中，字体不仅是UI设计的基础，更是品牌和文化的延伸。过去，修改字体需要通过复杂的系统更新，甚至涉及底层操作，风险和成本极高。再到Android 15，可变字体支持和动态实例化技术大大提升了字
阅读更多2025-01-22
【力扣系列题目】不同路径组合总和最大连续1个数打家劫舍{持续更新中...}
【代码】【力扣系列题目】不同路径组合总和最大连续1个数打家劫舍{持续更新中...}
阅读更多2025-01-22
【专题】为2025制定可付诸实践的IT战略规划报告汇总PDF洞察（附原数据表）
在当今瞬息万变的商业环境中，制定有效的IT战略规划对于企业的成功与可持续发展至关重要。本报告深入探讨了制定IT战略规划的关键活动，旨在为企业和决策者提供全面且实用的指导。Gartner的《为2025制
阅读更多2025-01-22
stm32使用MDK5.35时遇到*** TOOLS.INI: TOOLCHAIN NOT INSTALLED
mdk5.35出现***TOOLS.INI:TOOLCHAINNOTINSTALLED的问题！以管理员身份重新打开MDK5.35.0.0，用keygen破解密码，但是一直提示我是没有破解成功。targ
阅读更多2025-01-22