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