Ben Richardson

Cómo usar las funciones de Windows en SQL Server

December 16, 2019 by

Todos los usuarios de la base de datos conocen las funciones agregadas regulares que operan en una tabla completa y se usan con una cláusula GROUP BY. Pero muy pocas personas usan las funciones de Windows en SQL. Estos operan en un conjunto de filas y devuelven un único valor agregado para cada fila.

La principal ventaja de usar las funciones de Windows sobre las funciones agregadas regulares es: Las funciones de Windows no hacen que las filas se agrupen en una sola fila de salida, las filas retienen sus identidades separadas y solo se añadirá un valor agregado a cada fila.

Echemos un vistazo a cómo operan las funciones de Windows y luego veremos algunos ejemplos de su uso en la práctica para asegurarnos de que las cosas estén claras y también cómo se comparan las salidas del SQL con las funciones SUM().

Como siempre, asegúrese de tener una copia de seguridad completa, especialmente si usted está probando cosas nuevas con su base de datos.

Introducción a las funciones de Windows

Las funciones de Windows operan en un conjunto de filas y devuelven un único valor agregado para cada fila. El término Windows describe el conjunto de filas en la base de datos en las que operará la función.

Definimos Windows (conjunto de filas en las que funciona la función) usando una cláusula OVER( ). Discutiremos más sobre la cláusula OVER ( ) en el siguiente artículo.

Tipos de funciones de Windows

  • Funciones de Windows agregadas
    SUM(), MAX(), MIN(), AVG(). COUNT()
  • Funciones de clasificación de Windows
    RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
  • Funciones de valores de Windows
    LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Sintaxis

Argumentos

window_function
window_function Específica el nombre de la función de Windows

TODOS
TODOS es una palabra clave opcional. Cuando usted incluya TODO, contará todos los valores, incluidos los duplicados. DISTINCT no es compatible con las funciones de Windows

Expresión
La columna o expresión de destino es en la que operan las funciones. En otras palabras, es el nombre de la columna para la que necesitamos un valor agregado. Por ejemplo, una columna que contiene el monto del pedido hace que podamos ver el total de pedidos recibidos.

OVER
Especifica las cláusulas de Windows para funciones agregadas

PARTITION BY partition_list
Define Windows (conjunto de filas en las que opera la función de Windows) para las funciones de Windows. Necesitamos proporcionar un campo o una lista de campos para la partición después de la cláusula PARTITION BY. Los múltiples campos deben estar separados por una coma como de costumbre. Si no se especifica PARTITION BY, la agrupación se realizará en toda la tabla y los valores se agregarán en consecuencia

ORDER BY order_list
Ordena las filas dentro de cada partición. Si no se especifica ORDER BY, ORDER BY utilizara toda la tabla

Ejemplos

Creemos una tabla e insertemos registros ficticios para escribir más consultas. Luego ejecutar el código de abajo.

Funciones de Windows agregadas

SUM()

Todos conocemos la función agregada SUM( ). Esta función Realiza la suma del campo definido para el grupo especificado (como ciudad, estado, país, etc.) o para toda la tabla si no se especifica el grupo. Veremos cuál será el resultado de la función agregada SUM( ) regular y la función agregada SUM( ) de Windows.

El siguiente es un ejemplo de una función agregada SUM( ) regular. Suma el importe del pedido para cada ciudad.

Puede ver en el conjunto de resultados que una función de agregado regular agrupa varias filas en una sola fila de salida, lo que hace que las filas individuales pierdan su identidad.

Esto no sucede con las funciones de Windows agregado. Las filas conservan su identidad y también muestran un valor agregado para cada fila. En el ejemplo a continuación, la consulta hace lo mismo, es decir, agrega los datos para cada ciudad y muestra la suma del monto total del pedido para cada una de ellas. Sin embargo, la consulta ahora inserta otra columna para el importe total del pedido, de modo que cada fila conserve su identidad. La columna marcada grand_total (total final) es la nueva columna en el ejemplo a continuación.

AVG()

AVG o Promedio funciona exactamente de la misma manera con una función de Windows.

