從資料庫取N個字段,然後組合到一起用“,”分割顯示,起初想到用CONCAT()來處理,好是麻煩,沒想到在手冊裡居然有提到CONCAT_WS(),非常好用。

CONCAT_WS(separator, str1, str2,...)

它是一個特殊形式的CONCAT(),第一個參數剩餘參數間的分隔符,分隔符可以是與剩餘參數一樣的字串,如果分隔符號是NULL,則回傳值也會為NULL,這個函數會跳過分隔符號參數後的任何NULL 和空字串,分隔符號將會被加到被連接的字串之間。

簡單例子如下:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
       -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
       -> 'First name,Last Name'

Hive中collect相關的函數有collect_list和collect_set。

它們都是將分組中的某列轉為一個陣列傳回,不同的是collect_list不去重而collect_set去重。

做簡單的實驗加深理解,創建一張實驗用表,存放用戶每天點播影片的記錄:

create table t_visit_video (
    username string,
    video_name string
) partitioned by (day string)
row format delimited fields terminated by ',';

在本機檔案系統建立測試資料檔:

张三,大唐双龙传
李四,天下无贼
张三,神探狄仁杰
李四,霸王别姬
李四,霸王别姬
王五,机器人总动员
王五,放牛班的春天
王五,盗梦空间

將資料載入到Hive表:

load data local inpath '/root/hive/visit.data' into table t_visit_video partition (day='20180516');

按用戶分組,取出每位用戶每天看過的所有影片的名字:

select username, collect_list(video_name) from t_visit_video group by username ;
张三  ["大唐双龙传","神探狄仁杰"]
李四  ["天下无贼","霸王别姬","霸王别姬"]
…………

但上面的查詢結果有點問題,因為霸王別姬實在太好看了,所以李四這傢伙看了兩遍,這直接就導致得到的觀看過視頻列表有重複的,所以應該增加去重,使用collect_set,其與collect_list的差別就是會去重:

select username, collect_set(video_name) from t_visit_video group by username;
张三  ["大唐双龙传","神探狄仁杰"]
李四  ["天下无贼","霸王别姬"]
…………

李四的觀看紀錄中霸王別姬只出現了一次,實現了去重效果。

突破group by限制

也可以利用collect來突破group by的限制,Hive中在group by查詢的時候要求出現在select後面的列都必須是出現在group by後面的,即select列必須是作為分組依據的列,但是有的時候我們想根據A進行分組然後隨便取出每個分組中的一個B,代入到這個實驗中就是按照用戶進行分組,然後隨便拿出一個他看過的視頻名稱即可:

select username, collect_list(video_name)[0] from t_visit_video group by username;
张三  大唐双龙传
李四 天下无贼
…………

video_name不是分組列,依然能夠取出這列中的資料。

摘錄彙整於:

HTTPS://嗚嗚嗚.cn blog上.com/loco有/archive/2006/10/28/542751.HTML

HTTPS://嗚嗚嗚.cn blog上.com/曹操11001100/怕/9043946.HTML

發表評論