postgresql - 从 postgresql 中的面向行的表单中获取对话

Question

我有一个包含以下数据的表:

id | client_id | message                       | user_id | incoming
1  | 1         | Hi, anybody there?            | 2       | True
2  | 1         | I need help                   | 2       | True
3  | 1         | Yes, I am here to help you.   | 2       | False
4  | 2         | Did you solve it yet?         | 5       | True
5  | 3         | Is my issue resolved?         | 5       | True
6  | 2         | yes, it is solved             | 5       | False
7  | 5         | Are you happy with us?        | 3       | False
8  | 5         | yes, very much                | 3       | True

客户端正在与用户交谈，incoming=True 表示消息来自客户端，而 False 表示用户已发送消息。我希望结果是这样的:

client_id | client_message                    | user_id  | user_message
1         | Hi, anybody there? I need help    | 2        | Yes, I am here to help you.
2         | Did you solve it yet?             | 5        | yes, it is solved

我希望对话附加在一行中，其中客户端先发送消息，然后用户回复该消息。请注意，最后，在第 7,8 行中，似乎存在对话，但由于用户启动了对话，因此不会将其添加到结果中。 ID 为 5 的行被丢弃，因为它没有任何回复。

我目前的查询是这样的:

SELECT client_id, t1.message as client_message, user_id, t2.message as user_message
FROM conversations c1 INNER JOIN conversations c2
ON c1.client_id=c2.client_id AND c1.user_id=c2.user_id AND c1.incoming=True

但它不会导致正确的响应。任何帮助将不胜感激。谢谢!

Answer 1

这是一个有趣的问题。

查看此 dbfiddle .第一次运行后，取消注释 insert for id 15 以查看 dialog_id 是什么。

with responses as (
  select id, client_id, user_id, 
         row_number() 
           over (order by client_id, user_id, id) 
           as dialog_id
    from conversations
   where incoming = false
), dialogs as (
  select c.*, min(dialog_id) as dialog_id
    from conversations c
    join responses r
      on r.client_id = c.client_id
     and r.user_id = c.user_id
     and r.id >= c.id
   group by c.id, c.client_id, c.message, c.user_id, c.incoming
)
select dialog_id, client_id,
       array_agg(message order by id) 
         filter (where incoming = true) 
           as client_message,
       user_id,
       max(message) 
         filter (where incoming = false) 
           as user_message  
  from dialogs
 group by dialog_id, client_id, user_id
order by dialog_id;