Una guía para principiantes para comprender las funciones de la ventana SQL y sus capacidades

A lo largo del proceso de contratación, tuve el placer de conocer a muchos candidatos talentosos para mi equipo. Dado que nuestro trabajo implica el manejo de conjuntos de datos complejos, era importante para mí evaluar la capacidad de cada candidato para encontrar soluciones inteligentes. Pregunté sobre su experiencia con funciones de ventana en SQL para evaluar su competencia. Si bien la mayoría de ellos conocía estas funciones, pocos pudieron usarlas de manera efectiva.

A pesar de que las funciones de ventana existen desde hace casi 20 años, muchos desarrolladores de SQL aún las encuentran difíciles de comprender. No es raro que incluso los desarrolladores experimentados simplemente copien y peguen el código de StackOverflow sin comprender realmente lo que hace. ¡Este artículo está aquí para ayudar! Explicaré las funciones de la ventana de una manera que sea fácil de entender y proporcionaré ejemplos para mostrarle cómo funcionan en el mundo real.

¿Has oído hablar de las funciones de ventana? Son herramientas analíticas increíbles que pueden resolver muchos problemas. Por ejemplo, supongamos que necesita calcular un conjunto de filas que comparten un atributo común, como una ID de cliente. ¡Aquí es donde las funciones de ventana son útiles! Funcionan como funciones agregadas, pero le permiten mantener la singularidad de cada fila en lugar de agruparlas. Además, los resultados de las funciones de la ventana se muestran como un campo adicional en la selección de salida. Esto es muy útil cuando está haciendo informes analíticos, calculando promedios móviles y totales acumulados, o descubriendo diferentes modelos de atribución.

¡Bienvenido al mundo de SQL y las funciones de ventana! Si recién estás comenzando, estás en el lugar correcto. Este artículo es apto para principiantes, con explicaciones claras y sin terminología complicada ni conceptos avanzados. Podrás seguirlo con facilidad, incluso si eres completamente nuevo en el tema.

Descripción general del contenido

• Tipos de funciones utilizadas con funciones de ventana
  • Funciones agregadas
  • Funciones de clasificación
  • Funciones de valor
• Funciones de ventana agregadas
• Conclusiones clave

Tipos de funciones utilizadas con funciones de ventana

Hay tres tipos principales de funciones a las que se pueden aplicar funciones de ventana sobre un conjunto de filas (una llamada ventana): estas son funciones agregadas, de clasificación y de valor. En la imagen a continuación, puede ver los nombres de las diferentes funciones que se incluyen en cada categoría.

Funciones agregadas

Estos realizan operaciones matemáticas en un grupo de datos, lo que da como resultado un único valor acumulativo. Se utilizan para calcular varios agregados, incluido el promedio, el número total de filas, los valores máximos o mínimos o la suma total dentro de cada ventana o partición.

• SUMA: suma todos los valores de la columna

• COUNT: calcula el número de valores en la columna, excluyendo los valores NULL

• AVG: encuentra el valor promedio en la columna

• MAX: identifica el valor más alto en la columna

• MIN: identifica el valor más bajo de la columna

  
  

Funciones de clasificación

Se utilizan para dar a cada fila de una partición un rango u orden. Esto se hace evaluando criterios específicos, como asignar números secuenciales o basar la clasificación en valores específicos.

• ROW_NUMBER: asigna un número de rango secuencial a cada nuevo registro en una partición
• RANGO: especifica el rango de cada fila en el conjunto de resultados. En este caso, si el sistema detecta valores idénticos, les asignará el mismo rango y omitirá el siguiente valor.
• DENSE_RANK: asigna un rango a cada fila dentro de una partición del conjunto de resultados. A diferencia de la función RANK, la función devuelve rangos para valores idénticos sin omitir ningún valor posterior.
• NTILE: nos permite determinar a qué grupo pertenece la línea actual. El número de grupos se da entre paréntesis.
  

Funciones de valor

Estos facilitan la comparación de valores entre diferentes filas en un grupo y también le permiten comparar valores con el primer o último valor en ese grupo. Esto significa que puede moverse fácilmente a través de diferentes filas en una ventana y verificar los valores al principio o al final de la ventana.

• LAG o LEAD: acceda a los datos de la fila anterior o posterior sin tener que realizar una operación de autocombinación. Estas funciones son particularmente útiles cuando se resuelven problemas que requieren comparar una fila con otra fila dentro del mismo conjunto de resultados o partición, como calcular diferencias a lo largo del tiempo.
• FIRST_VALUE o LAST_VALUE: recuperar el primer o el último valor de una ventana o partición definida. Estas funciones son especialmente útiles cuando desea calcular diferencias dentro de un período de tiempo específico.

Para comenzar con las funciones de la ventana, creemos una tabla hipotética de 'salario' y llenémosla con datos.

Creación de tablas:

 create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real )

Llenando la tabla:

 insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700)

Comprobemos si hemos rellenado correctamente la tabla de 'salario':

 select * from salary 

