在Mysql中可以通過group_concat()函數(shù)實(shí)現(xiàn)分組字符串拼接，在HIVE SQL中可以使用concat_ws()+collect_set()/collect_list()函數(shù)實(shí)現(xiàn)相同的效果。
實(shí)例：
| a | b | c |
|---|---|---|
| 2014 | B | 9 |
| 2015 | A | 8 |
| 2014 | A | 10 |
| 2015 | B | 7 |
| 2014 | B | 6 |
1.concat_ws+collect_list 非去重拼接
select a
,concat_ws('-',collect_list(b)) as col_b
,concat_ws('-',collect_list(cast(c as string))) as col_c
from tb_name
group by a
;
查詢結(jié)果：
| a | col_b | col_c |
|---|---|---|
| 2014 | B-A-B | 9-10-6 |
| 2015 | A-B | 8-7 |
2.concat_ws+collect_set 去重拼接
select a
,concat_ws('-',collect_set(b)) as col_b
,concat_ws('-',collect_set(cast(c as string))) as col_c
from tb_name
group by a
;
查詢結(jié)果：
| a | col_b | col_c |
|---|---|---|
| 2014 | B-A | 9-10-6 |
| 2015 | A-B | 8-7 |
3.如果在分組拼接的時(shí)候需要保持拼接結(jié)果為有序排序，可以通過以下兩種方式
1）先排序再拼接
select a
,concat_ws('-',collect_set(b)) as col_b
,concat_ws('-',collect_set(cast(c as string))) as col_c
from
(
select a
,b
,c
,row_number() over (partition by a order by b asc) as rn
from tb_name
) t
group by a
;
查詢結(jié)果：
| a | col_b | col_c |
|---|---|---|
| 2014 | A-B | 10-6-9 |
| 2015 | A-B | 8-7 |
select a
,concat_ws('-',collect_list(b)) as col_b
,concat_ws('-',collect_list(cast(c as string))) as col_c
from
(
select a
,b
,c
,row_number() over (partition by a order by b asc) as rn
from tb_name
) t
group by a
;
查詢結(jié)果：(col_b與col_c 的位置對(duì)應(yīng)且col_b中的字符有序)文章來源：http://www.zghlxwxcb.cn/news/detail-657476.html
| a | col_b | col_c |
|---|---|---|
| 2014 | A-B-B | 10-6-9 |
| 2015 | A-B | 8-7 |
2）sort_array（）函數(shù)升序排列文章來源地址http://www.zghlxwxcb.cn/news/detail-657476.html
select a
,concat_ws('-',sort_array(collect_list(b))) as col_b
from tb_name
group by a
;
| a | col_b |
|---|---|
| 2014 | A-B-B |
| 2015 | A-B |
到了這里，關(guān)于HIVE SQL實(shí)現(xiàn)分組字符串拼接concat的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
