Hive学习笔记

前言

Apache Hive 是 Hadoop 生态系统中不可或缺的数据仓库工具，它提供了丰富的内置函数（Built-in Functions），让开发者能够高效地处理和分析大规模数据。熟练掌握 Hive 内置函数，不仅能大幅提升 SQL 编写效率，也是大数据面试中的高频考点。

本文将系统梳理 Hive 中常用的内置函数，涵盖字符串处理、日期计算、数值运算、类型转换、窗口函数、多维分析以及侧视图函数，配以大量实例代码，帮助你全面掌握 Hive 函数体系。

──────────────────────────────────────────────────

一、字符串函数

常用字符串操作

-- 字符串长度

select length('hello')

-- 替换

select replace('hello','l','x')

-- 截取（从第2位开始，截取3个字符）

select substr('hello',2,3)

-- 拼接方式一：concat 函数

select concat('hello','hello','hello')

-- 拼接方式二：|| 运算符

select 'hello'||'hello'||'hello'

-- 拼接表中字段

select concat(id, name) from hero

concat_ws —带分隔符的拼接

concat_ws 函数可以在拼接时添加分隔符，分隔符写在第一个参数位置。

select concat_ws(',', id, name, id) from hero

collect_set与collect_list

聚合函数，collect_set 聚合后去重，collect_list 聚合后保留所有值。常用于行转列操作。

select province_id,

collect_set(city_name) s, -- 去重聚合

collect_list(city_name) l -- 不去重聚合

from city

group by province_id

字符串清理与转换

select TRIM(' dsdsa ') -- 去除前后空格

select upper('xxx') -- 转大写

select lower('XXX') -- 转小写

💡面试考点：concat_ws与collect_set配合使用可实现高效的行转列。

──────────────────────────────────────────────────

二、日期函数

提取日期部分

-- 提取年份

select substr(CURRENT_TIMESTAMP, 1, 4)

select year(CURRENT_TIMESTAMP)

-- 提取月份

select substr(CURRENT_TIMESTAMP, 6, 2)

select month(CURRENT_TIMESTAMP)

-- 提取日

select day(CURRENT_TIMESTAMP)

-- 提取时

select hour(CURRENT_TIMESTAMP)

-- 提取时分秒（格式化方式）

SELECT date_format(CURRENT_TIMESTAMP, 'HH:mm:ss')

时间戳转换

将 bigint 类型的时间戳转换为可读的日期字符串：

select from_unixtime(1782972840, 'yyyy-MM-dd HH:mm:ss')

日期计算

-- 日期间隔天数

select datediff('2020-10-01', '2026-07-02')

select datediff(CURRENT_DATE, '2020-10-01')

-- 间隔月数

select months_between(CURRENT_DATE, '2020-10-01')

-- 往前/往后 N 天

select date_sub(CURRENT_DATE, 1) -- 前一天

select date_add(CURRENT_DATE, 1) -- 后一天

💡面试考点：datediff和date_sub/date_add是日期计算的高频考点，常用于计算留存率、连续登录天数等场景。

──────────────────────────────────────────────────

三、数值型函数

常用数值运算

-- 取模

select mod(10, 3) -- 结果：1

-- 向上取整 / 向下取整

select CEIL(3.14) -- 结果：4

select FLOOR(3.14) -- 结果：3

-- 四舍五入

select round(3.14159, 2) -- 结果：3.14

💡数值函数常用于数据处理中的精度控制、分页计算等场景。

──────────────────────────────────────────────────

四、转换函数

NULL值处理

-- nvl：将 NULL 替换为指定值（常用）

select nvl(null, 0) -- 结果：0

-- COALESCE：返回第一个不为 NULL 的参数（推荐）

select COALESCE(null, 200, null, 100) -- 结果：200

nvl 与 COALESCE 的区别：COALESCE 支持多个参数，返回第一个非 NULL 值；nvl 只支持两个参数。COALESCE 是 SQL 标准函数，可移植性更好。

多表连接中的NULL处理

在 FULL JOIN 场景中，经常需要处理关联字段为 NULL 的情况：

-- FULL JOIN 时处理 NULL

select nvl(a.id, b.id) from a full join b on xxx

select COALESCE(a.id, b.id, c.id) from a full join b on xxx

条件判断

-- IF 函数：三目运算

select if(1>2, 'a', 'b') -- 结果：'b'

-- CASE WHEN：多条件判断

select

case when score >= 90 then '优秀'

when score >= 60 then '及格'

else '不及格'

end as grade

from student

数据类型转换— CAST

CAST 用于数据类型转换，经常用于表连接的 ON 条件中，当两个表的关联字段类型不一致时：

select '2000', cast(200 as string)

-- 表连接中类型不一致时

on a.id = cast(b.id as int)

💡面试考点：nvl与COALESCE的区别、CAST在JOIN中的使用是常考内容。

──────────────────────────────────────────────────

五、窗口函数（⭐重点）

窗口函数语法结构

函数() OVER (

PARTITION BY xxx -- 分组

ORDER BY xxx -- 排序

ROWS BETWEEN xxx AND yyy -- 窗口范围

)

5.1排名开窗函数

排名开窗通常用于计算 Top-N 问题：

-- row_number()：连续排名 1,2,3,4（常用）

-- rank()：跳跃排名 1,1,3,4

-- DENSE_RANK()：密集排名 1,1,2,3

