Tengo una tabla con los siguientes datos:
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 | TrueLos clientes están hablando con los usuarios e incoming=True significa que el mensaje es del cliente, mientras que False significa que el usuario ha enviado el mensaje. Quiero que el resultado sea así:
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 solvedQuiero las conversaciones adjuntas en una fila donde el cliente haya enviado el mensaje primero y luego el usuario haya respondido a eso. Fíjate que al final, en las filas 7,8 parece que está la conversación pero como el usuario la inició, no se agrega en el resultado. La fila con ID 5 se descarta porque no tiene ninguna respuesta.
Mi consulta actualmente es así:
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=Truepero no está dando como resultado una respuesta adecuada. Cualquier ayuda será muy apreciada. ¡Gracias!
Este es un problema interesante.
Ver este dbfiddle . Después de ejecutarlo por primera vez, elimine el comentario de la insert para id 15 para ver de qué se trata 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;Este es un ejemplo de libro de texto en mi humilde opinión para demostrar funciones analíticas. Supongo que se pueden repetir varios mensajes entre el mismo par de cliente-usuario en ambas direcciones y desea agrupar los mensajes posteriores que van en la misma dirección, utilizando la identificación como número de secuencia. La posible solución podría ser (algunas filas agregadas por mí mismo):
with t (id, client_id, message, user_id, incoming) as (values (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), (9 , 1 , 'Hi, anybody there again?' , 2 , True), (10 , 1 , 'I need help' , 2 , True), (11 , 1 , 'Yes, I am here to help you.' , 2 , False), (12 , 1 , 'Again.' , 2 , False) ), ch as ( select t.* , case coalesce(incoming != lag(incoming) over ( partition by client_id, user_id order by id ) , true) when true then 1 else 0 end as incoming_changed from t ), groups as ( select ch.* , sum(incoming_changed) over ( partition by client_id, user_id order by id ) as grp from ch ), grouped as ( select client_id , string_agg(message, ' ') as message , user_id , incoming , min(id) as min_id from groups group by client_id, user_id, grp, incoming order by client_id, user_id, min_id ), paired as ( select grouped.* , lead(min_id) over ( partition by client_id, user_id order by min_id ) as response_id from grouped ) select pc.client_id , pc.message as client_message , pu.user_id , pu.message as user_message from paired pc join paired pu on pc.response_id = pu.min_id where pc.incoming Explicación: primero detecta cuando cambia la dirección de la conversación. Divide las filas en grupos. Los mensajes se agregan dentro de cada grupo (ver CTE select * from grouped ). Luego, empareje el mensaje de cada cliente con la respuesta del usuario (si corresponde), que es la siguiente por identificación (espero que esta columna tenga una función de marca de tiempo).