1、查看函數(shù)列表

-- 查看所有可用函數(shù)
show functions;
-- 查看count函數(shù)使用方式
describe function extended count;

2、數(shù)學(xué)函數(shù)

-- round 取整，設(shè)置小數(shù)精度
select round(3.1415926);		-- 取整(四舍五入)
select round(3.1415926, 4);		-- 設(shè)置小數(shù)精度4位(四舍五入)
-- 隨機(jī)數(shù)
select rand();					-- 完全隨機(jī)
select rand(3);					-- 設(shè)置隨機(jī)數(shù)種子，設(shè)置種子后每次運(yùn)行結(jié)果一致的
-- 絕對(duì)值
select abs(-3);
-- 求PI
select pi();

3、集合函數(shù)

-- 求元素個(gè)數(shù)
select size(work_locations) from test_array;
select size(members) from test_map;
-- 取出map的全部key
select map_keys(members) from test_map;
-- 取出map的全部value
select map_values(members) from test_map;
-- 查詢array內(nèi)是否包含指定元素，是就返回True
select * from test_array where ARRAY_CONTAINS(work_locations, 'tianjin');
-- 排序
select *, sort_array(work_locations) from test_array;

4、類(lèi)型轉(zhuǎn)換函數(shù)

-- 轉(zhuǎn)二進(jìn)制
select binary('hadoop');
-- 自由轉(zhuǎn)換，類(lèi)型轉(zhuǎn)換失敗報(bào)錯(cuò)或返回NULL
select cast('1' as bigint);

5、日期函數(shù)

-- 當(dāng)前時(shí)間戳
select current_timestamp();
-- 當(dāng)前日期
select current_date();
-- 時(shí)間戳轉(zhuǎn)日期
select to_date(current_timestamp());
-- 年月日季度等
select year('2020-01-11');
select month('2020-01-11');
select day('2020-01-11');
select quarter('2020-05-11');		--季度
select dayofmonth('2020-05-11');
select hour('2020-05-11 10:36:59');
select minute('2020-05-11 10:36:59');
select second('2020-05-11 10:36:59');
select weekofyear('2020-05-11 10:36:59');
-- 日期之間的天數(shù)
select datediff('2022-12-31', '2019-12-31');
-- 日期相加、相減
select date_add('2022-12-31', 5);
select date_sub('2022-12-31', 5);

6、條件函數(shù)

-- if判斷
SELECT if (truename is NULL,'沒(méi)有填寫(xiě)姓名',truename) from users;--null判斷
SELECT isnull(truename) from users;
SELECT isnotnull(truename) from users;--nvl ,如果字段值為null，則返回默認(rèn)值，否則返回字段的值，等同于if判斷的效果
SELECT nvl(truename,'無(wú)姓名') from users;
-- 返回第一個(gè)不是null的v，如果所有v都是null，則返回null
SELECT COALESCE (truename,brithday) from users;
-- 當(dāng)a=b，返回c，當(dāng)a=d時(shí)返回e，否則返回f
SELECT username ,CASE username when '周杰輪' THEN '著名歌星' WHEN '張魯依' THEN '著名演員' ELSE '未知人員' END FROM users;
SELECT truename ,CASE when truename is null then '無(wú)姓名' ELSE truename end FROM users;--如果 a=b ，則返回null，否則返回a。
SELECT truename ,NULLIF (truename,NULL) from users u ;

7、字符串函數(shù)

-- 連接字符串
SELECT CONCAT(loginname,username)  FROM users u ;
SELECT CONCAT_WS(',',loginname,username) from users u ;
-- 統(tǒng)計(jì)長(zhǎng)度
SELECT username ,LENGTH (username) FROM users u ;-- 轉(zhuǎn)換大小寫(xiě)
SELECT LOWER('ABC')  ;
SELECT UPPER('avc')  ;-- 去除收尾空格
SELECT TRIM('  hadoop   ds   ')  ;-- 字符串分割
SELECT split('aaaa,bbbb,ccc',',')[0];

8、數(shù)據(jù)脫敏函數(shù)

-- hash加密（結(jié)果是16進(jìn)制字符串）
SELECT mask_hash('hadoop');

9、其他函數(shù)

-- hash加密  結(jié)果是數(shù)字
SELECT hash('aaahsdd');-- 當(dāng)前用戶
SELECT CURRENT_USER();--當(dāng)前數(shù)據(jù)庫(kù)
SELECT CURRENT_DATABASE();--hive版本
SELECT VERSION(); --計(jì)算md5
SELECT MD5('HADOOP');