#COALESCE
由左到右,返回第一个非空值
select coalesce(1,2,3);
select coalesce(null,2,3);
select coalesce(1,null,3);
select coalesce(null,null,3);
select coalesce(null,null,null);
#EXPLODE
一进多出 属于udtf函数,能将map, array 拆成多行
select explode(array('A','B','C'));
select explode(map('A',10,'B',20,'C',30));
drop table if exists t1;
create table t1(
id int,
name string,
likes array<string>,
address map<string,string>
);
desc formatted t1;
insert into table t1 values
(1,'tim',array('A','B','C'),map('k1','10','k2','20','k3','30')),
(2,'tom',array('z','A','m'),map('a1','2','a2','3','a3','4'));
select * from t1;
select explode(likes) as likes from t1;
select explode(address) as (k,v) from t1;
#LATERAL VIEW 能将 udtf 结果转换成一个视图subView
select * from t1
lateral view explode(likes) t2 as subLikes;
select * from t1
lateral view explode(address) t2 as k,v;
select * from t1
lateral view json_tuple('{"name":"tim","age":12}', 'name','age') t2 as name, age;
#NVL
如果第一个值为空,就去第二个值
select nvl('1', '2');
select nvl(null, '2');
#时间
select date_format('2019-06-24 12:12:12','yyyyMMdd');
select unix_timestamp();
select from_unixtime(1565858389,'yyyy-MM-dd HH:mm:ss');
#天数计算
select date_add('2019-06-29',5);
select date_add('2019-06-29',-5);
select datediff('2019-06-29','2019-06-24');
select date_sub('2019-06-29',5);
#CASE WHEN
select
case
when 'ios' = 'android' then 'android'
when 'ios' = 'ios' then 'iPhone'
else 'PC'
end;
select
case 'other'
when 'android' then 'android'
when 'ios' then 'iPhone'
else 'PC'
end;
drop table if exists t1;
create table t1(
id string,
name string,
code string
);
insert into table t1 values
('1', 'a', '01'),
('2', 'b', '02');
select * from t1;
select
case id
when '1' then name
when '2' then code
end
from t1;
#CONCAT_WS
聚合函数,多字段合并 (行转列)
concat_ws(',', collect_set(name))
concat_ws(',', collect_list(name))
select concat_ws(',', 'hi', 'world','my','my');
select concat('a', ",", 'b');
#if
select if('hi' is not null, 1, 2);
select if(null is not null, 1, 2);
#截取字符串
select substring('2017-04-13',1,7);
#窗口函数 over() 通常搭配 row_number() ,min(),max(),sum()来使用
drop table if exists t1;
create table t1(
id string,
name string,
num int
);
desc formatted t1;
insert into table t1 values
(1,'a',1),
(2,'b',3),
(3,'a',5),
(4,'c',9),
(5,'b',3);
select * from t1;
-- over() 每一行的窗口都一样
SELECT id,name,num,
sum(num) over() sum1
from t1;
id name num sum1
5 b 3 21
4 c 9 21
3 a 5 21
2 b 3 21
1 a 1 21
-- over(order by id) 逐行累计计算
SELECT id,name,num,
sum(num) over(order by id) sum1
from t1;
id name num sum1
1 a 1 1
2 b 3 4
3 a 5 9
4 c 9 18
5 b 3 21
-- over(partition by name) 分区内计算
SELECT id,name,num,
sum(num) over(partition by name) sum1
from t1;
id name num sum1
3 a 5 6
1 a 1 6
5 b 3 6
2 b 3 6
4 c 9 9
-- over(partition by name order by id) 分区内逐行计算
SELECT id,name,num,
sum(num) over(partition by name order by id) sum1
from t1;
id name num sum1
1 a 1 1
3 a 5 6
2 b 3 3
5 b 3 6
4 c 9 9
-- row_number() 行号
SELECT id,name,num,
row_number() over() rownum from t1;
id name num rownum
5 b 3 1
4 c 9 2
3 a 5 3
2 b 3 4
1 a 1 5
SELECT id,name,num,
row_number() over(partition by name) rn
from t1;
id name num rn
3 a 5 1
1 a 1 2
5 b 3 1
2 b 3 2
4 c 9 1
SELECT id,name,num,
row_number() over(partition by name order by id) rn
from t1;
id name num rn
1 a 1 1
3 a 5 2
2 b 3 1
5 b 3 2
4 c 9 1
#regexp_replace()
select regexp_replace("12\r3", "\r", "\\r");
select regexp_replace("12\r3", "\r", "");
select regexp_replace("123", "1", "a");
select regexp_replace('12\r3', '\n|\t|\r', '\\r');
select regexp_replace('12\r3', '\r', '<br/>');