如何在两张表的LEFT JOIN中计算BigQuery的总营收平均值？

报错原因 #

使用聚合函数AVG()时，SELECT语句中包含的order_create_date、site_country等非聚合列，既没有被其他聚合函数包裹，也没有出现在GROUP BY子句中。BigQuery要求所有非聚合列必须明确分组或聚合，否则无法确定这些列与聚合结果的对应关系。

解决方案 #

根据你的需求（以item_id为键计算总营收的平均值），提供两种适配场景的写法：

场景1：仅按item_id输出平均值及关联产品信息 #

如果只需要每个item_id对应的平均值，以及该item的产品属性，用GROUP BY分组查询：

SELECT
  t0.item_id,
  p0.product_brand AS brand,
  p0.product_gender AS gender,
  p0.product_department AS department,
  AVG(t0.item_sale_price) AS average_gross_revenue
FROM
  `transactions` t0
LEFT JOIN
  `products` p0
ON
  t0.item_id = p0.item_id
GROUP BY
  t0.item_id, p0.product_brand, p0.product_gender, p0.product_department
ORDER BY
  t0.item_id

注：产品表的brand、gender、department通常与item_id一一对应，因此可以安全地加入GROUP BY，不会影响平均值计算。

场景2：保留交易明细同时显示对应item_id的平均值 #

如果需要保留原查询的所有明细列，同时附加该item_id的总营收平均值，用窗口函数实现：

SELECT
  t0.order_create_date AS day,
  t0.site_country AS country,
  p0.product_brand AS brand,
  p0.product_gender AS gender,
  p0.product_department AS department,
  t0.item_qty AS items_sold,
  t0.item_sale_price AS gross_revenue,
  t0.item_net_price AS net_revenue,
  AVG(t0.item_sale_price) OVER (PARTITION BY t0.item_id) AS average_gross_revenue
FROM
  `transactions` t0
LEFT JOIN
  `products` p0
ON
  t0.item_id = p0.item_id
ORDER BY
  country, day ASC

窗口函数PARTITION BY t0.item_id会为每个item_id单独计算平均值，并将结果附加到每一行明细数据中。

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