字符串拼接
select concat(col1,'-',col2,'-',id) from demo ;
select concat_ws('-',col1,col2,id) from demo ;
select col1,group_concat(col2,"-") from demo group by col1
其中 json_path 必须以 $ 符号作为开头,使用 . 作为路径分割符。如果路径中包含 . ,则可以使用双引号包围。 使用 [ ] 表示数组下标,从 0 开始
select get_json_string('{"k1":"v1", "k2":"v2"}', "$.k1") as val;
SELECT get_json_string('{"k1":"v1", "my.key":["e1", "e2", "e3"]}', '$."my.key"[1]');
SELECT get_json_string('{"k1.key":{"k2":["v1", "v2"]}}', '$."k1.key".k2[0]');
SELECT get_json_string('[{"k1":"v1"}, {"k2":"v2"}, {"k1":"v3"}, {"k1":"v4"}]', '$.k1[0]');
拆分取第几部分
select split_part("集团-产业公司-天缘", "-", 1);
select split_part("集团-产业公司-天缘-客房部", "-", -1);
模糊匹配
select 'ab' like '%_%';
select 'ab' like '%\_%';
select 'a_b' like '%\_%';
count(distinct)
select * from demo
select approx_count_distinct(col1) from demo;
开窗函数,分组排序后打行标
select *,row_number() over(partition by col2 order by col1) as rn from demo;
select *,rank() over(partition by col2 order by col1) as rn from demo;
select *,dense_rank() over(partition by col2 order by col1) as rn from demo;
分组排序后取第一个
分组排序后取上一个
select *,FIRST_VALUE(col1) over(partition by col2 order by id) as rn from demo;
select *,lag(id,1,0) over(partition by col2 order by col1) as rn from demo;
select idx,airport_code,flight_total,lag(flight_total,2,0) over(partition by airport_code order by idx) as rn from dws_d_prod_quota;