hive常用函数总结

不古鸟

已于 2025-05-22 18:41:26 修改

阅读量885

点赞数 16

文章标签： hive hadoop 数据仓库

于 2025-05-22 18:40:40 首次发布

本文链接：https://blog.csdn.net/happy_hound/article/details/148134581

版权

目录

一、时间与日期函数

二、字符串处理函数

三、数学计算函数

四、集合操作函数

五、JSON 处理函数

六、条件判断函数

七、聚合统计函数

八、窗口分析函数

九、类型转换函数

十、其他常用函数

一、日期与时间函数

函数名	描述	示例
`current_date()`	返回当前系统日期（格式：`yyyy-MM-dd`）。	`current_date()` → `'2025-05-22'`
`current_timestamp()`	返回当前系统时间戳（格式：`yyyy-MM-dd HH:mm:ss.ms`）。	`current_timestamp()` → `'2025-05-22 14:30:00.123'`
`unix_timestamp()`	返回当前系统时间戳（距离 1970 年 1 月 1 日的秒数）。	`unix_timestamp()` → `1747843800`
`from_unixtime(unix_ts [, fmt])`	将时间戳转换为字符串（默认格式：`yyyy-MM-dd HH:mm:ss`）。	`from_unixtime(1747843800)` → `'2025-05-22 14:30:00'`
`to_date(timestamp/string)`	将时间戳或字符串转换为`DATE`类型（字符串需符合`yyyy-MM-dd`格式）。	`to_date('2025-05-22 14:30:00')` → `'2025-05-22'`
`date_format(date, fmt)`	将日期按指定格式转换为字符串（如`yyyy/MM/dd`）。	`date_format('2025-05-22', 'yyyy/MM/dd')` → `'2025/05/22'`
`datediff(end_date, start_date)`	计算两个日期相差的天数（`end_date - start_date`）。	`datediff('2025-01-01', '2024-12-25')` → `7`
`months_between(date1, date2)`	计算两个日期相差的月数（可精确到小数）。	`months_between('2025-01-01', '2024-12-01')` → `1.0`
`date_add(date, days)`	日期加指定天数（正数向后推，负数向前推）。	`date_add('2025-05-22', 3)` → `'2025-05-25'`
`date_sub(date, days)`	日期减指定天数。	`date_sub('2025-05-22', 7)` → `'2025-05-15'`
`add_months(date, months)`	日期加指定月数（自动处理月底日期）。	`add_months('2025-01-31', 1)` → `'2025-02-28'`
`last_day(date)`	返回当月的最后一天。	`last_day('2025-02-15')` → `'2025-02-28'`
`trunc(date, 'YEAR'/'MONTH')`	截断日期到年初或月初。	`trunc('2025-05-22', 'MONTH')` → `'2025-05-01'`
`next_day(date, 'weekday')`	返回指定日期后的第一个周几（如`'Monday'`）。	`next_day('2025-05-22', 'Thursday')` → `'2025-05-29'`
`year/month/day(date)`	提取日期中的年、月、日。	`year('2025-05-22')` → `2025`；`month('2025-05-22')` → `5`
`hour/minute/second(timestamp)`	提取时间戳中的时、分、秒。	`hour('2025-05-22 14:30:00')` → `14`
`weekofyear(date)`	返回日期是当年的第几周（范围 1-53）。	`weekofyear('2025-01-01')` → `1`
`dayofweek(date)`	返回日期是星期几（1 = 星期日，2 = 星期一，...，7 = 星期六）。	`dayofweek('2025-05-22')` → `5`（星期四）

二、字符串处理函数

