concat函数_sql中级进阶(三)hive的 collect_set 与oracle的wm_concat

1.oracle的wm_concat (多行合并为一行)

(1)原始版本,逗号链接

后来填了一个重复数据,这是没添加之前的,应该还有一个,644

select 

(2)修改版本,|链接

select d_id,replace(wm_concat(namee),',','|') from EMMM t group by d_id

(2)修改版本2,|链接,并去重

select d_id,replace(wm_concat(distinct(namee)),',','|') from EMMM t group by d_id

2.一行拆分成多行

select regexp_substr('aaa,bbb,ccc,ddd,eee','[^,]+',1,LEVEL,'i') from dual

CONNECT BY LEVEL <= LENGTH('aaa,bbb,ccc,ddd,eee') - LENGTH(REGEXP_REPLACE('aaa,bbb,ccc,ddd,eee', ',', '')) + 1

REGEXP_SUBSTR函数格式如下：

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr ：需要进行正则处理的字符串

__pattern ：进行匹配的正则表达式

__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

__occurrence ：标识第几个匹配组，默认为1

__modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

———————————————

3.hive的 collect_set(去重),collect_list(不去重)

1）创建原数据表

hive (gmall)>

drop table if exists stud;

create table stud (name string, area string, course string, score int);

2）向原数据表中插入数据

hive (gmall)>

insert into table stud values('zhang3','bj','math',88);

insert into table stud values('li4','bj','math',99);

insert into table stud values('wang5','sh','chinese',92);

insert into table stud values('zhao6','sh','chinese',54);

insert into table stud values('tian7','bj','chinese',91);

3）查询表中数据

hive (gmall)> select * from stud;

stud.name stud.area stud.course stud.score

zhang3 bj math 88

li4 bj math 99

wang5 sh chinese 92

zhao6 sh chinese 54

tian7 bj chinese 91

4）把同一分组的不同行的数据聚合成一个集合

hive (gmall)> select course, collect_set(area), avg(score) from stud group by course;

chinese ["sh","bj"] 79.0

math ["bj"] 93.5

5） 用下标可以取某一个

hive (gmall)> select course, collect_set(area)[0], avg(score) from stud group by course;

chinese sh 79.0

math bj 93.5

6. 一般常用最终写法

concat_ws(,, collect_set(area))

sh,bj

用,号相连 也可以改成用|相连

总结:都是一回事情,只不过写法不同,也是一个挺常用的函数