分组后的数据多行聚合成一行
| 序号 | id | class | tag |
|---|---|---|---|
| 1 | 1 | AA | f |
| 2 | 1 | BB | f |
| 3 | 1 | CC | f |
| 4 | 1 | DD | f |
| 5 | 1 | EE | f |
| 6 | 2 | f | |
| 7 | 2 | WW | f |
| 8 | 2 | MM | f |
| 序号 | id | class |
|---|---|---|
| 1 | 1 | AA,BB,CC |
| 2 | 2 | QQ,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
