SQL按不同列上的数据分组

戴维·普里菲蒂

我正在使用一个关系数据库，其中有如下数据：

messages        sender    receiver
[1,2,3]      |    A     |    B
[4,5,6]      |    C     |    D
[7,8,9]      |    D     |    C
[10,11,12]   |    B     |    A

我需要像这样在完整的对话中将单独的消息分组：

conversation                 participant_1    particiapant_2
[1,2,3, 10, 11, 12]      |        A         |        B          -- order of participants does not matter
[4,5,6, 7,8,9]           |        C         |        D

我想到了两个可行的解决方案：

翻转发送方和接收方列并创建一个临时表，将其与原始表进行合并，以便该表如下所示：

messages         p_1        p_2
[1,2,3]      |    A     |    B
[4,5,6]      |    C     |    D
[7,8,9]      |    D     |    C
[10,11,12]   |    B     |    A
[1,2,3]      |    B     |    A
[4,5,6]      |    D     |    C
[7,8,9]      |    C     |    D
[10,11,12]   |    A     |    B

最后按p_1或p_2分组并在元素数组上使用和聚合函数将产生结果。

第二种解决方案是为会话分配一个特殊的ID，如果发送方和接收方以及接收方和发送方相等，则如下所示：

messages        sender    receiver      id
[1,2,3]      |    A     |    B       |   1
[4,5,6]      |    C     |    D       |   2
[7,8,9]      |    D     |    C       |   2
[10,11,12]   |    B     |    A       |   1

按ID分组将使我在发送方和接收方列中使用聚合函数，因为否则分组将失败。因此，我认为解决方案1会更好。

但是，我不知道如何用SQL很好地表达这些解决方案。还是对此有更好，更优化的解决方案？如果是这样，您怎么能解决这样的问题？

编辑：原始表的架构是这样的：

message             from           to         message_sid
"message 1"     |     A       |     B      |   "SM716241"  
"message 2"     |     A       |     B      |   "SM762415"
"test"          |     B       |     A      |   "SM723741"
reply           |     C       |     D      |   "SM142469"

我以第一个表的形式提出的查询是这样的：

select json_agg(json_build_object('message', body, 'date', date_sent, 'direction', direction)),
       message.from as sender,
       message.to   as receiver
from message
group by message.from, message.to;

我放置了一个更简化的模式版本，以使其更易于理解。该查询还包括日期和消息方向（入站或出站），但它们对于问题不是必需的。

凯斯·贾德

您可以使用'A'<'B'来交换它们的事实，例如：

SELECT
  CASE WHEN sender < receiver THEN sender ELSE receiver END as participant1,
  CASE WHEN sender < receiver THEN receiver ELSE sender END as participant2

然后对您的消息ID进行字符串结尾（或者是存储您的消息，我不太理解问题中消息ID的表示形式）。这意味着您无需繁琐的“ x / y联合y / x”即可获得一致的“对话方列表”

另外，在PGSQL中，您拥有可以实现相同功能的GREATEST和LEAST函数：

SELECT
  LEAST(sender, receiver) as participant1,
  GREATEST(sender, receiver) as participant2

select json_agg(json_build_object('message', body, 'date', date_sent, 'direction', direction)),
       GREATEST(message.from,message.to) as party1,
       LEAST(message.from,message.to) as party2
from message
group by GREATEST(message.from, message.to), LEAST(message.from, message.to) ;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。