GoogleSQL中UNION子句报错：求运费均值最高/最低的前5个州

原SQL的核心问题 #

1. UNION语法错误：在UNION ALL之前的子查询使用了ORDER BY但未搭配LIMIT，BigQuery不允许单独在UNION的子查询里使用ORDER BY（单独排序对UNION后的最终结果无意义，除非限定返回行数）。
2. 分组逻辑错误：需求是按州计算平均运费，但原SQL把freight_value、订单时间等无关字段都加入GROUP BY，导致分组维度变成「州+单个运费值+订单时间」，完全没计算州级的平均运费，偏离核心需求。

修正后的SQL实现 #

先计算每个州的平均运费，再分别筛选最低和最高的5个州，最后合并结果：

WITH state_avg_freight AS (
  SELECT 
    c.customer_state,
    AVG(ori.freight_value) AS avg_freight
  FROM sql_projects.orders o
  INNER JOIN sql_projects.customers c ON o.customer_id = c.customer_id
  INNER JOIN sql_projects.order_items ori ON o.order_id = ori.order_id
  GROUP BY c.customer_state
)
-- 取平均运费最低的5个州
SELECT customer_state, avg_freight, '最低运费' AS category
FROM state_avg_freight
ORDER BY avg_freight ASC
LIMIT 5

UNION ALL

-- 取平均运费最高的5个州
SELECT customer_state, avg_freight, '最高运费' AS category
FROM state_avg_freight
ORDER BY avg_freight DESC
LIMIT 5

代码说明 #

• CTE部分：通过state_avg_freight完成核心聚合，仅按customer_state分组，用AVG()函数计算每个州的平均运费。
• 子查询逻辑：两个子查询分别对平均运费做升序/降序排序，用LIMIT 5取前5条；新增category字段标识结果组，方便区分是最低还是最高运费的州。
• UNION ALL使用：合并两个子查询结果，由于两组数据无重复，用UNION ALL比UNION效率更高。

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