PostgreSQL使用GROUP BY选择非分组列报错原因及解决方法咨询

问题描述 #

执行以下查询时出现错误：

select i.customer_id, cu.first_name, cu.last_name, sum(total) as amount 
from invoice i 
join customer cu on i.customer_id = cu.customer_id 
group by i.customer_id 
order by amount desc

错误信息：

ERROR:  column "i.customer_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select i.customer_id, cu.first_name, cu.last_name, sum(total...
^
SQL state: 42803
Character: 8

但将GROUP BY改为cu.customer_id后，查询可正常运行：

select cu.customer_id, cu.first_name, cu.last_name, sum(total) as amount 
from invoice i 
join customer cu on i.customer_id = cu.customer_id 
group by cu.customer_id 
order by amount desc

问题原因 #

这是PostgreSQL遵循SQL标准中GROUP BY的严格规则导致的：

• 当使用GROUP BY分组时，SELECT列表中的列要么是分组列，要么被聚合函数（如SUM、MAX）包裹，除非该列在逻辑上依赖于分组列（比如分组列是对应表的主键）。
• 第一个查询中，GROUP BY i.customer_id，cu.first_name、cu.last_name属于customer表，PostgreSQL的查询解析器不会自动推断i.customer_id和cu.customer_id的关联关系能保证这两个列在分组内唯一，因此判定它们既不是分组列也未使用聚合函数，抛出错误。
• 第二个查询中，GROUP BY cu.customer_id，cu.customer_id是customer表的主键（主键本身唯一），所以cu.first_name、cu.last_name在每个分组内必然只有唯一值，PostgreSQL允许直接选择这些依赖于主键的列，因此查询正常执行。

如何在GROUP BY时选择非分组列 #

针对单表或多表场景，有几种常用解决方式：

• 将非分组列加入GROUP BY：如果该列在分组内是唯一的（比如和分组列是一对一关系），可以直接把它加到GROUP BY子句中。例如：

  select customer_id, first_name, last_name, sum(total) as amount
  from invoice i join customer cu on i.customer_id = cu.customer_id
  group by customer_id, first_name, last_name
  order by amount desc
  

• 用聚合函数包裹非分组列：如果非分组列在分组内唯一，用MAX()、MIN()这类聚合函数包裹后，结果和原列值一致，同时符合GROUP BY规则。例如：

  select i.customer_id, max(cu.first_name), max(cu.last_name), sum(total) as amount
  from invoice i join customer cu on i.customer_id = cu.customer_id
  group by i.customer_id
  order by amount desc
  

• 利用主键/唯一约束的特性：如果分组列是某个表的主键或唯一约束列，该表的其他列可以直接被SELECT，因为PostgreSQL能确定这些列在分组内唯一（就像你第二个查询的情况）。
• 先聚合再关联：先在子查询中完成聚合计算，再关联其他表获取需要的非分组列，这种方式逻辑更清晰，适合复杂场景。例如：

  select cu.customer_id, cu.first_name, cu.last_name, agg.amount
  from (
      select customer_id, sum(total) as amount
      from invoice
      group by customer_id
  ) agg
  join customer cu on agg.customer_id = cu.customer_id
  order by agg.amount desc
  

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