Hive高阶聚合函数用法示例

发布时间：2021-12-10 11:11:32 作者：小新
来源：亿速云阅读：303

# Hive高阶聚合函数用法示例

## 一、概述

Hive作为Hadoop生态系统中的数据仓库工具，提供了丰富的聚合函数来处理大规模数据集。除了基础的`COUNT`、`SUM`、`AVG`等函数外，Hive还支持多个高阶聚合函数，能够实现复杂的数据分析需求。本文将详细介绍`GROUPING SETS`、`CUBE`、`ROLLUP`、`窗口函数`等高级聚合用法，并通过实际示例演示其应用场景。

## 二、GROUPING SETS 多维聚合

### 2.1 基本概念
`GROUPING SETS`允许在单个查询中执行多个GROUP BY操作的联合，相当于多个GROUP BY查询的UNION ALL。

```sql
SELECT 
    department, 
    job_role, 
    COUNT(employee_id) as emp_count
FROM employees
GROUP BY GROUPING SETS (
    (department, job_role),
    (department),
    (job_role),
    ()
);

2.2 典型应用场景

同时计算不同维度的聚合指标
生成包含小计和总计的报表

2.3 执行结果说明

department	job_role	emp_count
HR	Manager	5
HR	NULL	20
NULL	Manager	15
NULL	NULL	100

三、CUBE 多维立方体聚合

3.1 基本语法

CUBE会生成GROUP BY子句中所有列的可能组合。

SELECT 
    continent,
    country,
    city,
    SUM(population) as total_pop
FROM locations
GROUP BY CUBE(continent, country, city);

3.2 与GROUPING SETS的关系

CUBE(a,b,c)等价于：

GROUPING SETS (
    (a,b,c), (a,b), (a,c), (b,c),
    (a), (b), (c), ()
)

3.3 性能优化建议

当维度较多时，考虑使用部分CUBE：CUBE((a,b), c)
配合hive.map.aggr=true启用map端聚合

四、ROLLUP 层级滚动聚合

4.1 基本语法

ROLLUP生成分层次的聚合结果，适用于层级数据分析。

SELECT 
    year,
    quarter,
    month,
    SUM(sales) as total_sales
FROM sales_data
GROUP BY ROLLUP(year, quarter, month);

4.2 典型输出结构

2023 | Q1 | Jan | 1000
2023 | Q1 | NULL| 3000  <- Q1小计
2023 | NULL| NULL| 10000 <- 年度总计
NULL | NULL| NULL| 50000 <- 全局总计

4.3 使用场景

时间序列的层级汇总（年→季→月）
地理层级汇总（国家→省→城市）

五、窗口函数聚合

5.1 基本窗口语法

SELECT 
    product_id,
    sale_date,
    daily_sales,
    SUM(daily_sales) OVER(PARTITION BY product_id ORDER BY sale_date 
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM product_sales;

5.2 常用窗口函数

函数	说明
SUM() OVER()	窗口范围内求和
AVG() OVER()	计算移动平均值
RANK()	排名（有间隔）
DENSE_RANK()	密集排名（无间隔）
ROW_NUMBER()	行号

5.3 高级窗口控制

帧定义：RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
动态分区：PARTITION BY product_category, store_id

六、GROUPING函数与标识

6.1 GROUPING函数

用于区分结果行中的NULL是来自原始数据还是聚合生成：

SELECT 
    department,
    job_role,
    COUNT(*) as cnt,
    GROUPING__ID as gid,
    GROUPING(department) as g_dept,
    GROUPING(job_role) as g_job
FROM employees
GROUP BY CUBE(department, job_role);

6.2 结果解析

gid	g_dept	g_job	说明
0	0	0	细粒度数据
1	0	1	按department聚合
3	1	1	全局聚合

七、性能优化实践

7.1 配置参数

SET hive.groupby.skewindata=true;  -- 处理数据倾斜
SET hive.map.aggr.hash.percentmemory=0.5; -- 内存分配

7.2 执行计划分析

通过EXPLN EXTENDED查看聚合阶段的执行计划，重点关注： - 是否触发map端聚合 - 是否有不必要的shuffle操作

八、综合应用案例

8.1 零售业销售分析

-- 多维度销售分析
SELECT 
    store_region,
    product_category,
    payment_type,
    YEAR(sale_date) as year,
    QUARTER(sale_date) as quarter,
    SUM(amount) as total_sales,
    GROUPING__ID as gid
FROM sales
GROUP BY 
    GROUPING SETS (
        (store_region, product_category, payment_type),
        (store_region, year, quarter),
        (product_category, year),
        ()
    );

8.2 用户行为路径分析

-- 使用窗口函数计算用户访问路径
SELECT 
    user_id,
    session_id,
    page_url,
    event_time,
    LEAD(page_url, 1) OVER(PARTITION BY user_id, session_id ORDER BY event_time) as next_page,
    COUNT(*) OVER(PARTITION BY user_id) as total_actions
FROM user_events;

九、总结

Hive的高阶聚合函数为大数据分析提供了强大工具： 1. GROUPING SETS/CUBE/ROLLUP实现多维分析 2. 窗口函数支持复杂的时间序列和路径分析 3. 合理使用可以大幅减少查询代码量 4. 需要注意数据倾斜和性能优化

实际应用中，建议结合业务需求选择最合适的聚合方式，并通过执行计划验证优化效果。 “`

注：本文示例基于Hive 2.x+版本，部分函数在早期版本中可能不支持。在实际使用时请根据具体Hive版本调整语法。