sql - 按外键分组时使用 MAX(id) 获取整行的有效方法

Question

考虑表 A、B 和 C。B 和 C 通过外键与 A 关联，并且有许多 B 和 C 具有相同的 A 外键。

假设以下查询:

SELECT
  A.pk AS pk_a,
  MAX(B.id) AS new_b,
  MAX(C.id) AS new_c
FROM A
INNER JOIN B ON B.fk_a = pk_a
INNER JOIN C ON C.fk_a = pk_a
GROUP BY pk_a

我想为每个 GROUP BY pk_a 从 B 和 C 检索整个 new_b 和 new_c 行。

当然，我可以将其包装为一个子选择并 JOIN B ON b.id = new_b，对于 C 也是如此，但是 B 和 C 很大，我会想避免这种情况。

我还可以使用 SELECT DISTINCT ON(A.pk) A.pk, B.*, C.* 和 ORDER BY A.pk, B.id, C.id ，但这只能保证最新的 B.，而不是最新的 C.。

我还缺少其他方法吗？

Answer 1

在 B 和 C 中， 中的每行 很少 行(例如平均 2 或 3 或 5 行，视情况而定) A，DISTINCT ON 通常最快。

对于A 中的每行许多 行，有(很多)更有效的解决方案。而您的信息:“B 和 C 很大” 也表明了这一点。
我建议使用 ORDER BY 和 LIMIT 1 的 LATERAL 子查询，由匹配索引支持。

SELECT A.pk AS pk_a, B.*, C.*
FROM   A
LEFT   JOIN LATERAL (
   SELECT *
   FROM   B
   WHERE  B.fk_a = A.pk  -- lateral reference
   ORDER  BY B.id DESC
   LIMIT  1
   ) B ON true
LEFT JOIN LATERAL (
   SELECT *
   FROM   C
   WHERE  C.fk_a = A.pk  -- lateral reference
   ORDER  BY C.id DESC
   LIMIT  1
   ) C ON true;

假设 B.id 和 C.id 是 NOT NULL。

您需要至少在 FK 列上建立索引。理想情况下，B (fk_a, id DESC) 和 C (fk_a, id DESC) 上的多列索引。

使用LEFT JOIN!不排除 A 中未在 B 或 C 中引用的行。在这里使用 [INNER] JOIN 将是一个邪恶的陷阱，因为您连接到两个不相关的表。

详细解释:

• Optimize GROUP BY query to retrieve latest record per user

相关:

• Select first row in each GROUP BY group?
• What is the difference between LATERAL and a subquery in PostgreSQL?

具有智能命名约定的更简单语法

上面的查询结果有一次pk_a和两次fk_a。无用的镇流器 - 两次相同的列名可能是一个实际问题，具体取决于您的客户。

您可以在外部 SELECT 中拼出一个列列表(而不是语法快捷方式 A.*, B.*)以避免冗余。如果有更多重复名称，或者如果您不想要所有 列，您可能必须以任何一种方式执行此操作。

但是通过智能命名约定，USING 子句可以为您折叠冗余的 PK 和 FK 列:

SELECT *
FROM   A
LEFT   JOIN LATERAL (
   SELECT * FROM B
   WHERE  B.a_id = A.a_id
   ORDER  BY B.id DESC
   LIMIT  1
   ) B USING (a_id)
LEFT   JOIN LATERAL (
   SELECT * FROM C
   WHERE  C.a_id = A.a_id
   ORDER  BY C.id DESC
   LIMIT  1
   ) C USING (a_id);

逻辑上，USING (a_id) 在这里是多余的，因为子查询中的 WHERE B.a_id = A.a_id 已经过滤了相同的方式。但是 USING 的附加效果是连接列被折叠成 一个 实例。所以只有 一个 a_id 保留在结果中。 The manual:

Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.

对相同的数据使用相同的名称通常也很有意义。所以:a_id 用于 PK 和 FK 列。