函数名	描述	示例
`concat(str1, str2, ...)`	拼接多个字符串（若有`NULL`则返回`NULL`）。	`concat('Hello', ' ', 'World')` → `'Hello World'`
`concat_ws(sep, str1, str2, ...)`	带分隔符的字符串拼接（忽略`NULL`）。	`concat_ws(',', 'A', 'B', NULL)` → `'A,B'`
`substr(str, pos [, len])`	截取子字符串（`pos`从 1 开始，`len`可选）。	`substr('hive.apache.org', 6)` → `'apache.org'`
`length(str)`	返回字符串长度。	`length('hello')` → `5`
`trim/ltrim/rtrim(str)`	去除字符串首尾 / 左侧 / 右侧空格。	`trim(' hive ')` → `'hive'`
`upper/lower(str)`	转换为大写 / 小写。	`upper('hive')` → `'HIVE'`；`lower('HIVE')` → `'hive'`
`regexp_extract(str, pattern, idx)`	按正则表达式提取匹配的子串（`idx`为匹配组序号）。	`regexp_extract('www.example.com', '\\.(\\w+)\\.', 1)` → `'example'`
`regexp_replace(str, pattern, repl)`	按正则表达式替换匹配的子串。	`regexp_replace('hello world', '\\s+', '-')` → `'hello-world'`
`split(str, regex)`	将字符串按正则表达式分割为数组。	`split('a,b,c', ',')` → `['a', 'b', 'c']`
`instr(str, substr)`	返回子串在字符串中第一次出现的位置（从 1 开始，未找到返回 0）。	`instr('hello', 'l')` → `3`
`lpad/rpad(str, len, pad)`	左 / 右填充字符串至指定长度。	`lpad('123', 5, '0')` → `'00123'`；`rpad('abc', 5, 'x')` → `'abcxx'`
`repeat(str, n)`	将字符串重复`n`次。	`repeat('abc', 3)` → `'abcabcabc'`
`reverse(str)`	反转字符串。	`reverse('hello')` → `'olleh'`

三、数学计算函数

函数名	描述	示例
`abs(num)`	返回绝对值。	`abs(-10)` → `10`
`round(num [, d])`	四舍五入（保留`d`位小数，默认 0）。	`round(3.1415, 2)` → `3.14`；`round(3.5)` → `4`
`ceil/floor(num)`	向上 / 向下取整。	`ceil(3.14)` → `4`；`floor(3.99)` → `3`
`pow/power(x, y)`	计算 x 的 y 次方。	`pow(2, 3)` → `8`；`power(10, 0.5)` → `3.16227766`
`sqrt(num)`	计算平方根。	`sqrt(16)` → `4`
`log(x, y)`	计算以 x 为底 y 的对数。	`log(10, 100)` → `2`；`log(2, 8)` → `3`
`log10/log2(num)`	计算以 10/2 为底的对数。	`log10(100)` → `2`；`log2(8)` → `3`
`exp(num)`	计算 e 的 x 次方。	`exp(2)` → `7.38905609893`
`rand([seed])`	生成随机数（范围 0-1，`seed`相同时结果可重复）。	`rand()` → `0.56789`（每次不同）；`rand(123)` → 固定值
`pi()`	返回圆周率 π。	`pi()` → `3.14159265358979`
`sin/cos/tan(num)`	三角函数（参数为弧度）。	`sin(pi()/2)` → `1`；`cos(pi())` → `-1`

四、集合操作函数

函数名	描述	示例
`size(array/map)`	返回数组或映射的元素个数。	`size(array(1, 2, 3))` → `3`；`size(map('a', 1, 'b', 2))` → `2`
`array_contains(array, value)`	判断数组是否包含某个值（返回`BOOLEAN`）。	`array_contains(array('a', 'b'), 'b')` → `true`
`sort_array(array)`	对数组元素进行排序（默认升序）。	`sort_array(array(3, 1, 2))` → `[1, 2, 3]`
`map_keys/map_values(map)`	返回映射的键 / 值数组。	`map_keys(map('a', 1, 'b', 2))` → `['a', 'b']`
`explode(array/map)`	将数组或映射展开为多行（常用于行转列）。	`SELECT explode(array(1, 2, 3))` → 生成 3 行数据：`1`、`2`、`3`
`collect_list/collect_set(expr)`	将分组内的字段值收集为数组（保留 / 去重）。	`SELECT dept_id, collect_list(name) FROM employees GROUP BY dept_id`

五、JSON 处理函数

