Oracle含UNPIVOT脚本迁移Databricks Spark SQL数据异常问题求助

• 未过滤空值日期行：Oracle原生UNPIVOT会自动跳过值为NULL的列，不会为其生成对应行。比如某行DT_INBOUND_DATE为NULL时，原SQL只会生成1条Outbound类型的行，但你的改写逻辑不判断空值就生成所有类型的行，导致多了大量DT_FLIGHT_DATE为NULL的无效数据，是结果不符合预期的核心原因。
• 缺失空值转0处理：原Oracle SQL最后聚合时使用了SUM(NVL(NB_PESQUISA, 0))、SUM(NVL(NB_PNRS, 0))，会把空值转为0再求和，你的改写逻辑直接对字段求和，空值会被忽略，部分场景下会出现结果为NULL而不是0的逻辑差异。
• 冗余聚合逻辑：中间表seu_bloco已经按照DT_CAPTURE_DATE、DT_OUTBOUND_DATE、DT_INBOUND_DATE、TX_MERCADO做过分组聚合，拆行时不需要再次分组求和，重复聚合属于冗余逻辑，也可能引入不可预期的误差。
• 额外说明：Databricks Runtime 7.0及以上版本（基于Spark 3.x）已经原生支持UNPIVOT语法，不需要手动改写，可直接复用原Oracle的UNPIVOT逻辑。

select count(*) from
  (WITH pq
     AS (SELECT DT_CAPTURE_DATE,
                DT_OUTBOUND_DATE,
                DT_INBOUND_DATE,
                Upper(Least(Substr(TX_TRECHO, 1, 3) || Substr(TX_TRECHO, 5, 3), Substr(TX_TRECHO, 5, 3) || Substr(TX_TRECHO, 1, 3))) AS TX_MERCADO,
                SUM(NB_QUANTIDADE_PESQUISA) AS NB_QUANTIDADE_PESQUISA
         FROM   bigq.bigq_data_pesquisas
         WHERE  DT_CAPTURE_DATE = '2021-08-16' -- 日期字段直接过滤性能优于函数转换
                 AND DT_OUTBOUND_DATE >= DT_CAPTURE_DATE
                 AND (DT_INBOUND_DATE >= DT_OUTBOUND_DATE OR DT_INBOUND_DATE IS NULL)
         GROUP BY DT_CAPTURE_DATE,
                  DT_OUTBOUND_DATE,
                  DT_INBOUND_DATE,
                  Upper(Least(Substr(TX_TRECHO, 1, 3) || Substr(TX_TRECHO, 5, 3), Substr(TX_TRECHO, 5, 3) || Substr(TX_TRECHO, 1, 3)))),
     pn
     AS (SELECT DT_CAPTURE_DATE,
                DT_OUTBOUND_DATE,
                DT_INBOUND_DATE,
                Upper(Least(Substr(TX_TRECHO, 1, 3) || Substr(TX_TRECHO, 5, 3), Substr(TX_TRECHO, 5, 3) || Substr(TX_TRECHO, 1, 3))) AS TX_MERCADO,
                Sum(NB_QUANTIDADE_PNRS) AS NB_QUANTIDADE_PNRS
         FROM   bigq.bigq_data_pnrs
         WHERE  DT_CAPTURE_DATE = '2021-08-16'
                AND DT_OUTBOUND_DATE >= DT_CAPTURE_DATE
                AND (DT_INBOUND_DATE >= DT_OUTBOUND_DATE OR DT_INBOUND_DATE IS NULL)
         GROUP  BY DT_CAPTURE_DATE,
                   DT_OUTBOUND_DATE,
                   DT_INBOUND_DATE,
                   Upper(Least(Substr(TX_TRECHO, 1, 3) || Substr(TX_TRECHO, 5, 3), Substr(TX_TRECHO, 5, 3) || Substr(TX_TRECHO, 1, 3)))),
     seu_bloco
     AS (-- 用FULL OUTER JOIN替代LEFT+RIGHT JOIN UNION，逻辑等价性能更好
         SELECT 
                COALESCE(pq.DT_CAPTURE_DATE, pn.DT_CAPTURE_DATE) AS DT_CAPTURE_DATE,
                COALESCE(pq.DT_OUTBOUND_DATE, pn.DT_OUTBOUND_DATE) AS DT_OUTBOUND_DATE,
                COALESCE(pq.DT_INBOUND_DATE, pn.DT_INBOUND_DATE) AS DT_INBOUND_DATE,
                COALESCE(pq.TX_MERCADO, pn.TX_MERCADO) AS TX_MERCADO,
                SUM(pq.NB_QUANTIDADE_PESQUISA) NB_PESQUISA,
                SUM(pn.NB_QUANTIDADE_PNRS) NB_PNRS
         FROM   pq
                FULL OUTER JOIN pn
                       ON pq.DT_CAPTURE_DATE = pn.DT_CAPTURE_DATE
                          AND pq.TX_MERCADO = pn.TX_MERCADO
                          AND pq.DT_OUTBOUND_DATE = pn.DT_OUTBOUND_DATE
                          AND pq.DT_INBOUND_DATE = pn.DT_INBOUND_DATE
         GROUP  BY 
                COALESCE(pq.DT_CAPTURE_DATE, pn.DT_CAPTURE_DATE),
                COALESCE(pq.DT_OUTBOUND_DATE, pn.DT_OUTBOUND_DATE),
                COALESCE(pq.DT_INBOUND_DATE, pn.DT_INBOUND_DATE),
                COALESCE(pq.TX_MERCADO, pn.TX_MERCADO)
),
-- 实现UNPIVOT逻辑，增加空值过滤
unpivot_data AS (
SELECT 
    DT_CAPTURE_DATE,
    DT_OUTBOUND_DATE AS DT_FLIGHT_DATE,
    'Outbound' AS TX_FLIGHT_TYPE,
    TX_MERCADO,
    NB_PESQUISA,
    NB_PNRS
FROM seu_bloco
WHERE DT_OUTBOUND_DATE IS NOT NULL
UNION ALL
SELECT 
    DT_CAPTURE_DATE,
    DT_INBOUND_DATE AS DT_FLIGHT_DATE,
    'Inbound' AS TX_FLIGHT_TYPE,
    TX_MERCADO,
    NB_PESQUISA,
    NB_PNRS
FROM seu_bloco
WHERE DT_INBOUND_DATE IS NOT NULL
)
-- 最终聚合，匹配原逻辑空值转0
SELECT
    DT_CAPTURE_DATE,
    DT_FLIGHT_DATE,
    TX_FLIGHT_TYPE,
    TX_MERCADO,
    SUM(COALESCE(NB_PESQUISA, 0)),
    SUM(COALESCE(NB_PNRS, 0))
FROM unpivot_data
GROUP BY 
    DT_CAPTURE_DATE,
    DT_FLIGHT_DATE,
    TX_FLIGHT_TYPE,
    TX_MERCADO
)

内容的提问来源于stack exchange，提问作者Luís Gustavo