Oracle/Snowflake SQL优化：替代25个单字段左连接的高效方案

问题背景 #

处理列数多但行数少的数据集时，原代码通过25个左连接实现DX_ID到DX_CODE/DX_DESC的映射，近期出现卡顿超时，需要更优实现方案，优先兼容Oracle与Snowflake，若无法兼容则优先适配Snowflake。

通用优化方案（兼容Oracle & Snowflake） #

核心思路：将宽表转置为长表，仅关联一次诊断对照表后再转回宽表，彻底减少连接次数。

优化后SQL #

WITH GET_CLAIM_DATA AS (
    SELECT
      '123456789' AS CLAIM_ID,
      'DX1'  AS DX_ID_1,
      'DX2'  AS DX_ID_2,
      'DX3'  AS DX_ID_3,
      'DX25' AS DX_ID_25
    FROM DUAL
    UNION
    SELECT
      '459745630' AS CLAIM_ID,
      'DX1'  AS DX_ID_1,
      'DX25' AS DX_ID_2,
      NULL   AS DX_ID_3,
      NULL   AS DX_ID_25
    FROM DUAL
),
DIAGNOSIS_CROSSWALK AS (
    SELECT
      'DX1' AS DX_ID,
      '112.4' AS DX_CODE,
      'Flarble' AS DX_DESC
    FROM DUAL
    UNION
    SELECT
      'DX2' AS DX_ID,
      '158.6' AS DX_CODE,
      'Severe flarble' AS DX_DESC
    FROM DUAL
    UNION
    SELECT
      'DX3' AS DX_ID,
      'H65' AS DX_CODE,
      'Blaggle floddle' AS DX_DESC
    FROM DUAL
    UNION
    SELECT
      'DX25' AS DX_ID,
      'H65.7' AS DX_CODE,
      'Headache' AS DX_DESC
    FROM DUAL
),
-- 步骤1：宽表转长表，拆分所有DX_ID字段
CLAIM_LONG AS (
    SELECT
        CLAIM_ID,
        DX_POSITION,
        DX_ID
    FROM GET_CLAIM_DATA
    UNPIVOT (
        DX_ID FOR DX_POSITION IN (
            DX_ID_1 AS '1',
            DX_ID_2 AS '2',
            DX_ID_3 AS '3',
            DX_ID_25 AS '25'
            -- 补充DX_ID_4到DX_ID_24的映射项
        )
    )
),
-- 步骤2：仅关联一次诊断对照表
CLAIM_MAPPED AS (
    SELECT
        c.CLAIM_ID,
        c.DX_POSITION,
        d.DX_CODE,
        d.DX_DESC
    FROM CLAIM_LONG c
    LEFT JOIN DIAGNOSIS_CROSSWALK d
        ON c.DX_ID = d.DX_ID
)
-- 步骤3：转回宽表结构
SELECT
    CLAIM_ID,
    "1" AS DX_1,
    "2" AS DX_2,
    "3" AS DX_3,
    "25" AS DX_25
    -- 补充DX_4到DX_24的列定义
FROM CLAIM_MAPPED
PIVOT (
    MAX(DX_CODE) FOR DX_POSITION IN (
        '1' AS "1",
        '2' AS "2",
        '3' AS "3",
        '25' AS "25"
        -- 对应UNPIVOT中的位置项
    )
)
ORDER BY CLAIM_ID;

Snowflake专属优化方案 #

若无需兼容Oracle，可利用Snowflake原生FLATTEN函数处理数组，代码更简洁且性能更优：

WITH GET_CLAIM_DATA AS (
    SELECT
      CLAIM_ID,
      ARRAY_CONSTRUCT(DX_ID_1, DX_ID_2, DX_ID_3, DX_ID_25) AS DX_ID_ARRAY
    FROM (
        SELECT
          '123456789' AS CLAIM_ID,
          'DX1'  AS DX_ID_1,
          'DX2'  AS DX_ID_2,
          'DX3'  AS DX_ID_3,
          'DX25' AS DX_ID_25
        FROM DUAL
        UNION
        SELECT
          '459745630' AS CLAIM_ID,
          'DX1'  AS DX_ID_1,
          'DX25' AS DX_ID_2,
          NULL   AS DX_ID_3,
          NULL   AS DX_ID_25
        FROM DUAL
    )
),
DIAGNOSIS_CROSSWALK AS (
    SELECT
      'DX1' AS DX_ID,
      '112.4' AS DX_CODE,
      'Flarble' AS DX_DESC
    FROM DUAL
    UNION
    SELECT
      'DX2' AS DX_ID,
      '158.6' AS DX_CODE,
      'Severe flarble' AS DX_DESC
    FROM DUAL
    UNION
    SELECT
      'DX3' AS DX_ID,
      'H65' AS DX_CODE,
      'Blaggle floddle' AS DX_DESC
    FROM DUAL
    UNION
    SELECT
      'DX25' AS DX_ID,
      'H65.7' AS DX_CODE,
      'Headache' AS DX_DESC
    FROM DUAL
)
SELECT
    c.CLAIM_ID,
    -- 按数组索引提取对应诊断码
    MAX(CASE WHEN f.INDEX = 0 THEN d.DX_CODE END) AS DX_1,
    MAX(CASE WHEN f.INDEX = 1 THEN d.DX_CODE END) AS DX_2,
    MAX(CASE WHEN f.INDEX = 2 THEN d.DX_CODE END) AS DX_3,
    MAX(CASE WHEN f.INDEX = 24 THEN d.DX_CODE END) AS DX_25
FROM GET_CLAIM_DATA c
LEFT JOIN FLATTEN(c.DX_ID_ARRAY) f
LEFT JOIN DIAGNOSIS_CROSSWALK d
    ON f.VALUE = d.DX_ID
GROUP BY c.CLAIM_ID
ORDER BY CLAIM_ID;

优化原理 #

• 原方案的25次左连接会导致执行计划复杂度指数级上升，容易产生不必要的笛卡尔积，尤其当数据集行数增加时问题更明显。
• 转置后仅需一次关联，执行计划更简洁，对于行数少、列数多的场景，转置的计算开销远低于多次连接的开销。
• Snowflake的FLATTEN函数对数组操作做了深度优化，专属方案能进一步提升查询性能。

内容的提问来源于stack exchange，提问作者Michael K