如何格式化表关联语句？解决嵌套与项目ID错误问题

原始需求 #

• 关联表：x-1269.WorkingData.lease_approved_3_month（别名a）和x-1269.94317304.ga_sessions_*（别名GA）
• 关联键：fullVisitorId
• 查询字段：
  a.date, a.device, a.fullVisitorId, a.visitId, a.MessageGUId, a.Status, a.LeadStatus, a.LeadDate, a.UpdatedDateTime, eventCategory, eventAction, eventLabel
• 过滤规则：eventCategory = 'request tour' 且 eventAction = 'submit'

常见错误分析 #

1. 简化版问题：

   • UNNEST 语句位置错误，必须在FROM或JOIN子句中展开数组
   • 直接引用未展开的嵌套数组字段GA.hits.eventInfo.*，导致语法或数据逻辑错误
   • 字符串常量未加单引号（request_tour应写为'request tour'）

2. 当前版本问题：

   • 引用未定义的表别名b
   • SELECT子句字段拼接混乱，语法错误
   • 展开hits后仍使用GA.hits.eventInfo.*的写法，触发"Invalid project ID 'GA.hits'"错误
   • 字符串常量未加单引号
   • 使用逗号连接表，易引发关联逻辑歧义

修复后的SQL（带日期范围过滤） #

WITH vars AS (
    SELECT 
        '20210801' AS startdate,
        '20220801' AS enddate
)
SELECT 
    a.date,
    a.device,
    a.fullVisitorId,
    a.visitId,
    a.MessageGUId,
    a.Status,
    a.LeadStatus,
    a.LeadDate,
    a.UpdatedDateTime,
    hit.eventInfo.eventCategory,
    hit.eventInfo.eventAction,
    hit.eventInfo.eventLabel
FROM `x-1269.WorkingData.lease_approved_3_month` a
LEFT JOIN (
    SELECT 
        fullVisitorId,
        hit
    FROM `x-1269.94317304.ga_sessions_*` GA,
         vars,
         UNNEST(GA.hits) hit
    WHERE 
        _table_suffix BETWEEN vars.startdate AND vars.enddate
        AND hit.eventInfo.eventCategory = 'request tour'
        AND hit.eventInfo.eventAction = 'submit'
) ga_filtered 
    ON ga_filtered.fullVisitorId = a.fullVisitorId

修复说明 #

• 先在子查询中展开GA表的hits数组并过滤目标事件，减少关联的数据量
• 显式使用JOIN语法，避免表关联逻辑歧义
• 字符串常量添加单引号，符合SQL语法规范
• 展开数组后直接引用hit别名下的事件字段，解决嵌套引用错误
• 保留日期范围过滤，确保只查询指定时段的GA会话数据

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