julio 18, 2020

SQLShack (Español)

En este artículo, vamos a discutir la función SQL ROW_NUMBER. Esta es una continuación de la serie esencial de SQL. En esta guía, explicaré de qué se trata una función de ventana y verá ejemplos de muestra para comprender los conceptos detrás de la función SQL ROW_NUMBER.

Introducción

Lo más La función más utilizada en SQL Server es la función SQL ROW_NUMBER. La función SQL ROW_NUMBER está disponible en SQL Server 2005 y versiones posteriores.

ROW_NUMBER agrega un número incremental único a la cuadrícula de resultados. El orden en el que se aplican los números de fila está determinado por la expresión ORDER BY. La mayoría de las veces, se especifican una o más columnas en la expresión ORDER BY, pero es posible utilizar expresiones más complejas o incluso una subconsulta. Por lo tanto, crea un valor integral en constante aumento y siempre comienza en 1 y las filas siguientes obtienen el siguiente valor más alto.

También puede usarlo con una cláusula PARTITION BY. Pero cuando cruza un límite o límite de partición, restablece el contador y comienza desde 1. Por lo tanto, la partición puede tener valores 1, 2, 3, y así sucesivamente y las segundas particiones vuelven a iniciar el contador desde 1, 2, 3 … y así sucesivamente y así sucesivamente.

Conceptos básicos:

  1. La función SQL ROW_NUMBER es una generación no persistente de una secuencia de valores temporales y se calcula dinámicamente cuando se ejecuta la consulta.
  2. No hay garantía de que las filas devueltas por una consulta SQL usando la función SQL ROW_NUMBER se ordenen exactamente igual con cada ejecución.
  3. Las funciones ROW_NUMBER y RANK son similares. La salida de ROW_NUMBER es una secuencia de valores que comienza desde 1 con un incremento de 1, pero mientras que la función RANK, los valores también se incrementan en 1 pero los valores se repetirán para los empates.
  4. Si ha una experiencia con Oracle, el ROWNUM le resultará más familiar. Es una pseudocolumna. Comienza con 1 y va hacia abajo aumentando en uno, hasta el final de la tabla.
  5. La función SQL ROW_NUMBER es de naturaleza dinámica y se nos permite restablecer los valores utilizando la cláusula PARTITION BY
  6. La cláusula ORDER BY de la consulta y la cláusula ORDER BY del OVER cláusula no tienen nada que ver entre sí.

Sintaxis

1
2

ROW_NUMBER ()
OVER (] order_by_clause col1, col2 ..)

ROW_NUMBER

ROW_NUMBER seguido de la función OVER y luego, entre paréntesis, use una cláusula ORDER BY. Es necesario utilizar la cláusula ORDER BY para imponer una especie de orden para el conjunto de resultados.

OVER

La cláusula OVER define la ventana o el conjunto de filas que la ventana funciona, por lo que es muy importante que lo comprenda. Los posibles componentes de la Cláusula OVER son ORDER BY y PARTITION BY.

La expresión ORDER BY de la cláusula OVER se admite cuando las filas deben estar alineadas de cierta manera para que la función funcione.

1
PARTICIÓN POR value_expression1

PARTITION BY

La cláusula Partition By es opcional. Al especificar el valor, divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función SQL ROW_NUMBER. Los valores especificados en la cláusula PARTITION definen los límites del conjunto de resultados. Si no se especifica la cláusula PARTITION BY, la cláusula OVER opera en todas las filas del conjunto de resultados como un único conjunto de datos. Esta cláusula puede constar de una o más columnas, una expresión más compleja o incluso una subconsulta.

order_by_clause

La cláusula Order by es una cláusula obligatoria. Determina la secuencia y asociación del valor temporal a las filas de una partición especificada. La cláusula ORDER BY es una expresión de la cláusula OVER y determina cómo se deben alinear las filas de cierta manera para la función.

Demo

En esta sección, Echaremos un vistazo a la función SQL ROW_NUMBER. Para la demostración completa, he utilizado la base de datos AdventureWorks2016.

Cómo usar ROW_NUMBER en SQL Query

En los siguientes ejemplos, veremos el uso de la cláusula OVER.

Consigamos la lista de todos los clientes proyectando las columnas como SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue y RowNum. La función Row_Number se aplica con el orden de la columna CustomerID.El valor temporal comienza desde 1 asignado según el orden del CustomerID, y los valores continúan hasta las últimas filas de la tabla. El orden de CustomerID no está garantizado porque no especificamos la cláusula ORDER BY en la consulta.

