La cláusula OVER se agregó a SQL Server «muy atrás» en SQL Server 2005 y se expandió en SQL Server 2012. Se utiliza principalmente con las «Funciones de ventana»; la única excepción es la función de secuencia SIGUIENTE VALOR PARA. La cláusula OVER se usa para determinar qué filas de la consulta se aplican a la función, en qué orden son evaluadas por esa función y cuándo deben reiniciarse los cálculos de la función. Dado que se usa junto con otras funciones, y este artículo trata específicamente solo sobre la cláusula OVER, se hablará de estas funciones solo en lo que respecta a la cláusula OVER en los ejemplos dados.

La sintaxis de la cláusula OVER es:

<function> OVER ( )

Al observar la sintaxis, parece que todas las subcláusulas son opcionales. De hecho, cada función que puede utilizar la cláusula OVER determina cuáles de las subcláusulas están permitidas y cuáles son necesarias. Dependiendo de la función que se utilice, la cláusula OVER en sí misma puede ser opcional. Hay un gráfico al final de este artículo que muestra qué funciones permiten / requieren qué partes de la cláusula OVER.

La cláusula PARTITION BY se usa para dividir el conjunto de resultados de la consulta en subconjuntos de datos, o particiones. Si no se utiliza la cláusula PARTITION BY, todo el conjunto de resultados de la consulta es la partición que se utilizará. La función de ventana que se utiliza se aplica a cada partición por separado y el cálculo que realiza la función se reinicia para cada partición. Usted define un conjunto de valores que determinan las particiones en las que dividir la consulta. Estos valores pueden ser columnas, funciones escalares, subconsultas escalares o variables.

Por ejemplo, examinemos la siguiente consulta:

SELECT COUNT(*)FROM .sys.indexes;

Esta consulta devuelve el siguiente conjunto de resultados:

Este es simplemente el número de filas devueltas por la consulta; en este caso, el número de índices en la base de datos msdb. Ahora agreguemos la cláusula OVER a esta consulta:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

Los resultados resumidos son:

Esta consulta devuelve object_id e index_id para cada índice, y el número total de índices en el conjunto de resultados. Como no se utilizó una cláusula PARTITION BY, todo el conjunto de resultados se trató como una sola partición. Ahora es el momento de agregar la cláusula PARTITION BY y ver cómo esto cambia los resultados:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

Los resultados resumidos son:

Esta consulta devuelve una fila para cada índice, pero ahora la consulta especifica una cláusula PARTITION BY de la columna object_id, por lo que la función de recuento devuelve el número de índices en ese object_id en particular. La cláusula ORDER BY controla el orden en que la función evalúa las filas. Esto se demostrará en breve. La cláusula ROWS o RANGE determina el subconjunto de filas dentro de la partición que se aplicarán a la función. Cuando usa FILAS o RANGO, especifica el punto inicial y final de la ventana. Los valores permitidos son:

Hay dos sintaxis para especificar la ventana:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Si solo se especifica el marco inicial, el marco final predeterminado es CURRENT ROW.

La palabra clave UNBOUNDED especifica el comienzo de la partición (para PRECEDING) o el final de la partición (para SEGUIR). CURRENT ROW especifica que la fila actual es el inicio de la ventana o el final de la ventana, dependiendo de la posición del marco de la ventana en la que se usa. «N» especifica un número de filas antes de la fila actual (para PRECEDING ), o después de la fila actual (para SIGUIENTE) para usar en el marco de la ventana.

Las siguientes son especificaciones de ventana válidas:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

Para usar la cláusula ROWS o RANGE, también debe especificar la cláusula ORDER BY. Por el contrario, si usa la cláusula ORDER BY y no especifica una cláusula ROWS o RANGE, entonces el RANGE predeterminado ENTRE UNBOUNDED PRECEDING Y CURRENT Se utiliza ROW.

Para demostrar las cláusulas ORDER BY y ROWS o RANGE, creemos algunos datos de prueba: dos cuentas, cuatro fechas por cuenta y una cantidad para cada fecha. La consulta mostrará ambas cláusulas que se utilizan de diferentes maneras:

Esta consulta devuelve el siguiente conjunto de resultados:

