行数没有区别 [英] Rows count without distinct

问题描述

我有这个查询：

SELECT DISTINCT ON (pair) *,
    CASE WHEN sender_id<recipient_id
      THEN (sender_id,recipient_id)
      ELSE (recipient_id,sender_id)
    END AS pair FROM "messages" WHERE 
    ((recipient_id = 6 and recipient_delete = false) or 
    (sender_id = 6 and sender_delete = false))  
    GROUP BY messages.id
    ORDER BY  pair, created_at, unread_count DESC

哪个带来了用户对话的最新消息（用户收件箱）。

Which brings latest message of a conversation of a user(User Inbox).

现在，我需要显示未读邮件的总数每次对话时，已打开表中都有一个标志，用于指示消息是否未读。

Now I need to show total unread messages counts for each conversation, there is a flag in the table 'opened', which states whether a message is unread or not.

这是我为此编写的查询：

This is the query I wrote for it:

SELECT DISTINCT ON (pair) *,
    CASE WHEN sender_id<recipient_id
      THEN (sender_id,recipient_id)
      ELSE (recipient_id,sender_id)
    END AS pair, COUNT(messages.opened= false) AS unread_count FROM "messages" WHERE 
    ((recipient_id = 6 and recipient_delete = false) or 
    (sender_id = 6 and sender_delete = false))  
    GROUP BY messages.id
    ORDER BY  pair, created_at, unread_count DESC

但是即使在有超过2或3条消息（我想这是由于不同）。

But it shows count a 1 even when there are more than 2 or 3 messages(I guess this is because of Distinct).

任何想法吗？

如果您想知道为什么我们有用户 CASE WHEN 函数，请检查此问题：

If you need to know why we have user CASE WHEN function please check this question :

推荐答案

我是有点困惑，因为您的逻辑似乎带来了最早的消息而不是最新消息。

I'm a little confused, because your logic would appear to bring the earliest message not the latest one.

尽管上的区别非常强大，但我不确定您是否能轻松获得想要的东西。我的倾向是切换到使用窗口函数：

Although distinct on is quite powerful, I'm not sure if you can readily get what you want. My inclination is to switch to using window functions:

SELECT m.*
FROM (SELECT m.*,
             (CASE WHEN sender_id < recipient_id
                   THEN (sender_id, recipient_id)
                   ELSE (recipient_id, sender_id)
              END) AS pair,
             ROW_NUMBER() OVER (PARTITION BY (CASE WHEN sender_id < recipient_id
                                                   THEN (sender_id, recipient_id)
                                                   ELSE (recipient_id, sender_id)
                                              END)
                                ORDER BY created_at, unread_count DESC
                               ) as seqnum,
             SUM(CASE WHEN m.opened = false THEN 0 ELSE 1 END) OVER
                 (PARTITION BY (CASE WHEN sender_id < recipient_id
                                     THEN (sender_id, recipient_id)
                                     ELSE (recipient_id, sender_id)
                                END)
                 ) as NumUnopened
      FROM "messages" m
      WHERE ((recipient_id = 6 and recipient_delete = false) or 
             (sender_id = 6 and sender_delete = false))  
     ) t
WHERE seqnum = 1;

这篇关于行数没有区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！