Funciones de la ventana SQL: todo lo que necesita saber sobre su uso

La versatilidad de SQL como lenguaje de consulta de DBMS ha aumentado a lo largo de los años. Su amplia utilidad y versatilidad lo convierten en el favorito de todos los tiempos para todos los analistas de datos.

Hay bastantes funciones de nivel avanzado además de las normales de SQL. Estas funciones se conocen comúnmente como funciones de ventana. Si está tratando con datos complejos y desea realizar cálculos avanzados, puede usarlos para aprovechar al máximo sus datos.

La importancia de las funciones de ventana

Varias funciones de ventana están disponibles en SQL, y cada una lo ayudará a realizar una serie de cálculos. Desde la creación de particiones hasta la clasificación de filas o la asignación de números de fila, estas funciones de ventana hacen un poco de todo.

Las funciones de ventana son beneficiosas cuando aplica funciones agregadas sobre un conjunto de datos específico o una colección de filas. Estas funciones van más allá de las funciones de agregación que proporciona GROUP BY. Sin embargo, la principal diferencia es que, a diferencia de la función de agrupación, sus datos no se combinan en una sola fila.

No puede usar funciones de ventana dentro de las declaraciones WHERE , FROM y GROUP BY .

Sintaxis de una función de ventana

Cuando se refiere a cualquier función de ventana, debe seguir la estructura de sintaxis predeterminada para que funcione correctamente. Si estructura el comando incorrectamente, obtendrá un error y su código no se ejecutará.

Esta es la sintaxis predeterminada:

SELECT columnname1,
{window_function}(columnname2)
OVER([PARTITION BY columnname1] [ORDER BY columnname3]) AS new_column
FROM table_name;

Donde:

• coulmnname1 es el nombre de la primera columna que le gustaría seleccionar.
• {window_function} es el nombre de una función agregada como sum, avg, count, row_number, rank o dense_rank.
• columnname2 es el nombre de la columna en la que está aplicando la función de ventana.
• columnname3 es el nombre de la tercera columna, que formará la base para la partición.
• new_column es una etiqueta para la nueva columna que puede aplicar con la palabra clave AS .
• table_name es el nombre de la tabla de origen.

Las funciones de ventana son diferentes de algunos de los comandos SQL más básicos. A diferencia de las funciones agregadas en SQL, puede usar estas funciones de ventana para realizar funciones avanzadas.

Preparación del conjunto de datos

Puede usar la declaración CREATE TABLE para crear una nueva tabla en SQL. Aquí hay un conjunto de datos de muestra que esta guía usará para definir algunas funciones de ventana:

La función de suma explicada

Suponga que desea calcular las ventas totales para cada valor dentro de la columna de categoría. Así es como puedes hacerlo:

SELECT category, color,
sum(sale_price)
OVER (order by category) AS total_sales
FROM sahil.sample;

En el código anterior, la instrucción SQL extrae la categoría y el color del conjunto de datos original. La función sum suma la columna sale_price. Lo hace por categoría, ya que la cláusula OVER especifica el orden por columna de categoría. El resultado final es el siguiente:

Cómo utilizar la función de ventana Avg()

Al igual que la función de suma, puede calcular el promedio por fila de datos con la función avg . En lugar de la suma, obtendrá una columna con las ventas promedio.

SELECT category, color,
avg(sale_price)
OVER (order by category) AS avg_sales
FROM sahil.sample;

Aprenda a usar la función de ventana Count()

Similar a las funciones de suma y promedio, la función de ventana de conteo en SQL es bastante sencilla y funciona de la misma manera que las otras dos funciones. Cuando pasa la función de conteo, obtiene el conteo total de cada valor dentro de la nueva columna.

Así es como puedes calcular el conteo:

SELECT category, color,
count(category)
OVER (order by category) AS item_count
FROM sahil.sample;

La función de ventana Row_Number()

A diferencia de algunas de las otras funciones de ventana enumeradas anteriormente, row_number() funciona de manera ligeramente diferente. La función row_number() asigna un número de fila a cada fila, según la cláusula order by. El número de fila inicial es 1; el número_fila asigna un valor correspondiente a cada fila hasta el final.

Aquí está la estructura básica de una función row_number():

SELECT category, color,
row_number()
OVER (order by category) AS item_number
FROM sahil.sample;

Pero, ¿qué sucede si desea asignar números de fila separados a cada elemento de categoría? La sintaxis anterior establece un número de serie móvil, independientemente de los elementos almacenados en la categoría. Por ejemplo, la categoría de electrodomésticos debe tener su numeración exclusiva, seguida de carpetas, etc.

Puede usar la función de partición para realizar esta tarea simple pero práctica. La palabra clave de partición asigna números de fila designados en función de cada elemento de categoría.

SELECT category, color,
row_number()
OVER (partition by category order by category) AS item_number
FROM sahil.sample;

Las funciones Rank() y Dense_Rank()

La función rank() funciona de manera diferente a la función row_number() . Debe especificar el nombre de la columna dentro de la orden por función, para usarlo como base para definir los valores de rango. Por ejemplo, en el siguiente ejemplo de código, puede usar la columna de color dentro de la orden por función. La consulta luego usará ese orden para asignar un valor de rango a cada fila.

Puede usar la sintaxis del código a continuación para pasar una función de clasificación en SQL:

SELECT category, color,
rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

Eche un vistazo a la salida para comprender cómo funciona esta función.

La función ordenar por ordena la categoría de color, mientras que la función de clasificación asigna una clasificación a cada color. Sin embargo, todos los mismos valores de color tienen el mismo rango, mientras que los diferentes colores tienen rangos separados. El color negro aparece tres veces dentro del conjunto de datos; en lugar de asignar un valor de clasificación de 1, 2 y 3, los elementos de color negro obtienen una clasificación de 1.

Sin embargo, el siguiente color marrón obtiene un rango 4 en lugar del rango 2. La función de rango omite valores y asigna el siguiente valor cronológico a las diferentes entradas. Si desea asignar un valor de rango más significativo, puede usar la función dense_rank() .

La función dense_rank no omite ningún valor de clasificación durante la orden por función. Por ejemplo, los tres primeros elementos de color (negro) tendrán rango 1. Sin embargo, el siguiente color (marrón) no tendrá rango 4, sino rango 2, que es el siguiente número cronológico en la lista de numeración. La función dense_rank es una función de ventana más práctica, ya que asigna un valor significativo a la lista de elementos.

Así es como puede usar la función dense_rank en SQL:

SELECT category, color,
dense_rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

Y aquí hay un ejemplo de cómo se verá la salida de esta función:

Funciones SQL al rescate

Las funciones de ventana de SQL son ideales para realizar operaciones analíticas avanzadas. Sin embargo, puede usar muchos otros comandos SQL para asegurarse de que sus habilidades computacionales sean de primera categoría. Cuando combina y calcula múltiples resultados de una sola vez, no hay nada mejor que usar las subconsultas de SQL.

Las subconsultas son una excelente herramienta para realizar funciones avanzadas, mejorando la calidad de sus resultados. Dependiendo de la necesidad de la hora, puede personalizar sus consultas y hacerlas más efectivas para satisfacer sus requisitos.