Funciones de combinación de varias filas Collect_set, Collect_list, concat_ws en SQL

Tome N campos de la base de datos y luego combínelos y use "," para separar la visualización. Al principio, pensé en usar CONCAT () para procesarlo, pero fue problemático. Inesperadamente, se mencionó CONCAT_WS (). manual, que es muy fácil de usar.

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

Es una forma especial de CONCAT(). El primer parámetro es el separador entre los parámetros restantes. El separador puede ser la misma cadena que los parámetros restantes. Si el separador es NULL, el valor de retorno también será NULL y esta función. jump. Cualquier cadena NULL y vacía después del parámetro delimitador, el delimitador se agregará entre las cadenas que se concatenan.

Un ejemplo sencillo es el siguiente:

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'

Las funciones relacionadas con la recopilación en Hive incluyen Collect_list y Collect_set.

Ambos convierten una columna de la agrupación en una matriz y la devuelven. La diferencia es que Collect_list no elimina los duplicados, pero Collect_set sí.

Realice un experimento simple para profundizar su comprensión y cree una tabla experimental para almacenar los registros de los videos bajo demanda diarios de los usuarios:

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

Cree un archivo de datos de prueba en el sistema de archivos local:

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

Cargue datos en la tabla de Hive:

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

Agrupe por usuario y recupere los nombres de todos los videos vistos por cada usuario todos los días:

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

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

Sin embargo, hay un problema con los resultados de la consulta anterior. Debido a que Farewell My Concubine es tan bueno, Li Si lo vio dos veces. Esto conduce directamente a duplicados en la lista de videos vistos, por lo que se debe agregar duplicación y se debe usar Collect_set. La diferencia con Collect_list es que eliminará duplicados:

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

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

Farewell My Concubine solo aparece una vez en los registros de visualización de Li Si, logrando el efecto de deduplicación.

Romper grupo por restricciones

También puede usar recopilar para romper las restricciones de agrupar por. En Hive, al consultar agrupar por, se requiere que las columnas que aparecen después de seleccionar aparezcan después de agrupar por, es decir, la columna de selección debe ser la columna utilizada como. la base para agrupar, pero algunas veces queremos agrupar según A y luego sacar aleatoriamente una B en cada grupo. En este experimento, agrupamos por usuario y luego seleccionamos aleatoriamente el nombre de un video que ha visto:

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

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

video_name no es una columna de agrupación y los datos de esta columna aún se pueden recuperar.

Extractos resumidos en:

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

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