postgresql：如何从group by子句中获取主键？

Question

这是一个选择一组所需行的查询：

select max(a), b, c, d, e
from T
group by b, c, d, e;

该表有一个主键，位于列 id 中。

我想通过从每一行获取主键来在进一步的查询中识别这些行。我该怎么做呢？这不起作用：

select id, max(a), b, c, d, e
from T 
group by b, c, d, e;

ERROR:  column "T.id" must appear in the GROUP BY clause or be used in an aggregate function

我通过研究其他一些 postgresql 问题尝试过这个，但没有运气：

select distinct on (id) id, max(a), b, c, d, e
from T 
group by b, c, d, e;

ERROR:  column "T.id" must appear in the GROUP BY clause or be used in an aggregate function

我该怎么办？我知道每个结果只能有一个 id，因为它是主键...我确实想要主键以及其余数据，对于初始（工作）的每一行查询返回。

Answer 1

只是好奇将其添加到 group by 子句是否有效？

select T.id, max(a), b, c, d, e
from T 
group by T.id b, c, d, e;

Answer 2

使用原始查询作为子查询，并使用这些结果连接回原始表以查找 id。

SELECT T.id, T.a, T.b, T.c, T.d, T.e
    FROM (SELECT max(a) AS MaxA, b, c, d, e
              FROM T
              GROUP BY b,c,d,e) q
        INNER JOIN T
            ON T.a = q.MaxA
                AND T.b = q.b
                AND T.c = q.c
                AND T.d = q.d
                AND T.e = q.e

Answer 3

这不是你问的，但我怀疑你想要做的是获取与由其他几列定义的每个组的一列的最大值相对应的单行。例如，查找全年最热的星期一/星期二等的一天。

我发现执行此操作的最佳方法是使用视图来查找组的所有最大值。如果您的原始表

create table T as (
  id integer primary key,
  a integer,
  b integer,
  c integer,
  d integer)

创建“最大”视图

create view T_maxgroups as 
  select max(a) as a, b, c, d 
  from T
  group by b, c, d

（这是您的初始查询），然后将该视图加入到您的表中以获取具有最大值的行：

select T.* from T join maxgroups using (a,b,c,d) 

Answer 4

由于您分组这一事实，每个返回的记录可能（并且很可能）有多个匹配记录（例如，多个id 值）。

PostgreSQL 非常严格——它不会猜测你的意思。

1. 您可以运行一个子查询
2. 您可以基于 b,c,d,e 运行另一个查询
3. 您可以使用 array_agg 分组函数来获取 id< 的数组/code> 每条记录的值。

请参阅此问题：Postgresql GROUP_CONCAT 等效项？

我建议您将#3 视为最有效的的可能性。

希望这有帮助。谢谢！

Answer 5

如果您不关心获得哪个 id，那么您只需将您的 id 包装在某个聚合函数中，该函数保证为您提供有效的 id.我想到了 max 和 min 聚合：

-- Or min(id) if you want better spiritual balance.
select max(id), max(a), b, c, d, e
from T 
group by b, c, d, e;

---

根据您的数据，我认为使用窗口函数将是一个更好的计划（感谢邪恶的奥托引导到头部） ：

select id, a, b, c, d, e
from (
    select id, a, b, c, d, e, rank() over (partition by b,c,d,e order by a desc) as r
    from T
) as dt
where r = 1