一、内置函数
1.数学函数
-- 四舍五入函数
select round(3.1415926,4); -- 四舍五入 保留4位小数 3.1416

-- 获取 [1,100]之间的随机数
select `floor`(rand() * 100) + 1;

-- 向下取整
select floor(2.8999); -- 2
1
2
3
4
5
6
7
8
2.字符串函数
-- 字符串拼接
select concat(rand(),'-',sid) as sid, sname from student;
select concat(rand(),'-',sid) as sid, sname from student;


-- 字符串拼接，带分隔符
select concat_ws('-','2022','10','15');
select log10(100)


-- 字符串截取
select substr('2022-12-23 10:13:45',1,4); -- 2022
select substr('2022-12-23 10:13:45',6,6); -- 12


-- 字符串替换
select regexp_replace('foobar', 'oo|ar', '');


-- 解析URL
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'PATH');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k1');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k2');


-- 字符串切割
select split('2022-12-23','-');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
3.日期函数
-- 获取当前的时间
select `current_date`(); -- 2022-10-25

-- 通过unix_timestamp和from_unixtime两个日期函，将不规则的时间格式转换成规则的格式
select from_unixtime(unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss'),'yyyy-MM-dd HH:mm:ss');

-- 将日期转为指定的格式
select date_format('2022-1-1 1:1:1','yyyy-MM-dd HH:mm:ss') -- 2022-01-01 01:01:01


select to_date('2022-01-01 01:01:01'); -- 年月日
select year('2022-01-01 01:01:01'); -- 年
select month('2022-01-01 01:01:01'); -- 月
select day('2022-01-01 01:01:01'); -- 日
select hour('2022-01-01 01:01:01'); -- 小时
select minute('2022-01-01 01:01:01'); -- 分钟
select second('2022-01-01 01:01:01'); -- 秒

select quarter('2022-10-15 01:01:01'); -- 季度
select weekofyear('2022-10-15 01:01:01'); -- 获取今年的第几周
select `dayofweek`('2022-10-16 01:01:01'); -- 获取今天是周几(1-7)

select date_add('2022-10-15',10); -- 将日期向后推10天
select date_add('2022-10-15',-10);-- 将日期向前推10天
select date_sub('2022-10-15',10); -- 将日期向前推10天

select abs(datediff('2022-02-13','2022-10-15')); -- 求日期之间的差值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
二、 条件函数
1.If 语句
-- 标记每个学生的及格和不及格的情况
select *, if(sscore >= 60,'及格','不及格') as flag from score;

-- 同时计算及格和不及格的平均分
select avg(if(sscore>=60,sscore,null)) as avg1,avg(if(sscore<60,sscore,null)) as avg2 from score

1
2
3
4
5
6
2.Case When 语句
方式1

/*
订单号 金额 支付方式
order_id amount payment_type payment_name
1001 50 1 支付宝支付
1002 80 2 微信支付
1003 60 3 余额支付
1004 40 4 货到付款
*/

select
case payment_type
when 1
then '支付宝支付'
when 2
then '微信支付'
when 3
then '余额支付'
else '货到付款'
end as payment_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
方式2，方式2经常用到。

select
*,
case when sscore >= 90 and sscore <= 100
then '优秀'
when sscore >= 80
then '良好'
when sscore >= 60
then '一般'
else
'及格'
end as flag
from score;
1
2
3
4
5
6
7
8
9
10
11
12
三、 类型降转函数
select cast(12.35 as int);
select cast('123' as int);
select cast('2020-12-05' as date);
1
2
3
四、 行转列


-- 1、准备数据
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER

-- 2、创建表
create table emp(
deptno int,
ename string
) row format delimited fields terminated by '\t';

-- 3、加载数据
load data local inpath "/export/data/hivedatas/emp.txt" into table emp;

-- 4、实现功能

-- collect_set(去重)/collect_list(不去重) 可以将每一组的ename存入一个集合中（set集合，list集合）
select deptno, collect_list(ename) from emp group by deptno;

select deptno,concat_ws("|",collect_set(ename)) as ems from emp group by deptno;

select deptno, collect_set(ename)[0] from emp group by deptno; -- 用索引访问

免责申明：

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

《数据治理行业实践白皮书》下载地址：https://fs80.cn/4w2atu

《数栈V6.0产品白皮书》下载地址：https://fs80.cn/cw0iw1

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs

同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack