Hive 常用内置函数

前言

Hive 函数是数据仓库开发日常高频使用的工具，本文整理工作中一些常用的 Hive 内置函数，包含字符串、日期、数值、类型转换、窗口函数、JSON 解析、多维聚合、侧视图列转行等场景，附带可直接运行的 SQL 示例，适合数据开发、数仓面试复习收藏。

一、字符串函数

1. length 获取字符串长度

-- 返回字符长度 select length('hello');

2. replace 字符串替换

-- 将所有 l 替换为 x select replace('hello','l','x');

3. substr 字符串截取

-- 从第2位开始截取3个字符 select substr('hello',2,3);

4. concat / || 字符串拼接

-- 多字符串直接拼接 select concat('hello','hello','hello'); -- || 拼接写法 select 'hello'||'hello'||'hello'; -- 表字段拼接 select concat(id,name) from hero;

5. concat_ws 带分隔符拼接

第一个参数为分隔符，自动忽略 null 值，开发最常用：

-- 使用逗号拼接多个字段 select concat_ws(',',id,name,id) from hero;

6. split 字符串分割（返回数组）

-- 按逗号分割成数组 ["a","b","c"] select split('a,b,c',','); -- 取数组第0位，提取年份 select split('2026-07-02','-')[0];

7. collect_set /collect_list 聚合数组函数

collect_list：分组聚合，不去重

collect_set：分组聚合，自动去重

select province_id, collect_set(city_name) city_distinct, collect_list(city_name) city_all from city group by province_id;

8. trim 去除首尾空格

select TRIM(' dsdsa ');

9. upper 转大写

转小写 --lower

select upper('aaa');

二、日期函数

1. 提取年 / 月 / 日 / 时

-- 方式1：substr截取时间戳字符串 select substr(CURRENT_TIMESTAMP,1,4); select substr(CURRENT_TIMESTAMP,6,2); -- 方式2：专用日期函数（推荐） select year(CURRENT_TIMESTAMP); select month(CURRENT_TIMESTAMP); select day(CURRENT_TIMESTAMP); select hour(CURRENT_TIMESTAMP);

2. from_unixtime 时间戳转日期字符串

-- bigint时间戳格式化 select from_unixtime(1782972840,'yyyy-MM-dd HH:mm:ss');

3. datediff 计算两日期间隔天数

-- 前减后，返回差值 select datediff('2020-10-01','2026-07-02'); -- 当前日期对比指定日期 select datediff(CURRENT_DATE,'2020-10-01');

4. months_between 计算间隔月数

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

5. date_add /date_sub 日期加减 N 天

-- 当前日期前1天 select date_sub(CURRENT_DATE,1); -- 当前日期后1天 select date_add(CURRENT_DATE,1);

三、数值型函数

1. 基础四则运算

select 10 + 3, 10 - 3, 10 * 3, 10 / 3, 10 % 3;

2. 除法区分 / 和 div

/：浮点除法，返回小数

div：整数除法，只保留整数部分

select 5 / 2; -- 2.5 select 5 div 2; -- 2

3. round 四舍五入

语法：round(x [,小数位数])，默认保留 0 位小数

select round(3.1415), round(3.1415,2), round(-2.6); -- 输出：3 , 3.14 , -3

4. ceil 向上取整

select ceil(3.1), ceil(-2.1); -- 4 , -2

5. floor 向下取整

select floor(3.9), floor(-2.1); -- 3 , -3

6. truncate 截断小数（不四舍五入）

select truncate(3.99,1), truncate(-2.9); -- 3.9 , -2

四、条件 & 类型转换函数

1. nvl 空值替换

单字段空值判断，参数 1 为空则返回参数 2

select nvl(1,2) -- 全连接取非空id select nvl(a.id,b.id) from a full join b on xxx;

2. COALESCE 多字段依次取非空值

支持多个参数，从左到右返回第一个不为 null 的值，多表合并主键首选

