Base de datos

Estrategía 1: Transformar los valores usando CASE WHEN y hacer el cálculo

Algoritmo

Para calcular el tiempo para completar un proceso, necesitamos conocer la diferencia entre el tiempo de inicio y el tiempo de finalización de cada máquina y proceso. Si negamos el tiempo de inicio, podemos obtener la diferencia de tiempo usando SUM(), dado que (-start) + end es igual a end - start, lo cuál indica la diferencia de tiempo.

Para hacer esto, usamos CASE WHEN para multiplicar todos los tiempos de inicio por -1, asi que el total de tiempo se convierte en el tiempo para completar el proceso para cada máquina.

SUM(
    CASE WHEN activity_type = 'start' THEN
        timestamp*-1
    ELSE
        timestamp
    END
)

Dado que necesitamos el promedio por cada machine_id y podría haber múltiples procesos ejecutándose en cada máquina, manualmente calculamos el promedio dividiendo el tiempo del proceso entre el número de procesos. Afortunadamente, para esta pregunta, todas las máquinas tienen el mismo número de procesos.

SUM(
    CASE WHEN activity_type='start' THEN
        timestamp*-1
    ELSE
        timestamp
    END
) * 1.0 / (SELECT COUNT(DISTINCT process_id))

Por último, redondeamos el processing_time a 3 decimales con la función ROUND() y renombramos la columna.

ROUND(
    SUM(
        CASE WHEN activity_type='start' THEN
            timestamp*-1
        ELSE
            timestamp END
    ) * 1.0 / (SELECT COUNT(DISTINCT process_id)), 3
) AS processing_time

Implementación

SELECT
    machine_id,
    ROUND(
        SUM(
            CASE WHEN activity_type='start' THEN
                timestamp*-1
            ELSE
                timestamp END
        ) * 1.0 / (SELECT COUNT(DISTINCT process_id)), 3
    ) AS processing_time
FROM
    Activity
GROUP BY machine_id

Estrategía 2: Usar la tabla original dos veces y calcular como si fueran dos columnas

Algoritmo

Para este método, usamos la tabla dos veces, una vez como la tabla que tiene el tiempo de inicio y otra como la tabla que tiene el tiempo de finalización. Para crear ambos aliases, le asignamos diferentes nombres, y filtramos cada tabla por activity_type. También nos aseguramos de que las dos tablas sea unidas a través de su machine_id y process_id, de manera que el resultado tendrá el tiempo de inicio y tiempo de finalización en dos columnas diferentes para cada máquina y proceso.

SELECT *
FROM Activity a,
     Activity b
WHERE
    a.machine_id = b.machine_id
AND
    a.process_id = b.process_id
AND
    a.activity_type = 'start'
AND
    b.activity_type = 'end'

Un ejemplo de la salida se muenstra enseguida:

machine_id	process_id	activity_type	timestamp	machine_id	process_id	activity_type	timestamp
0	0	start	0.712	0	0	end	1.52
0	1	start	3.14	0	1	end	4.12
1	0	start	0.55	1	0	end	1.55
1	1	start	0.43	1	1	end	1.42
2	0	start	4.1	2	0	end	4.512
2	1	start	2.5	2	1	end	5

Con esta tabla, podemos actualizar el cálculo de processing_time substrayendo los timestamps de la tabla b con los de la tabla a:

SELECT (
    b.timestamp - a.timestamp
) AS processing_time

Dado que queremos el promedio de processing_time por cada máquina, agregamos AVG() al cálculo de processing_time y redondeamos a 3 decimales usando la función ROUND().

SELECT a.machine_id,
       ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time

Implementation

SELECT a.machine_id,
       ROUND(
           AVG(b.timestamp - a.timestamp),
           3
        ) AS processing_time
FROM Activity a,
     Activity b
WHERE
    a.machine_id = b.machine_id
AND
    a.process_id = b.process_id
AND
    a.activity_type = 'start'
AND
    b.activity_type = 'end'
GROUP BY machine_id