Cómo encontrar la diferencia entre los valores de la misma columna en diferentes filas con una condición en Redshift?

Answer

1. 

   davy.ai

   July 17, 2023 at 4:37 am

   Para calcular el tiempo que cada usuario pasa en cada habitación, puedes utilizar una combinación de las funciones lag() y datediff() en Redshift. Aquí hay una manera de hacerlo:

   CON joined_left AS (
     SELECT room_id, user_id, event, time,
       LAG(time) OVER (PARTITION BY room_id, user_id ORDER BY time) AS prev_time,
       LAG(event) OVER (PARTITION BY room_id, user_id ORDER BY time) AS prev_event
     FROM chat_room
   )
   SELECT room_id, user_id, SUM(DATEDIFF(second, prev_time, time)) AS time_spend
   FROM joined_left
   WHERE prev_event = 'join' AND event = 'leave'
   GROUP BY room_id, user_id
   

   Así es como funciona esta consulta:

   1. La cláusula WITH crea una subconsulta que utiliza LAG() para obtener los valores anteriores de event y time para cada combinación de room_id/user_id. Esto nos permite comparar el evento actual de join o leave con el anterior y calcular el tiempo pasado en la habitación.
   2. La consulta principal selecciona room_id, user_id y la suma del tiempo pasado en segundos (DATEDIFF()) entre el evento anterior y el actual para cada combinación de room_id/user_id donde el evento anterior fue un join y el evento actual es un leave.
   3. Los resultados se agrupan por room_id y user_id.

   Esto debería darte el resultado deseado:

    room_id | user_id | time_spend
   ---------+---------+------------
          1 |       1 |         60
          1 |       2 |         20
          1 |       3 |         40
   

   Ten en cuenta que el tiempo que el usuario 3 pasa en la habitación es de 40 segundos porque ingresó a la habitación dos veces y la dejó una vez.

Comments are closed.