Google BigQuery递归SQL CASE运算符错误排查与修复请求

错误提示明确指出CASE语句中THEN和ELSE分支返回的类型无法兼容（INT64与STRING），问题出在两个核心点：

1. code字段类型不统一：递归JOIN时用CAST(i.parent AS STRING) = r.code，说明r.code是STRING类型，但原查询中直接使用i.code（假设为INT64类型）作为CASE的THEN结果，与ELSE分支的r.level_x_code（STRING类型）类型冲突。
2. 字段名错误：递归部分的CASE语句中使用了i.name，但原表中定义的字段是name_en，这不仅会导致字段不存在错误，还可能因类型匹配问题触发报错。

修复后的SQL语句 #

WITH RECURSIVE EducationClassification AS (
SELECT 
    CAST(code AS STRING) AS code,
    name_en,
    description_en,
    education_classification_code,
    historic_ind,
    source,
    effective_date,
    expiration_date,
    CAST(level AS INT64) AS level,
    CAST(parent AS STRING) AS parent,
    CAST(code AS STRING) AS level_0_code,
    name_en AS level_0_name,
    CAST(NULL AS STRING) AS level_1_code,
    CAST(NULL AS STRING) AS level_1_name,
    CAST(NULL AS STRING) AS level_2_code,
    CAST(NULL AS STRING) AS level_2_name,
    CAST(NULL AS STRING) AS level_3_code,
    CAST(NULL AS STRING) AS level_3_name,
    CASE 
        WHEN CAST(level AS INT64) = 0 THEN 'Root'
        ELSE 'Leaf'
    END AS node_type
FROM 
    input_table
WHERE 
    CAST(level AS INT64) = 0

UNION ALL

SELECT 
    CAST(i.code AS STRING) AS code,
    i.name_en,
    i.description_en,
    i.education_classification_code,
    i.historic_ind,
    i.source,
    i.effective_date,
    i.expiration_date,
    CAST(i.level AS INT64),
    CAST(i.parent AS STRING) AS parent,
    r.level_0_code,
    r.level_0_name,
    CASE 
        WHEN CAST(i.level AS INT64) = 1 THEN CAST(i.code AS STRING)
        ELSE r.level_1_code
    END AS level_1_code,
    CASE 
        WHEN CAST(i.level AS INT64) = 1 THEN i.name_en
        ELSE r.level_1_name
    END AS level_1_name,
    CASE 
        WHEN CAST(i.level AS INT64) = 2 THEN CAST(i.code AS STRING)
        ELSE r.level_2_code
    END AS level_2_code,
    CASE 
        WHEN CAST(i.level AS INT64) = 2 THEN i.name_en
        ELSE r.level_2_name
    END AS level_2_name,
    CASE 
        WHEN CAST(i.level AS INT64) = 3 THEN CAST(i.code AS STRING)
        ELSE r.level_3_code
    END AS level_3_code,
    CASE 
        WHEN CAST(i.level AS INT64) = 3 THEN i.name_en
        ELSE r.level_3_name
    END AS level_3_name,
    'Leaf' AS node_type
FROM 
    input_table i
INNER JOIN 
    EducationClassification r ON CAST(i.parent AS STRING) = r.code)
SELECT * FROM EducationClassification;

关键修复细节 #

• 所有code字段统一转换为STRING类型，确保递归过程中类型一致，避免CASE分支的类型冲突。
• 将递归部分的i.name替换为i.name_en，与初始查询的字段名保持一致。
• 初始查询中的NULL值明确转换为STRING类型，确保各level_x字段的类型统一。

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