BigQuery中搭配row number函数使用LEFT JOIN报语法错误咨询

错误原因 #

你的SQL存在3处核心问题：

• 括号不匹配：bq子查询缺少2个闭合括号，导致SQL解析到ON关键字时仍认为处于子查询定义阶段，触发「预期为")"，实际获取到关键字ON」的报错
• 窗口函数别名使用错误：SQL执行顺序中WHERE子句早于窗口函数计算，同层级WHERE无法直接引用窗口函数别名RN，你当前把AND RN = 1写在了计算RN的同层级WHERE中，会触发二次报错
• 子查询逻辑冗余：bq子查询中写了两层重复的字段提取逻辑，属于无效冗余代码

修正后的完整SQL #

SELECT 
  paymentsDB.*,
  bq.*
FROM (
  SELECT 
    string_field_0 AS orderId_payments,
    string_field_1 AS type,
    string_field_2 AS status
  FROM `xxxx-xxxx.xxxx_xxxxxx_1aug2021`
) paymentsDB
LEFT JOIN (
  SELECT 
    orderId_bq,
    event_action,
    trx_id,
    page,
    hitnumber,
    RN
  FROM (
    SELECT
      ROW_NUMBER() OVER(
        PARTITION BY (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 6) 
        ORDER BY hitnumber DESC
      ) AS RN,
      (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 6) AS orderId_bq, 
      hits.eventinfo.eventaction AS event_action, 
      hits.transaction.transactionId AS trx_id, 
      hits.page.pagePath AS page, 
      hitnumber AS hitnumber 
    FROM `xxxx-xxxxx.ga_sessions_*` t,
    UNNEST(HITS) AS hits 
    WHERE (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 8) = 'se'
      AND (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 4) = 'soffadirekt'
      AND _TABLE_SUFFIX BETWEEN '20210801' AND '20210802'
  )
  WHERE RN = 1
) bq
ON paymentsDB.orderId_payments = bq.orderId_bq

逻辑说明 #

修正后的代码保留了你要求的业务逻辑：会为每个orderId_bq取hitnumber最大的最新一条记录，和payments表中同orderId的支付状态做关联，输出的字段完全匹配你给出的示例表要求。

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