hive列转行函数collect_list和COLLECT_SET

hive函数

COLLECT_SET(列名)将分组中的某列转为一个数组返回，用于列转行，去重

collect_list(列名)用于列转行，不去重

collect_list(列名)[0]取分组后数组的第一个

例如，table1

id name

111 张三

111 李四

111 王五

111 张三

	select id,collect_list(name)
	from table1
	group by id

collect_list(name)

结果 111 ["张三","李四","王五","张三"]

collect_set(name)

结果 111 ["张三","李四","王五"]

collect_set(name)[0] 取分组后数组的第一个

结果 111 ["张三"]

select id,concat_ws('_'，collect_list(name)）
	from table1
	group by id

结果

111 张三_李四_王五_张三

附加

COLLECT_SET(
  CASE WRNT_MD 
			WHEN '1' THEN 'D' 
			WHEN '2' THEN 
				CASE PLEDG_GRTE_FORM 
						WHEN '1' THEN 'C02' 
						WHEN '2' THEN 'C0103' 
						WHEN '3' THEN 'C0102' 
						ELSE 'C99' 
				END 
			WHEN '3' THEN 'B' 
			WHEN '4' THEN 'A' 
	END) AS MX_LIST

结果["C02","B","A"]

2021-11-13