BigQuery WITH子句语法错误排查及错误定位含义咨询

1. 错误位置解析 #

错误提示Expected keyword AS but got "("的核心问题是第二个临时表（CTE）的定义缺少AS关键字。

结合你的需求场景，错误代码大概率是类似这样的写法：

WITH store_sales AS (
  SELECT storeid, SUM(sales) AS total_sales
  FROM your_table
  GROUP BY storeid
),
sales_avg (  -- 此处缺少AS关键字，直接写了(
  SELECT AVG(total_sales) AS avg_sales
  FROM store_sales
)
SELECT s.storeid, s.total_sales
FROM store_sales s, sales_avg a
WHERE s.total_sales > a.avg_sales

错误就出在第二个CTE的名称（比如sales_avg）后面，本该写AS的位置直接写了(，触发了语法校验错误。

2. BigQuery错误提示[7:14]的含义 #

[7:14]确实是行号:列号，行号和列号都从1开始计数。如果你觉得实际错误不在自己认知的第7行，大概率是代码中的空行、注释行被算入行号统计了。比如WITH关键字所在的行是第1行，后续的空行也会被计数，需要逐行核对代码的实际行号。

正确代码需补全AS关键字：

WITH store_sales AS (
  SELECT storeid, SUM(sales) AS total_sales
  FROM your_table
  GROUP BY storeid
),
sales_avg AS (  -- 补上AS关键字
  SELECT AVG(total_sales) AS avg_sales
  FROM store_sales
)
SELECT s.storeid, s.total_sales
FROM store_sales s
CROSS JOIN sales_avg a
WHERE s.total_sales > a.avg_sales

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