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!

Continuar...

SUMATORIAS ACUMULADAS

A partir de la versión 2012 es posible generar sumatorias acumuladas sin necesidad de utilizar un subquery, les mostraré como: Primero crearemos una tabla de pruebas:

IF OBJECT_ID( 'datosPrueba') IS NOT NULL
       DROP TABLE datosPrueba

CREATE TABLE datosPrueba ( cve INT IDENTITY(1,1) , valor INT )
GO

INSERT INTO datosPrueba( valor )
VALUES( 2 ) , ( 3 ) , ( 4 ) , ( 5 ) , ( 10 ) , ( 20 ) , ( 34 ) , ( 50 )

SELECT * FROM datosPrueba

Ahora, simplemente aplicamos un OVER después de la función de agregado.

SELECT *  , SUM( valor ) OVER( ORDER BY cve ) as total
FROM datosPrueba

También podemos utilizar la clausula PARTITION BY para separarlos por grupos.

IF OBJECT_ID( 'datosPrueba') IS NOT NULL
       DROP TABLE datosPrueba

CREATE TABLE datosPrueba ( cve INT IDENTITY(1,1) , letra CHAR(1), valor INT )
GO

INSERT INTO datosPrueba( letra, valor )
VALUES( 'a' , 2 ) , ( 'a', 3 ) , ( 'a', 4 ) , ( 'b', 5 ) , ( 'b', 10 ) , ( 'b', 20 ) , ( 'c', 34 ) , ( 'c', 50 )

SELECT *  , SUM( valor ) OVER( PARTITION BY letra ORDER BY cve ) as total
FROM datosPrueba

SALUDOS!

Continuar...

REMOVER FILAS DUPLICADAS CON ROW_NUMBER

Hola, en esta entrega les mostraré una manera para remover duplicados en nuestras tablas temporales antes de pasarlos a tablas en productivo.

Para el caso de este ejemplo, crearé una tabla temporal con algunos datos de muestra.

IF OBJECT_ID (N'tablaPruebas', N'U') IS NOT NULL
DROP TABLE tablaPruebas

CREATE TABLE tablaPruebas ( 
 cve TINYINT
 , nombre VARCHAR(30)
 , fecha DATE
)

INSERT INTO tablaPruebas
VALUES( 1, 'PEDRO' , '20130101' ) , ( 1 , 'PEDRO' , '20130101' ) 
, ( 2 , 'JUAN', '20130403' )
,( 3, 'LUIS' , '20130521' ) , ( 3 , 'RODRIGO' , '20130525' ) , ( 3 , 'ENRIQUE' , '20130910' ) 
, ( 4 , 'ALBERTO', '20131014' ) , ( 5 , 'RICARDO', '20131015' )

Suponiendo que para este ejemplo, la llave primaria en la tabla en productivo es la columna cve podemos observar que tenemos filas duplicadas:

SELECT * FROM tablaPruebas

Para el ejemplo, se requiere insertar solo las filas más antiguas o las primeras en ser registradas, para ello utilizamos ROW_NUMBER con PARTITION BY por la columna cve y ordenamos por la fecha:

--APLICAMOS ROW_NUMBER
SELECT ROW_NUMBER() OVER ( PARTITION BY cve ORDER BY fecha ) as rn, *
FROM tablaPruebas

--UTILIZAMOS UN SUBQUERY
SELECT * FROM ( 
 SELECT ROW_NUMBER() OVER ( PARTITION BY cve ORDER BY fecha ) as rn, *
 FROM tablaPruebas
) AS x
WHERE rn = 1

Espero que les sirva.

SALUDOS!

Continuar...

RANKING FUNCTIONS

Hola que tal amigos, hoy les explicaré lo que en el mundo de SQL SERVER se conoce como RANKING FUNCTIONS, las cuales son:

1. ROW_NUMBER.
2. RANK.
3. DENSE_RANK.
4. NTILE.

Utilizaremos la base de datos de ejemplo AdventureWorks2012 ( aquí podemos encontrar la liga para descargar los archivos y la forma de instalarlo ), además de la vista vSalesPerson, para nuestros ejercicios de ejemplo solo utilizaremos las siguientes columnas.

SELECT BusinessEntityID, FirstName, LastName, City
, StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear
FROM Sales.vSalesPerson

ROW_NUMBER

Nos ayuda a enumerar las filas obtenidas en nuestro resultado, es indispensable la clausula OVER( ORDER BY nombreColumna ):

