2014/10/07

OPERACIONES ENTRE FILA ACTUAL Y N FILAS ANTERIORES/POSTERIORES?

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