BigQuery运行SQL报错：指定数据集在US区域未找到，求解决

• 表引用格式错误：SQL中多次直接引用数据集warehouse_orders而非具体表名，比如FROM project-3-395116.warehouse_orders AS warehouse，BigQuery会将其解析为尝试访问project-3-395116项目下名为warehouse_orders的表，但实际该名称是数据集ID，导致系统误查找project-3-395116:project-3-395116数据集，触发解析错误。
• 子查询表引用遗漏：多个统计总订单数的子查询中，同样只写到数据集层级，未指定orders表，进一步加剧了解析逻辑混乱。

1. 所有表引用严格遵循项目ID.数据集ID.表名格式，明确指定warehouse和orders表
2. 用CTE提前计算总订单数，避免重复子查询，优化性能
3. 统一别名大小写，避免歧义

修正后的SQL代码：

WITH total_orders_cte AS (
  SELECT COUNT(*) AS total
  FROM `project-3-395116.warehouse_orders.orders`
)
SELECT
  w.warehouse_id,
  CONCAT(w.state, ':', w.warehouse_alias) AS warehouse_name,
  COUNT(o.order_id) AS number_of_orders,
  t.total AS total_orders,
  CASE
    WHEN COUNT(o.order_id) / t.total <= 0.20 THEN "Fulfilled 0-20% of Orders"
    WHEN COUNT(o.order_id) / t.total > 0.20 AND COUNT(o.order_id) / t.total <= 0.60 THEN "Fulfilled 21-60% of Orders"
    ELSE "Fulfilled more than 60% of Orders"
  END AS fulfillment_summary
FROM
  `project-3-395116.warehouse_orders.warehouse` AS w
LEFT JOIN
  `project-3-395116.warehouse_orders.orders` AS o
  ON o.warehouse_id = w.warehouse_id
CROSS JOIN total_orders_cte t
GROUP BY
  w.warehouse_id,
  warehouse_name,
  t.total
HAVING
  COUNT(o.order_id) > 0;

• CTE（公共表表达式）提前计算总订单数，避免多次重复执行相同子查询，提升查询效率
• 表别名简化为w和o，增强代码可读性
• 确保所有表引用完整，符合BigQuery的标准格式要求

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