Hive常用函数

1、时间日期函数

1.1 字符串转时间戳

unix_timestamp用来将字符串转为时间戳，其格式为unix_timestamp(时间字符串)，其中时间字符串最细粒度是yyyy-MM-dd HH:mm:ss格式，也可以只取到分钟、小时、天、日、月、年等格式。当无参数时返回当前时间。

SELECT unix_timestamp('2024-01-02 03:04:05') FROM table_name
-- 结果：1704135845

1.2 时间戳转字符串

from_unixtime用来将时间戳转换为字符串，其格式为from_unixtime(时间戳, 时间格式)，其中时间格式最细粒度是yyyy-MM-dd HH:mm:ss格式，也可以只取到分钟、小时、天、日、月、年等格式。

SELECT from_unixtime(unix_timestamp('2024-01-02 03:04:05') , 'yyyy-MM-dd HH:mm:ss') FROM table_name
-- 结果：2024-01-02 03:04:05

1.3 计算时间差天数

datediff用来计算时间差天数，其格式为datediff(日期1,日期2)，其中日期1和日期2是以yyyy-MM-dd HH:mm:ss或yyyy MM-dd格式表示的字符串。如果日期1早于日期2，结果将为负数。

SELECT datediff('2024-01-02', '2024-01-01') FROM table_name;
-- 结果：1

1.4 取年月日时分秒

year`、`month`、`day`、`hour`、`minute`、`second`分别用于取时间的年月日时分秒，以`year`为例，其其格式为`year(日期)
SELECT year('2024-01-02 03:04:05'), year(to_date('2024-01-02 03:04:05')) FROM table_name
-- 结果：2024、2024

1.5 增加指定天数

date_add用来为日期加上指定的天数，其格式为date_add(日期, 天数)，其中天数可以为负数，此时会减去对应的天数。返回结果是yyyy-MM-dd格式

SELECT date_add('2024-01-05 03:04:05', 3), date_add('2024-01-05 03:04:05', -3) FROM table_name
-- 结果：2024-01-08、2024-01-02

1.6 增加指定月数

add_months用来为日期加上指定的月数，其格式为add_months(日期, 月数)，其中月数可以为负数，此时会减去对应的月数。返回结果是yyyy-MM-dd格式

SELECT add_months('2024-02-02 03:04:05', 1), add_months('2024-02-02 03:04:05', -1) FROM table_name
-- 结果：2024-03-02、2024-01-02

1.7 当月最后一日

last_day用来取当前日期所在月份的最后一日，其格式为last_day(日期)。返回结果是yyyy-MM-dd格式

SELECT last_day('2024-02-02 03:04:05') FROM table_name
-- 结果：2024-02-29

1.8 获取天级日期

to_date用来获取天级日期，参数必须是yyyy-MM-dd HH:mm:ss格式字符串（可只取到分、时、日）、date或timestamp类型（timestamp类型自测不行），返回值是date类型。

SELECT to_date('2024-01-02 03:04:05'), to_date('2024-01-02 03'), to_date(to_date('2024-01-02 03')) FROM table_name
-- 结果：2024-01-02、2024-01-02、2024-01-02

2、字符串函数

2.1 获取字符串长度

length`用来获取字符串长度，其格式为`length(字符串)
SELECT length('abcdefgh') FROM table_name
-- 结果：8

2.2 字符串取子串

substring用来取字符串的子串，其格式为substring(起始下标，长度)，其中字符串下标从1开始。

SELECT substring('abcdefg', 1, 4) FROM table_name
-- 结果：abcd

2.3 查找子串

locate用来在字符串里查找子串，如果存在要查找的子串，则返回第一次出现的下标，否则返回0。其格式为locate(字符串, 子串, [起始下标])，其中起始下标是可选参数，用来控制从第几个字符开始查找子串。下标从1开始。

SELECT locate('bcd', 'abcdefg'), locate('bcd', 'abcdabcd'), locate('aaa', 'abcdefg'), locate('bcd', 'abcdefg', 2), locate('bcd', 'abcdefg', 3) FROM table_name
-- 结果：2、2、0、2、0

2.4 字符串中子串替换

replace用来替换字符串中的子串，其格式为replace(字符串, 旧子串, 新子串)，当字符串中存在多个旧子串时，会全部被新子串替换

SELECT replace('abcdeabcde', 'bc', '333'), replace('aaaaa', 'aa', 'bb') FROM table_name
-- 结果：a333dea333de、bbbba

2.5 字符串拼接

concat用来拼接多个字符串，其格式为concat(字符串1, 字符串2, 字符串3, ...)，其中只要有个一个字符串为NULL，则结果为NULL

SELECT concat('a', 'bb', 'ccc'), concat('a', null, 'ccc') FROM table_name
-- 结果：abbccc、NULL

concat_ws用来以指定分隔符拼接多个字符串，其格式为concat_ws(分隔符, 字符串1, 字符串2, 字符串3, ...)，其中有字符串为NULL是会被忽略

SELECT concat_ws(',', 'a', 'bb', 'ccc'), concat_ws(',,', 'a', null, 'ccc') FROM table_name
-- 结果：a,bb,ccc、a,,ccc

1. 其他函数

3.1 数据类型转换

cast用来做数据类型转换，其格式为cast(字段名 AS 数据类型)，数据类型可以是tinyint、smallint、int、bigint、boolean、float、double、string、binary、timestamp、decimal中的一种。

SELECT cast(1.23 AS string) FROM table_name
-- 结果：1.23

3.2 取最大最小值函数

least和greatest分别用来计算多个值的最小值和最大值，格式为least(值1, 值2, 值3, ...)和greatestt(值1, 值2, 值3, ...)。参数可以是多个值，数据类型支持整数、浮点数、字符串、时间戳等。字符串会按照字典序比较。

SELECT  least(1, 3, 5),
        greatest(1, 3, 5),
        least(1.11, 3.33, 5.55),
        greatest(1.11, 3.33, 5.55),
        least('aaa', 'b', 'abc'),
        greatest('aaa', 'b', 'abc'),
        least(
            unix_timestamp(),
            unix_timestamp()+1,
            unix_timestamp()+2
        ),
        greatest(
            unix_timestamp(),
            unix_timestamp()+1,
            unix_timestamp()+2
        )
FROM table_name
-- 结果：1、5、1.11、5.55、aaa、b、1726649632、1726649634

3.3 if条件判断

if用来做条件判断，其格式为if(表达式, 值1, 值2)，如果表达式的值为true则取值1，否则取值2。

SELECT if(1=1, 1, 0), if(1>1, 1, 0) FROM table_name
-- 结果：1、0

3.4 case-when条件判断

直接看例子吧。

SELECT case
            when 55>90 then '优'
            when 55>60 then '良'
            else '不及格'
        end
FROM table_name

3.5 获取json字符串key对应的value

get_json_object用来获取json字符串指定key对应的value，其格式为get_json_object(json字符串, $.key)，注意$.是必须的，不可省略。

SELECT  get_json_object('{"abcdef":"111"}', '$.abc'),
        get_json_object('{"abcdef":"111"}', '$.abcdef')   
FROM table_name
-- 结果：空、111