sort_array 和 reflect

create table  tmp_url_info(
 user_id string comment "用户id",
 visit_url string comment "访问url",
 visit_cnt int comment "浏览次数/pv",
visit_time timestamp comment "浏览时间",
 visit_date string comment "浏览日期"
)
row format delimited
fields terminated by ','
stored as textfile;



select
  user_id,
  collect_set(visit_date) over(partition by user_id) as visit_date_set 
from tmp_url_info;



select
  user_id,
  collect_list(visit_date) over(partition by user_id) as visit_date_set 
from tmp_url_info;




 select -sort_array(array(-a,-b,-c))[0] ,
 -sort_array(array(-a,-b,-c))[1],
 -sort_array(array(-a,-b,-c))[2]
 from (
    select 1 as a, 3 as b, 2 as c
 ) as data;


 select -sort_array(array(-a,-b,-c))[0] as max_value
 from (
    select 1 as a, 3 as b, 2 as c
 ) as data;


select 
visit_url,
parse_url(visit_url, 'HOST') as url_host, --解析host
parse_url(visit_url, 'PATH') as url_path, --解析path
parse_url(visit_url, 'QUERY') as url_query,--解析请求参数
parse_url(visit_url, 'REF') as url_ref, --解析ref
parse_url(visit_url, 'PROTOCOL') as url_protocol, --解析协议
parse_url(visit_url, 'AUTHORITY') as url_authority,--解析author
parse_url(visit_url, 'FILE') as url_file, --解析filepath
parse_url(visit_url, 'USERINFO') as url_user_info --解析userinfo
from tmp_url_info;

select 
reflect("java.net.URLEncoder", "encode", visit_url, "UTF-8") as visit_url_encode
from tmp_url_info;

--url解码
select 
  visit_url,
 reflect("java.net.URLDecoder", "decode", visit_url_encode, "UTF-8") as visit_url_decode
from 
(
  select 
  visit_url,
  reflect("java.net.URLEncoder", "encode", visit_url, "UTF-8") as visit_url_encode
  from wedw_tmp.tmp_url_info
)t