HiveSQL基础命令04_floor hivesql-CSDN博客

本文链接：https://blog.csdn.net/2202_75300139/article/details/135665902

函数分类

UDF: 全称 user defined functions, 也叫: 普通函数.
核心: 一进一出. 即: 给函数10行数据, 处理之后, 返回10行.
例如: abs(), round()...
UDAF: 全称 user defined Aggregate functions, 也叫: 聚合函数.
核心: 多进一出, 即: 给函数10行数据, 处理之后, 返回1行.
例如: count(), max(), sum(), avg(), min()
UDTF: 全称 user defined Table Generating functions, 也叫: 表生成函数.
核心: 一进多出, 即: 给函数1条, 它返回多条.
例如: explode() 爆炸函数(炸裂函数)

3.获取当前秒值，即: 从时间原点(1970年1月1日 00:00:00) 至当前时间的秒值

一、字符串相关函数

1.字符串切割函数

split('字符串')

select split('a2dd3b7cc9','\\d');
select split('a2dd3b7cc9','[0-9]');
----["a","dd","b","cc",""]

2.字符串拼接函数

concat('字符串1','字符串2')

concat_ws('拼接符','字符串1','字符串2'……)

select concat('-', 'aa', 'bb', 11, 'false'); 
--   -aabb11false
select concat_ws('-', 'aa', 'bb', 'cc', array('x', 'y', 'z')); 
-- 只能拼字符串, 或者字符串数组      aa-bb-cc-x-y-z

3.移除两端空格

trim('字符串')

select trim('    aa   bb     ');      
-- 'aa   bb'

4.字符串截取

substr('字符串','位置','长度')

select substr('中国大',2,1);
--  国
select substr('qwer',-2,2);
-- er

5.转大小写

lower() upper()

select lower('ABC123');     -- abc123
select upper('abc123');     -- ABC123

6.正则替换函数

regexp_replace('字符串','正则表达式','替换内容')

select regexp_replace('aa11bb22cc33dd', '\\d+', '#');
-- aa#bb#cc#dd

7.URL地址解析

parse_url('URL地址','参数2')

参数2：HOST , PATH , QUERY

select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'HOST'); -- www.itcast.cn
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'PATH'); -- /index.html
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'QUERY');   
-- username=root&password=123456'
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'QUERY', 'username'); -- root

8.处理json文件

逐个解析

get_json_object('列名','变量标识')

get_json_object('[{'name':'张三','age':'31'},{'name':'李四','age':'22'}]','$.[1].name');

----李四

批量解析

json_tuple(json字符串, '键名1', '键名2', '键名3'...) as (别名1, 别名2, 别名3...)

别名重名可以自动转换

二、日期相关

1.获取当前时间戳

current_timestamp();

2.获取当前时间

current_date();

3.获取当前秒值，即: 从时间原点(1970年1月1日 00:00:00) 至当前时间的秒值

unix_timestamp();

4.把秒值转成对应的时间.

from_unixtime(数值);

5.日期格式转换

to_date(`current_timestamp`())

6.计算两个日期的差值.

select datediff('2024-01-10 00:00:00', '2024-01-05 00:00:00'); -- 结果: 前 - 后, 5

7.从日期中获取对应的内容.

select year('2024-01-10 13:14:21');
select month('2024-01-10 13:14:21');
select day('2024-01-10 13:14:21');
select hour('2024-01-10 13:14:21');
select minute('2024-01-10 13:14:21');
select second('2024-01-10 13:14:21');
select quarter('2024-01-10 13:14:21');

8.偏移指定的时间.

select date_add('2024-01-10 13:14:21', 2); -- 往后推2天, 2024-01-12
select date_sub('2024-01-10 13:14:21', -2); -- 往后推2天, 2024-01-12, subtract: 减法

select date_add('2024-01-10 13:14:21', -2); -- 往前推2天, 2024-01-08
select date_sub('2024-01-10 13:14:21', 2); -- 往前推2天, 2024-01-08, subtract: 减法

三、数字相关

可以在括号里写字段，即使是string类型的值只要是数字（'123'）也可转换

1.求绝对值

abs()

2.四舍五入

round(值,小数位数)

select round(123.456,2); --123.46

select floor(123.4+0.5); --四舍五入原理

3.向上取整

ceil(数值)

select ceil(10.3); --11
select ceil(10.0); --10

4.向下取整

floor(数值)

select floor(10.9); --10

5.随机数

rand() --[0.0 ~ 1.0)

如要产生[n,m]范围内的随机数：

floor(rand()*(m-n+1)+n);

6.平方值

pow(3,2) --9

四、转换相关

1.if()

if(条件,值1,值2) 条件成立返回值1，不成立返回值2

2.isnull()

isnull(值/字段) 返回true/false

3.isnotnull()

返回true/false

4.nvl()

nvl(参数1,参数2)

参数1为空就返回参数2，不为空返回参数1

5.获取众多参数中的第一个非空值

coalesce(值1,值2,值3……)

多列统计无法获取数组类型，图片右上角

6.case when

select
case
when 5 > 3 then '大于'
when 5 < 3 then '小于'
else '等于'
end as col1;

-- case when语法糖, 如果都是等于判断, 且是同一列, 可以简写为如下写法.
select
case 1 -- 应该是: case 列名
when 1 then '星期一'
when 2 then '星期二'
when 3 then '星期三'
else '没有这样的时间'
end as week;

cast()函数, 类型转换.
select cast(10.3 as int); -- 10
select cast(10.6 as int); -- 10
select cast(10 as string); -- '10'
select cast('123' as int); -- 123, 字符串 -> 数字
select cast('123' as boolean); -- true, 非空 '', 非0, 非null
select cast('' as boolean); -- false

五、脱敏加密相关

1. 获取哈希值.

select hash('abc'); -- 96354

2. 加密相关函数.

select md5('Aa1'); -- 698d51a19d8a121ce581499d7b701668
select sha1('Aa1'); -- f55cd2f8d3186c960aa86782452979118760e96d
select sha2('Aa1', 512); -- 2907b40d2cb5527fe522b1c97f90dbf49423dd0cfd411f5d91528bc38c0c2ee9515d981816149d138ffa9d2ac80ef1d0435dc0213bdb85b2f85f446c4a035f3f
describe function extended sha2;
select crc32('Aa1'); -- 2736874553

3. 脱敏相关.

select mask('xyzABC123'); --结果为: xxxXXXnnn, 默认顺序是: 大写字母X, 小写字母x, 数字n
-- 细节: 我想指定小写字母用小来替换. 参1: 要脱敏的字符串. 参2: 大写字母替换符, 参3: 小写字母替换符, 参4: 数字替换符.
select mask('xyzABC123', 'X', '小'); -- xxx小小小nnn

只脱敏前几个字符.
select mask_first_n('ABCabc123', 4); -- XXXxbc123 只处理前 4 个字符.
select mask_show_first_n('ABCabc123', 4); -- ABCaxxnnn 除了前 4 个字符, 其它都处理.
-- 自学: mask_last_n(), mask_show_last_n()

需求: 把13112345678 => 131****5678
select concat(substr('13112345678', 1, 3), mask(substr('13112345678', 4, 4), 'X', 'x', '*'), substr('13112345678', -4));

sort_array()函数, 对数组进行排序, 属于: 集合函数.
select sort_array(array(11, 33, 22, 55)); -- [11,22,33,55]