En este artículo, vamos a analizar y discutir la función SQL ROW_NUMBER. Debido al hecho de que 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 podremos observar la variedad de los casos de ejemplo para comprender los conceptos detrás de la función SQL ROW_NUMBER.
Introducción
Debemos indicar que La función más utilizada en SQL Server es la función SQL ROW_NUMBER. Por cuanto esta función SQL ROW_NUMBER está disponible en SQL Server 2005 y versiones posteriores.
ROW_NUMBER agrega e incluye un número incremental único a la grilla y cuadrícula de resultados. El orden, en el que se aplican los números de fila, está determinado por la expresión ORDER BY. Por cuanto La mayoría de las veces, se especifican una o más columnas en la expresión ORDER BY, sin embargo, es posible usar expresiones más complejas o incluso una sub-consulta. Por lo tanto, crea un valor integral cada vez mayor y que siempre comienza en 1 y las filas posteriores obtienen el siguiente valor incremental más alto.
Por otro lado, también puede ser utilizado con una cláusula PARTITION BY. Sin embargo, cuando cruza un límite o límite de partición, procede a restablecer 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 inician también nuevamente el contador desde 1, 2, 3 … y así sucesivamente y así sucesivamente de forma continua.
Lo esencial:
- La función SQL ROW_NUMBER corresponde a una generación no persistente de una secuencia de valores temporales y por lo cual se calcula dinámicamente cuando se ejecuta la consulta
- No hay garantía de que las filas retornadas por una consulta SQL utilizando la función SQL ROW_NUMBER se mantengan en el orden exactamente igual con cada ejecución
- Las funciones ROW_NUMBER y RANK son similares. La salida de ROW_NUMBER es una secuencia de valores que comienza en 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
- Si ha tenido una experiencia previa en Oracle, entonces el ROWNUM le es más familiar. Es una pseudocolumna. Comienza con 1 y sube aumentando en uno, hasta el final de la tabla
- La función SQL ROW_NUMBER es por naturaleza dinámica y podemos restablecer los valores utilizando la cláusula PARTITION BY
- La cláusula ORDER BY de la consulta y la cláusula ORDER BY de la cláusula OVER no tienen nada que ver entre sí
Sintaxis
|
1 2 |
ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression1 , ... [ n ] ] order_by_clause col1,col2..) |
NUMERO DE FILA
La instrucción ROW_NUMBER cuando esta seguido de la función OVER y luego entre paréntesis deberá usar una cláusula ORDER BY. Recuerde lo importante es que debe usar la cláusula ORDER BY para imponer una especie de orden para el conjunto de resultados.
OVER
La instrucción de La cláusula OVER define la ventana o el conjunto de filas en las que opera la función de ventana, consiguientemente 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 está plenamente justificada cuando las filas deben alinearse de cierta manera para que la función funcione.
|
1 |
PARTITION BY value_expression1 |
PARTITION BY
Debemos mencionar que La cláusula Partition By es opcional. Cuando usted especifica 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. Se podrá observar que cuando los valores especificados en la cláusula PARTITION definen los límites del conjunto de resultados. Caso contrario, si no se especifica la cláusula PARTITION BY, la cláusula OVER funciona en todas las filas del conjunto de resultados como un conjunto de datos único. Es importante mencionar que esta cláusula puede consistir en una o más columnas, como también en una expresión más compleja o incluso una subconsulta.
order_by_clause
Es importante mencionar que cuando utilice la orden por cláusula la misma sin duda es una cláusula obligatoria. Determina la secuencia y la asociación del valor temporal a las filas de una partición específica. Recuerde que la cláusula ORDER BY es una expresión de la cláusula OVER y determina cómo deberán alinearse las filas de una determinada manera para la función.
Demostración
En esta sección, veremos y analizaremos detenidamente la función SQL ROW_NUMBER. Para los ingresos de información pertinente a toda la demostración, he usado la base de datos AdventureWorks2016.
Cómo usar ROW_NUMBER en una consulta SQL
A continuación, En los siguientes ejemplos, veremos el uso de la cláusula OVER.
En este caso de estudio, permítanos obtener la lista de todos los clientes proyectando las columnas como SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue y RowNum. En este acápite veremos que 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. Recuerde que el pedido 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 usar la cláusula ORDER BY
El siguiente ejemplo que pasamos a ilustrar como se utiliza la cláusula ORDER BY en la consulta. La cláusula ORDER BY en la consulta se halla aplicada en la columna SalesOrderID. Allá Podemos ver que las filas en la salida todavía están ordenadas y devueltas en ese orden. Por tanto, Row_Number todavía se aplica al CustomerID. La salida de la información nos indica que ORDER BY de la consulta y ORDER BY de la cláusula OVER son independientes de la salida.
|
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 usar múltiples columnas con la cláusula OVER
A continuación, en el siguiente ejemplo vera usted que en esta ocasión hemos enumerado customerID y OrderDate en la cláusula ORDER BY. Esto proporciona al cliente detalles con las especificaciones más definidas del pedido más reciente 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 utiliza la cláusula PARTITION BY en los campos CustomerID y OrderDate. En la salida de la información pertinente, 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 acción realizada de 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, usted vera lo importante y lo fácil que es 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 con CTE y ROW_NUMBER
En el siguiente ejemplo vamos a analizar SalesOrderHeader para mostrar los cinco pedidos más grandes e importantes que se han realizado por cada cliente cada mes. Usando la función Mes, las columnas orderDate se manipulan para obtener la parte del mes. De esta forma, se particionan de manera más precisa las ventas correspondientes a un mes específico (OrderDate) junto con el cliente (CustomerID).
Para enumerar los cinco pedidos más grandes e importantes en cada mes para cada cliente, se utiliza un CTE. En esta instrucción Se crea una ventana en los datos de la partición y se le asigna los valores y luego se llama al CTE para obtener los pedidos más grandes.
CTE para obtener 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
Como podemos observar Hasta ahora, hemos revisado la función SQL ROW_NUMBER en detalle. Así mismo, hemos discutido varios ejemplos, desde lo más simples hasta unos más complejos. Además, hemos analizado y discutido la forma de cómo usar la función SQL ROW_NUMBER con CTE (expresiones de tabla comunes). Consiguientemente, en la mayoría de los casos, siempre verá indicada una cláusula over con cada función de ventana.
La cláusula over define la ventana que ve cada fila observada. Dentro de la cláusula over, hay un PARTITION BY, nuevamente es importante mencionar que la misma es compatible con todas las funciones de la ventana, seguida de la cláusula ORDER BY. Eso es todo por ahora … Espero que hayas disfrutado leyendo el artículo.
Mi especialidad es el diseño y la implementación de soluciones de alta disponibilidad y la migración de bases de datos multiplataforma. Las tecnologías en las que trabajo actualmente son SQL Server, PowerShell, Oracle y MongoDB.
Ver todas las publicaciones de Prashanth Jayaram
- Descripción general de la función SQL CAST y SQL CONVERT - December 6, 2019
- Revisión del operador relacional y descripción general dePivot y Unpivot estático y dinámico de SQL - November 6, 2019
- Revisión, ejemplos y uso de SQL Union - November 4, 2019