前言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 转大写转小写 --lowerselect upper(aaa);二、日期函数1. 提取年 / 月 / 日 / 时-- 方式1substr截取时间戳字符串 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; -- 23. round 四舍五入语法round(x [,小数位数])默认保留 0 位小数select round(3.1415), round(3.1415,2), round(-2.6); -- 输出3 , 3.14 , -34. ceil 向上取整select ceil(3.1), ceil(-2.1); -- 4 , -25. floor 向下取整select floor(3.9), floor(-2.1); -- 3 , -36. truncate 截断小数不四舍五入select truncate(3.99,1), truncate(-2.9); -- 3.9 , -2四、条件 类型转换函数1. nvl 空值替换单字段空值判断参数 1 为空则返回参数 2select 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(12,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 场景函数特点示例序列row_number()连续自增无重复排名1,2,3,4rank()相同值同排名跳过后续序号1,1,3,4dense_rank()相同值同排名不跳序号1,1,2,32. 窗口行范围关键字x preceding向前x行x following向后x行current row当前行unbounded窗口边界无限制-- 从分区第一行累计到当前行 ... rows between unbounded preceding and current row3. 聚合开窗累计求和 / 平均值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;总结字符串函数日常清洗必用concat_ws、split、trim使用频率最高日期函数优先使用year/month/day专用函数可读性高于 substr 截取空值处理优先COALESCE适配多表合并主键窗口函数是数仓核心排名、累计、环比、分层全部依赖explode 侧视图实现列转行解决数组多值拆分场景grouping sets/rollup/cube 大幅简化多维度报表 SQL。
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 转大写转小写 --lowerselect upper(aaa);二、日期函数1. 提取年 / 月 / 日 / 时-- 方式1substr截取时间戳字符串 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; -- 23. round 四舍五入语法round(x [,小数位数])默认保留 0 位小数select round(3.1415), round(3.1415,2), round(-2.6); -- 输出3 , 3.14 , -34. ceil 向上取整select ceil(3.1), ceil(-2.1); -- 4 , -25. floor 向下取整select floor(3.9), floor(-2.1); -- 3 , -36. truncate 截断小数不四舍五入select truncate(3.99,1), truncate(-2.9); -- 3.9 , -2四、条件 类型转换函数1. nvl 空值替换单字段空值判断参数 1 为空则返回参数 2select 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(12,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 场景函数特点示例序列row_number()连续自增无重复排名1,2,3,4rank()相同值同排名跳过后续序号1,1,3,4dense_rank()相同值同排名不跳序号1,1,2,32. 窗口行范围关键字x preceding向前x行x following向后x行current row当前行unbounded窗口边界无限制-- 从分区第一行累计到当前行 ... rows between unbounded preceding and current row3. 聚合开窗累计求和 / 平均值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;总结字符串函数日常清洗必用concat_ws、split、trim使用频率最高日期函数优先使用year/month/day专用函数可读性高于 substr 截取空值处理优先COALESCE适配多表合并主键窗口函数是数仓核心排名、累计、环比、分层全部依赖explode 侧视图实现列转行解决数组多值拆分场景grouping sets/rollup/cube 大幅简化多维度报表 SQL。