1
2
3
4
5
6
7
8
9
10
11

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Cómo utilizar la cláusula Order by

El siguiente ejemplo utiliza la cláusula ORDER BY en la consulta. La cláusula ORDER BY de la consulta aplicada en la columna SalesOrderID. Podemos ver que las filas en la salida todavía están ordenadas y devueltas. El Row_Number todavía se aplica al CustomerID. El resultado indica que el ORDER BY de la consulta y el ORDER BY de la cláusula OVER son independientes del resultado.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

Cómo utilizar varias columnas con la cláusula OVER

En el siguiente ejemplo, puede ver que hemos incluido customerID y OrderDate en la cláusula ORDER BY. Esto le da al cliente los detalles con los detalles del pedido más recientes junto con la secuencia de números asignados a todo el conjunto de resultados.

1
2
3
4
5
6
7
8
9
10

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (ORDER BY CustomerID, OrderDate DESC) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader

Cómo usar la función SQL ROW_NUMBER con PARTITION

El siguiente ejemplo usa la cláusula PARTITION BY en los campos CustomerID y OrderDate. En la salida, puede ver que el cliente 11019 tiene tres pedidos para el mes 2014-junio. En este caso, la partición se realiza en más de una columna.

La partición es una combinación de OrderDate y CustomerID. Row_Number comenzará de nuevo para cada combinación única de OrderDate y CustomerID. De esta forma, es fácil encontrar al cliente que ha realizado más de un pedido el mismo día.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (PARTITION BY CustomerID,
DATEADD (MONTH, DATEDIFF (MONTH, 0, OrderDate), 0)
ORDER BY SubTotal DESC) AS MonthlyOrders,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Cómo devolver un subconjunto de filas usando CTE y ROW_NUMBER

En el siguiente ejemplo, analizaremos SalesOrderHeader para mostrar los cinco pedidos más grandes realizados por cada cliente cada mes. Con la función Month, las columnas orderDate se manipulan para obtener la parte del mes.De esta manera, se particionan las ventas correspondientes a un mes específico (OrderDate) junto con el cliente (CustomerID).

Para enumerar los cinco pedidos más grandes de cada mes para cada cliente, se utiliza un CTE. Se crea una ventana en los datos de la partición y se le asignan los valores y luego se llama al CTE para buscar los pedidos más grandes.

1
2
3
4
5
6
7
8
9
10
11
12

WITH cte
AS (SELECT ROW_NUMBER OVER (PARTITION BY customerID, Month (OrderDate) ORDER BY Subtotal DESC, TotalDue DESC) AS ROW_NUM,
CustomerID,
MONTH (OrderDate) Month,
SubTotal,
TotalDue,
OrderDate
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5

Resumen

Hasta ahora, revisamos la función SQL ROW_NUMBER en detalle. Hemos analizado varios ejemplos, desde simples hasta complejos. Además, discutimos cómo usar la función SQL ROW_NUMBER con CTE (Expresiones de tabla comunes). En la mayoría de los casos, siempre verá una cláusula over con cada función de ventana.

La cláusula over define la ventana que ve cada fila. Dentro de la cláusula over, hay una partición por, nuevamente es compatible con cada función de ventana, seguida de la cláusula order by. Eso es todo por ahora … Espero que hayas disfrutado leyendo el artículo.

  • Autor
  • Publicaciones recientes
Soy un tecnólogo de bases de datos y tengo más de 11 años de experiencia práctica en tecnologías de bases de datos. Soy un profesional certificado por Microsoft y estoy respaldado por una maestría en aplicaciones informáticas.
Mi especialidad radica en diseñar & implementando soluciones de alta disponibilidad y migración de bases de datos multiplataforma. Las tecnologías en las que se trabaja actualmente son SQL Server, PowerShell, Oracle y MongoDB.
Ver todas las publicaciones de Prashanth Jayaram

Últimas publicaciones de Prashanth Jayaram (ver todas)
  • Una descripción general rápida de la auditoría de bases de datos en SQL – 28 de enero de 2021
  • Cómo configurar Azure Data Sync entre bases de datos de Azure SQL y SQL Server local – 20 de enero de 2021
  • Cómo realizar operaciones de importación / exportación de bases de datos de Azure SQL con PowerShell – 14 de enero , 2021

admin
0

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Archivos

Entradas recientes