-- 输出 200 select COALESCE(null,200,null,100); -- 多表关联合并主键 select COALESCE(a.id,b.id,c.id) from a full join b on xxx;

3. if 条件判断

语法：if(条件,满足返回值,不满足返回值)

select if(1>2,'a','b');

4. case when 多分支条件

select id, case when score >=90 then '优秀' when score >=60 then '及格' else '不及格' end level from student;

5. cast 强制类型转换

关联表、字段类型不匹配时高频使用

-- 数字转字符串 select cast(200 as string); -- 关联时统一字段类型 ... on a.id = cast(b.id as int)

五、窗口函数（数仓核心）

通用语法：

函数() over(partition by 分组字段 order by 排序字段 rows between ... and ...)

1. 排名类窗口函数（TopN 场景）

2. 窗口行范围关键字

x preceding：向前x行

x following：向后x行

current row：当前行

unbounded：窗口边界（无限制）

-- 从分区第一行累计到当前行 ... rows between unbounded preceding and current row

3. 聚合开窗（累计求和 / 平均值）

sum、avg、max、min、count 配合 over 实现累计计算

select dt, city, amt, sum(amt) over(order by dt rows between unbounded preceding and current row) total_amt from city_amt;

4. 偏移开窗 lag /lead（环比、连续数据）

lag (列，偏移量，[默认值])：取上 N 行数据

lead (列，偏移量，[默认值])：取下 N 行数据

select dt, amt, lag(amt,1,0) over(order by dt) last_amt, amt - lag(amt,1,0) over(order by dt) chain_diff from test;

5. ntile 切片开窗（百分比分层）

将分区数据均匀切分为 N 份，用于分层统计（高低用户分层）

-- 部门内工资降序，分成4组 select a.*, ntile(4) over(PARTITION BY deptno ORDER BY sal desc) tile_level from emp a;

六、JSON 处理函数 get_json_object

解析表中 JSON 字符串字段，提取指定 key 的值 JSON 样例：{"systemtype": "android", "education": "doctor", "marriage_status": "1", "phonebrand": "VIVO"}

select a.*, get_json_object(extra1, '$.phonebrand') phone_brand, get_json_object(extra1, '$.education') education from user_info a;

七、多维聚合分析 grouping sets /rollup/cube

多维度自由聚合，替代多组 union all，简化报表统计

1. grouping sets 自定义维度组合

select sex,city,count(1) user_cnt from user_info a group by sex,city GROUPING sets(sex,city);

2. rollup 逐层上卷聚合（从细到粗）

select sex,city,count(1) user_cnt from user_info a group by sex,city with ROLLUP;

3. cube 全维度笛卡尔聚合（所有维度组合）

select sex,city,count(1) user_cnt from user_info a group by sex,city with cube;

八、侧视图 & explode 列转行（UDTF 函数）

函数分类补充

• UDF：一进一出（length、replace 等普通函数）
• UDAF：多进一出（sum、count、collect_set 聚合函数）
• UDTF：一进多出（explode 炸裂函数）

1. explode 数组炸裂

-- 将分割后的数组拆分成多行 select explode(split(category_detail,',')) from user_goods_category;

2. LATERAL VIEW 侧视图（关联原表字段）

业务场景：用户多商品品类拆分，统计每个品类购买人数

select a.user_name, b.pl category from user_goods_category a LATERAL view explode(split(category_detail,',')) b as pl;

总结

1. 字符串函数日常清洗必用，concat_ws、split、trim使用频率最高；
2. 日期函数优先使用year/month/day专用函数，可读性高于 substr 截取；
3. 空值处理优先COALESCE适配多表合并主键；
4. 窗口函数是数仓核心，排名、累计、环比、分层全部依赖；
5. explode + 侧视图实现列转行，解决数组多值拆分场景；
6. grouping sets/rollup/cube 大幅简化多维度报表 SQL。