SELECT – Cláusula OVER (Transact-SQL)

• 08/11/2017
• 17 minutos de lectura
• • V
  • L
  • c
  • j
  • M
  • +11

Se aplica a: SQL Server (todos versiones compatibles) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Almacenamiento de datos en paralelo

Determina la partición y el orden de un conjunto de filas antes de que se aplique la función de ventana asociada. Es decir, la cláusula OVER define una ventana o un conjunto de filas especificado por el usuario dentro de un conjunto de resultados de consulta. Luego, una función de ventana calcula un valor para cada fila de la ventana. Puede utilizar la cláusula OVER con funciones para calcular valores agregados como promedios móviles, agregados acumulados, totales acumulados o un N superior por resultados de grupo.

• Funciones de clasificación

• Funciones agregadas

• Funciones analíticas

• SIGUIENTE VALOR PARA la función

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

Las funciones de ventana pueden tener los siguientes argumentos en su OVER cláusula:

• PARTITION BY que divide el conjunto de resultados de la consulta en particiones.
• ORDER BY que define el orden lógico de las filas dentro de cada partición del conjunto de resultados.
• ROWS / RANGE que limita las filas dentro de la partición especificando puntos de inicio y finalización dentro de la partición. Requiere el argumento ORDER BY y el valor predeterminado es desde el inicio de la partición hasta el elemento actual si se especifica el argumento ORDER BY.

Si no especifica ningún argumento, las funciones de la ventana se aplicarán a todo el conjunto de resultados.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTICIÓN POR

Divide el conjunto de resultados de la consulta en particiones. La función de ventana se aplica a cada partición por separado y el cálculo se reinicia para cada partición ion.

Si no se especifica PARTITION BY, la función trata todas las filas del conjunto de resultados de la consulta como una sola partición. La función se aplicará a todas las filas en la partición si no especifica la cláusula ORDER BY.

PARTITION BY value_expression

Especifica la columna por la cual se particiona el conjunto de filas. value_expression solo puede hacer referencia a las columnas disponibles mediante la cláusula FROM. value_expression no puede hacer referencia a expresiones o alias en la lista de selección. value_expression puede ser una expresión de columna, subconsulta escalar, función escalar o variable definida por el usuario.

ORDER BY

Define el orden lógico de las filas dentro de cada partición del conjunto de resultados. Es decir, especifica el orden lógico en el que se realiza el cálculo de la función de ventana.

• Si no se especifica, el orden predeterminado es ASC y La función de ventana usará todas las filas en la partición.
• Si se especifica, y no se especifica FILAS / RANGO, entonces RANGE UNBOUNDED PRECEDING AND CURRENT ROW predeterminado se usa como predeterminado para la ventana marco por las funciones que pueden aceptar la especificación ROWS / RANGE opcional (por ejemplo, min o max).

order_by_expression
Especifica una columna o expresión por la que ordenar. order_by_expression solo puede hacer referencia a las columnas disponibles mediante la cláusula FROM. No se puede especificar un número entero para representar un nombre de columna o alias.

COLLATE collation_name
Especifica que la operación ORDER BY debe realizarse de acuerdo con la intercalación especificada en collation_name. collation_name puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Para obtener más información, consulte Compatibilidad con intercalación y Unicode. COLLATE es aplicable solo para columnas de tipo char, varchar, nchar y nvarchar.

ASC | DESC
Especifica que los valores de la columna especificada deben ordenarse en orden ascendente o descendente. ASC es el orden de clasificación predeterminado.Los valores nulos se tratan como los valores más bajos posibles.

ROWS o RANGE

Se aplica a: SQL Server 2012 (11.x) y posterior.

Límites adicionales las filas dentro de la partición especificando puntos de inicio y finalización dentro de la partición. Esto se hace especificando un rango de filas con respecto a la fila actual, ya sea por asociación lógica o asociación física. La asociación física se logra usando la cláusula ROWS.

