不同表多字段唯一匹配值计数的实现难题

表结构说明 #

我有两张存储产品信息的表，包含字段：发票号（Num）、产品编码（Code）、厂商（Developer）、Brand、Model、Article，其中Brand和Article可能为NULL。

Table1 数据 #

+-------------+--------+-----------+-------+---------+---------+
| Num         | Code   | Developer | Brand | Model   | Article |
+-------------+--------+-----------+-------+---------+---------+
| 111/111/111 | 0803   | Dev1      | Bra1  | Mod1    | Art1    |
| 222/222/222 | 0706   | Dev2      | Bra2  | Mod2    | Art2    |
| 222/222/222 | 0706   | Dev2      | Bra2  | Mod2    | Art3    |
| 222/222/222 | 0706   | Dev2      | Bra2  | Mod3    | Art5    |
| 333/333/333 | 0717   | Dev3      | Bra3  | Mod4    | Art4    |
| 333/333/333 | 0717   | Dev3      | Bra3  | Mod4    | Art6    |
| 444/444/444 | 0805   | Dev1      | Bra1  | Mod1    | Art1    |
| 444/444/444 | 0805   | Dev1      | Bra1  | Mod1    | Art7    |
+-------------+--------+-----------+-------+---------+---------+

Table2 数据 #

+-------------+--------+-----------+-------+---------+---------+
| Num         | Code   | Developer | Brand | Model   | Article |
+-------------+--------+-----------+-------+---------+---------+
| 666/666/666 | 0803   | Dev1      | Bra1  | Mod1    | Art1    |
| 777/777/777 | 0706   | Dev2      | Bra2  | Mod7    | Art7    |
| 777/777/777 | 0706   | Dev2      | Bra2  | Mod7    | Art7    |
| 888/888/888 | 0717   | Dev3      | Bra2  | Mod4    | Art4    |
| 888/888/888 | 0717   | Dev3      | Bra3  | Mod4    | Art4    |
| 888/888/888 | 0717   | Dev3      | Bra3  | Mod8    | Art8    |
| 999/999/999 | 0805   | Dev1      | Bra1  | Mod1    | Art1    |
| 999/999/999 | 0805   | Dev1      | Bra1  | Mod1    | Art7    |
+-------------+--------+-----------+-------+---------+---------+

已完成的步骤 #

通过Code和Developer字段关联两表，使用listagg函数聚合Model、Article字段，得到以下结果：

+-------------+-------------+-----------+------------+------------+----------------+------------+
| Num_Tab1    | Num_Tab2    | Developer | Model_Tab1 | Model_Tab2 | Art_Tab1       | Art_Tab2   |
+-------------+-------------+-----------+------------+------------+----------------+------------+
| 111/111/111 | 666/666/666 | Dev1      | Mod1       | Mod1       | Art1           | Art1       |
| 222/222/222 | 777/777/777 | Dev2      | Mod2;Mod3  | Mod7       | Art2;Art3;Art5 | Art7       |
| 333/333/333 | 888/888/888 | Dev3      | Mod4       | Mod4;Mod8  | Art4;Art6      | Art4;Art8  |
| 444/444/444 | 999/999/999 | Dev1      | Mod1       | Mod1       | Art1;Art7      | Art1;Art7  |
+-------------+-------------+-----------+------------+------------+----------------+------------+

需求 #

需要为每对发票统计Model、Article的唯一匹配值数量，预期结果如下：

+-------------+-------------+------+------------+------------+----------------+------------+-------+-----+
| Num_Tab1    | Num_Tab2    | Dev  | Model_Tab1 | Model_Tab2 | Art_Tab1       | Art_Tab2   | Mod_c |Art_c|
+-------------+-------------+------+------------+------------+----------------+------------+-------+-----+
| 111/111/111 | 666/666/666 | Dev1 | Mod1       | Mod1       | Art1           | Art1       | 1     | 1   |
| 222/222/222 | 777/777/777 | Dev2 | Mod2;Mod3  | Mod7       | Art2;Art3;Art5 | Art7       | 0     | 0   |
| 333/333/333 | 888/888/888 | Dev3 | Mod4       | Mod4;Mod8  | Art4;Art6      | Art4;Art8  | 1     | 1   |
| 444/444/444 | 999/999/999 | Dev1 | Mod1       | Mod1       | Art1;Art7      | Art1;Art7  | 1     | 2   | 
+-------------+-------------+------+------------+------------+----------------+------------+-------+-----+

尝试过的方法及问题 #

