如何让BigQuery识别分组依据为唯一标识符以避免非聚合列报错？

问题场景 #

在BigQuery中，若表存在唯一主键列（如tbl表的id），执行分组查询时如果SELECT包含非分组的其他列，会触发报错：

SELECT references tbl.attr which is neither grouped nor aggregated

示例报错查询：

select tbl.id, tbl.attr
from tbl
group by tbl.id

用any_value()函数可以规避报错，但当需要保留主表多列时，必须为每列添加any_value()，既降低可读性，也可能影响查询性能。

实际常见场景：关联其他表做聚合计算的同时，保留主表（如客户表）的全部信息，例如查询客户ID、所在城市及订单总金额：

-- 需为每个客户列添加any_value()
select clients.client_id, any_value(clients.city), sum(orders.order_value)
from clients
left join orders on clients.client_id = orders.client_id
group by clients.client_id

若用子查询关联规避any_value()，则会增加查询复杂度：

select 
  cwt.client_id, 
  clients.city, 
  cwt.sum_order_total
from (
  select clients.client_id, sum(orders.order_value) as sum_order_total
  from clients
  left join orders on orders.client_id = clients.client_id
  group by clients.client_id
) as cwt
left join clients on cwt.client_id = clients.client_id

可行解决方案 #

1. 用STRUCT包装多列+ANY_VALUE()（推荐） #

将主表需要保留的多列打包成STRUCT，只需调用一次any_value()，再通过.展开列，兼顾简洁性与安全性：

select 
  clients.client_id,
  -- 打包客户表多列，一次调用any_value后展开
  any_value(struct(clients.city, clients.name, clients.phone)).*,
  sum(orders.order_value) as sum_order_total
from clients
left join orders on clients.client_id = orders.client_id
group by clients.client_id

2. 开启allow_unsafe_group_by参数（谨慎使用） #

通过设置会话参数告知BigQuery：分组列是唯一标识符，无需对其他列做聚合。但此操作属于不安全行为，仅当你能100%确认分组列是绝对唯一主键时使用：

-- 开启参数
SET allow_unsafe_group_by = true;

select 
  clients.client_id,
  clients.city,
  sum(orders.order_value) as sum_order_total
from clients
left join orders on clients.client_id = orders.client_id
group by clients.client_id

3. 窗口函数+DISTINCT替代分组查询 #

用窗口函数预先计算聚合值，再通过distinct去重，无需对主表列做任何聚合处理：

select distinct
  clients.client_id,
  clients.city,
  -- 按客户ID分区计算订单总额
  sum(orders.order_value) over (partition by clients.client_id) as sum_order_total
from clients
left join orders on clients.client_id = orders.client_id

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