DORIS常用函数

字符串拼接

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;