Funzioni di unione su più righe Collect_set, Collect_list, concat_ws in SQL

Prendi N campi dal database, quindi combinali insieme e usa "," per separare la visualizzazione. Inizialmente, ho pensato di utilizzare CONCAT() per elaborarlo, ma inaspettatamente è stato menzionato CONCAT_WS() manuale, che è molto facile da usare.

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

È una forma speciale di CONCAT(). Il primo parametro è il delimitatore tra i parametri rimanenti. Il delimitatore può essere la stessa stringa dei parametri rimanenti. Se il delimitatore è NULL, anche il valore restituito sarà NULL salterà qualsiasi stringa NULL e vuota dopo il parametro delimitatore, il delimitatore verrà aggiunto tra le stringhe da concatenare.

Un semplice esempio è il seguente:

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'

Le funzioni relative alla raccolta in Hive includono Collect_list e Collect_set.

Entrambi convertono una colonna nel raggruppamento in un array e la restituiscono. La differenza è che Collect_list non rimuove i duplicati ma Collect_set sì.

Fai un semplice esperimento per approfondire la tua comprensione e crea una tabella sperimentale per archiviare i record dei video on-demand giornalieri degli utenti:

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

Creare un file di dati di test sul file system locale:

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

Carica i dati nella tabella Hive:

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

Raggruppa per utente e recupera i nomi di tutti i video guardati da ciascun utente ogni giorno:

select username, collect_list(video_name) from t_visit_video group by username ;

张三  ["大唐双龙传","神探狄仁杰"]
李四  ["天下无贼","霸王别姬","霸王别姬"]
…………

Tuttavia, c'è un problema con i risultati della query di cui sopra. Poiché Farewell My Concubine è così bello, Li Si lo ha guardato due volte. Ciò porta direttamente a duplicati nell'elenco dei video guardati, quindi è necessario aggiungere la duplicazione e utilizzare Collect_set la differenza tra questo e collector_list è che rimuoverà i duplicati:

select username, collect_set(video_name) from t_visit_video group by username;

张三  ["大唐双龙传","神探狄仁杰"]
李四  ["天下无贼","霸王别姬"]
…………

Addio mia concubina appare solo una volta nei registri di visualizzazione di Li Si, ottenendo l'effetto di deduplicazione.

Supera il gruppo in base alle restrizioni

È inoltre possibile utilizzare la raccolta per superare le restrizioni del raggruppamento per. In Hive, quando si esegue una query su raggruppamento per, è necessario che le colonne visualizzate dopo la selezione debbano apparire dopo il raggruppamento per, ovvero la colonna di selezione deve essere la colonna utilizzata come. la base per il raggruppamento, ma alcune volte vogliamo raggruppare in base ad A e poi estrarre casualmente una B in ciascun gruppo. In questo esperimento, raggruppiamo per utente e poi scegliamo casualmente il nome di un video che ha guardato:

select username, collect_list(video_name)[0] from t_visit_video group by username;

张三  大唐双龙传
李四 天下无贼
…………

video_name non è una colonna di raggruppamento e i dati in questa colonna possono comunque essere recuperati.

Estratti riassunti in:

HTTPS://wuwuwu.cn blog on.com/loco有/archive/2006/10/28/542751.HTML

HTTPS://wuwuwu.cn blog on.com/Cao Cao11001100/fear/9043946.HTML