tabla - t sql temporal tables
Agregar solo registros adyacentes con T-SQL (4)
Tengo (simplificado para el ejemplo) una tabla con los siguientes datos
Row Start Finish ID Amount
--- --------- ---------- -- ------
1 2008-10-01 2008-10-02 01 10
2 2008-10-02 2008-10-03 02 20
3 2008-10-03 2008-10-04 01 38
4 2008-10-04 2008-10-05 01 23
5 2008-10-05 2008-10-06 03 14
6 2008-10-06 2008-10-07 02 3
7 2008-10-07 2008-10-08 02 8
8 2008-10-08 2008-11-08 03 19
Las fechas representan un período en el tiempo, la ID es el estado en que se encontraba un sistema durante ese período y la cantidad es un valor relacionado con ese estado.
Lo que quiero hacer es agregar las cantidades para las filas adyacentes con el mismo número de identificación, pero mantener la misma secuencia general para que se puedan combinar las ejecuciones contiguas. Por lo tanto, quiero terminar con datos como:
Row Start Finish ID Amount
--- --------- ---------- -- ------
1 2008-10-01 2008-10-02 01 10
2 2008-10-02 2008-10-03 02 20
3 2008-10-03 2008-10-05 01 61
4 2008-10-05 2008-10-06 03 14
5 2008-10-06 2008-10-08 02 11
6 2008-10-08 2008-11-08 03 19
Estoy buscando una solución de T-SQL que se pueda poner en un SP, sin embargo, no puedo ver cómo hacerlo con consultas simples. Sospecho que puede requerir iteración de algún tipo pero no quiero ir por ese camino.
El motivo por el que quiero hacer esta agregación es que el siguiente paso en el proceso es hacer una SUMA () y un Conteo () agrupados por las identificaciones únicas que ocurren dentro de la secuencia, de modo que mis datos finales se vean de la siguiente manera:
ID Counts Total
-- ------ -----
01 2 71
02 2 31
03 2 33
Sin embargo, si hago un simple
SELECT COUNT(ID), SUM(Amount) FROM data GROUP BY ID
En la tabla original obtengo algo así como
ID Counts Total
-- ------ -----
01 3 71
02 3 31
03 2 33
Que no es lo que quiero
Probablemente necesite crear un cursor y recorrer los resultados, mantener un registro de la ID con la que está trabajando y acumular datos a lo largo del camino. Cuando la identificación cambia, puede insertar los datos acumulados en una tabla temporal y devolver la tabla al final del procedimiento (seleccione todo de ella). Una función basada en tablas podría ser mejor, ya que puede simplemente insertarla en la tabla de resultados a medida que avanza.
Sospecho que puede requerir iteración de algún tipo pero no quiero ir por ese camino.
Creo que esa es la ruta que tendrá que tomar, use un cursor para poblar una variable de tabla. Si tiene una gran cantidad de registros, puede usar una tabla permanente para almacenar los resultados, entonces, cuando necesite recuperar los datos, puede procesar solo los datos nuevos.
Agregaría un campo de bit con un valor predeterminado de 0 a la tabla de origen para realizar un seguimiento de los registros que se han procesado. Suponiendo que nadie está utilizando select * en la tabla, agregar una columna con un valor predeterminado no afectará el resto de su aplicación.
Agregue un comentario a esta publicación si desea ayuda para codificar la solución.
Si lee el libro "Desarrollo de aplicaciones de bases de datos orientadas al tiempo en SQL" de RT Snodgrass (cuyo pdf está disponible en su sitio web en publicaciones), y llegue hasta la Figura 6.25 en p165-166, encontrará el - SQL trivial que se puede usar en el ejemplo actual para agrupar varias filas con el mismo valor de ID e intervalos de tiempo continuos.
El siguiente desarrollo de consultas está cerca de ser correcto, pero hay un problema detectado al final, que tiene su origen en la primera instrucción SELECT. Todavía no he rastreado por qué se está dando la respuesta incorrecta. [Si alguien puede probar el SQL en su DBMS y decirme si la primera consulta funciona correctamente allí, ¡sería de gran ayuda!]
Se ve algo así como:
-- Derived from Figure 6.25 from Snodgrass "Developing Time-Oriented
-- Database Applications in SQL"
CREATE TABLE Data
(
Start DATE,
Finish DATE,
ID CHAR(2),
Amount INT
);
INSERT INTO Data VALUES(''2008-10-01'', ''2008-10-02'', ''01'', 10);
INSERT INTO Data VALUES(''2008-10-02'', ''2008-10-03'', ''02'', 20);
INSERT INTO Data VALUES(''2008-10-03'', ''2008-10-04'', ''01'', 38);
INSERT INTO Data VALUES(''2008-10-04'', ''2008-10-05'', ''01'', 23);
INSERT INTO Data VALUES(''2008-10-05'', ''2008-10-06'', ''03'', 14);
INSERT INTO Data VALUES(''2008-10-06'', ''2008-10-07'', ''02'', 3);
INSERT INTO Data VALUES(''2008-10-07'', ''2008-10-08'', ''02'', 8);
INSERT INTO Data VALUES(''2008-10-08'', ''2008-11-08'', ''03'', 19);
SELECT DISTINCT F.ID, F.Start, L.Finish
FROM Data AS F, Data AS L
WHERE F.Start < L.Finish
AND F.ID = L.ID
-- There are no gaps between F.Finish and L.Start
AND NOT EXISTS (SELECT *
FROM Data AS M
WHERE M.ID = F.ID
AND F.Finish < M.Start
AND M.Start < L.Start
AND NOT EXISTS (SELECT *
FROM Data AS T1
WHERE T1.ID = F.ID
AND T1.Start < M.Start
AND M.Start <= T1.Finish))
-- Cannot be extended further
AND NOT EXISTS (SELECT *
FROM Data AS T2
WHERE T2.ID = F.ID
AND ((T2.Start < F.Start AND F.Start <= T2.Finish)
OR (T2.Start <= L.Finish AND L.Finish < T2.Finish)));
El resultado de esa consulta es:
01 2008-10-01 2008-10-02
01 2008-10-03 2008-10-05
02 2008-10-02 2008-10-03
02 2008-10-06 2008-10-08
03 2008-10-05 2008-10-06
03 2008-10-05 2008-11-08
03 2008-10-08 2008-11-08
Editado : hay un problema con la penúltima fila, no debería estar allí. Y no estoy claro (todavía) de dónde viene.
Ahora necesitamos tratar esa expresión compleja como una expresión de consulta en la cláusula FROM de otra instrucción SELECT, que sumará los valores de cantidad para un ID dado sobre las entradas que se superponen con los rangos máximos que se muestran arriba.
SELECT M.ID, M.Start, M.Finish, SUM(D.Amount)
FROM Data AS D,
(SELECT DISTINCT F.ID, F.Start, L.Finish
FROM Data AS F, Data AS L
WHERE F.Start < L.Finish
AND F.ID = L.ID
-- There are no gaps between F.Finish and L.Start
AND NOT EXISTS (SELECT *
FROM Data AS M
WHERE M.ID = F.ID
AND F.Finish < M.Start
AND M.Start < L.Start
AND NOT EXISTS (SELECT *
FROM Data AS T1
WHERE T1.ID = F.ID
AND T1.Start < M.Start
AND M.Start <= T1.Finish))
-- Cannot be extended further
AND NOT EXISTS (SELECT *
FROM Data AS T2
WHERE T2.ID = F.ID
AND ((T2.Start < F.Start AND F.Start <= T2.Finish)
OR (T2.Start <= L.Finish AND L.Finish < T2.Finish)))) AS M
WHERE D.ID = M.ID
AND M.Start <= D.Start
AND M.Finish >= D.Finish
GROUP BY M.ID, M.Start, M.Finish
ORDER BY M.ID, M.Start;
Esto da:
ID Start Finish Amount
01 2008-10-01 2008-10-02 10
01 2008-10-03 2008-10-05 61
02 2008-10-02 2008-10-03 20
02 2008-10-06 2008-10-08 11
03 2008-10-05 2008-10-06 14
03 2008-10-05 2008-11-08 33 -- Here be trouble!
03 2008-10-08 2008-11-08 19
Editado : Este es casi el conjunto de datos correcto sobre el cual hacer la agregación COUNT y SUMA solicitada por la pregunta original, por lo que la respuesta final es:
SELECT I.ID, COUNT(*) AS Number, SUM(I.Amount) AS Amount
FROM (SELECT M.ID, M.Start, M.Finish, SUM(D.Amount) AS Amount
FROM Data AS D,
(SELECT DISTINCT F.ID, F.Start, L.Finish
FROM Data AS F, Data AS L
WHERE F.Start < L.Finish
AND F.ID = L.ID
-- There are no gaps between F.Finish and L.Start
AND NOT EXISTS
(SELECT *
FROM Data AS M
WHERE M.ID = F.ID
AND F.Finish < M.Start
AND M.Start < L.Start
AND NOT EXISTS
(SELECT *
FROM Data AS T1
WHERE T1.ID = F.ID
AND T1.Start < M.Start
AND M.Start <= T1.Finish))
-- Cannot be extended further
AND NOT EXISTS
(SELECT *
FROM Data AS T2
WHERE T2.ID = F.ID
AND ((T2.Start < F.Start AND F.Start <= T2.Finish) OR
(T2.Start <= L.Finish AND L.Finish < T2.Finish)))
) AS M
WHERE D.ID = M.ID
AND M.Start <= D.Start
AND M.Finish >= D.Finish
GROUP BY M.ID, M.Start, M.Finish
) AS I
GROUP BY I.ID
ORDER BY I.ID;
id number amount
01 2 71
02 2 31
03 3 66
Revisión : ¡Oh! Drat ... la entrada para 3 tiene el doble de la "cantidad" que debería tener. Las partes anteriores ''editadas'' indican dónde las cosas empezaron a ir mal. Parece que la primera consulta es sutilmente incorrecta (tal vez está destinada a una pregunta diferente), o el optimizador con el que estoy trabajando se está portando mal. Sin embargo, debe haber una respuesta estrechamente relacionada con esto que dará los valores correctos.
Para el registro: probado en IBM Informix Dynamic Server 11.50 en Solaris 10. Sin embargo, debería funcionar bien en cualquier otro SQL DBMS moderadamente estándar.
Bueno, decidí ir por la ruta de iteración usando una combinación de combinaciones y cursores. Al UNIR la tabla de datos contra sí mismo, puedo crear una lista de enlaces de solo aquellos registros que son consecutivos.
INSERT INTO #CONSEC
SELECT a.ID, a.Start, b.Finish, b.Amount
FROM Data a JOIN Data b
ON (a.Finish = b.Start) AND (a.ID = b.ID)
Entonces puedo desenrollar la lista iterando sobre ella con un cursor, y haciendo actualizaciones de nuevo a la tabla de datos para ajustar (Y borrar los registros ahora extraños de la tabla de datos)
DECLARE CCursor CURSOR FOR
SELECT ID, Start, Finish, Amount FROM #CONSEC ORDER BY Start DESC
@Total = 0
OPEN CCursor
FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT
WHILE @FETCH_STATUS = 0
BEGIN
@Total = @Total + @Amount
@Start_Last = @Start
@Finish_Last = @Finish
@ID_Last = @ID
DELETE FROM Data WHERE Start = @Finish
FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT
IF (@ID_Last<> @ID) OR (@Finish<>@Start_Last)
BEGIN
UPDATE Data
SET Amount = Amount + @Total
WHERE Start = @Start_Last
@Total = 0
END
END
CLOSE CCursor
DEALLOCATE CCursor
Todo esto funciona y tiene un rendimiento aceptable para los datos típicos que estoy usando.
Encontré un pequeño problema con el código anterior. Originalmente estaba actualizando la tabla de datos en cada ciclo a través del cursor. Pero esto no funcionó. Parece que solo puede hacer una actualización en un registro y que múltiples actualizaciones (para seguir agregando datos) vuelven a la lectura del contenido original del registro.