Cómo utilizar la función ROW_NUMBER en SQL Server

ROW_NUMBER, función analítica y de clasificación en SQL Server. Asigna un número de secuencia a las filas de la tabla al incrementar los valores enteros a partir de 1 para la primera fila. Los números de fila asignados actúan como valor temporal para el conjunto de resultados no persistente. La función ROW_NUMBER trabaja con la cláusula ORDER BY para ordenar las filas en el orden definido y luego numera el conjunto de resultados de la consulta.

Por ejemplo, tenemos 10 filas es una tabla, y usando Función ROW_NUMBER con cláusula ORDER BY asigna números que comienzan en 1 y terminan en 10.

Igualmente, La función ROW_NUMBER también se usa junto con la cláusula PARTITION BY para crear una ventana separada de filas según las condiciones. Por ejemplo, tenemos 10 filas en la tabla Persona, 5 hombres y 6 mujeres. El uso de la función ROW_NUMBER y la columna PARTICIÓN POR Género asignará de 1 a 5 números a los hombres y de 1 a 6 números a la partición de las mujeres.

ROW_NUMBER con partición por ORDER BY en SQL Server

Sintaxis

ROW_NUMBER () OVER(PARTITION BY column_name ORDER BY column_name)

Argumentos

PARTITION BY - Optional, seperate rows into different partitions and applies ROW_NUMBER function to each partition.ORDER BY - Sorts the row in a defined order and assigns number to query result set.

Función ROW_NUMBER: puntos para reflexionar

1.Asigna valores enteros crecientes a partir de 1.

2.Las filas posteriores obtendrán el siguiente valor más alto, idealmente el valor del número de fila anterior + 1.

3.Los números asignados son temporales, no persistentes; para los números persistentes, utilice los objetos IDENTITY y SEQUENCE.

4.La cláusula PARTITION BY se utiliza para crear una ventana de filas que es opcional.

5.Se pueden usar varias columnas con ROW_NUMBER y PARTITION BY para crear ventanas y asignar números a cada ventana comenzando en 1.

6.No se permiten parámetros con ROW_NUMBER.

Función ROW_NUMBER: ejemplos

Demostremos la función ROW_NUMBER con ejemplos prácticos y útiles.

1.

ROW_NUMBER - Con cláusula ORDER BY

La siguiente consulta usa la función ROW_NUMBER con la cláusula ORDER BY en la columna ID. RowNum, un nombre de columna derivado, un alias para la salida de la función ROW_NUMBER. Ordenar las filas por columna de ID y asignar un número a cada fila comenzando con 1 y aumentando el valor de las filas posteriores.

--ROW_NUMBER - With ORDER BY Clause
USE tempdb;
GO

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL 
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
  ID		INT	    PRIMARY KEY
 ,Name		VARCHAR(50)
 ,Gender	VARCHAR(6)
);

INSERT INTO dbo.Person
VALUES
 (101,'Falak'   ,'Female')
,(102,'Vishal'  ,'Male')
,(103,'Avni'    ,'Female')
,(104,'Akanksha','Female')
,(105,'Atul'	,'Male')
,(106,'Ravi'	,'Male')
,(107,'Niraj'	,'Male')
,(108,'Chetan'  ,'Male')
,(109,'Ranjana' ,'Male')
,(110,'Heena'   ,'Female');

--RowNumber with ORDER BY Clause
SELECT Id, Name, Gender, ROW_NUMBER() OVER(ORDER BY Id) As RowNum
FROM Person;

Conjunto resultante

ROW_NUMBER con cláusula ORDER BY en SQL Server

2.

ROW_NUMBER - Con cláusulas PARTITION BY y ORDER BY

En la siguiente consulta, reutilizando el dbo.Person mesa. Aquí, la función ROW_NUMBER utilizada junto con PARTICIÓN POR y cláusula ORDER BY. Primero, creando dos ventanas de partición basadas en la columna Género. En total tenemos 11 filas, por lo tanto Primera partición ventana creada para Mujer con 4 filas. La función ROW_NUMBER se aplica a cada fila en la partición femenina, asignando así un número del 1 al 4.

Segunda partición Se crea una ventana para Hombre que tiene 6 filas. La función ROW_NUMBER se aplicó a cada fila en la partición masculina, asignando un número del 1 al 6.

En resumen, La función ROW_NUMBER restablece el número de cada partición y comienza en 1 al cruzar el límite de la partición.

--ROW_NUMBER - With Partition By & ORDER BY Clause
USE tempdb;
GO

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL 
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
  ID		INT	      PRIMARY KEY
 ,Name		VARCHAR(50)
 ,Gender	VARCHAR(6)
);

INSERT INTO dbo.Person
VALUES
 (101,'Falak'   ,'Female')
