Oracle中对比另一视图行集后提取seq_id的替代方案

在Oracle数据库中，需从View 1提取符合条件的seq_id，判断逻辑是对比View 2的完整行集顺序。目前已实现通过LISTAGG函数结合GROUP BY seq_id，将View 1的pattern_id按顺序聚合后与View 2匹配的方法，现提供以下几种可行替代方案。

前提说明 #

• View 1的seq_id为连续序列（0,1,2...）
• 每个seq_id下的pattern_id有固定顺序，可通过ROW_NUMBER() OVER()进行排序

示例数据 #

View 1定义及数据

WITH view1(seq_id, pattern_id,rnum) AS (
    SELECT 0 , 1 ,1 FROM dual UNION
    SELECT 0 , 2,2 FROM dual UNION
    SELECT 0 , 3,3 FROM dual UNION
    SELECT 0 , 4,4 FROM dual UNION
    SELECT 1 , 3,5 FROM dual UNION
    SELECT 1 , 4,6 FROM dual UNION
    SELECT 1 , 1,7 FROM dual UNION
    SELECT 1 , 2,8 FROM dual UNION
    SELECT 2 , 2,9 FROM dual UNION
    SELECT 2 , 4,10 FROM dual UNION
    SELECT 2 , 1,11 FROM dual UNION
    SELECT 2 , 3,12 FROM dual 
)
SELECT seq_id AS "seq id", pattern_id AS "pattern" FROM view1 ORDER BY rnum;

输出结果：

seq id   pattern
------   -------
0          1
0          2
0          3
0          4
1          3
1          4
1          1
1          2
2          2
2          4
2          1
2          3

View 2数据

pk_id   pattern id
------  ------------
1        3
2        4
3        1
4        2

期望输出

seq id
------
1

替代方案 #

方案1：窗口函数+全连接对比

通过给每个seq_id下的pattern_id按顺序编号，与View 2的pk_id一一对应，再检查对应位置的pattern_id是否完全匹配，且数量一致。

WITH view1_ranked AS (
    SELECT 
        seq_id,
        pattern_id,
        ROW_NUMBER() OVER(PARTITION BY seq_id ORDER BY rnum) AS seq_rn
    FROM view1
),
view2_ref AS (
    SELECT pk_id, pattern_id FROM view2
)
SELECT v1.seq_id AS "seq id"
FROM view1_ranked v1
FULL JOIN view2_ref v2 
    ON v1.seq_rn = v2.pk_id 
    AND v1.pattern_id = v2.pattern_id
GROUP BY v1.seq_id
HAVING COUNT(v1.pattern_id) = (SELECT COUNT(*) FROM view2)
   AND COUNT(v2.pattern_id) = (SELECT COUNT(*) FROM view2);

方案2：集合运算（MULTISET）

利用Oracle的嵌套表类型，将每个seq_id的pattern_id按顺序收集为集合，直接与View 2的目标集合对比。

首先需定义嵌套表类型（若未存在）：

CREATE TYPE pattern_list AS TABLE OF NUMBER;
/

执行查询：

WITH view1_collected AS (
    SELECT 
        seq_id,
        CAST(COLLECT(pattern_id ORDER BY rnum) AS pattern_list) AS pattern_set
    FROM view1
    GROUP BY seq_id
),
view2_set AS (
    SELECT CAST(COLLECT(pattern_id ORDER BY pk_id) AS pattern_list) AS target_set
    FROM view2
)
SELECT seq_id AS "seq id"
FROM view1_collected, view2_set
WHERE pattern_set = target_set;

注：COLLECT函数的ORDER BY子句需Oracle 12c及以上版本支持，低版本可先对数据排序再收集。

方案3：哈希值对比

将每个seq_id的pattern_id按顺序拼接后计算哈希值，通过对比哈希值判断是否匹配，避免LISTAGG字符串过长的限制。

WITH view1_hash AS (
    SELECT 
        seq_id,
        DBMS_CRYPTO.HASH(
            UTL_RAW.CAST_TO_RAW(LISTAGG(pattern_id, '|') WITHIN GROUP (ORDER BY rnum)),
            DBMS_CRYPTO.HASH_SH256
        ) AS pattern_hash
    FROM view1
    GROUP BY seq_id
),
view2_hash AS (
    SELECT 
        DBMS_CRYPTO.HASH(
            UTL_RAW.CAST_TO_RAW(LISTAGG(pattern_id, '|') WITHIN GROUP (ORDER BY pk_id)),
            DBMS_CRYPTO.HASH_SH256
        ) AS target_hash
    FROM view2
)
SELECT seq_id AS "seq id"
FROM view1_hash, view2_hash
WHERE pattern_hash = target_hash;

注：执行用户需拥有DBMS_CRYPTO权限。

内容的提问来源于stack exchange，提问作者Rajiv A