sql - 当我按键聚合时，为什么不能从 `GROUP BY` 中排除依赖列？

Question

如果我有以下表(例如使用 PostgreSQL，但可以是任何其他关系数据库)，其中 car有两个键( id 和 vin ):

create table car (
  id int primary key not null,
  color varchar(10),
  brand varchar(10),
  vin char(17) unique not null
);

create table appraisal (
  id int primary key not null,
  recorded date not null,
  car_id int references car (id),
  car_vin char(17) references car (vin),
  price int
);

我可以成功包含 c.color和 c.brand在选择列表中而不聚合它们，因为它们依赖于 c.id :

select 
  c.id, c.color, c.brand,
  min(price) as min_appraisal,
  max(price) as max_appraisal
from car c
left join appraisal a on a.car_id = c.id
group by c.id; -- c.color, c.brand are not needed here

但是，以下查询失败，因为它不允许我包含 c.color和 c.brand在选择列表中，即使它确实依赖于 c.vin (这是一个键)表。

select 
  c.vin, c.color, c.brand,
  min(price) as min_appraisal,
  max(price) as max_appraisal
from car c
left join appraisal a on a.car_vin = c.vin
group by c.vin; -- Why are c.color, c.brand needed here?

Error: ERROR: column "c.color" must appear in the GROUP BY clause or be used in an aggregate function Position: 18

DB Fiddle 中的示例.

Answer 1

因为只有 PK 覆盖了 GROUP BY 中基础表的所有列条款。因此，您的第一个查询有效。 A UNIQUE约束没有。

不可延期的组合UNIQUE和一个 NOT NULL约束也符合条件。但这并没有实现 - 以及 SQL 标准已知的一些其他功能依赖项。该功能的主要作者 Peter Eisentraut 有更多想法，但当时确定需求低，相关成本可能很高。见 discussion about the feature on pgsql-hackers .

The manual:

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

和 more explicitly:

PostgreSQL recognizes functional dependency (allowing columns to be omitted from GROUP BY) only when a table's primary key is included in the GROUP BY list. The SQL standard specifies additional conditions that should be recognized.

自 c.vin是 UNIQUE NOT NULL ，您可以改用 PK 列来修复您的第二个查询:

...
group by c.id;

此外，虽然强制执行参照完整性并查询整个表，但两个给定的查询都可以大大降低成本:聚合 appraisal 中的行在加入之前。这消除了对 GROUP BY 的需要在外 SELECT先验。喜欢:

SELECT c.vin, c.color, c.brand
     , a.min_appraisal
     , a.max_appraisal
FROM   car c
LEFT   JOIN (
   SELECT car_vin
        , min(price) AS min_appraisal
        , max(price) AS max_appraisal
   FROM   appraisal
   GROUP  BY car_vin
   ) a ON a.car_vin = c.vin;

看:

Multiple array_agg() calls in a single query

有关的:

SQL statement working in MySQL not working in Postgresql - Sum & group_by rails 3

PostgreSQL - GROUP BY clause