La cláusula ROWS limita las filas dentro de una partición especificando un número fijo de filas que preceden o siguen a la fila actual. Alternativamente, la cláusula RANGE limita lógicamente las filas dentro de una partición al especificar un rango de valores con respecto al valor en la fila actual. Las filas anteriores y siguientes se definen en función del orden en la cláusula ORDER BY. El marco de ventana «RANGE … CURRENT ROW …» incluye todas las filas que tienen los mismos valores en la expresión ORDER BY que la fila actual. Por ejemplo, FILAS ENTRE 2 FILAS PRECEDENTES Y ACTUALES significa que la ventana de filas en la que opera la función tiene un tamaño de tres filas, comenzando con 2 filas anteriores hasta e incluyendo la fila actual.

UNBOUNDED PRECEDING

Se aplica a: SQL Server 2012 (11.x) y posteriores.

Especifica que la ventana comienza en la primera fila de la partición. UNBOUNDED PRECEDING solo se puede especificar como punto de inicio de la ventana.

< especificación de valor sin firmar > PRECEDING
Especificado con < especificación de valor sin firmar > para indicar el número de filas o valores que precederán a la fila actual. Esta especificación no está permitida para RANGE.

CURRENT ROW

Se aplica a: SQL Server 2012 (11.x) y posterior.

Especifica que la ventana comienza o termina en la fila actual cuando se usa con ROWS o el valor actual cuando se usa con RANGE. CURRENT ROW se puede especificar como punto inicial y final.

BETWEEN AND

Se aplica a: SQL Server 2012 (11.x) y posterior.

Se utiliza con FILAS o RANGO para especificar los puntos límite inferior (inicial) y superior (final) de la ventana. < límite de marco de ventana > define el punto de inicio del límite y < límite de marco de ventana > define el punto final del límite. El límite superior no puede ser más pequeño que el límite inferior.

UNBOUNDED FOLLOWING

Se aplica a: SQL Server 2012 (11.x) y posterior.

Especifica que la ventana termina en la última fila de la partición. SIGUIENTE SIN LÍMITES solo se puede especificar como un punto final de ventana. Por ejemplo, RANGE BETWEEN CURRENT ROW Y UNBOUNDED FOLLOWING define una ventana que comienza con la fila actual y termina con la última fila de la partición.

< especificación de valor sin firmar > SIGUIENTE
Especificado con < especificación de valor sin firmar > para indicar el número de filas o valores para seguir la fila actual. Cuando < especificación de valor sin firmar > FOLLOWING se especifica como el punto de inicio de la ventana, el punto final debe ser < especificación de valor sin firmar > SIGUIENTE. Por ejemplo, FILAS ENTRE 2 SIGUIENTES Y 10 SIGUIENTES define una ventana que comienza con la segunda fila que sigue a la fila actual y termina con la décima fila que sigue a la fila actual. Esta especificación no está permitida para RANGE.

Literal de entero sin signo
Se aplica a: SQL Server 2012 (11.x) y posterior.

Es un literal de entero positivo (incluido 0 ) que especifica el número de filas o valores que precederán o seguirán a la fila o valor actual. Esta especificación es válida solo para ROWS.

Comentarios generales

Se puede usar más de una función de ventana en una sola consulta con una sola cláusula FROM. La cláusula OVER para cada función puede diferir en la partición y el orden.

Si no se especifica PARTITION BY, la función trata todas las filas del conjunto de resultados de la consulta como un solo grupo.

Importante !

Si se especifica ROWS / RANGE y < marco de ventana anterior a > se usa para < extensión del marco de la ventana > (sintaxis corta) entonces esta especificación se usa para el punto de inicio del límite del marco de la ventana y CURRENT ROW se usa para el punto final del límite. Por ejemplo, «FILAS 5 PRECEDENTES» es igual a «FILAS ENTRE 5 FILAS PRECEDENTES Y ACTUALES».

Limitaciones y restricciones

La cláusula OVER no se puede usar con la función agregada CHECKSUM.

RANGE no se puede usar con < especificación de valor sin signo > PRECEDING o < especificación de valor sin firmar > SIGUIENTE.

Dependiendo de la clasificación, agregación o analítica función utilizada con la cláusula OVER, < cláusula ORDER BY > y / o < Es posible que las cláusulas ROWS y RANGE > no sean compatibles.

