【FAQ】HIVE多行聚合-concat

【FAQ】HIVE多行聚合- concat 场景

分组后的数据多行聚合成一行

序号idclasstag11AAf21BBf31CCf41DDf51EEf62QQf72WWf82MMf 期望 序号idclass11AA,BB,CC22QQ,WW,MM 实现

两步走

• 先分组
• 再拼接

with temp as (
   select
    id as id,
    class as class,
    sum(tag) as cnt
  from
    `xxx`
  group by
    id,
    class
  order by
    tag desc
)
select
  t.id,
  concat_ws(',', collect_list(t.class))
from
  temp t
group by
  t.id

HIVE语法大全

官方文档
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-json_tuple