Funções de mesclagem de várias linhas collect_set, collect_list, concat_ws em sql

Pegue N campos do banco de dados, combine-os e use "," para separar a exibição. A princípio pensei em usar CONCAT() para processá-lo, mas foi inesperado, CONCAT_WS() foi mencionado no. manual, que é muito fácil de usar.

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

É uma forma especial de CONCAT(). O primeiro parâmetro é o delimitador entre os parâmetros restantes. O delimitador pode ser a mesma string dos parâmetros restantes. saltará qualquer string NULL e vazia após o parâmetro delimitador, o delimitador será adicionado entre as strings que estão sendo concatenadas.

Um exemplo simples é o seguinte:

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'

As funções relacionadas à coleta no Hive incluem collect_list e collect_set.

Ambos convertem uma coluna do agrupamento em um array e a retornam. A diferença é que collect_list não remove duplicatas, mas collect_set sim.

Faça um experimento simples para aprofundar seu entendimento e crie uma tabela experimental para armazenar os registros dos vídeos diários sob demanda dos usuários:

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

Crie um arquivo de dados de teste no sistema de arquivos local:

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

Carregue dados na tabela Hive:

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

Agrupe por usuário e recupere os nomes de todos os vídeos assistidos por cada usuário todos os dias:

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

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

No entanto, há um problema com os resultados da consulta acima. Como Farewell My Concubine é tão bom, Li Si assistiu duas vezes. Isso leva diretamente a duplicações na lista de vídeos assistidos, portanto, a duplicação deve ser adicionada e o conjunto_de_coleção deve ser usado. A diferença entre ele e collect_list é que ele removerá duplicatas:

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

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

Farewell My Concubine aparece apenas uma vez nos registros de visualização de Li Si, alcançando o efeito de desduplicação.

Romper o grupo por restrições

Você também pode usar o collect para romper as restrições do group by. No Hive, ao consultar o group by, é necessário que as colunas que aparecem após o select apareçam após o group by, ou seja, a coluna select deve ser a coluna utilizada como. a base para o agrupamento, mas algumas vezes queremos agrupar de acordo com A e, em seguida, retirar aleatoriamente um B em cada grupo. Neste experimento, agrupamos por usuário e, em seguida, escolhemos aleatoriamente o nome de um vídeo que ele assistiu:

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

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

video_name não é uma coluna de agrupamento e os dados nesta coluna ainda podem ser recuperados.

Trechos resumidos em:

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

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