BQML逻辑回归模型构建SQL语句错误排查求助

存在的问题 #

• 子查询语法错误：子查询后直接拼接CASE语句的写法不符合SQL规范，且End as label as combined属于重复命名的语法错误。正确逻辑应在子查询内部完成Course_Grade转数值的操作，并为子查询指定合法别名。
• GROUP BY与SELECT字段不匹配：BigQuery要求SELECT子句中的字段要么出现在GROUP BY中，要么是聚合函数。原代码中SELECT *包含未分组的非聚合字段，会触发语法报错。
• 中文引号使用错误：判定合格/不合格时使用了中文单引号‘’，需替换为英文单引号''。
• 聚合字段引用时机错误：average是聚合计算的别名，无法在同一条SELECT的CASE语句中直接引用（BigQuery不支持同一层级SELECT中直接调用聚合别名）。
• BQML模型标签类型不兼容：逻辑回归模型要求目标标签为数值型，原代码中success是字符串类型的pass/fail，无法作为训练标签。
• 多余表联合：需求说明是从三个表取数据，但代码中用UNION ALL联合了四个表（1617、1718、1819、1920），需根据实际需求调整。
• SELECT *的潜在风险：直接使用SELECT *若四个表结构不一致会导致字段不匹配，建议明确指定所需字段（如unique_identifier, Course_Grade）。

修正后的SQL语句 #

CREATE OR REPLACE MODEL `bqml_test.ModelTest`
OPTIONS(model_type='logistic_reg', input_label_cols=['is_pass']) AS
-- 先计算每个用户的平均成绩，再转换为数值型标签
SELECT
  unique_identifier,
  average_score,
  -- 逻辑回归需要数值型标签，用1表示合格，0表示不合格
  CASE WHEN average_score > 0.4 THEN 1 ELSE 0 END AS is_pass
FROM (
  -- 子查询：联合目标表，转换成绩为数值，计算每个用户的平均分
  SELECT
    unique_identifier,
    AVG(
      CASE
        WHEN Course_Grade = "A" THEN 1
        WHEN Course_Grade = "B" THEN 0.8
        WHEN Course_Grade = "C" THEN 0.6
        WHEN Course_Grade = "D" THEN 0.4
        WHEN Course_Grade = "F" THEN 0.2
        ELSE 0
      END
    ) AS average_score
  FROM (
    -- 联合三个目标表，明确指定字段避免结构不一致问题
    SELECT unique_identifier, Course_Grade FROM `interns2022.uniqueIdGrades.1617`
    UNION ALL
    SELECT unique_identifier, Course_Grade FROM `interns2022.uniqueIdGrades.1718`
    UNION ALL
    SELECT unique_identifier, Course_Grade FROM `interns2022.uniqueIdGrades.1819`
    -- 若不需要1920表则删除该行
    -- UNION ALL SELECT unique_identifier, Course_Grade FROM `interns2022.uniqueIdGrades.1920`
  ) AS combined_grades
  GROUP BY unique_identifier
) AS user_avg_scores

修正说明 #

1. 调整嵌套层级，先在最内层完成表联合与成绩转数值，再计算平均分，最后生成数值型标签is_pass，符合BQML逻辑回归的要求。
2. 明确指定所需字段，避免SELECT *的潜在风险。
3. 将字符串型的pass/fail转换为数值型的1/0，作为逻辑回归的训练标签。
4. 修正GROUP BY与SELECT字段的匹配问题，确保所有非聚合字段都在GROUP BY中。
5. 移除错误语法结构，确保SQL符合BigQuery规范。

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