• 使用regexp_count()：将一个表的聚合字段拆分为匹配模式（用|分隔），统计另一个表字段中的匹配数。但发票条目过多时，会触发**ORA-12733（正则表达式过长）**错误。
• 子查询关联统计：尝试在主查询中嵌入子查询统计Model匹配数，但因未关联外部发票号，导致报错或结果不符合预期。
• 子查询放入FROM子句：调整结构后仍无法得到正确的匹配计数。

解决方案 #

方法1：利用集合类型统计交集数量 #

先对每个发票的Model、Article去重，存储为集合类型，再统计两个集合的交集元素数：

WITH tab1_unique AS (
    SELECT 
        Num AS Num_Tab1,
        Code,
        Developer,
        LISTAGG(DISTINCT Model, ';') WITHIN GROUP (ORDER BY Model) AS Model_Tab1,
        LISTAGG(DISTINCT Article, ';') WITHIN GROUP (ORDER BY Article) AS Art_Tab1,
        COLLECT(DISTINCT Model) AS model_set_tab1,
        COLLECT(DISTINCT Article) AS article_set_tab1
    FROM Table1
    GROUP BY Num, Code, Developer
),
tab2_unique AS (
    SELECT 
        Num AS Num_Tab2,
        Code,
        Developer,
        LISTAGG(DISTINCT Model, ';') WITHIN GROUP (ORDER BY Model) AS Model_Tab2,
        LISTAGG(DISTINCT Article, ';') WITHIN GROUP (ORDER BY Article) AS Art_Tab2,
        COLLECT(DISTINCT Model) AS model_set_tab2,
        COLLECT(DISTINCT Article) AS article_set_tab2
    FROM Table2
    GROUP BY Num, Code, Developer
)
SELECT 
    t1.Num_Tab1,
    t2.Num_Tab2,
    t1.Developer AS Dev,
    t1.Model_Tab1,
    t2.Model_Tab2,
    t1.Art_Tab1,
    t2.Art_Tab2,
    (SELECT COUNT(*) 
     FROM TABLE(t1.model_set_tab1) m1 
     JOIN TABLE(t2.model_set_tab2) m2 ON m1.COLUMN_VALUE = m2.COLUMN_VALUE) AS Mod_c,
    (SELECT COUNT(*) 
     FROM TABLE(t1.article_set_tab1) a1 
     JOIN TABLE(t2.article_set_tab2) a2 ON a1.COLUMN_VALUE = a2.COLUMN_VALUE) AS Art_c
FROM tab1_unique t1
JOIN tab2_unique t2 ON t1.Code = t2.Code AND t1.Developer = t2.Developer
ORDER BY t1.Num_Tab1;

方法2：基于去重后的明细关联统计 #

先提取每个发票的唯一Model和Article，再通过关联统计交集数量，同时完成聚合：

WITH tab1_unique AS (
    SELECT DISTINCT Num AS Num_Tab1, Code, Developer, Model, Article
    FROM Table1
),
tab2_unique AS (
    SELECT DISTINCT Num AS Num_Tab2, Code, Developer, Model, Article
    FROM Table2
)
SELECT 
    t1.Num_Tab1,
    t2.Num_Tab2,
    t1.Developer AS Dev,
    LISTAGG(DISTINCT t1.Model, ';') WITHIN GROUP (ORDER BY t1.Model) AS Model_Tab1,
    LISTAGG(DISTINCT t2.Model, ';') WITHIN GROUP (ORDER BY t2.Model) AS Model_Tab2,
    LISTAGG(DISTINCT t1.Article, ';') WITHIN GROUP (ORDER BY t1.Article) AS Art_Tab1,
    LISTAGG(DISTINCT t2.Article, ';') WITHIN GROUP (ORDER BY t2.Article) AS Art_Tab2,
    COUNT(DISTINCT CASE WHEN t1.Model = t2.Model THEN t1.Model END) AS Mod_c,
    COUNT(DISTINCT CASE WHEN t1.Article = t2.Article THEN t1.Article END) AS Art_c
FROM tab1_unique t1
JOIN tab2_unique t2 ON t1.Code = t2.Code AND t1.Developer = t2.Developer
GROUP BY t1.Num_Tab1, t2.Num_Tab2, t1.Developer
ORDER BY t1.Num_Tab1;

方案说明 #

• 两种方法均从原始明细数据出发，先去重再处理，避免了先聚合后拆分的长度限制问题。
• 方法1利用Oracle集合类型存储唯一值，通过集合交集统计匹配数；方法2直接在关联后的明细中用条件计数统计交集，逻辑更直观。

内容的提问来源于stack exchange，提问作者Roman Lukyanov