HiveSQL基础命令04

函数分类

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() 爆炸函数(炸裂函数)

目录

一、字符串相关函数

1.字符串切割函数

2.字符串拼接函数

3.移除两端空格

4.字符串截取

5.转大小写

6.正则替换函数

7.URL地址解析

8.处理json文件

二、日期相关

1.获取当前时间戳

2.获取当前时间

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

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

5.日期格式转换

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

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

8.偏移指定的时间.

三、数字相关

1.求绝对值

2.四舍五入

3.向上取整

4.向下取整

5.随机数

6.平方值

四、转换相关

1.if()

2.isnull()

3.isnotnull()

4.nvl()

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

6.case when

五、脱敏加密相关 

1. 获取哈希值.

2. 加密相关函数.

3. 脱敏相关.

---

一、字符串相关函数

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]