BigQuery SQL报错Unrecognized name: inventory，如何修复？

错误原因 #

你在SELECT语句里引用了inventory.*，但FROM子句中的表my-project-7869-408305.sales.inventory没有定义别名inventory，BigQuery无法识别这个未声明的名称。另外原CTEsales_history的GROUP BY包含ProductId，但SELECT列表里没返回该字段，后续子查询匹配时会触发新的字段未识别错误，需要一并修复。

修复方案 #

给FROM子句中的inventory表添加对应别名，同时在sales_history的SELECT列表中加入ProductId字段。以下是两种可选的修正后的SQL：

方案一：用inventory作为表别名 #

with sales_history as (
  SELECT
    EXTRACT(YEAR FROM Date) AS YEAR 
  , EXTRACT(MONTH FROM Date) AS MONTH 
  , StoreID 
  , ProductId  -- 新增字段，用于子查询匹配
  , SUM(quantity) AS UnitsSold 
  , AVG(UnitPrice) AS UnitPriceProxy 
  , COUNT(DISTINCT salesID) AS NumTransactions 
  FROM `my-project-7869-408305.sales.sales_info`
  GROUP BY YEAR, MONTH, ProductId, StoreID
)
SELECT
  inventory.*
  , (SELECT AVG(UnitsSold) FROM sales_history
      WHERE inventory.ProductID=sales_history.ProductID
      AND inventory.StoreID=sales_history.StoreID) AS avg_quantity_sold_in_a_month
FROM `my-project-7869-408305.sales.inventory` inventory  -- 给表添加别名inventory

方案二：用更简洁的别名（如inv） #

with sales_history as (
  SELECT
    EXTRACT(YEAR FROM Date) AS YEAR 
  , EXTRACT(MONTH FROM Date) AS MONTH 
  , StoreID 
  , ProductId  -- 新增字段，用于子查询匹配
  , SUM(quantity) AS UnitsSold 
  , AVG(UnitPrice) AS UnitPriceProxy 
  , COUNT(DISTINCT salesID) AS NumTransactions 
  FROM `my-project-7869-408305.sales.sales_info`
  GROUP BY YEAR, MONTH, ProductId, StoreID
)
SELECT
  inv.*
  , (SELECT AVG(UnitsSold) FROM sales_history
      WHERE inv.ProductID=sales_history.ProductID
      AND inv.StoreID=sales_history.StoreID) AS avg_quantity_sold_in_a_month
FROM `my-project-7869-408305.sales.inventory` inv  -- 给表添加别名inv

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