Oracle 19c多列优先级关联表：查询正确性及替代方案咨询

现有SQL能否得到预期输出？ #

现有SQL存在表名拼写错误：contry_fact应为COUNTRY_FACT，不修正的话会直接触发“表不存在”的错误。

修正表名后，针对当前给定的测试数据，该SQL可以得到预期输出：

• USA和United States都会关联到US维度，SUM(30+2)=32
• India关联到IN，SUM=6
• United Kingdom关联到UK，SUM=10
• South Korea和Republic of Korea关联到ROK，SUM(2+1)=3
• Germany关联到DE，SUM=4

但该SQL存在潜在风险：如果某条COUNTRY_NAME同时匹配多个不同维度的记录（比如一个名称既是A国的SHORT_NAME，又是B国的FULL_NAME），会导致该事实记录被重复统计，最终SUM值偏大。

其他实现方式（Oracle 19c） #

方式一：按优先级依次左关联，确保单条记录仅匹配一次 #

通过多级LEFT JOIN，只有前一级匹配失败时才尝试下一级，避免重复统计：

SELECT matched.COUNTRY_ID, SUM(fact.VALUE) AS VALUE
FROM COUNTRY_FACT fact
LEFT JOIN COUNTRY_DIM dim1 
  ON UPPER(fact.COUNTRY_NAME) = UPPER(dim1.SHORT_NAME)
LEFT JOIN COUNTRY_DIM dim2 
  ON UPPER(fact.COUNTRY_NAME) = UPPER(dim2.FULL_NAME) 
  AND dim1.ID IS NULL
LEFT JOIN COUNTRY_DIM dim3 
  ON dim3.ALTERNATE_NAME IS NOT NULL 
  AND UPPER(fact.COUNTRY_NAME) = UPPER(dim3.ALTERNATE_NAME) 
  AND dim1.ID IS NULL 
  AND dim2.ID IS NULL
CROSS APPLY (
    SELECT COALESCE(dim1.ID, dim2.ID, dim3.ID) AS COUNTRY_ID
) matched
WHERE matched.COUNTRY_ID IS NOT NULL
GROUP BY matched.COUNTRY_ID

方式二：用ROW_NUMBER标记优先级，取最高匹配项 #

通过窗口函数给匹配结果按优先级排序，仅保留每个国家名的最高优先级匹配，彻底避免重复关联：

WITH matched_facts AS (
    SELECT 
        fact.VALUE,
        dim.ID AS COUNTRY_ID,
        ROW_NUMBER() OVER (
            PARTITION BY fact.COUNTRY_NAME 
            ORDER BY 
                CASE 
                    WHEN UPPER(fact.COUNTRY_NAME) = UPPER(dim.SHORT_NAME) THEN 1
                    WHEN UPPER(fact.COUNTRY_NAME) = UPPER(dim.FULL_NAME) THEN 2
                    WHEN dim.ALTERNATE_NAME IS NOT NULL AND UPPER(fact.COUNTRY_NAME) = UPPER(dim.ALTERNATE_NAME) THEN 3
                END
        ) AS rn
    FROM COUNTRY_FACT fact
    JOIN COUNTRY_DIM dim 
      ON UPPER(fact.COUNTRY_NAME) = UPPER(dim.SHORT_NAME)
      OR UPPER(fact.COUNTRY_NAME) = UPPER(dim.FULL_NAME)
      OR (dim.ALTERNATE_NAME IS NOT NULL AND UPPER(fact.COUNTRY_NAME) = UPPER(dim.ALTERNATE_NAME))
)
SELECT COUNTRY_ID, SUM(VALUE) AS VALUE
FROM matched_facts
WHERE rn = 1
GROUP BY COUNTRY_ID

方式三：CASE表达式直接匹配维度ID #

通过CASE按优先级直接返回匹配的维度ID，适合维度表中名称与ID一一对应的场景：

SELECT 
    matched_id AS COUNTRY_ID,
    SUM(VALUE) AS VALUE
FROM COUNTRY_FACT fact
CROSS APPLY (
    SELECT CASE
        WHEN EXISTS (SELECT 1 FROM COUNTRY_DIM dim WHERE UPPER(fact.COUNTRY_NAME) = UPPER(dim.SHORT_NAME))
            THEN (SELECT dim.ID FROM COUNTRY_DIM dim WHERE UPPER(fact.COUNTRY_NAME) = UPPER(dim.SHORT_NAME))
        WHEN EXISTS (SELECT 1 FROM COUNTRY_DIM dim WHERE UPPER(fact.COUNTRY_NAME) = UPPER(dim.FULL_NAME))
            THEN (SELECT dim.ID FROM COUNTRY_DIM dim WHERE UPPER(fact.COUNTRY_NAME) = UPPER(dim.FULL_NAME))
        WHEN EXISTS (SELECT 1 FROM COUNTRY_DIM dim WHERE dim.ALTERNATE_NAME IS NOT NULL AND UPPER(fact.COUNTRY_NAME) = UPPER(dim.ALTERNATE_NAME))
            THEN (SELECT dim.ID FROM COUNTRY_DIM dim WHERE dim.ALTERNATE_NAME IS NOT NULL AND UPPER(fact.COUNTRY_NAME) = UPPER(dim.ALTERNATE_NAME))
    END AS matched_id
)
WHERE matched_id IS NOT NULL
GROUP BY matched_id

内容的提问来源于stack exchange，提问作者Sayandip Ghatak