PostgreSQL使用GROUP BY去重

Question

我希望打印每个人的最后一条信息，但每个人只能打印他们最新的信息。我使用的是PostgreSQL 10。

+-----------+----------+--------------+
| name      |   body   |  created_at  |
+-----------+----------+--------------+
| Maria     | Test3    |  2017-07-07  |
| Paul      | Test5    |  2017-06-01  |
+-----------+----------+--------------+

我已经尝试使用以下SQL查询，能够正确地返回我需要的内容，但是很遗憾其中的人员重复了。

SELECT * FROM messages 
WHERE receive = 't'
GROUP BY name
ORDER BY MAX(created_at) DESC

+-----------+----------+--------------+
| name      |   body   |  created_at  |
+-----------+----------+--------------+
| Maria     | Test1    |  2016-06-01  |
| Maria     | Test2    |  2016-11-01  |
| Maria     | Test3    |  2017-07-07  |
| Paul      | Test4    |  2017-01-01  |
| Paul      | Test5    |  2017-06-01  |
+-----------+----------+--------------+

我试图使用DISTINCT去除重复项，但是不幸地收到了以下错误消息：

SELECT DISTINCT ON (name) * FROM messages 
WHERE receive = 't'
GROUP BY name
ORDER BY MAX(created_at) DESC

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: SELECT DISTINCT ON (name) * FROM messages ^ : SELECT DISTINCT ON (name) * FROM messages WHERE receive = 't' GROUP BY name ORDER BY MAX(created_at) DESC

你有任何解决这个问题的想法吗？

- Viseo

Answer 1

您可以按以下方式使用DISTINCT ON:

SELECT DISTINCT ON (name) * 
FROM messages 
WHERE receive = 't'
ORDER BY name, created_at DESC

那就是：

• 不需要GROUP BY子句

• DISTINCT ON(...)中列出的列必须先在ORDER BY子句中出现

• ...然后是用于打破组的列(这里是created_at)

请注意，distinct on查询结果总是按照子句中的列进行排序(因为这个排序用于确定哪些行应该被保留)。

如果您想更好地控制排序顺序，则可以使用窗口函数：

SELECT *
FROM (
    SELECT m.*, ROW_NUMBER() OVER(PARTITION BY name ORDER BY created_at DESC) rn
    FROM messages m
    WHERE receive = 't'
) t
WHERE rn = 1
ORDER BY created_at DESC

- GMB

Answer 2

使用DISTINCT ON，但要按照正确的ORDER BY排序:

SELECT DISTINCT ON (name) m.*
FROM messages m
WHERE receive = 't'
ORDER BY name, created_at DESC;

一般情况下，不会在GROUP BY中使用DISTINCT ON，而是与ORDER BY一起使用。它的工作原理是根据ORDER BY子句选择每个name的第一行。

您不应该将自己所做的事视为聚合。您想基于created_at进行过滤。在许多数据库中，您可以使用相关子查询来表达此内容：

select m.*
from messages m
where m.created_at = (select max(m2.created_at)
                      from messages m2
                      where m2.name = m.name and m2.receive = 't'
                     ) and
      m.receive = 't';   -- this condition is probably not needed

- Gordon Linoff

---

但是，然后按名称排序。当我尝试使用子查询进行此操作时，最新的条目会丢失。为什么会这样？ - Viseo

Answer 3

SELECT * 
FROM messages 
WHERE receive = 't' and not exists (
    select 1
    from messages m
    where m.receive = message.receive and messages.name = m.name and m.created_at > messages.created_at
)
ORDER BY created_at DESC

上面的查询找到满足以下标准的消息：

• receive为't'
• 不存在另一条消息
  • 具有相同的receive值
  • 具有相同的名称
  • 并且是更新的

假设相同的名称不会在完全相同的时间发送两条消息，这应该足够了。另一个要注意的问题是，如果某些空格字符存在于值中，则名称可能看起来相似但实际上是不同的，因此，如果在上述查询结果中看到具有相同名称但创建时间不同的两个记录，则很可能是空格字符在捣鬼。

- Lajos Arpad