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
|
1 2 3 4 |
window_function ( [ ALL ] expression ) OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] ) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE TABLE [dbo].[Orders] ( order_id INT, order_date DATE, customer_name VARCHAR(250), city VARCHAR(100), order_amount MONEY ) INSERT INTO [dbo].[Orders] SELECT '1001','04/01/2017','David Smith','GuildFord',10000 UNION ALL SELECT '1002','04/02/2017','David Jones','Arlington',20000 UNION ALL SELECT '1003','04/03/2017','John Smith','Shalford',5000 UNION ALL SELECT '1004','04/04/2017','Michael Smith','GuildFord',15000 UNION ALL SELECT '1005','04/05/2017','David Williams','Shalford',7000 UNION ALL SELECT '1006','04/06/2017','Paum Smith','GuildFord',25000 UNION ALL SELECT '1007','04/10/2017','Andrew Smith','Arlington',15000 UNION ALL SELECT '1008','04/11/2017','David Brown','Arlington',2000 UNION ALL SELECT '1009','04/20/2017','Robert Smith','Shalford',1000 UNION ALL SELECT '1010','04/25/2017','Peter Smith','GuildFord',500 |
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.
|
1 2 3 4 |
SELECT city, SUM(order_amount) total_order_amount FROM [dbo].[Orders] GROUP BY city |
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.
|
1 2 3 4 5 |
SELECT order_id, order_date, customer_name, city, order_amount ,SUM(order_amount) OVER(PARTITION BY city) as grand_total FROM [dbo].[Orders] |
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!
|
1 2 3 4 5 |
SELECT order_id, order_date, customer_name, city, order_amount ,AVG(order_amount) OVER(PARTITION BY city, MONTH(order_date)) as average_order_amount FROM [dbo].[Orders] |
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.
|
1 2 3 4 5 |
SELECT order_id, order_date, customer_name, city, order_amount ,MIN(order_amount) OVER(PARTITION BY city) as minimum_order_amount FROM [dbo].[Orders] |
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.
|
1 2 3 4 5 |
SELECT order_id, order_date, customer_name, city, order_amount ,MAX(order_amount) OVER(PARTITION BY city) as maximum_order_amount FROM [dbo].[Orders] |
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:
|
1 2 3 4 5 |
SELECT city,COUNT(DISTINCT customer_name) number_of_customers FROM [dbo].[Orders] GROUP BY city |
Inválido para la función COUNT de Windows:
|
1 2 3 4 5 |
SELECT order_id, order_date, customer_name, city, order_amount ,COUNT(DISTINCT customer_name) OVER(PARTITION BY city) as number_of_customers FROM [dbo].[Orders] |
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.
|
1 2 3 4 5 |
SELECT order_id, order_date, customer_name, city, order_amount ,COUNT(order_id) OVER(PARTITION BY city) as total_orders FROM [dbo].[Orders] |
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.
|
1 2 3 4 5 |
SELECT order_id,order_date,customer_name,city, RANK() OVER(ORDER BY order_amount DESC) [Rank] FROM [dbo].[Orders] |
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.
|
1 2 3 4 5 |
SELECT order_id,order_date,customer_name,city, order_amount, DENSE_RANK() OVER(ORDER BY order_amount DESC) [Rank] FROM [dbo].[Orders] |
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
|
1 2 3 4 5 |
SELECT order_id,order_date,customer_name,city, order_amount, ROW_NUMBER() OVER(ORDER BY order_id) [row_number] FROM [dbo].[Orders] |
ROW_NUMBER() with PARTITION BY
|
1 2 3 4 5 |
SELECT order_id,order_date,customer_name,city, order_amount, ROW_NUMBER() OVER(PARTITION BY city ORDER BY order_amount DESC) [row_number] FROM [dbo].[Orders] |
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.
|
1 2 3 4 5 |
SELECT order_id,order_date,customer_name,city, order_amount, NTILE(4) OVER(ORDER BY order_amount) [row_number] FROM [dbo].[Orders] |
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 ( ):
|
1 2 3 4 5 6 |
SELECT order_id,customer_name,city, order_amount,order_date, --in below line, 1 indicates check for previous row of the current row LAG(order_date,1) OVER(ORDER BY order_date) prev_order_date FROM [dbo].[Orders] |
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( ):
|
1 2 3 4 5 6 |
SELECT order_id,customer_name,city, order_amount,order_date, --in below line, 1 indicates check for next row of the current row LEAD(order_date,1) OVER(ORDER BY order_date) next_order_date FROM [dbo].[Orders] |
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()
|
1 2 3 4 5 6 |
SELECT order_id,order_date,customer_name,city, order_amount, FIRST_VALUE(order_date) OVER(PARTITION BY city ORDER BY city) first_order_date, LAST_VALUE(order_date) OVER(PARTITION BY city ORDER BY city) last_order_date FROM [dbo].[Orders] |
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
- Tipos de copia de seguridad y estrategias para bases de datos SQL
- Artículo de TechNet sobre la cláusula OVER
- Artículo de MSDN sobre DENSE_RANK
Otros grandes artículos de Ben
| Cómo SQL Server selecciona una víctima de interbloqueo |
| Cómo usar las funciones de ventana |
Ver todas las entradas de Ben Richardson
- Cómo usar las funciones de Windows en SQL Server - December 16, 2019
- Como comprender el tipo de datos GUID en SQL Server - September 30, 2019
- Cómo utilizar las funciones integradas de SQL Server y crear funciones escalares definidas por el usuario - September 30, 2019