La siguiente consulta le dará un monto promedio de pedido para cada ciudad y para cada mes (aunque por simplicidad solo hemos usado datos en un mes).

Especificamos más de un promedio definiendo múltiples campos en la lista de particiones.

También vale la pena señalar que puede usar expresiones en las listas como MONTH (order_date) como se muestra en la consulta a continuación. ¡Como siempre, usted puede hacer que estas expresiones sean tan complejas como desee, siempre que la sintaxis sea la correcta!

De la imagen de arriba, podemos ver claramente que, en promedio, nosotros hemos recibido pedidos de 12,333 para la ciudad de Arlington en abril de 2017.

Monto promedio del pedido = Monto total del pedido / Total de pedidos
                                       = (20,000 + 15,000 + 2,000) / 3
                                       = 12,333

También se puede usar la combinación de la función SUM ( ) y COUNT() para calcular un promedio.

MIN()

La función agregada MIN( ) encontrará el valor mínimo para un grupo específico o para toda la tabla si no se define el grupo.

Por ejemplo, estamos buscando el pedido más pequeño (pedido mínimo) para cada ciudad, usaríamos la siguiente consulta.

MAX()

Así como las funciones MIN( ) le dan el valor mínimo, la función MAX() identificará el valor más grande de un campo específico para un grupo definido de filas o para toda la tabla si no se especifica un grupo.

Busquemos el pedido más grande (cantidad máxima de pedido) para cada ciudad.

COUNT()

La función COUNT( ) contará los registros/filas.

Tenga en cuenta que DISTINCT no es compatible con la función de Windows COUNT( ) , mientras que es compatible con la función COUNT() normal. DISTINCT te ayuda a encontrar los distintos valores de un campo específico.

Por ejemplo, si queremos ver cuántos clientes han realizado un pedido en abril de 2017, no podemos contar directamente a todos los clientes. Es posible que el mismo cliente haya realizado múltiples pedidos en el mismo mes.

COUNT(nombre-cliente) le dará un resultado incorrecto, ya que contará duplicados. Mientras que COUNT(DISTINCT customer_name) le dará el resultado correcto ya que cuenta cada cliente único solo una vez.

Válido para la función COUNT( ) regular:

Inválido para la función COUNT de Windows:

La consulta anterior con la función de Windows le dará el siguiente error.

Ahora, busquemos el pedido total recibido para cada ciudad utilizando la función COUNT( ) de Windows.

Funciones de clasificación de Windows

Al igual que las funciones de agregado de Windows incluyen el valor de un campo específico, las funciones de RANKING clasificarán los valores de un campo específico y los clasificarán de acuerdo con su rango.

El uso más común de las funciones de RANKING es encontrar los registros superiores(N) en función de un determinado valor. Por ejemplo, los 10 empleados mejor pagados, los 10 estudiantes mejor clasificados, los 50 pedidos más grandes, etc.

Las siguientes son funciones de RANKING compatibles:

RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

Hablemos de ellos uno por uno.

RANK()

La función RANK( ) se utiliza para otorgar un rango o categoría única a cada registro en función de un valor específico, por ejemplo, salario, cantidad de pedido, etc.

Si dos registros tienen el mismo valor, la función RANK( ) asignará el mismo rango a ambos registros omitiendo el siguiente rango. Esto significa que si hay dos valores idénticos en el rango 2, asignará el mismo rango 2 a ambos registros y luego saltará el rango 3 y asignará el rango 4 al siguiente registro.

Clasifiquemos cada pedido por el monto de su pedido.

En la imagen de arriba, puede ver que se asigna el mismo rango (3) a dos registros idénticos (cada uno con una cantidad de orden de 15,000) y luego omite el siguiente rango (4) y asigna el rango 5 al siguiente registro.

DENSE_RANK()

La función DENSE_ RANK( ) es idéntica a la función RANK() excepto que no omite ningún rango. Esto significa que si se encuentran dos registros idénticos, DENSE_ RANK ( ) asignará el mismo rango a ambos registros pero no lo omitirá y luego posteriormente omitirá el siguiente rango.