El » La columna RowNbr ”está usando g la función COUNT para devolver cuántas filas hay en la partición. La partición está ordenada por TranDate, y estamos especificando un marco de ventana de todas las filas desde el inicio de la partición hasta la fila actual. Para la primera fila, solo hay una fila en el marco de la ventana, por lo que se devuelve el valor «1». Para la segunda fila, ahora hay dos filas en el marco de la ventana, por lo que se devuelve el valor «2». Y así sucesivamente con el resto de filas de esta cuenta.

Dado que la cláusula PARTITION BY especifica la cuenta, cuando la cuenta cambia, los cálculos de la función se restablecen, lo que se puede ver al examinar las filas de la segunda cuenta en el conjunto de resultados. Este es un ejemplo de una agregación «en ejecución», donde la agregación se basa en cálculos anteriores. Un ejemplo de cuándo usaría esto sería para calcular el saldo de su cuenta bancaria después de cada transacción (también conocido como total acumulado).

La columna «DateCount» está realizando un recuento de cuántas filas, divididas por la fecha. En este ejemplo, cada una de las cuentas tiene una transacción en cada una de las mismas cuatro fechas, por lo que cada fecha tiene dos transacciones (una para cada cuenta). Esto da como resultado que se devuelva el valor «2» para cada fila. Esto es similar a realizar un recuento que usa GROUP BY para la fecha; la diferencia es que el total se devuelve para cada fila en lugar de solo una vez para cada fecha. Un ejemplo de cuándo usaría este método sería mostrar una «Fila X de Y», o calcular un porcentaje de la fila actual con respecto al total.

La columna «Last2Count» realiza un recuento de las filas dentro de la partición, para la fila actual y la fila inmediatamente anterior. Para la primera fila de cada cuenta, dado que no hay filas que la precedan, se devuelve un valor de «1». Para las filas restantes de cada cuenta, se devuelve un valor de «2». Este es un ejemplo de agregación «móvil» o «deslizante». Un ejemplo de cuándo utilizaría este método sería calcular una bonificación basada en ventas de los últimos dos meses.

En este punto, he mostrado solo la cláusula ROWS. La cláusula RANGE funciona de manera similar, pero en lugar de tratar las filas de una manera posicional, se ocupa de la valores devueltos por esa fila. Debido a que no es posicional, las cláusulas N PRECEDING / FOLLOWING no se pueden usar. Echemos un vistazo rápido a la diferencia entre ROWS y RANGE usando ambas en la misma consulta. Aquí tenemos una lista de personas (llamémosles administradores de bases de datos) y sus tarifas por hora. Tenga en cuenta que las filas con RowIDs 4 & 5 y 12 & 13 tienen la misma tasa. La consulta resumirá las tasas dos veces, una usando ROWS y la otra usando RANGE:

Esta consulta produce el siguiente conjunto de resultados:

En las columnas SumByRows y SumByRange, la cláusula OVER es idéntica con la excepción de la cláusula ROWS / RANGE. Tenga en cuenta también que, dado que no se especificó el rango final, el valor predeterminado es utilizar CURRENT ROW. Dado que estamos sumando el salario desde el comienzo del conjunto de resultados hasta la fila actual, lo que realmente estamos calculando es un total acumulado de la columna Salario. En la columna SumByRows, el valor se calcula usando la cláusula ROWS, y podemos ver que la suma de la fila actual es el salario de la fila actual más el total de la fila anterior. Sin embargo, la cláusula RANGE funciona con el valor de la columna Salario, por lo que suma todas las filas con el mismo salario o uno menor. Esto da como resultado que el valor de SumByRange sea el mismo valor para todas las filas con el mismo salario.

Una nota importante: la cláusula ORDER BY en la cláusula OVER solo controla el orden en que se utilizarán las filas de la partición por la función de ventana. No controla el orden del conjunto de resultados final. Sin una cláusula ORDER BY en la propia consulta, el orden de las filas no está garantizado. Puede notar que su consulta puede estar regresando en el orden de la última cláusula OVER especificada; esto se debe a la forma en que esto se implementa actualmente en SQL Server. Si el equipo de SQL Server en Microsoft cambia la forma en que funciona, es posible que ya no ordene sus resultados de la manera que está observando actualmente. Si necesita un orden específico para el conjunto de resultados, debe proporcionar una cláusula ORDER BY junto a la consulta en sí.

Finalmente, aquí hay un gráfico de las diversas funciones que pueden usar la cláusula OVER, así como qué partes de la cláusula están permitidas / requeridas / opcionales.

R-Requerido, O-Opcional, X-No permitido