La siguiente consulta mostrará los nombres y salarios de los empleados de nuestra tabla:

 select employee_name, salary from salary 

Calcular la suma de salarios, salario promedio, máximo, mínimo y número de filas son algunos casos de uso común de funciones agregadas:

Cuando se aplica una función agregada, los salarios se agregan y se muestran en una línea.

Pero, ¿y si queremos mostrar los nombres y salarios de los empleados de la tabla de 'salario' y, en la tercera columna, la suma de todos los salarios? Este valor debe ser el mismo para todas las filas.

¡Es una gran oportunidad para usar una función de ventana!

 select employee_name, salary, sum(salary) over() as sum_salary from salary 

Echemos un vistazo más de cerca a la función de ventana que calcula la suma de los salarios en cada fila de sum(salary) over() .

La expresión over() define una ventana o un conjunto de filas sobre las que opera la función. En nuestro ejemplo, la ventana es la tabla completa, lo que significa que la función se aplicará a todas las filas.

La expresión over() solo funciona cuando se combina con funciones que se solicitaron antes de over() .

Por ejemplo, sum(salary) over() , donde sum() es una función agregada. Y toda la expresión sum(salary) over() es una función de ventana agregada.

Como dije antes, todas las funciones a las que se aplican funciones de ventana se pueden dividir en tres grupos: funciones agregadas, de clasificación y de valor.

Las funciones agregadas sum() , count() , avg() , min() , max() junto con la expresión over() forman un grupo de funciones de ventana agregadas.

En este artículo, nos concentraremos en este tipo específico de funciones de ventana.

Funciones de ventana agregadas

¡Volvamos a los ejemplos!

Solicitemos los nombres de los empleados; sus salarios; la suma de todos los salarios; salario medio, máximo y mínimo; el número de empleados.

 select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 

Ahora que está más claro qué son las funciones de ventana, exploremos algunos casos en los que pueden ser útiles en su trabajo.

 select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 

Hemos calculado el porcentaje del presupuesto salarial total para cada salario en la cuarta columna. El salario de Jessa asciende a casi el 15% de todo el presupuesto de salarios.

Tenga en cuenta que también colocamos la fórmula que calcula los porcentajes salary/sum(salary)over() en el orden después de order by . Se puede encontrar una función de ventana no solo en la select de salida, sino también en order by .

Otro ejemplo: comparemos los salarios con el salario medio de la empresa.

 select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 

Como podemos ver, el salario de Andrew es 2110 menos que el promedio y el de Jessa es 1690 por encima del promedio.

Solicitemos tres columnas: nombre del empleado, departamento y salario. Además, los ordenaremos por departamento.

 select employee_name, department, salary from salary order by department 

Ahora solicitaremos las mismas tres columnas, más una columna con la suma de los salarios de todos los empleados. Ya sabes que se puede hacer con una función de ventana.

 select employee_name, department, salary, sum(salary)over() from salary order by department 

Pero, ¿qué sucede si queremos solicitar no la suma de todos los salarios, sino la suma de los salarios de cada departamento, como se muestra en la última columna?

Podemos hacerlo agregando la partition by parámetro a la expresión over() :

 select employee_name, department, salary, sum(salary)over(), sum(salary)over(partition by department) from salary order by department

Partition by nos permite aplicar la función de ventana no a todas las filas (toda la ventana), sino a las secciones de columna.

¿No parece una simple agrupación? Para calcular la suma de salarios de cada departamento, haríamos una agrupación por departamentos (secciones en el argot de las funciones de ventana) y calcularíamos la cantidad:

 select department, sum(salary) from salary group by department 

En esencia, la diferencia entre agrupar y partition by es que group by devuelve una fila por grupo, mientras que partition by , aunque los resultados de la función son idénticos a los resultados de una función agregada con group by , proporciona todas las filas con la función agregada basada en un grupo.

Volvamos a las funciones de la ventana:

 select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 

Usando la función de ventana, particularmente la partition by parámetro, podemos calcular la parte del salario de cada empleado a partir de la suma de los salarios del departamento. O, por ejemplo, para comparar sueldos con el sueldo medio del departamento.

Conclusiones clave

Recordar:

• Una función de ventana realiza un cálculo en un conjunto de filas que de alguna manera están relacionadas con la fila actual,

• Los principales tipos de funciones a las que se aplican las funciones de ventana son las funciones de agregación, clasificación y valor,

• Para usar una función de ventana, debe aplicar la cláusula over() que define una ventana (un conjunto de filas) dentro de un conjunto de resultados de consulta. La función de ventana luego calcula un valor para cada fila en la ventana,

• Para especificar la columna para la que desea realizar la agregación, debe agregar la cláusula partition by a la cláusula over() . Partition by es algo similar a la agrupación, pero devuelve todas las filas con la función agregada aplicada, en lugar de una fila por grupo.

  
  

¡Esto es todo por ahora! En los próximos artículos, exploraré conceptos de SQL más avanzados con ejemplos simples adecuados para principiantes, ¡así que mantente atento!