SELECT
ROW_NUMBER() OVER( ORDER BY CountryRegionName ) as colRowNumber
,BusinessEntityID, FirstName, LastName, City, StateProvinceName
, CountryRegionName, SalesYTD, SalesLastYear
FROM Sales.vSalesPerson

Ahora, agreguemos la clausula PARTITION BY:

SELECT
ROW_NUMBER() OVER( PARTITION BY CountryRegionName ORDER BY CountryRegionName ) as colRowNumber
,BusinessEntityID, FirstName, LastName, City, StateProvinceName
, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

El valor del consecutivo se reinicia una vez que el valor de la columna que especifiquemos en la clausula PARTITION BY cambia.

RANK, DENSE_RANK:

Para poder explicárselos, les mostraré el resultado del siguiente query:

SELECT 
ROW_NUMBER() OVER( ORDER BY StateProvinceName DESC ) as colRowNumber
,RANK() OVER( ORDER BY StateProvinceName DESC ) as colRank
,DENSE_RANK() OVER( ORDER BY StateProvinceName DESC ) as colDenseRank
,BusinessEntityID, FirstName, LastName, City, StateProvinceName
,CountryRegionName, SalesYTD, SalesLastYear
 FROM Sales.vSalesPerson

Como se observa, RANK ofrece resultados salteados al contrario de DENSE_RANK, con este ultimo jamás se perderá la secuencia y con RANK dependerá de la cantidad de filas con similar resultado en la columna que especificamos ( que en este caso es StateProvinceName ); como la ejecución del query encontró 4 filas con el valor Washington, el siguiente será 5 para RANK y 2 para DENSE_RANK.

Agregando la clausula PARTITION BY, funciona de la misma manera que en la función ROW_NUMBER(), reinicia el valor cada vez que el valor de la columna especificada cambia:

SELECT
RANK() OVER( PARTITION BY CountryRegionName ORDER BY StateProvinceName DESC ) as colRank
,DENSE_RANK() OVER( PARTITION BY CountryRegionName ORDER BY StateProvinceName DESC ) as colDenseRank
,BusinessEntityID, FirstName, LastName, City
,StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

Los cuadros de colores señalan los grupos de acuerdo a la columna indicada en la clausula PARTITION BY, a su vez en el último grupo podemos observar numeración salteada para el caso de la columna colRank y numeración continua para colDenseRank.

NTILE

Distribuye las filas y las divide en cierta cantidad de particiones indicadas en la función. Cabe señalar que esta distribución la realiza el motor automáticamente.

SELECT
NTILE(3) OVER( ORDER BY CountryRegionName ) as colNTile
,BusinessEntityID, FirstName, LastName, City
, StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

SELECT
NTILE(5) OVER( ORDER BY CountryRegionName ) as colNTile
,BusinessEntityID, FirstName, LastName, City
,StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

SELECT
NTILE(10) OVER( ORDER BY CountryRegionName ) as colNTile
,BusinessEntityID, FirstName, LastName, City
,StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

SELECT
NTILE(20) OVER( ORDER BY CountryRegionName ) as colNTile
,BusinessEntityID, FirstName, LastName, City
,StateProvinceName, CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

Cabe señalar que si la cantidad de grupos deseada es mayor a la cantidad de filas, mostrará un resultado como el anterior, muy parecido a un ROW_NUMBER.

Agregando la clausula PARTITION BY, las particiones ahora se realizan por grupos, estos grupos se definen por el valor de la columna indicada en la clausula. En el ejemplo, deseamos obtener particiones de 3 por cada grupo de la columna CountryRegionName:

SELECT
NTILE(3) OVER( PARTITION BY CountryRegionName ORDER BY BusinessEntityID ) as colNTile
,BusinessEntityID, FirstName, LastName, City, StateProvinceName
,CountryRegionName, SalesYTD, SalesLastYear 
FROM Sales.vSalesPerson

El único grupo del ejemplo que permite particionar sus filas en 3 subgrupos más es United States, debido a que es el único que contiene más de 3 filas.

A grandes rasgos, este es el funcionamiento de las llamadas RANKING FUNCTIONS, es importante tenerlas presente porque en muchas ocasiones pueden ayudarnos a obtener el resultado esperado.

Cualquier duda, sugerencia o comentario, será bien recibido( y aclarado).

SALUDOS

Continuar...