时间戳

首页 > 解决方案 > 当我按键聚合时，为什么不能从“GROUP BY”中排除依赖列？

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

问题描述

如果我有以下表（作为使用 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?

错误：错误：列“c.color”必须出现在 GROUP BY 子句中或用于聚合函数位置：18

DB Fiddle中的示例。

标签： sqlpostgresqlgroup-byprimary-key

解决方案

GROUP BY因为只有 PK 涵盖子句中基础表的所有列。因此，您的第一个查询有效。UNIQUE约束没有。

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

手册：

当GROUP BY存在或存在任何聚合函数时，列表表达式引用未分组列是无效的，SELECT除非在聚合函数内或当未分组列在功能上依赖于分组列时，否则可能会有多个为未分组的列返回的值。如果分组列（或其子集）是包含未分组列的表的主键，则存在功能依赖性。

更明确地说：

GROUP BY仅当表的主键包含在GROUP BY列表中时，PostgreSQL 才识别功能依赖性（允许从 中省略列）。SQL 标准指定了应识别的附加条件。

由于c.vinis 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;

看：

有关的：

推荐阅读