oraclesql去重

㈠ sql中级进阶(三)hive的 collect_set 与oracle的wm_concat

Oracle的wm_concat用于多行合并为一行，原始版本使用逗号链接。例如：

（1）原始版本: 'aaa,bbb,ccc,ddd,eee'

修改版本使用'|'链接，例如：

（2）修改版本: 'aaa|bbb|ccc|ddd|eee'

（2）修改版本2: 'aaa|bbb|ccc|ddd|eee'，并去重。

Oracle的SQL使用REGEXP_SUBSTR函数来一行拆分成多行。其格式为：

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

参数说明如下：

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


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


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


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


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

Hive的collect_set函数用于去重地将同一分组的不同行数据聚合成一个集合。例如，创建原数据表：

CREATE TABLE stud (name string, area string, course string, score int);

向原数据表插入数据：

INSERT INTO TABLE stud VALUES('zhang3','bj','math',88);

查询表中数据：

SELECT * FROM stud;

将同一分组的数据聚合成一个集合：

SELECT course, collect_set(area), avg(score) FROM stud GROUP BY course;

使用下标可以取某一个集合元素：

SELECT course, collect_set(area)[0], avg(score) FROM stud GROUP BY course;

通常，此过程的最终写法可以使用concat_ws函数连接集合中的元素，例如：

concat_ws(',', collect_set(area))

输出结果为：sh,bj

连接符号也可以改为其他字符，例如'|'。

总结：Oracle和Hive中wm_concat与collect_set、collect_list函数的主要功能相似，都是用于数据聚合处理。它们的不同在于语法和应用场景。这些函数在数据处理和分析中广泛使用，提供了有效的方法来管理多行数据。