Hace algunos días me enfrente con un problema en el cual era
necesario realizar operaciones entre el valor de la columna de la fila actual y
2 filas anteriores y posteriores, después de dar tantas vueltas encontré la
solución y se las explico.
Primero crearemos nuestra tabla
IF OBJECT_ID( 'datosPrueba') IS NOT NULL DROP TABLE datosPrueba CREATE TABLE datosPrueba ( cve INT IDENTITY(1,1) , particion CHAR(1), valor INT ) GO INSERT INTO datosPrueba( particion , valor ) VALUES( 'a', 2 ) , ( 'a', 3 ) , ( 'a',4 ) , ( 'b', 5 ) , ( 'b',10 ) , ( 'b',20 ) , ( 'b', 34 ) , ( 'b',50 )
Ahora utilizaremos algo similar
para hacer sumatorias
acumuladas, solo que en esta ocasión agregaremos ROWS BETWEEN CURRENT ROW AND
2 FOLLOWING para filas posteriores y ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
para filas anteriores, veamos el ejemplo.
SELECT * , SUM( valor ) OVER( ORDER BY cve ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS sm1 , SUM( valor ) OVER( ORDER BY cve ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS sm2 FROM datosPrueba
Con ROJO
estoy indicando el valor de la fila actual y con AZUL los valores de las filas
posteriores para la columna sm1 y
para la columna sm2 serían los
valores de las filas anteriores. Es necesario señalar que si no hay filas anteriores
o posteriores, se tomará NULL.
Veamos otro ejemplo donde también
se puede utilizar PARTITION BY para definir nuestros bloques dentro de la
consulta.
SELECT * , SUM( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS sm1 , SUM( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS sm2 FROM datosPrueba
Además es posible utilizar otra clase de funciones de
agregado como COUNT, MIN, MAX, AVG, etc.
IF OBJECT_ID( 'datosPrueba') IS NOT NULL DROP TABLE datosPrueba CREATE TABLE datosPrueba ( cve INT IDENTITY(1,1) , particion CHAR(1), valor DECIMAL(5,2) ) GO INSERT INTO datosPrueba( particion , valor ) VALUES( 'a', 2 ) , ( 'a', 3 ) , ( 'a',4 ) , ( 'b', 5 ) , ( 'b',10 ) , ( 'b',20 ) , ( 'b', 34 ) , ( 'b',50 ) SELECT * , SUM( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS sm1 , SUM( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS sm2 , AVG( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS avg1 , AVG( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg2 , MAX( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS mx1 , MAX( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS mx2 , MIN( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS mn1 , MIN( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS mn2 , COUNT( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS cn1 , COUNT( valor ) OVER( PARTITION BY particion ORDER BY cve ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS cnt2 FROM datosPrueba
Espero que les sirva.
SALUDOS!
COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE
0 comentarios:
Publicar un comentario