En el ejemplo a seguir, tenemos un sistema de registro de tickets, con una tabla histórica que registra el momento en que un ticket cambió de estado.
Lo que queremos hacer es un reporte con el tiempo total en cada uno de esos estados, con un único SQL.
Esta no es una consulta trivial, ya que cada registro contiene únicamente una fecha, por tanto tendremos que asociar al menos dos de ellos para obtener un intervalo de tiempo.
Para complicar un poco, lo resolveremos primero con MySQL, que tiene bastante menos potencial que Oracle para este tipo de consultas. De hecho este ejercicio surgió a partir de la necesidad de un colega trabajando con ese motor.
Comenzamos creando la tabla:
CREATE TABLE status_historyIncluyamos nuestro primer ticket, con algunos cambios de estado entre A, B y C. Los estados tienen un orden cronológico y es posible volver al mismo repetidas veces.
(id INTEGER PRIMARY KEY,
ticket_id INTEGER,
estado VARCHAR(1),
fecha DATETIME);
INSERT INTO status_history VALUES (1, 1, 'A', '2010-01-01 14:00:00');
INSERT INTO status_history VALUES (2, 1, 'B', '2010-01-03 18:30:00');
INSERT INTO status_history VALUES (3, 1, 'C', '2010-01-07 10:00:00');
INSERT INTO status_history VALUES (4, 1, 'A', '2010-01-11 12:10:00');
INSERT INTO status_history VALUES (5, 1, 'C', '2010-01-14 15:00:00');
Observando los datos de la tabla, deducimos que la diferencia de tiempo entre el segundo registro 'B' y el primero 'A' es tiempo transcurrido en estado 'A' (en el diagrama representado como tA1). Lo mismo para el último intervalo tA2. La suma de tA1 y tA2 representará el tiempo total que el ticket estuvo en estado 'A'.
La estrategia será usar un auto-join y relacionar los registros n y n+1. Para eso necesitamos tener un campo secuencial y sin huecos para que la igualdad funcione.
Veremos primero el caso simple donde cada registro tiene un id consecutivo, y luego nos enfocaremos en resolver el problema de los huecos.
CASO 1 - IDs continuos (1,2,3,4,5....)
En este caso, sabemos que cada id está a una distancia 1 del id siguiente (vale también para otras diferencias), por tanto haremos el join usando la condición id=id+1 para obtener las fechas y la diferencia entre ellas.
SELECT s1.estado,
TIME_TO_SEC(timediff(s2.fecha,s1.fecha))/60/60 AS total_horas
FROM
status_history s1, status_history s2
WHERE s1.id+1 = s2.id
AND s1.ticket_id=1
AND s2.ticket_id=1
Para manipular la diferencia entre dos fechas, usamos primero TIMEDIFF que me retorna un tipo TIME (HH:MI:SS) y luego TIME_TO_SEC para obtener la cantidad total de segundos.
Obtenemos las siguientes tuplas:
ESTADO TOTAL_HORAS
====== ============
'A' 52.50000000
'B' 87.50000000
'C' 98.16666667
'A' 74.83333333
Cabe observar que aparecen dos instancias del estado A, que en el diagrama anterior equivalen a tA1 y tA2. Para obtener la suma total, agrupamos por estado y utilizamos la función SUM:
SELECT s1.estado,
SUM(TIME_TO_SEC(timediff(s2.fecha,s1.fecha)))/60/60 AS total_horas
FROM
status_history s1, status_history s2
WHERE s1.id+1 = s2.id
AND s1.ticket_id=1
AND s2.ticket_id=1
GROUP BY estado
Finalmente el resultado deseado:
ESTADO TOTAL_HORASCASO 2 - IDs con huecos (1,2,4,5,9....)
====== ============
'A' 127.33333333
'B' 87.50000000
'C' 98.16666667
En el caso anterior, podiamos usar la condicion id=id+1, ya que estabamos seguros que siempre iba a haber una correspondencia (con excepción del último registro). En el caso de existir huecos, el id ya no nos sirve; necesitamos otro campo para ese fin. En Oracle, esto sería muy fácil con la columna virtual rownum, pero en MySQL no tenemos ese recurso.
Hay sin embargo, una forma de simular rownum, y es usando una variable declarada en SQL, inicializada en un select interior. La sintáxis no queda muy amigable pero sirve al propósito de este problema, que es tener un campo secuencial sin huecos.
Columna virtual ROWNUM con MySQL:
SELECT @rownum:=@rownum+1 rownum, t.*Ahora si, podemos sustituir las tablas por este código y usar rownum como condición.
FROM (SELECT @rownum:=0) r, <tabla o consulta> t
Para probarlo con nuestro ejemplo, agregaremos un nuevo cambio de estado, con id=8:
INSERT INTO status_history VALUES (8, 1, 'D', '2010-01-16 11:00:00');La secuencia de ids ahora es 1, 2, 3, 4, 5, 8
La consulta queda así:
SELECT t1.estado,Observar que cada subconsulta utiliza su propia variable @rownum, ya que si usarámos la misma, se incrementaría incorrectamente.
SUM(TIME_TO_SEC(timediff(t2.fecha,t1.fecha)))/60/60 AS total_horas
FROM
(SELECT @rownum1:=@rownum1+1 rownum, s1.*
FROM (SELECT @rownum1:=0) r, status_history s1
WHERE s1.ticket_id=1
ORDER BY s1.id) t1,
(SELECT @rownum2:=@rownum2+1 rownum, s2.*
FROM (SELECT @rownum2:=0) r, status_history s2
WHERE s2.ticket_id=1
ORDER BY s2.id) t2
WHERE t1.rownum+1 = t2.rownum
GROUP BY t1.estado
El resultado es:
ESTADO TOTAL_HORASSolamente el tiempo de 'C' se vio incrementado con el pasaje de 'C' a 'D'. El estado 'D' no tiene tiempo asociado por tratarse del estado terminal.
====== ============
'A' 127.33333333
'B' 87.50000000
'C' 142.16666667
La solución Oracle con Funciones Analíticas
Como parte final, voy a mostrar cómo el poder de las funciones analíticas de Oracle nos permiten resolver este tipo de problemas con elegancia y sencillez, sin preocuparnos con auto-joins, rownums ni huecos.
La función ideal en este caso es LEAD. Esta función nos retorna el siguiente registro basado en algun criterio de ordenación, que puede ser una columna.
Todo se resume a usar lo siguiente:
LEAD(fecha, 1, NULL) OVER(ORDER BY id)
- El primer parámetro es la columna que voy a retornar, en mi caso, fecha.
- El segundo es el offset que quiero recuperar, es decir cuantas filas 'adelante'.
- El tercer parámetro es el valor que quiero mostrar si no hay siguiente registro.
- Por último, la columna que voy a usar como criterio de ordenación.
SELECT t1.estado,Estoy restando la fecha del registro siguiente (recuperado por LEAD) al registro actual. Ver que tampoco necesito funciones de fechas, basta con restarlas y multiplicar por 24.
(LEAD(fecha, 1, NULL) OVER(ORDER BY id) - t1.fecha)*24 AS total_horas
FROM status_history t1
ORDER BY id
Solo resta agrupar los estados y sumar los totales en una query exterior:
SELECT estado,El resultado es el mismo, lo pueden comprobar si se animan a hacer la prueba.
SUM(total_horas) total_horas
FROM (SELECT t1.estado,
(LEAD(fecha, 1, NULL) OVER(ORDER BY id) - t1.fecha)*24 total_horas
FROM status_history t1
ORDER BY id)
WHERE total_horas IS NOT NULL
GROUP BY estado
ORDER BY estado;
Tengo que agregar la condición total_horas IS NOT NULL, ya que el uso de LEAD no impide que se muestre el estado D con tiempo NULL (recuerdan el tercer parámetro de LEAD?), que en este caso no nos interesa.
Como comentario, la función LAG nos retorna el registro anterior usando la misma sintáxis que LEAD.
Conclusiones
Las funciones analíticas son útiles y necesarias en muchos casos. Además de ser más eficientes, nos permiten resolver rápidamente el problema de agrupar y buscar relaciones entre filas de una misma consulta.
Bases de datos como MySQL no poseen estos recursos, y nos obligan a resolver el problema con mucho más esfuerzo. Esperemos que con la nueva adquisición, Oracle piense en incorporarlas, para beneficio de quienes trabajan con SQL :)
Mientras tanto, en el caso de MySQL lo importante es determinar cuál es la condición que define la consecutividad entre dos registros, teniendo cuidado si hay huecos. En el caso de Oracle, simplemente es encontrar una relación de precedencia entre dos registros.
Ver también:
Documentación de Funciones Analíticas en Oracle 10g