,(102,'Vishal'  ,'Male')
,(103,'Avni'    ,'Female')
,(104,'Akanksha','Female')
,(105,'Atul'	,'Male')
,(106,'Ravi'	,'Male')
,(107,'Niraj'	,'Male')
,(108,'Chetan'  ,'Male')
,(109,'Ranjana' ,'Male')
,(110,'Heena'   ,'Female');

--RowNumber with Partition By Clause
SELECT Id, Name, Gender, 
       ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY Id) As RowNum
FROM   Person;

Conjunto resultante

ROW_NUMBER con cláusula ORDER BY en SQL Server

3.

ROW_NUMBER - Con PARTITION BY en varias columnas

La siguiente consulta demuestra el uso de varias columnas en PARTITION BY junto con ORDER BY. Adicional Departamento columna a dbo.Person mesa. PARTICIÓN POR implica Género y Departamento columna, por lo que tenemos una partición diferente basada en ellos.

Tenga en cuenta que cada género y departamento está numerado por la función ROW_NUMBER. Cruzar el límite de la partición ROW_NUMBER restablece el valor y comienza en 1.

--ROW_NUMBER - With Multiple Columns Partition By
USE tempdb;
GO

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL 
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
  ID		INT			 PRIMARY KEY
 ,Name		VARCHAR(50)
 ,Gender	VARCHAR(6)
 ,Dept		VARCHAR(10)
);

INSERT INTO dbo.Person
VALUES
 (101,'Falak'   ,'Female','IT')
,(102,'Vishal'  ,'Male'  ,'HR')
,(103,'Avni'    ,'Female','Admin')
,(104,'Akanksha','Female','HR')
,(105,'Atul'	,'Male'  ,'Admin')
,(106,'Ravi'	,'Male'  ,'Admin')
,(107,'Niraj'	,'Male'  ,'IT')
,(108,'Chetan'  ,'Male'  ,'HR')
,(109,'Ranjana' ,'Male'  ,'Admin')
,(110,'Heena'   ,'Female','IT');

--RowNumber with Multiple Columns Partition By Clause
SELECT Id, Name, Gender, Dept,
       ROW_NUMBER() OVER(PARTITION BY Gender, Dept ORDER BY Gender, Dept) As RowNum
FROM   Person
ORDER BY Gender, Dept;

Conjunto resultante

ROW_NUMBER con partición de varias columnas por

4

ROW_NUMBER - Búsqueda de duplicados con una expresión de tabla común

En la siguiente consulta, usando PARTICIÓN POR en filas duplicadas y asignándoles un número. Cada partición de fila duplicada obtendrá un número de fila a partir de 1.

Además, el uso de la expresión de tabla común (CTE) para recuperar solo las filas que tienen RowNum = 1, eliminando así los valores duplicados y seleccionando una única instancia de cada fila.

--ROW_NUMBER - With ORDER BY Clause
USE tempdb;
GO

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL 
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
  ID		INT			 
 ,Name		VARCHAR(50)
 ,Gender	VARCHAR(6)
);

INSERT INTO dbo.Person
VALUES
 (101,'Falak'   ,'Female')
,(102,'Vishal'  ,'Male')
,(101,'Falak'   ,'Female')
,(102,'Vishal'  ,'Male')
,(102,'Vishal'  ,'Male')
,(106,'Ravi'	,'Male')
,(107,'Niraj'	,'Male')
,(106,'Ravi'	,'Male')
,(109,'Ranjana' ,'Male')
,(107,'Niraj'	,'Male')
,(106,'Ravi'	,'Male')
,(110,'Heena'   ,'Female');

--With Duplicates
SELECT * FROM dbo.Person ORDER BY Id;

--Applying ROW_NUMBER and Partition by and Number Duplicates
SELECT   Id, Name, 
         Gender, 
         ROW_NUMBER() OVER(PARTITION BY Id, Name, Gender ORDER BY Id) as RowNum
FROM     dbo.Person;


--Removing Duplicates with the use CTE/ RowNumber
With DuplicateCTE AS
(
SELECT   Id, Name, 
         Gender, 
		 ROW_NUMBER() OVER(PARTITION BY Id, Name, Gender ORDER BY Id) as RowNum
FROM     dbo.Person 
)

SELECT Id, Name, Gender
FROM   DuplicateCTE
WHERE  RowNum = 1;

Conjunto resultante

ROW_NUMBER - Eliminando duplicados con ROW_NUMBER y CTE en SQL Server
ROW_NUMBER - Eliminando duplicados con ROW_NUMBER y CTE en SQL Server

Artículos de referencia

1. Función NTILE en SQL Server
2. Cómo encontrar enésimo / segundo salario más alto y más bajo en SQL

Resumen

Para resumir, hemos aprendido a usar la función ROW_NUMBER con la cláusula ORDER BY y PARTITION BY para aprender a clasificar las filas según las condiciones. Usando ROW_NUMBER con CTE para encontrar valores duplicados. Si le gusta esta publicación, puede leer las publicaciones del archivo de la base de datos de Tech-Recipes para aprender algunas cosas más útiles.

Subir