BigQuery递归SQL查询优化：600万行数据性能提升咨询

问题背景 #

我正在使用BigQuery开发，数据字段说明：

• ANUMCL、ANUMFI：NUMCLI、NUMFIC的旧值
• NNUMCL、NNUMFI：NUMCLI、NUMFIC的新值

需求是生成一张表，将每个NUMCLI-NUMFIC键映射到它的最终状态。我写的递归SQL在小量测试数据上正常运行，但在600万行的全量数据上执行耗时过长，求优化方法。

原递归查询代码 #

WITH RECURSIVE RecursiveTransformation AS (
    -- 注：原代码中多余的`auparavant)`需删除，否则会报错
    SELECT 
        NUMCLI AS NUMPER, 
        NUMFIC AS FILPER, 
        NUMCLI AS CurrentNUMCL, 
        NUMFIC AS CurrentNUMFI
    FROM 
        `mytable` 
    WHERE 
        NNUMCL <> 0

    UNION ALL

    SELECT 
        rt.NUMPER, 
        rt.FILPER, 
        yt.NUMCLI AS CurrentNUMCL, 
        yt.NUMFIC AS CurrentNUMFI
        -- 注：原代码中此处多余的逗号需删除
    FROM 
        RecursiveTransformation rt
    JOIN 
        `mytable` yt ON rt.CurrentNUMCL = yt.ANUMCL AND rt.CurrentNUMFI = yt.ANUMFI
),

LastTransform AS (
    SELECT 
        NUMPER, 
        FILPER, 
        CurrentNUMCL, 
        CurrentNUMFI
    FROM RecursiveTransformation
    WHERE NOT EXISTS (
        SELECT 1 FROM `mytable` yt 
        WHERE RecursiveTransformation.CurrentNUMCL = yt.ANUMCL 
        AND RecursiveTransformation.CurrentNUMFI = yt.ANUMFI
    )
)

SELECT DISTINCT
    NUMPER, 
    FILPER, 
    CurrentNUMCL AS DNUMCL, 
    CurrentNUMFI AS DNUMFI
FROM 
    LastTransform
ORDER BY 
    NUMPER, FILPER

优化方案 #

方案1：使用BigQuery原生CONNECT BY语法 #

BigQuery对CONNECT BY层级遍历的优化远优于递归CTE，更适合大数据量场景：

WITH final_nodes AS (
    SELECT
        CONNECT_BY_ROOT NUMCLI AS NUMPER,
        CONNECT_BY_ROOT NUMFIC AS FILPER,
        NUMCLI AS CurrentNUMCL,
        NUMFIC AS CurrentNUMFI
    FROM `mytable` t1
    WHERE NNUMCL <> 0
    -- 遍历转换链，直到没有后续节点
    CONNECT BY ANUMCL = PRIOR NUMCLI AND ANUMFI = PRIOR NUMFIC
    -- 只保留最终节点（没有后续转换的节点）
    AND NOT EXISTS (
        SELECT 1 FROM `mytable` t2 
        WHERE t2.ANUMCL = t1.NUMCLI AND t2.ANUMFI = t1.NUMFIC
    )
)
SELECT DISTINCT
    NUMPER,
    FILPER,
    CurrentNUMCL AS DNUMCL,
    CurrentNUMFI AS DNUMFI
FROM final_nodes
ORDER BY NUMPER, FILPER

方案2：路径追踪+取最终节点 #

通过ARRAY_AGG收集转换路径，直接提取路径的最后一个节点作为最终状态：

WITH path_tracking AS (
    SELECT
        CONNECT_BY_ROOT NUMCLI AS NUMPER,
        CONNECT_BY_ROOT NUMFIC AS FILPER,
        -- 按层级倒序聚合，第一个元素就是最终节点
        ARRAY_AGG(STRUCT(NUMCLI, NUMFIC) ORDER BY LEVEL DESC LIMIT 1)[OFFSET(0)] AS final_node
    FROM `mytable`
    WHERE NNUMCL <> 0
    CONNECT BY ANUMCL = PRIOR NUMCLI AND ANUMFI = PRIOR NUMFIC
    GROUP BY NUMPER, FILPER
)
SELECT
    NUMPER,
    FILPER,
    final_node.NUMCLI AS DNUMCL,
    final_node.NUMFIC AS DNUMFI
FROM path_tracking
ORDER BY NUMPER, FILPER

额外性能优化建议 #

• 聚类表优化：将mytable按ANUMCL、ANUMFI字段聚类，BigQuery会自动优化连接和过滤操作的性能。
• 减少数据范围：确认NNUMCL <> 0的过滤条件是否足够严格，进一步过滤无效行。
• 移除冗余DISTINCT：测试CONNECT BY的结果是否已经唯一，若无需去重则删除DISTINCT，减少计算开销。

内容的提问来源于stack exchange，提问作者Antoine Giroud