BigQuery报错：US区域未找到表sql-practice-361723:warehouse_orders.orders

错误原因 #

1. 表引用不完整：子查询中直接写FROM warehouse_orders.orders，未带上项目IDsql-practice-361723，BigQuery无法准确定位表；而JOIN部分用了完整表路径sql-practice-361723.warehouse_orders.Orders，因此单独执行JOIN逻辑正常，加入子查询后触发找不到表的错误。
2. CASE语句逻辑错误：将订单数占比的计算嵌套在COUNT函数中完全不符合逻辑——COUNT是统计行数，应先算出每个仓库订单数占总订单数的比例，再用该比例判断区间。
3. HAVING子句语法错误：COUNT(Orders.order_id > 0)写法错误，应改为COUNT(Orders.order_id) > 0，前者会把Orders.order_id > 0当作布尔值统计，无法实现过滤有订单仓库的需求。

修正后的完整SQL #

SELECT
  warehouse.warehouse_id,
  CONCAT(warehouse.state, ':', warehouse.warehouse_alias) AS warehouse_name,
  COUNT(Orders.order_id) AS num_of_orders,
  total_orders,
  CASE
    WHEN COUNT(Orders.order_id) / total_orders <= 0.2 THEN "Fulfilled 0%-20% of Orders"
    WHEN COUNT(Orders.order_id) / total_orders > 0.2 AND COUNT(Orders.order_id) / total_orders <= 0.6 THEN "Fulfilled 21%-60% of Orders"
    ELSE "Fulfilled more than 60% of Orders"
  END AS fulfillment_summary 
FROM `sql-practice-361723.warehouse_orders.Warehouse` AS warehouse
LEFT JOIN `sql-practice-361723.warehouse_orders.Orders` AS Orders
  ON warehouse.warehouse_id = Orders.warehouse_id
CROSS JOIN (SELECT COUNT(*) AS total_orders FROM `sql-practice-361723.warehouse_orders.Orders`) AS total
GROUP BY
  warehouse.warehouse_id,
  warehouse_name,
  total_orders
HAVING
  COUNT(Orders.order_id) > 0

额外优化说明 #

• 将总订单数的子查询改为CROSS JOIN，避免多次重复计算，提升查询效率。
• 统一表别名大小写，保持代码一致性。
• 修正拼写错误：fullfillment改为fulfillment。

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