第八章_函数【concat、concat_ws、collect_list、collect_set、行转列】

1. concat
    说明 : 将多个字符串 按照默认分隔符(空) ,拼接成一个字符串
    示例 : select concat('gao','cun');
    结果 : gaocun

2. concat_ws
    说明 : 将多个字符串 或者array, 按照指定分隔符(第一个参数), 拼接成一个字符串
    示例 : select concat_ws('-','gao','cun',split('da,wang',','));
    结果 : gao-cun-da-wang

3. collect_set (聚合函数 UDAF)
    说明 : 将分组内 指定字段封装成一个set(对元素去重) 返回
    示例 :

4. collect_list (聚合函数 UDAF)
    说明 : 将分组内 指定字段封装成一个list(对元素不去重) 返回
    示例 :

5. group_concat (mysql 中函数)
    说明 : group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
    示例 : select name
         , group_concat(distinct friend order by friend desc separator '@') as friends1
         , group_concat(friend) as friends2
    from (
             select '刘备' as name, '张飞' as friend
             union all
             select '刘备' as name, '张飞' as friend
             union all
             select '刘备' as name, '关羽' as friend
             union all
             select '刘备' as name, '赵云' as friend
             union all
             select '曹操' as name, '许褚' as friend
             union all
             select '曹操' as name, '荀彧' as friend
         ) as t1
    group by name;
-- 结果
name    friends1        friends2
刘备     赵云@张飞@关羽   张飞,张飞,关羽,赵云
曹操     许褚@荀彧      许褚,荀彧

6. 行转列
-- 人员表
name   friend
刘备    张飞
刘备    张飞
刘备    关羽
刘备    赵云
曹操    许褚
曹操    荀彧
-- 统计结果
name    friends
刘备     张飞、关羽、赵云
曹操     许褚、荀彧
-- 执行sql
with t1 as (
select '刘备' as name,'张飞' as friend union all
select '刘备' as name,'张飞' as friend union all
select '刘备' as name,'关羽' as friend union all
select '刘备' as name,'赵云' as friend union all
select '曹操' as name,'许褚' as friend union all
select '曹操' as name,'荀彧' as friend )
select
name
,concat_ws(',', collect_set(friend)) as friends1 -- 对元素去重
,concat_ws(',', collect_list(friend)) as friends2 -- 对元素不去重

from t1
group by name;
-- 结果
name    friends1        friends2
刘备    张飞,关羽,赵云     张飞,张飞,关羽,赵云
曹操    许褚,荀彧         许褚,荀彧