sql - 如何通过两列复合键的任一排列对行进行分组

Question

不确定该问题的措辞是否应清晰（这是我能想到的最好的），但这是一个澄清问题的示例。我有一个观点，Chats应该是总结两个人之间的对话历史。该视图由以下列组成：Sender、Recipient、Timestamp和。LatestMessageUnreadMessageCount

视图的Chats列都来自一个表，Direct_Messages该表存储有关系统用户之间交换的个人聊天消息的详细信息。以下是它的列： ID, Sender, Recipient, Body, Timestamp, TimeRead（如果邮件尚未被收件人阅读，则为 null）。视图Timestamp和LatestMessage列具有两个参与者之间最新的直接消息的值（TimestampFWIW 的最新消息）。

问题实际上源于这样一个事实，即视图中应该只存在一个组合列的排列，Sender即RecipientChats两个参与者之间的最新交换的排列。例如，如果 Gary 向 Barry 发送了一条“Hi”消息，那么 Barry 回复了“Hello”——Chats这两个人之间的唯一条目应该是Senderas 'Barry'，Recipientas 'Gary'，Timestamp作为时间戳Barry 的回复，LatestMessage作为 'Hello' 和 UnreadMessageCount 作为Recipient尚未阅读的消息数。

我尝试过使用GROUP BY "Sender", "Recipient" OR "Recipient", "Sender"，但它只返回两列：一列按 Barry、Gary 分组；另一个由 Gary、Barry 分组

这是我的代码：

SELECT Sender AS Sender,
       Recipient AS Recipient,
       Timestamp AS Timestamp,
       Body AS LatestMessage,
       (SUM(CASE WHEN TimeRead IS NULL THEN 1 ELSE 0 END) ) AS UnreadMessageCount
FROM Direct_Messages
GROUP BY Sender, Recipient OR Recipient, Sender
ORDER BY Timestamp DESC

编辑：这是表中的示例数据和视图Direct_Messages中的相应输出Chats

从Direct_Messages

ID          Sender  Recipient   Body    Timestamp                   TimeRead
148567984   Gary    Barry       Hi      2018-12-12 23:53:39.487     2018-12-12 23:55:45
1668701120  Barry   Gary        Hello   2018-12-12 23:54:49.326     NULL

结果Chats：

Sender  Recipient   Timestamp                 LatestMessage UnreadMessageCount
Gary    Barry       2018-12-12 23:53:39.487   Hi            0
Barry   Gary        2018-12-12 23:54:49.326   Hello         1

标签： sqldatabasesqlitegroup-by

Answer 1

您可以“预先收集”您的数据，以便始终让来自每个用户组合的消息朝着相同的方向发送。

示例，如果您的数据是：

Sender Recipient
A ---> B
B ---> A

您将其更改为：

U1     U2
B ---> A (changed)
B ---> A

像这样：

SELECT (case when Sender > Recipient then Sender else Recipient end) AS u1,
       (case when Sender > Recipient then Recipient else Sender end) AS u2,
       Timestamp AS Timestamp,
       Body AS LatestMessage,
       (SUM(CASE WHEN TimeRead IS NULL THEN 1 ELSE 0 END) ) AS UnreadMessageCount
FROM Direct_Messages_cooked
GROUP BY 
     (case when Sender > Recipient then Sender else Recipient end), 
     (case when Sender > Recipient then Recipient else Sender end) 
ORDER BY Timestamp DESC

注意：注意性能（我想这并不重要，因为您被标记为 sqlite 的问题）

您可以使用 CTE 来预先处理您的数据并获得更具可读性的查询：

with Direct_Messages_coocked as
(
    select
      (case when Sender > Recipient then Sender else Recipient end) AS U1,
      (case when Sender > Recipient then Recipient else Sender end) AS U2,
      *
    from Direct_Messages
)
SELECT U1 AS U1,
       U2 AS U2,
       Timestamp AS Timestamp,
       Body AS LatestMessage,
       (SUM(CASE WHEN TimeRead IS NULL THEN 1 ELSE 0 END) ) AS UnreadMessageCount
FROM Direct_Messages_coocked
GROUP BY U1, U2
ORDER BY Timestamp DESC