函数名	描述	示例
`get_json_object(json_str, path)`	提取 JSON 字符串中指定路径的值。	`get_json_object('{"name":"Alice","age":25}', '$.age')` → `25`
`json_tuple(json_str, k1, k2, ...)`	一次性提取 JSON 中的多个字段。	`json_tuple('{"name":"Alice","age":25}', 'name', 'age')` → 返回两列：`Alice`、`25`
`parse_json(json_str)`	将 JSON 字符串解析为对应的数据类型（如`MAP`、`STRUCT`）。	`parse_json('{"a":1, "b":2}')` → `map('a', 1, 'b', 2)`
`to_json(struct/map/array)`	将 Hive 数据类型转换为 JSON 字符串。	`to_json(named_struct('name', 'Alice', 'age', 25))` → `'{"name":"Alice","age":25}'`

六、条件判断函数

函数名	描述	示例
`if(condition, true_val, false_val)`	简单条件判断（满足条件返回`true_val`，否则返回`false_val`）。	`if(score >= 60, '及格', '不及格')` → `'及格'`
`case when condition1 then val1 ... else default end`	多条件判断（类似 SQL 的`CASE`）。	`CASE WHEN age < 18 THEN '未成年' ELSE '成年' END`
`coalesce(val1, val2, ...)`	返回第一个非空值。	`coalesce(null, 'default')` → `'default'`
`nullif(val1, val2)`	如果`val1`等于`val2`，返回`NULL`；否则返回`val1`。	`nullif(10, 10)` → `NULL`；`nullif(10, 20)` → `10`
`isnull/isnotnull(expr)`	判断表达式是否为`NULL`（返回`BOOLEAN`）。	`isnull(null)` → `true`；`isnotnull('value')` → `true`

七、聚合统计函数

函数名	描述	示例
`count(expr)`	统计非空值的行数。	`count(id)` → 统计`id`非空的记录数
`sum/avg/max/min(expr)`	求和、求平均、求最大值、求最小值（仅适用于数值类型）。	`sum(sales)` → 计算销售额总和
`var_pop/var_samp(expr)`	计算总体方差 / 样本方差。	`var_pop(salary)` → 薪资的总体方差
`stddev_pop/stddev_samp(expr)`	计算总体标准差 / 样本标准差。	`stddev_samp(salary)` → 薪资的样本标准差
`corr(expr1, expr2)`	计算两个字段的皮尔逊相关系数。	`corr(x, y)` → x 和 y 的相关系数
`percentile_approx(col, p)`	计算数值字段的 p 分位数（如中位数：`p=0.5`）。	`percentile_approx(salary, 0.5)` → 薪资中位数

八、窗口分析函数

函数名	描述	示例（按部门分组，按薪资排序）
`row_number()`	为分组内的行分配唯一序号（从 1 开始，重复值不并列）。	`row_number() OVER (PARTITION BY dept ORDER BY salary DESC)`
`rank/dense_rank()`	排名（重复值并列，`rank`会跳过名次，`dense_rank`不跳）。	`rank() OVER (PARTITION BY dept ORDER BY salary DESC)`
`lead/lag(expr, n)`	取当前行之后 / 之前第`n`行的值（默认`n=1`）。	`lead(salary, 1) OVER (ORDER BY id)` → 后一行薪资
`first_value/last_value(expr)`	返回分组内的第一个 / 最后一个值。	`first_value(salary) OVER (PARTITION BY dept ORDER BY salary)` → 最低薪资
`sum/avg/max/min OVER()`	分组内的累计计算（需指定`ORDER BY`）。	`sum(sales) OVER (PARTITION BY month ORDER BY day)` → 按天累计月销售额

九、类型转换函数

函数名	描述	示例
`cast(expr AS type)`	强制类型转换（如`INT`、`STRING`、`DATE`等）。	`cast('123' AS INT)` → `123`；`cast(123 AS STRING)` → `'123'`
`binary(str)`	将字符串转换为二进制类型。	`binary('hello')` → `0x68656C6C6F`

十、其他常用函数

函数名	描述	示例
`greatest/least(expr1, expr2, ...)`	返回最大值 / 最小值（忽略`NULL`）。	`greatest(3, 5, 1)` → `5`；`least(3, 5, 1)` → `1`
`md5/sha1/sha2(str)`	计算字符串的哈希值（返回 32/40/64 位十六进制字符串）。	`md5('hello')` → `'5d41402abc4b2a76b9719d911017c592'`
`url_encode/url_decode(str)`	URL 编码 / 解码。	`url_encode('hello world')` → `'hello%20world'`