Ejemplos

A. Uso de la cláusula OVER con la función ROW_NUMBER

El siguiente ejemplo muestra el uso de la cláusula OVER con la función ROW_NUMBER para mostrar un número de fila para cada fila dentro de una partición. La cláusula ORDER BY especificada en la cláusula OVER ordena las filas de cada partición por la columna SalesYTD. La cláusula ORDER BY en la declaración SELECT determina el orden en el que se devuelve todo el conjunto de resultados de la consulta.

Aquí está el conjunto de resultados.

B. Uso de la cláusula OVER con funciones agregadas

El siguiente ejemplo usa la cláusula OVER con funciones agregadas en todas las filas devueltas por la consulta. En este ejemplo, usar la cláusula OVER es más eficiente que usar subconsultas para derivar los valores agregados.

Aquí está el conjunto de resultados.

El siguiente ejemplo muestra el uso de la cláusula OVER con una función agregada en un valor calculado.

Aquí está el conjunto de resultados. Observe que los agregados se calculan mediante SalesOrderID y el Percent by ProductID se calcula para cada línea de cada SalesOrderID.

C. Producir un promedio móvil y un total acumulativo

El siguiente ejemplo utiliza las funciones AVG y SUM con la cláusula OVER para proporcionar un promedio móvil y el total acumulativo de ventas anuales para cada territorio en el Sales.SalesPerson tabla. Los datos están divididos por TerritoryID y ordenados lógicamente por SalesYTD. Esto significa que la función AVG se calcula para cada territorio según el año de ventas. Observe que para TerritoryID 1, hay dos filas para el año de ventas 2005 que representan a los dos vendedores con ventas ese año. Se calcula el promedio de ventas para estas dos filas y luego se incluye en el cálculo la tercera fila que representa las ventas para el año 2006.

Aquí está el conjunto de resultados.

En este ejemplo, la cláusula OVER no incluye PARTITION BY. Esto significa que la función se aplicará a todas las filas devueltas por la consulta. La cláusula ORDER BY especificada en la cláusula OVER determina el orden lógico en el que se aplica la función AVG. La consulta devuelve un promedio móvil de ventas por año para todos los territorios de ventas especificados en la cláusula WHERE. La cláusula ORDER BY especificada en la instrucción SELECT determina el orden en que se muestran las filas de la consulta.

Aquí está el conjunto de resultados.

D. Especificar la cláusula ROWS

Se aplica a: SQL Server 2012 (11.x) y posterior.

El siguiente ejemplo utiliza la cláusula ROWS para definir una ventana sobre la que se calculan las filas como la fila actual y el número N de filas que siguen (1 fila en este ejemplo).

Aquí está el conjunto de resultados.

En el siguiente ejemplo, la cláusula ROWS se especifica con UNBOUNDED PRECEDING. El resultado es que la ventana comienza en la primera fila de la partición.

Aquí está el conjunto de resultados.

Ejemplos: Almacenamiento de datos en paralelo

E. Uso de la cláusula OVER con la función ROW_NUMBER

El siguiente ejemplo devuelve ROW_NUMBER para los representantes de ventas según su cuota de ventas asignada.

Aquí hay un conjunto de resultados parcial.

F. Uso de la cláusula OVER con funciones agregadas

Los siguientes ejemplos muestran el uso de la cláusula OVER con funciones agregadas. En este ejemplo, usar la cláusula OVER es más eficiente que usar subconsultas.

Aquí está el conjunto de resultados.

El siguiente ejemplo muestra el uso de la cláusula OVER con una función agregada en un valor calculado. Observe que los agregados se calculan mediante SalesOrderNumber y el porcentaje del pedido de ventas total se calcula para cada línea de cada SalesOrderNumber.

El primer inicio de este conjunto de resultados es:

Consulte también

Funciones agregadas (Transact-SQL)
Funciones analíticas (Transact-SQL)
Excelente publicación de blog sobre funciones de ventana y OVER, en sqlmag.com, por Itzik Ben-Gan