Veamos cómo funciona esto en la práctica.

Como usted puede ver claramente anteriormente, se otorgó el mismo rango a dos registros idénticos (cada uno con la misma cantidad de orden) y luego se asignará el siguiente número de rango al siguiente registro sin omitir un valor de rango.

ROW_NUMBER()

El nombre se explica por sí mismo. Estas funciones asignan un número de fila único a cada registro.

El número de fila se restablecerá para cada partición si se especifica PARTITION BY. Veamos cómo funciona ROW_ NUMBER( ) sin PARTITION BY y luego con PARTITION BY.

ROW_ NUMBER() without PARTITION BY

ROW_NUMBER() with PARTITION BY

Tenga en cuenta que hemos hecho la partición en la ciudad. Esto significa que el número de fila se restablece para cada ciudad y, por lo tanto, se reinicia en 1 nuevamente. Sin embargo, el orden de las filas está determinado por la cantidad de la orden, de modo que para cualquier ciudad dada, la cantidad de la orden más grande será la primera fila y, por lo tanto, la fila con el número 1 será asignada.

NTILE()

NTILE( ) es una función muy útil de Windows. Le ayuda a identificar en qué percentil (o cuartil, o cualquier otra subdivisión) cae una fila determinada.

Esto significa que si tiene 100 filas y desea crear 4 cuartiles basados en un campo de valor especificado, puede hacerlo fácilmente y ver cuántas filas caen en cada cuartil.

Veamos un ejemplo. En la consulta a continuación, hemos especificado que queremos crear cuatro cuartiles en función del monto del pedido. Luego queremos ver cuántas órdenes caen en cada cuartil.

NTILE crea los mosaicos basados en la siguiente fórmula:

Nro de filas en cada mosaico = número de filas en el conjunto de resultados/número de mosaicos especificados

Aquí está nuestro ejemplo, tenemos un total de 10 filas y se especifican 4 mosaicos en la consulta, por lo que el número de filas en cada mosaico será 2.5 (10/4). Como el número de filas debe ser un número entero, no un decimal. El motor SQL asignará 3 filas para los primeros dos grupos y 2 filas para los dos grupos restantes.

Valores de Funciones de Windows

Los valores de las funciones Windows se utilizan para encontrar los valores primero, último, anterior y siguiente. Las funciones que se pueden usar son LAG( ), LEAD(), FIRST_VALUE(), LAST_VALUE()

LAG() y LEAD()

Las funciones LEAD( ) y LAG() son muy poderosas pero pueden ser complejas de explicar.

Como este es un artículo introductorio a continuación, estamos viendo un ejemplo muy simple para ilustrar cómo usarlos.

La función LAG permite acceder a los datos de la fila anterior en el mismo conjunto de resultados sin el uso de ninguna combinación SQL. Usted Puede ver en el siguiente ejemplo, utilizando la función LAG encontramos la fecha de pedido anterior.

Script para encontrar la fecha de pedido anterior usando la función LAG ( ):

La función LEAD permite acceder a los datos de la siguiente fila en el mismo conjunto de resultados sin el uso de uniones SQL. Entonces, puedes ver en el siguiente ejemplo, usando la función LEAD como encontramos la próxima fecha de pedido.

Script para encontrar la próxima fecha de pedido utilizando la función LEAD( ):

FIRST_VALUE() y LAST_VALUE()

Estas funciones lo ayudan a identificar el primer y el último registro dentro de una partición o tabla completa si no se especifica PARTITION BY.

Encontremos el primer y último pedido de cada ciudad a partir de nuestro conjunto de datos existente. Nota La cláusula ORDER BY es obligatoria para las funciones FIRST_ VALUE ( ) y LAST_VALUE()

De la imagen de arriba, podemos ver claramente que el primer pedido recibido el 2017-04-02 y el último pedido recibido el 2017-04-11 para la ciudad de Arlington funcionan igual para otras ciudades.

Enlaces útiles

Otros grandes artículos de Ben

Cómo SQL Server selecciona una víctima de interbloqueo
Cómo usar las funciones de ventana

Ben Richardson
1,289 Views