使用Postgres聚合函数选择每个GROUP BY组中的第一行？

Question

我有两个表，一个是user用来跟踪用户信息，另一个是users_classes用来跟踪用户和班级（班级在另一张表中）的关系。我想连接这些表并返回用户信息以及他们所在班级的数组。有没有有效的方法可以做到这一点？

表结构如下：

CREATE TABLE users (
  id        BIGSERIAL PRIMARY KEY,
  username  VARCHAR(255) UNIQUE,
  email     VARCHAR(255) NOT NULL UNIQUE
)

CREATE TABLE users_classes (
  user_id   BIGINT REFERENCES users(id),
  class_id  BIGINT REFERENCES classes(id),
  PRIMARY KEY(user_id, class_id)
)

My attempt:

SELECT
  u.id,
  FIRST(u.username) AS username,
  FIRST(u.email) AS email,
  array_agg(c.class_id) AS classes
FROM users_classes AS c
JOIN users AS u
ON c.user_id = u.id
GROUP BY u.id;

我觉得这样可以行（在实现 FIRST() 后），但是否有更标准的方法来完成？

- jhhayashi

---

请提供样本数据、您所得到的结果以及您期望的结果。 - Viki888

你所做的方式非常标准。你按照其他列进行分组，将列值聚合到数组中。有什么看起来不标准的地方吗？ - Vao Tsun

@VaoTsun 我很惊讶Postgres本身没有内置FIRST()函数，尽管他们在他们的维基页面上有一个First/last_(aggregate)。 - jhhayashi

这只是一个创建聚合函数的示例。现有的列表在这里：https://www.postgresql.org/docs/current/static/functions-aggregate.html - Vao Tsun

哦，我的意思是我很惊讶没有像他们的FIRST()示例那样已经存在的函数，因为我认为我正在尝试做的事情相对常见。 - jhhayashi

我的意思是在你的情况下，尝试将“first”替换为“MIN”。 - Vao Tsun

Answer 1

Postgres要求您指定如何在组中选择行投影，因此您必须为每个选择投影指定聚合函数。

您可以使用mode()来选择组中的第一/最后一行。以下是基于您的问题的示例：

SELECT
  mode() WITHIN GROUP (ORDER BY u.id) as userId,
  mode() WITHIN GROUP (ORDER BY u.username DESC) as username, -- pick last username
  mode() WITHIN GROUP (ORDER BY u.email) as email,
  array_agg(c.class_id) AS classes
FROM users_classes AS c
JOIN users AS u
ON c.user_id = u.id
GROUP BY u.id;

- Hank

Answer 2

只需要将列添加到GROUP BY子句中，就像这样：

SELECT
  u.id,
  u.username,
  u.email,
  array_agg(c.class_id) AS classes
FROM users_classes AS c
  JOIN users AS u
    ON c.user_id = u.id
GROUP BY u.id, u.username, u.email;

- Laurenz Albe