Hive SQL函数大全：常用函数一览
Hive SQL函数大全：常用函数一览
Hive SQL是基于Hadoop的数据仓库工具，提供了丰富的内置函数，用于数据处理、转换和分析。本文将详细介绍Hive SQL中常用的函数，涵盖字符串函数、日期函数、数学函数、集合函数、条件函数以及窗口函数等，并提供示例说明，帮助读者更好地理解和应用这些函数。
一、字符串函数
字符串函数用于操作和处理字符串数据。以下是一些常用的字符串函数：
-
length(str)： 返回字符串的长度。
sql
SELECT length('Hello World'); -- 输出 11 -
lower(str) / upper(str)： 将字符串转换为小写/大写。
sql
SELECT lower('Hello World'), upper('Hello World'); -- 输出 hello world, HELLO WORLD -
substr(str, start, len) / substring(str, start, len)： 提取子字符串，从start位置开始，提取len个字符。
sql
SELECT substr('Hello World', 7, 5); -- 输出 World -
trim(str)： 去除字符串两端的空格。
sql
SELECT trim(' Hello World '); -- 输出 Hello World -
ltrim(str) / rtrim(str)： 去除字符串左端/右端的空格。
sql
SELECT ltrim(' Hello World '), rtrim(' Hello World '); -- 输出 Hello World , Hello World -
concat(str1, str2, ...) / ||： 连接多个字符串。
sql
SELECT concat('Hello', ' ', 'World'), 'Hello' || ' ' || 'World'; -- 输出 Hello World, Hello World -
regexp_replace(str, pattern, replacement)： 使用正则表达式替换字符串。
sql
SELECT regexp_replace('Hello World', 'World', 'Hive'); -- 输出 Hello Hive -
split(str, delimiter)： 使用分隔符分割字符串，返回一个数组。
sql
SELECT split('Hello,World,Hive', ','); -- 输出 ["Hello","World","Hive"] -
find_in_set(str, strList)： 查找字符串在字符串列表中的位置。
sql
SELECT find_in_set('World', 'Hello,World,Hive'); -- 输出 2
二、日期函数
日期函数用于处理日期和时间数据。
-
current_date() / current_timestamp()： 返回当前日期/当前时间戳。
sql
SELECT current_date(), current_timestamp(); -
year(date) / month(date) / day(date)： 提取日期的年/月/日。
sql
SELECT year('2023-10-27'), month('2023-10-27'), day('2023-10-27'); -- 输出 2023, 10, 27 -
date_add(date, days) / date_sub(date, days)： 日期加/减指定天数。
sql
SELECT date_add('2023-10-27', 7), date_sub('2023-10-27', 7); -
datediff(date1, date2)： 计算两个日期之间的天数差。
sql
SELECT datediff('2023-10-27', '2023-10-20'); -- 输出 7 -
unix_timestamp() / from_unixtime(unixtime)： 将当前时间转换为Unix时间戳/将Unix时间戳转换为日期时间。
sql
SELECT unix_timestamp(), from_unixtime(unix_timestamp()); -
date_format(date, format)： 格式化日期。
sql
SELECT date_format('2023-10-27', 'yyyy-MM-dd HH:mm:ss'); -- 输出 2023-10-27 00:00:00
三、数学函数
数学函数用于执行数学运算。
-
round(num) / floor(num) / ceil(num)： 四舍五入/向下取整/向上取整。
sql
SELECT round(3.14), floor(3.14), ceil(3.14); -- 输出 3, 3, 4 -
rand()： 生成随机数。
sql
SELECT rand(); -
abs(num)： 取绝对值。
sql
SELECT abs(-5); -- 输出 5 -
pow(num, power) / power(num, power)： 计算幂次方。
sql
SELECT pow(2, 3), power(2, 3); -- 输出 8, 8 -
sqrt(num)： 计算平方根。
sql
SELECT sqrt(9); -- 输出 3
四、集合函数
集合函数用于对一组值进行操作。
-
size(array)： 返回数组的大小。
sql
SELECT size(array('a','b','c')); -- 输出 3 -
map_keys(map) / map_values(map)： 返回map的键/值集合。
sql
SELECT map_keys(map('a', 1, 'b', 2)), map_values(map('a', 1, 'b', 2)); -- 输出 ["a","b"], [1,2]
五、条件函数
条件函数根据条件返回不同的值。
-
if(condition, true_value, false_value)： 如果条件为真，返回true_value，否则返回false_value。
sql
SELECT if(1 > 0, 'true', 'false'); -- 输出 true -
case when condition1 then result1 when condition2 then result2 else resultN end： 类似于switch语句。
sql
SELECT case when 1 > 0 then 'true' else 'false' end; -- 输出 true -
coalesce(value1, value2, ...)： 返回第一个非空值。
sql
SELECT coalesce(NULL, 'Hello', 'World'); -- 输出 Hello
六、窗口函数
窗口函数对一组行执行计算，这些行与当前行相关。
-
row_number() over (partition by column1 order by column2)： 分组排序后，为每行分配一个唯一的行号。
-
rank() over (partition by column1 order by column2)： 分组排序后，为每行分配一个排名，允许并列排名。
-
dense_rank() over (partition by column1 order by column2)： 与rank()类似，但不允许并列排名。
-
lag(column, offset, default) over (partition by column1 order by column2) / lead(column, offset, default) over (partition by column1 order by column2)： 访问当前行之前/之后的行的数据.
-
sum(column) over (partition by column1 order by column2 rows between preceding 2 and current row)： 计算移动平均值等。
以上列举了Hive SQL中常用的函数，涵盖了各种数据类型的操作和处理。熟练掌握这些函数能够 greatly 提高数据处理的效率和灵活性。 希望本文能够帮助读者更好地理解和应用Hive SQL函数，在实际工作中发挥其强大作用。 当然，Hive SQL还提供了更多其他的内置函数，读者可以参考官方文档进行更深入的学习和探索。