Hive實戰(2)：Hive 函數(4)HiveSQL 高階函數合集實戰（二）集合相關、URL相關、JSON相關、列轉行相關

來源:https://mp.weixin.qq.com/s/PLWovsMDxO0wUrDTMaOh4w

集合

collect_set：使用頻率 ★★★★★

將分組內的數據放入到一個集合中，具有去重的功能；

1 --統計每個用戶具體哪些天訪問過
2 select
3   user_id,
4   collect_set(visit_date) over(partition by user_id) as visit_date_set 
5 from wedw_tmp.tmp_url_info

collect_list：使用頻率 ★★★★★

和collect_set一樣，但是沒有去重功能

1 select
2   user_id,
3   collect_set(visit_date) over(partition by user_id) as visit_date_set 
4 from wedw_tmp.tmp_url_info
5
6 --如下圖可見，user2在2020-05-15號多次訪問，這里也算進去了

sort_array：使用頻率 ★★★

數組內排序；通常結合collect_set或者collect_list使用；

如collect_list為例子，可以發現日期并不是按照順序組合的，這里有需求需要按照時間升序的方式來組合

1 --按照時間升序來組合
2 select
3   user_id,
4   sort_array(collect_list(visit_date) over(partition by user_id)) as visit_date_set 
5 from wedw_tmp.tmp_url_info
6 --結果如下圖所示；

如果突然業務方改需求了，想要按照時間降序來組合，那基于上面的sql該如何變通呢？哈哈哈哈，其實沒那么復雜，這里根據沒必要按照sort_array來實現，在collect_list中的分組函數內直接按照visit_date降序即可，這里只是為了演示sort_array如何使用

1 --按照時間降序排序
2 select
3   user_id,
4   collect_list(visit_date) over(partition by user_id order by visit_date desc) as visit_date_set 
5 from wedw_tmp.tmp_url_info

這里還有一個小技巧，對于數值類型統計多列或者數組內的最大值，可以使用sort_array來實現

 1 --具體思路就是先把數值變成負數，然后升序排序即可
 2 select -sort_array(array(-a,-b,-c))[0] as max_value
 3 from (
 4     select 1 as a, 3 as b, 2 as c
 5 ) as data
 6
 7+------------+--+
 8| max_value  |
 9+------------+--+
10| 3          |
11+------------+--+

size：

集合中元素的個數
select size(friends) from test3;

map_keys：

返回map中的key
select map_keys(children) from test3;

map_values:

返回map中的value
select map_values(children) from test3;

array_contains:

判斷array中是否包含某個元素
select array_contains(friends,'bingbing') from test3;

URL相關

parse_url：使用頻率 ★★★★

用于解析url相關的參數,直接上sql

 1 select 
 2 visit_url,
 3 parse_url(visit_url, 'HOST') as url_host, --解析host
 4 parse_url(visit_url, 'PATH') as url_path, --解析path
 5 parse_url(visit_url, 'QUERY') as url_query,--解析請求參數
 6 parse_url(visit_url, 'REF') as url_ref, --解析ref
 7 parse_url(visit_url, 'PROTOCOL') as url_protocol, --解析協議
 8 parse_url(visit_url, 'AUTHORITY') as url_authority,--解析author
 9 parse_url(visit_url, 'FILE') as url_file, --解析filepath
10 parse_url(visit_url, 'USERINFO') as url_user_info --解析userinfo
11 from wedw_tmp.tmp_url_info

reflect：使用頻率 ★★

該函數是利用java的反射來實現一些功能，目前筆者只用到了關于url編解碼

1 --url編碼
2 select 
3 visit_url,
4 reflect("java.net.URLEncoder", "encode", visit_url, "UTF-8") as 
 visit_url_encode
5 from wedw_tmp.tmp_url_info

 1 --url解碼
 2 select 
 3   visit_url,
 4  reflect("java.net.URLDecoder", "decode", visit_url_encode, "UTF-8") as visit_url_decode
 5 from 
 6 (
 7  select 
 8  visit_url,
 9  reflect("java.net.URLEncoder", "encode", visit_url, "UTF-8") as visit_url_encode
10  from wedw_tmp.tmp_url_info
11 )t

JSON相關

get_json_object：使用頻率 ★★★★★

通常用于獲取json字符串中的key,如果不存在則返回null

1 select 
2  get_json_object(json_data,'$.user_id') as user_id,
3  get_json_object(json_data,'$.age') as age --不存在age，則返回null
4 from 
5 (
6   select 
7      concat('{"user_id":"',user_id,'"}') as json_data
8   from wedw_tmp.tmp_url_info
9 )t

列轉行相關

explode：使用頻率 ★★★★★

列轉行，通常是將一個數組內的元素打開，拆成多行

 1 --簡單例子
 2 select  explode(array(1,2,3,4,5))
 3+------+--+
 4| col  |
 5+------+--+
 6| 1    |
 7| 2    |
 8| 3    |
 9| 4    |
10| 5    |
11+------+-
12 --結合lateral view 使用
13 select 
14   get_json_object(user,'$.user_id')
15 from 
16(
17  select 
18   distinct collect_set(concat('{"user_id":"',user_id,'"}')) over(partition by year(visit_date)) as user_list
19  from wedw_tmp.tmp_url_info
20 )t
21 lateral view explode(user_list) user_list as user

posted @ 2021-01-02 23:15 秋華閱讀(528) 評論(0) 收藏舉報

刷新頁面返回頂部

秋華

Hive實戰(2)：Hive 函數(4)HiveSQL 高階函數合集實戰（二）集合相關、URL相關、JSON相關、列轉行相關

集合