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函數的主要功能相似，都是用於數據聚合處理。它們的不同在於語法和應用場景。這些函數在數據處理和分析中廣泛使用，提供了有效的方法來管理多行數據。