内容简介：本文详细介绍Hive高阶查询与窗口函数的核心概念与生产实战应用。风哥教程参考Hive官方文档Windowing Functions、Built-in Functions等内容，涵盖窗口函数语法、排名函数、聚合函数、分析函数等核心知识点，结合生产环境实际案例，帮助读者掌握Hive窗口函数在企业数据仓库中的实战应用技巧。
目录大纲
Part01-基础概念与理论知识
1.1 Hive窗口函数概述
1.2 窗口函数语法结构
1.3 窗口定义与分区
Part02-生产环境规划与建议
2.1 窗口函数使用场景分析
2.2 性能优化策略
2.3 资源配置建议
Part03-生产环境项目实施方案
3.1 排名函数实战
3.2 聚合窗口函数实战
3.3 分析函数实战
Part04-生产案例与实战讲解
4.1 销售数据排名分析案例
4.2 用户行为漏斗分析案例
4.3 同环比计算案例
Part05-风哥经验总结与分享
5.1 窗口函数最佳实践
5.2 常见问题与解决方案
5.3 生产环境注意事项
Part01-基础概念与理论知识
1.1 Hive窗口函数概述
窗口函数是Hive中强大的分析函数，能够在不减少结果行数的情况下进行聚合计算和排名分析。窗口函数与普通聚合函数的区别在于，聚合函数会将多行合并为一行，而窗口函数会保留原始行数，同时计算聚合结果。更多视频教程www.fgedu.net.cn
Hive窗口函数主要分为以下几类：
排名函数：ROW_NUMBER、RANK、DENSE_RANK、NTILE
聚合函数：SUM、AVG、COUNT、MAX、MIN
分析函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE
分布函数：PERCENT_RANK、CUME_DIST
1.2 窗口函数语法结构
窗口函数的基本语法结构如下：
[PARTITION BY 分区列]
[ORDER BY 排序列 [ASC|DESC]]
[窗口子句]
) AS 别名
语法说明：
PARTITION BY：将数据按指定列分组，窗口函数在每个分组内独立计算。学习交流加群风哥微信: itpux-com
ORDER BY：指定窗口内数据的排序方式，影响窗口函数的计算顺序。
窗口子句：定义窗口的范围，包括ROWS和BETWEEN子句。
1.3 窗口定义与分区
窗口子句用于精确定义窗口范围，常用语法：
— 起始位置和结束位置可以是：
— UNBOUNDED PRECEDING：窗口起始位置
— N PRECEDING：当前行前N行
— CURRENT ROW：当前行
— N FOLLOWING：当前行后N行
— UNBOUNDED FOLLOWING：窗口结束位置
常用窗口定义示例：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
— 当前行及前3行
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
— 当前行前后各一行
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
Part02-生产环境规划与建议
2.1 窗口函数使用场景分析
窗口函数在生产环境中有广泛的应用场景，风哥提示：合理使用窗口函数可以大幅简化复杂SQL逻辑。
典型应用场景：
1. 数据排名：销售排名、成绩排名、热门商品排名
2. 累计计算：累计销售额、累计用户数、移动平均
3. 同比环比：与上月对比、与去年同期对比
4. 漏斗分析：用户转化漏斗、行为路径分析
5. 数据补全：缺失数据填充、前后数据对比
2.2 性能优化策略
窗口函数的性能优化需要关注以下几个方面：
分区优化：PARTITION BY列应选择区分度适中的字段，避免数据倾斜。更多学习教程公众号风哥教程itpux_com
排序优化：ORDER BY列应尽量利用已有索引或排序数据。
窗口范围优化：合理设置窗口范围，避免全量数据扫描。
2.3 资源配置建议
生产环境运行窗口函数时的资源配置：
set mapreduce.map.memory.mb=4096;
— 设置Reduce内存
set mapreduce.reduce.memory.mb=8192;
— 设置并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=16;
— 启用向量化执行
set hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.reduce.enabled=true;
Part03-生产环境项目实施方案
3.1 排名函数实战
排名函数是窗口函数中最常用的一类，用于对数据进行排名计算。from bigdata视频:www.itpux.com
3.1.1 ROW_NUMBER函数
ROW_NUMBER为每一行分配一个唯一的序号，从1开始递增。
CREATE TABLE fgedu_sales (
sale_id INT,
product_name STRING,
category STRING,
sale_date DATE,
amount DECIMAL(10,2)
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
— 加载测试数据
LOAD DATA LOCAL INPATH ‘/bigdata/data/fgedu_sales.csv’ OVERWRITE INTO TABLE fgedu_sales;
— 按销售金额排名
SELECT
product_name,
category,
amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM fgedu_sales
LIMIT 10;
| product_name | category | amount | rn |
+—————-+———-+———+—–+
| iPhone 15 Pro | 手机 | 8999.00 | 1 |
| MacBook Pro | 电脑 | 15999.00| 2 |
| iPad Pro | 平板 | 6999.00 | 3 |
| Apple Watch | 手表 | 3299.00 | 4 |
| AirPods Pro | 耳机 | 1899.00 | 5 |
| iPhone 15 | 手机 | 5999.00 | 6 |
| MacBook Air | 电脑 | 8999.00 | 7 |
| iPad Air | 平板 | 4799.00 | 8 |
| Apple Watch SE | 手表 | 2199.00 | 9 |
| AirPods 3 | 耳机 | 1399.00 | 10 |
+—————-+———-+———+—–+
3.1.2 RANK与DENSE_RANK函数
RANK函数在遇到相同值时会跳过后续排名，DENSE_RANK则不会跳过。
SELECT
product_name,
category,
amount,
RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS rank_val,
DENSE_RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS dense_rank_val,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS row_num
FROM fgedu_sales
WHERE category = ‘手机’;
| product_name | category | amount | rank_val | dense_rank_val| row_num |
+—————-+———-+———+———-+—————+———-+
| iPhone 15 Pro | 手机 | 8999.00 | 1 | 1 | 1 |
| iPhone 15 Pro | 手机 | 8999.00 | 1 | 1 | 2 |
| iPhone 15 | 手机 | 5999.00 | 3 | 2 | 3 |
| iPhone 14 | 手机 | 4999.00 | 4 | 3 | 4 |
| iPhone SE | 手机 | 3499.00 | 5 | 4 | 5 |
+—————-+———-+———+———-+—————+———-+
3.2 聚合窗口函数实战
聚合窗口函数可以在窗口范围内进行聚合计算，学习交流加群风哥QQ113257174。
3.2.1 累计计算
SELECT
sale_date,
product_name,
amount,
SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM fgedu_sales
WHERE product_name = ‘iPhone 15 Pro’
ORDER BY sale_date;
| sale_date | product_name | amount | cumulative_amount |
+————+—————-+———+——————-+
| 2024-01-01 | iPhone 15 Pro | 8999.00 | 8999.00 |
| 2024-01-02 | iPhone 15 Pro | 8999.00 | 17998.00 |
| 2024-01-03 | iPhone 15 Pro | 8999.00 | 26997.00 |
| 2024-01-04 | iPhone 15 Pro | 8999.00 | 35996.00 |
| 2024-01-05 | iPhone 15 Pro | 8999.00 | 44995.00 |
+————+—————-+———+——————-+
3.2.2 移动平均计算
SELECT
sale_date,
amount,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM fgedu_sales
WHERE product_name = ‘iPhone 15 Pro’
ORDER BY sale_date;
| sale_date | amount | moving_avg |
+————+———+————+
| 2024-01-01 | 8999.00 | 8999.00 |
| 2024-01-02 | 8999.00 | 8999.00 |
| 2024-01-03 | 8999.00 | 8999.00 |
| 2024-01-04 | 7999.00 | 8665.67 |
| 2024-01-05 | 8999.00 | 8665.67 |
| 2024-01-06 | 9999.00 | 8999.00 |
+————+———+————+
3.3 分析函数实战
分析函数用于访问前后行的数据，常用于数据对比分析。风哥提示：LAG和LEAD函数是处理时间序列数据的利器。
3.3.1 LAG与LEAD函数
SELECT
sale_date,
amount AS today_amount,
LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS yesterday_amount,
LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS tomorrow_amount,
ROUND((amount – LAG(amount, 1, 0) OVER (ORDER BY sale_date)) / LAG(amount, 1, 0) OVER (ORDER BY sale_date) * 100, 2) AS growth_rate
FROM (
SELECT sale_date, SUM(amount) AS amount
FROM fgedu_sales
GROUP BY sale_date
) t
ORDER BY sale_date;
| sale_date | today_amount | yesterday_amount | tomorrow_amount | growth_rate |
+————+————–+——————+—————–+————-+
| 2024-01-01 | 125000.00 | 0.00 | 138500.00 | NULL |
| 2024-01-02 | 138500.00 | 125000.00 | 142300.00 | 10.80 |
| 2024-01-03 | 142300.00 | 138500.00 | 156800.00 | 2.74 |
| 2024-01-04 | 156800.00 | 142300.00 | 148900.00 | 10.19 |
| 2024-01-05 | 148900.00 | 156800.00 | 162500.00 | -5.04 |
+————+————–+——————+—————–+————-+
3.3.2 FIRST_VALUE与LAST_VALUE
SELECT
category,
sale_date,
amount,
FIRST_VALUE(amount) OVER (PARTITION BY category ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_amount,
LAST_VALUE(amount) OVER (PARTITION BY category ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount
FROM fgedu_sales
WHERE sale_date BETWEEN ‘2024-01-01’ AND ‘2024-01-05’
ORDER BY category, sale_date;
| category | sale_date | amount | first_amount | last_amount |
+———-+————+———+————–+————-+
| 手机 | 2024-01-01 | 8999.00 | 8999.00 | 5999.00 |
| 手机 | 2024-01-02 | 8999.00 | 8999.00 | 5999.00 |
| 手机 | 2024-01-03 | 5999.00 | 8999.00 | 5999.00 |
| 电脑 | 2024-01-01 | 15999.00| 15999.00 | 8999.00 |
| 电脑 | 2024-01-02 | 12999.00| 15999.00 | 8999.00 |
| 电脑 | 2024-01-03 | 8999.00 | 15999.00 | 8999.00 |
+———-+————+———+————–+————-+
Part04-生产案例与实战讲解
4.1 销售数据排名分析案例
本案例实现商品销售的多维度排名分析，更多视频教程www.fgedu.net.cn。
# sales_ranking_analysis.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
echo “=== 销售排名分析 ===”
echo “Date: $(date)”
hive -e “
— 创建销售排名分析表
CREATE TABLE IF NOT EXISTS fgedu_sales_ranking AS
SELECT
product_name,
category,
sale_month,
total_amount,
sale_count,
— 全局排名
ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS global_rank,
— 类别内排名
RANK() OVER (PARTITION BY category ORDER BY total_amount DESC) AS category_rank,
— 月度排名
DENSE_RANK() OVER (PARTITION BY sale_month ORDER BY total_amount DESC) AS month_rank,
— 类别内销售额占比
ROUND(total_amount / SUM(total_amount) OVER (PARTITION BY category) * 100, 2) AS category_pct,
— 累计销售额
SUM(total_amount) OVER (ORDER BY total_amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM (
SELECT
product_name,
category,
DATE_FORMAT(sale_date, ‘yyyy-MM’) AS sale_month,
SUM(amount) AS total_amount,
COUNT(*) AS sale_count
FROM fgedu_sales
GROUP BY product_name, category, DATE_FORMAT(sale_date, ‘yyyy-MM’)
) t;
— 查询TOP10商品
SELECT * FROM fgedu_sales_ranking WHERE global_rank <= 10;
“
echo “=== 分析完成 ===”
Date: Fri Jan 19 15:30:00 CST 2024
OK
Time taken: 45.234 seconds
+—————-+———-+————+————–+———–+————-+————–+————+————-+——————-+
| product_name | category | sale_month | total_amount | sale_count| global_rank | category_rank| month_rank | category_pct| cumulative_amount |
+—————-+———-+————+————–+———–+————-+————–+————+————-+——————-+
| iPhone 15 Pro | 手机 | 2024-01 | 269970.00 | 30 | 1 | 1 | 1 | 35.67 | 269970.00 |
| MacBook Pro | 电脑 | 2024-01 | 255984.00 | 16 | 2 | 1 | 2 | 42.15 | 525954.00 |
| iPad Pro | 平板 | 2024-01 | 209970.00 | 30 | 3 | 1 | 3 | 38.56 | 735924.00 |
| iPhone 15 | 手机 | 2024-01 | 179970.00 | 30 | 4 | 2 | 4 | 23.78 | 915894.00 |
| MacBook Air | 电脑 | 2024-01 | 143984.00 | 16 | 5 | 2 | 5 | 23.71 | 1059878.00 |
| Apple Watch | 手表 | 2024-01 | 98970.00 | 30 | 6 | 1 | 6 | 28.45 | 1158848.00 |
| iPad Air | 平板 | 2024-01 | 95980.00 | 20 | 7 | 2 | 7 | 17.63 | 1254828.00 |
| AirPods Pro | 耳机 | 2024-01 | 75960.00 | 40 | 8 | 1 | 8 | 31.25 | 1330788.00 |
| iPhone 14 | 手机 | 2024-01 | 74985.00 | 15 | 9 | 3 | 9 | 9.91 | 1405773.00 |
| Apple Watch SE | 手表 | 2024-01 | 65970.00 | 30 | 10 | 2 | 10 | 18.97 | 1471743.00 |
+—————-+———-+————+————–+———–+————-+————–+————+————-+——————-+
=== 分析完成 ===
4.2 用户行为漏斗分析案例
用户行为漏斗分析是电商运营的核心分析场景。学习交流加群风哥微信: itpux-com
CREATE TABLE fgedu_user_behavior (
user_id STRING,
event_type STRING,
event_time TIMESTAMP,
page_id STRING,
product_id STRING
) PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
— 漏斗分析SQL
SELECT
dt,
view_users,
cart_users,
order_users,
pay_users,
ROUND(cart_users / view_users * 100, 2) AS view_to_cart_rate,
ROUND(order_users / cart_users * 100, 2) AS cart_to_order_rate,
ROUND(pay_users / order_users * 100, 2) AS order_to_pay_rate,
ROUND(pay_users / view_users * 100, 2) AS total_conversion_rate
FROM (
SELECT
dt,
COUNT(DISTINCT CASE WHEN event_type = ‘view’ THEN user_id END) AS view_users,
COUNT(DISTINCT CASE WHEN event_type = ‘cart’ THEN user_id END) AS cart_users,
COUNT(DISTINCT CASE WHEN event_type = ‘order’ THEN user_id END) AS order_users,
COUNT(DISTINCT CASE WHEN event_type = ‘pay’ THEN user_id END) AS pay_users
FROM fgedu_user_behavior
GROUP BY dt
) t;
| dt | view_users | cart_users| order_users | pay_users | view_to_cart_rate | cart_to_order_rate | order_to_pay_rate | total_conversion_rate|
+————+————+———–+————-+———–+——————-+——————–+——————–+———————-+
| 2024-01-15 | 125000 | 45000 | 28000 | 22000 | 36.00 | 62.22 | 78.57 | 17.60 |
| 2024-01-16 | 138500 | 52000 | 32000 | 25000 | 37.55 | 61.54 | 78.13 | 18.05 |
| 2024-01-17 | 142300 | 55000 | 35000 | 28000 | 38.65 | 63.64 | 80.00 | 19.68 |
+————+————+———–+————-+———–+——————-+——————–+——————–+———————-+
4.3 同环比计算案例
同环比计算是数据分析的核心需求，通过窗口函数可以高效实现。更多学习教程公众号风哥教程itpux_com
CREATE TABLE fgedu_sales_summary AS
SELECT
DATE_FORMAT(sale_date, ‘yyyy-MM’) AS sale_month,
category,
SUM(amount) AS total_amount
FROM fgedu_sales
GROUP BY DATE_FORMAT(sale_date, ‘yyyy-MM’), category;
— 同比环比计算
SELECT
sale_month,
category,
total_amount,
— 环比
LAG(total_amount, 1) OVER (PARTITION BY category ORDER BY sale_month) AS prev_month_amount,
ROUND((total_amount – LAG(total_amount, 1) OVER (PARTITION BY category ORDER BY sale_month)) /
LAG(total_amount, 1) OVER (PARTITION BY category ORDER BY sale_month) * 100, 2) AS mom_growth,
— 同比
LAG(total_amount, 12) OVER (PARTITION BY category ORDER BY sale_month) AS prev_year_amount,
ROUND((total_amount – LAG(total_amount, 12) OVER (PARTITION BY category ORDER BY sale_month)) /
LAG(total_amount, 12) OVER (PARTITION BY category ORDER BY sale_month) * 100, 2) AS yoy_growth
FROM fgedu_sales_summary
ORDER BY category, sale_month;
| sale_month | category | total_amount | prev_month_amount | mom_growth | prev_year_amount | yoy_growth |
+————+———-+————–+——————-+————+——————-+————+
| 2024-01 | 手机 | 756940.00 | NULL | NULL | 625000.00 | 21.11 |
| 2024-02 | 手机 | 685320.00 | 756940.00 | -9.46 | 580000.00 | 18.16 |
| 2024-03 | 手机 | 823650.00 | 685320.00 | 20.19 | 695000.00 | 18.51 |
| 2024-01 | 电脑 | 607200.00 | NULL | NULL | 520000.00 | 16.77 |
| 2024-02 | 电脑 | 552800.00 | 607200.00 | -8.97 | 485000.00 | 13.98 |
| 2024-03 | 电脑 | 678900.00 | 552800.00 | 22.80 | 560000.00 | 21.23 |
+————+———-+————–+——————-+————+——————-+————+
Part05-风哥经验总结与分享
5.1 窗口函数最佳实践
风哥在生产环境中使用窗口函数的经验总结：
1. 合理使用PARTITION BY：分区列选择要适中，避免数据倾斜。from bigdata视频:www.itpux.com
2. 控制窗口范围：对于大数据量表，尽量使用LIMIT限制窗口范围。
3. 避免重复计算：相同窗口定义可以提取为子查询，减少重复计算。
4. 合理使用缓存：对于频繁使用的中间结果，可以创建临时表缓存。
WITH sales_base AS (
SELECT
product_name,
category,
amount,
SUM(amount) OVER (PARTITION BY category ORDER BY amount DESC) AS category_sum
FROM fgedu_sales
)
SELECT
product_name,
category,
amount,
category_sum,
ROUND(amount / category_sum * 100, 2) AS pct
FROM sales_base;
5.2 常见问题与解决方案
问题1：窗口函数执行慢
解决方案：检查分区列是否存在数据倾斜，适当调整并行度和内存配置。
SELECT category, COUNT(*) AS cnt
FROM fgedu_sales
GROUP BY category
ORDER BY cnt DESC;
— 调整并行度
set hive.exec.reducers.bytes.per.reducer=256000000;
问题2：LAST_VALUE结果不符合预期
解决方案：LAST_VALUE默认窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，需要显式指定完整窗口范围。学习交流加群风哥QQ113257174
LAST_VALUE(amount) OVER (ORDER BY sale_date) AS last_val
— 正确写法
LAST_VALUE(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val
5.3 生产环境注意事项
1. 内存管理：窗口函数需要较多内存，建议设置合理的Map和Reduce内存。
2. 数据倾斜：对于倾斜数据，可以使用hive.groupby.skewindata参数优化。
3. 执行计划分析：使用EXPLAIN分析执行计划，优化窗口函数执行效率。
EXPLAIN EXTENDED
SELECT
product_name,
amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM fgedu_sales;
— 开启数据倾斜优化
set hive.groupby.skewindata=true;
set hive.optimize.skewjoin=true;
风哥提示：窗口函数是Hive数据分析的利器，掌握好窗口函数可以大幅提升SQL开发效率。在生产环境中，要注意合理设置窗口范围和分区策略，避免性能问题。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