-- 示例：每个部门薪资排名

SELECT

empno, ename, deptno, sal,

row_number() OVER (PARTITION BY deptno ORDER BY sal DESC) rn,

rank() OVER (PARTITION BY deptno ORDER BY sal DESC) rk,

DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) dr

FROM emp

5.2 ROWS BETWEEN —窗口范围详解

窗口范围的关键字：

• preceding：向前找（之前的行）

• following：向后找（之后的行）

• CURRENT ROW：当前行

• UNBOUNDED：一直到窗口的边界

最常用的窗口范围 — 累计计算：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

含义：从窗口的第一行到当前行，常用于累计求和。

5.3聚合开窗函数

聚合函数配合窗口使用，实现累计计算：

-- 累计求和

SELECT dt, amt, SUM(amt) OVER (ORDER BY dt) cumulative_sum

FROM test

-- 累计平均

SELECT dt, amt, AVG(amt) OVER (ORDER BY dt) cumulative_avg

FROM test

支持的聚合函数：SUM、AVG、MAX、MIN、COUNT

5.4偏移开窗函数— LAG / LEAD

偏移开窗将"行与行的关系"转换成"列与列的关系"，常用于计算同比、环比和连续性问题。

-- LAG：向上偏移（看前面的行）

-- LEAD：向下偏移（看后面的行）

-- 语法：lag/lead(要偏移的列, 偏移量, 默认值)

-- 示例：计算每日金额与前一天金额的差值（环比）

SELECT

dt, amt,

lag(amt, 1, 0) OVER (ORDER BY dt) prev_amt,

amt - lag(amt, 1, 0) OVER (ORDER BY dt) diff

FROM test

💡面试考点：LAG/LEAD是处理连续登录天数、留存分析、同比环比计算的核心函数。

5.5切片开窗— NTILE

NTILE 将数据均匀切片，一般用于百分比计算（如计算前 25% 的用户）。

-- NTILE(4)：将数据均匀分成 4 片

SELECT a.*,

ntile(4) OVER (PARTITION BY deptno ORDER BY sal DESC) bucket

FROM emp a

──────────────────────────────────────────────────

六、JSON字符串处理

Hive 提供 get_json_object 函数处理 JSON 格式的字符串字段。

-- 示例 JSON：

-- {"systemtype": "android", "education": "doctor",

-- "marriage_status": "1", "phonebrand": "VIVO"}

-- 提取 JSON 中的字段

SELECT a.*,

get_json_object(extra1, '$.phonebrand') phone

FROM zhiyun.user_info a

💡实际工作中，日志数据常以JSON格式存储，get_json_object是解析JSON的必备函数。

──────────────────────────────────────────────────

七、多维分析函数

GROUPING SETS / ROLLUP / CUBE

多维分析函数用于生成多个维度组合的聚合结果，减少 UNION ALL 的使用。

GROUPING SETS

指定需要聚合的维度组合：

SELECT sex, city, count(0) c

FROM zhiyun.user_info a

GROUP BY sex, city

GROUPING SETS(sex, city)

ROLLUP

从最细粒度向上逐级聚合（层次聚合）：

SELECT sex, city, count(0) c

FROM zhiyun.user_info a

GROUP BY sex, city WITH ROLLUP

CUBE

对所有维度组合进行聚合（笛卡尔积）：

SELECT sex, city, count(0) c

FROM zhiyun.user_info a

GROUP BY sex, city WITH CUBE

💡三种多维分析的对比：GROUPING SETS最灵活（指定组合），ROLLUP有层级关系，CUBE是全组合。

──────────────────────────────────────────────────

八、侧视图函数（Lateral View）

列转行操作

侧视图函数用于将一行中的复杂字段（数组、Map、分隔字符串）展开为多行，实现列转行。

UDF / UDAF / UDTF概念区分

• UDF（User Defined Function）：一进一出，如 length()、upper()

• UDAF（User Defined Aggregation Function）：多进一出，如 sum()、count()

• UDTF（User Defined Table-Generating Function）：一进多出，如 explode()

explode函数

explode 将数组或 Map 展开为多行：

-- 按逗号分隔后展开

select explode(split(category_detail, ','))

from zhiyun.user_goods_category

LATERAL VIEW用法

LATERAL VIEW 配合 explode 使用，可以在展开的同时保留原表的其他字段：

SELECT

a.user_name,

b.pl

FROM zhiyun.user_goods_category a

LATERAL VIEW explode(split(category_detail, ',')) b AS pl

💡面试考点：LATERAL VIEW + explode是Hive中列转行的标准写法，必须熟练掌握。

──────────────────────────────────────────────────

九、总结对比

下表总结了 Hive 内置函数的核心分类和应用场景：

──────────────────────────────────────────────────

🌟面试重点提示

1️⃣窗口函数是 Hive 面试的绝对核心，row_number / rank / lag / lead 必考！

2️⃣concat_ws + collect_set 实现行转列是常见的 SQL 笔试题。

3️⃣nvl 与 COALESCE 的区别需要能清晰表达。

4️⃣LATERAL VIEW + explode 列转行是实际工作中最常用的技巧之一。

5️⃣多维分析 GROUPING SETS / ROLLUP / CUBE 了解即可，但能说出三者的区别会加分。

──────────────────────────────────────────────────

📚如果觉得有帮助，欢迎点赞👍收藏⭐关注🚀

更多大数据技术干货持续更新中...