¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Duración de cada estado de PostgreSQL

29 May, 2023 Programación 0

Tengo una tabla que tiene varios estados para cada ticket, por ejemplo:
– ABIERTO
– CERRADO
– RE-ABIERTO
– FINALIZADO
– RECHAZADO
– …
Y cada fila en la tabla de estado tiene la marca de tiempo de cuando se estableció / cambió un estado, algo así:

tiempo	estado
2021-11-22 09:40	ABIERTO
2021-11-22 09:50	CERRADO
2021-11-22 10:10	RE-ABIERTO
2021-11-22 10:30	FINALIZADO
2021-11-22 10:50	CERRADO
2021-11-22 11:30	RE-ABIERTO
2021-11-22 12:10	RECHAZADO

Lo que me interesa es cómo calcular la duración de cada estado en un rango de tiempo. El rango de tiempo puede ser desde una hora hasta un par de días. Usemos un ejemplo para tratar de calcular la duración para cada estado entre

2021-11-22 10:00 – 2021-11-22 11:00
Si echamos un vistazo a la tabla:

tiempo	estado
2021-11-22 09:50	CERRADO
2021-11-22 10:10	RE-ABIERTO
2021-11-22 10:30	FINALIZADO
2021-11-22 10:50	CERRADO
2021-11-22 11:30	RE-ABIERTO

10:00 – 10:10 -> CERRADO
10:10 – 10:30 -> RE-ABIERTO
10:30 – 10:50 -> FINALIZADO
10:50 – 11:00 -> CERRADO

La tabla de resultados se vería algo así:

estado	duración
ABIERTO	00:00
CERRADO	00:20
RE-ABIERTO	00:20
RECHAZADO	00:00
FINALIZADO	00:20

P.D. Como pueden ver, aunque la primera aparición de una entrada de estado en el rango de tiempo seleccionado / deseado (10:00 – 11:00) fue a las 10:10, tenemos que incluir el último estado informado antes del rango de tiempo seleccionado / deseado.

Agradezco la ayuda.

Tags: ANDROID-STATUSBAR, AZURE-DATABASE-POSTGRESQL, DURATION

Answer

davy.ai

May 29, 2023 at 12:50 pm
Para calcular la duración de cada estado en un rango de tiempo dado, podemos utilizar la siguiente consulta SQL:
```
SELECT status,
       sum(
           CASE
             WHEN time >= '2021-11-22 10:00' AND time <= '2021-11-22 11:00'
               THEN
                 (LEAD(time) OVER (ORDER BY time) - time)
             WHEN time < '2021-11-22 10:00' AND LEAD(time) OVER (ORDER BY time) >= '2021-11-22 10:00'
               THEN
                 (LEAD(time) OVER (ORDER BY time) - '2021-11-22 10:00')
             WHEN time < '2021-11-22 10:00' AND LEAD(time) OVER (ORDER BY time) < '2021-11-22 10:00'
               THEN
                 (LEAD(time) OVER (ORDER BY time) - time)
             ELSE 0
           END
       ) AS duración
FROM
   (SELECT time, status,
           ROW_NUMBER() OVER (ORDER BY time) rn
    FROM mytable) t
WHERE
    (time >= '2021-11-22 10:00' AND time <= '2021-11-22 11:00') OR
    (LEAD(time) OVER (ORDER BY time) >= '2021-11-22 10:00' AND time < '2021-11-22 10:00')
GROUP BY
    status;
```
Explicación:
- En primer lugar, seleccionamos el estado y la duración para cada estado dentro del rango de tiempo dado.
- Para calcular la duración, utilizamos una combinación de la declaración CASE y la función LEAD.
- La declaración CASE comprueba tres condiciones:
  - Si la entrada de estado está completamente dentro del rango de tiempo, calculamos la diferencia entre el tiempo actual y el siguiente tiempo.
  - Si la entrada del estado comenzó antes del rango de tiempo pero termina dentro de él, calculamos la diferencia entre el inicio del rango de tiempo y el siguiente tiempo.
  - Si la entrada del estado comenzó antes y termina después del rango de tiempo, calculamos la diferencia entre el tiempo actual y el siguiente tiempo.
- Luego agrupamos los resultados por estado para obtener la duración total de cada estado.
Nota: Esta consulta asume que no hay entradas de estado superpuestas dentro del mismo rango de tiempo. Si hay entradas superpuestas, el cálculo de duración puede no ser preciso.

Comments are closed.