按不同列排序时选择唯一值

Question

我来自MySQL背景，在那里，GROUP BY与Postgres有很大不同。在Postgres - 以及任何基于标准的SQL数据库中 - 您必须按所有选定的列进行分组，而在MySQL中，您可以挑选要分组的列。

我读到，您可以通过DISTINCT ON获得等效的效果，并且在大多数情况下确实如此。问题在于，您必须按所有不同的列进行ORDER BY，而此排序必须是最左边的排序。当我想首先按另一列排序时，这就成为了一个问题。

现在我的查询看起来像这样：

SELECT
  DISTINCT ON (eventable_id, eventable_type)
           events.eventable_id, events.eventable_type, events.*
  FROM events
  WHERE <query>
  ORDER BY eventable_id, eventable_type, events.created_at DESC

我希望将排序方式更改为以下方式：

  ORDER BY events.created_at, eventable_id, eventable_type DESC

有什么建议可以让这个工作起来吗？

- nullnullnull

---

你的表的主键是什么？你使用的是哪个版本的Postgres？ - Erwin Brandstetter

Answer 1

由于您选择了events.*，因此不应将eventable_id和eventable_type冗余地添加到输出列中。这将导致重复的列名。您知道在目标列表中不必包含DISTINCT ON子句中的列，对吗？

此外，直接使用eventable_type DESC可能会更快，因为您在最终排序顺序中已经包含了它。这也是可以的。

SELECT DISTINCT ON (eventable_id, eventable_type)
       *
FROM   events
WHERE  <condition>
ORDER  BY eventable_id, eventable_type DESC, created_at DESC

@Denis已经涵盖了其余部分：将其作为子查询并按您喜欢的方式在外部查询中排序。

另一种选择是使用带有GROUP BY和max()的子选择，但当每个组的最新created_at不唯一时，它会产生多列。 （可能是可取的，也可能不是。）而且它可能仍然比DISTINCT ON慢，在额外的ORDER BY步骤中。 使用EXPLAIN ANALYZE进行测试。

SELECT e.*
FROM   events e
JOIN  (
   SELECT eventable_id, eventable_type, max(created_at) AS created_at
   FROM   events
   WHERE  <condition>
   GROUP   BY 1, 2 DESC
   ) sub USING (eventable_id, eventable_type, created_at) -- maybe not unique
WHERE  <repeat condition if dupes may be eliminated>
ORDER  BY e.created_at, e.eventable_id, e.eventable_type DESC

- Erwin Brandstetter

---

谢谢你们两个。我之前不熟悉子查询，现在开启了很多新的可能性。 - nullnullnull

Answer 2

如果Postgres报错，请使用子查询：

select * from ( ... ) q order by ...

如果确实出现这种情况，我会将其视为查询计划不佳的提示。

- Denis de Bernardy

---

这可能会非常缓慢，而且不会正确支持LIMIT或OFFSET。 - Kevin Parker

@KevinParker：不过，任何按某列分组并按另一列排序的查询都会很慢... :) - Denis de Bernardy