如何在PL/SQL中按ID和NAME合并集合数据

需求说明 #

现有两个包含VARCHAR_NTT类型嵌套表的数据集，需将二者合并后，按ID和NAME分组，合并对应分组下的所有SUB集合元素（自动去重），重新组装为新的嵌套表，得到聚合后的目标结果。

原测试数据代码 #

CREATE TYPE VARCHAR_NTT AS TABLE OF VARCHAR2(50 CHAR);   

select * FROM( (with test_data1 as 
(
select '1' as ID, 'AAA' as NAME, VARCHAR_NTT('AAA') as SUB from dual
union all  select '2', 'BBB', VARCHAR_NTT('AAA','BBB') from dual
union all  select '3', 'CCC', VARCHAR_NTT('AAA','BBB','CCC') from dual
),
test_data2 as 
(
select '4' as ID, 'DDD' as NAME, VARCHAR_NTT('AAA') as SUB from dual
union all  select '5', 'EEE', VARCHAR_NTT('DDD','EEE') from dual
union all  select '1', 'AAA', VARCHAR_NTT('BBB','CCCC','DDD') from dual
union all  select '2', 'BBB', VARCHAR_NTT('FFF','GGG') from dual
)
SELECT  ID,NAME,SUB FROM test_data1
union all SELECT  ID,NAME,SUB FROM test_data2) a);

解决方案SQL #

CREATE TYPE VARCHAR_NTT AS TABLE OF VARCHAR2(50 CHAR);   

WITH combined_data AS (
    SELECT ID, NAME, SUB FROM test_data1
    UNION ALL
    SELECT ID, NAME, SUB FROM test_data2
),
unpacked_data AS (
    SELECT 
        ID, 
        NAME, 
        column_value AS sub_element
    FROM combined_data, TABLE(SUB)
),
distinct_elements AS (
    SELECT DISTINCT ID, NAME, sub_element
    FROM unpacked_data
)
SELECT 
    ID,
    NAME,
    CAST(COLLECT(sub_element ORDER BY sub_element) AS VARCHAR_NTT) AS SUB
FROM distinct_elements
GROUP BY ID, NAME
ORDER BY ID;

预期结果 #

执行上述SQL后，将得到如下格式的合并结果：

1   AAA VARCHAR_NTT('AAA','BBB','CCCC','DDD')
2   BBB VARCHAR_NTT('AAA','BBB','FFF','GGG')
3   CCC VARCHAR_NTT('AAA','BBB','CCC')
4   DDD VARCHAR_NTT('AAA')
5   EEE VARCHAR_NTT('DDD','EEE')

核心步骤解释 #

1. 合并原始数据：通过UNION ALL整合两个数据集的所有行
2. 展开嵌套表：用TABLE()函数将嵌套表SUB拆分为单行元素，方便后续去重和聚合
3. 元素去重：对每个ID+NAME分组下的集合元素做去重处理
4. 重新聚合嵌套表：用COLLECT()将去重后的元素重新组装为嵌套表，ORDER BY保证元素有序，CAST()指定最终类型

内容的提问来源于stack exchange，提问作者Manjunath D