Window Functions: Guía fácil para Dominar SQL con Ejercicios

Imagina que tienes una caja de herramientas para análisis de datos, pero hasta ahora solo has estado usando un destornillador básico (las consultas SQL tradicionales). Las Window Functions son como tener de repente un destornillador eléctrico con 20 accesorios diferentes – ¡un verdadero game changer!

En este artículo, vamos a desbloquear juntos el potencial de las Window Functions y te prometo que, al final, tendrás una nueva superpotencia en tu arsenal de análisis de datos. 🦸‍♂️

Te prometo que, al final de esta lectura, no solo habrás comprendido qué son las Window Functions, sino que serás capaz de aplicarlas en tus proyectos para simplificar tus consultas, obtener información más precisa y, lo mejor de todo, hacerlo con estilo y eficiencia. 😎

¿Por qué deberías aprender sobre las Window Functions? 🎯

Menos esfuerzo, más resultados: Podrás realizar cálculos complejos sobre tus datos sin necesidad de subconsultas tediosas.
Más claridad en tus reportes: ¿Quieres rankear, numerar, o comparar filas de manera eficiente? Las Window Functions te permiten hacer esto y mucho más, con un código limpio y entendible.
Satisfacción personal: No hay nada como esa sensación de entender algo que antes parecía difícil. 💪

Si hasta aquí te suena interesante, sigue leyendo porque vamos a profundizar en cómo puedes aplicar estas herramientas en tu trabajo diario.

¡Manos a los Datos! La Clave para Aprender SQL es Practicar

Cuando se trata de aprender una habilidad técnica como SQL, la teoría solo es una parte del camino. Para dominar realmente el análisis de datos, el secreto es la práctica. Cada consulta, cada función y cada unión que escribimos nos acerca un poco más a convertirnos en expertos, y no hay mejor manera de aprender que enfrentándonos a situaciones reales.

Por eso, te invito a descargar las mismas bases de datos que usaré en los ejemplos a lo largo de este post.

Descargar archivos de ejemplo para practicar

student_data Descarga

school_data Descarga

Estas bases de datos están diseñadas para simular entornos reales de análisis: un conjunto de datos sobre estudiantes y otro sobre escuelas, con información detallada que puedes manipular para practicar consultas, cálculos, rankings, comparaciones y mucho más.

Al practicar directamente sobre estos datos, no solo consolidarás tu conocimiento, sino que también ganarás la confianza necesaria para enfrentar cualquier reto de SQL que se cruce en tu camino. Así que, sin más preámbulos, ¡descarga los archivos y pongámonos manos a la obra! O mejor dicho, ¡manos a los datos! 💻📊

Ojo: de acuerdo a donde a con que programa o en que aplicación practiques tendrás que modificar la ruta en el FROM, yo usaré el nombre de la base de datos por defecto.

Un paseo por los tipos de funciones

Veamos algunas de las funciones de window más útiles en SQL y cómo pueden ayudarte en la vida real. Además, explicaré la estructura de cada consulta y lo que hace cada componente, como OVER y PARTITION BY, con un pequeño ejemplo práctico y el resultado esperado.

🏅 ROW_NUMBER() – Para numerar filas como un profesional

La función ROW_NUMBER() asigna un número consecutivo a cada fila de la consulta según el orden que especifiques. Es útil para crear una numeración única en las filas, pero sin tener en cuenta empates.

Estructura:

SELECT 
    columna1,
    columna2,
    ROW_NUMBER() OVER (ORDER BY columnaX DESC) AS Row_Num
FROM tabla;

ROW_NUMBER(): Genera una numeración para las filas.
OVER(ORDER BY columnaX): Define cómo ordenar las filas antes de aplicar la numeración. ORDER BY columnaX DESC significa que se ordenarán de mayor a menor valor en la columna columnaX.
AS Row_Num: Alias que muestra el número asignado o el titulo de la nueva columna ser generada para mostrar la numeración.

Ejemplo:

SELECT 
    First_Name,
    Last_Name,
    Exam_Score,
    ROW_NUMBER() OVER (ORDER BY Exam_Score DESC) AS Row_Num
FROM student_data;

Resultado esperado: Este código numerará a los estudiantes por su puntaje en el examen, de mayor a menor. El estudiante con el puntaje más alto recibirá el número 1, el siguiente recibirá el 2, y así sucesivamente.

🏆 RANK() y DENSE_RANK() – El arte de los empates

Como te habrás dado cuenta en los resultados de arriba hay varios estudiantes que empatan en puntaje sin embargo se le asignó una numeración diferente, porque con el ROW_NUMBER() no se consideran empates.

RANK() y DENSE_RANK() si se utilizan para clasificar filas, teniendo en cuenta empates. La diferencia es que RANK() deja huecos en la numeración después de un empate, mientras que DENSE_RANK() no.

Considerando por ejemplo que hay dos datos que empatan en el segundo lugar.

RANK(): Asigna el mismo número a filas empatadas, pero salta el siguiente número en la clasificación (si 2 filas están en el puesto 1, la siguiente fila estará en el puesto 3).
DENSE_RANK(): Asigna el mismo número a filas empatadas, pero no salta el siguiente número. El siguiente estudiante siempre recibirá el número consecutivo (si 2 filas están en el puesto 1, la siguiente fila estará en el puesto 2).

Ejemplo:

SELECT 
    First_Name,
    Last_Name,
    RANK() OVER (ORDER BY Exam_Score DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Exam_Score DESC) AS Dense_Rank
FROM student_data;

Resultado esperado: Clasifica a los estudiantes por su puntuación en el examen. Si dos estudiantes tienen el mismo puntaje. Por ejemplo hay 108 estudiantes con 99 puntos que comparten el primer lugar

Diferencia entre `RANK()` y `DENSE_RANK()` en caso de empates:

RANK(): Cuando hay varios estudiantes con la misma puntuación (empates), RANK() asigna el mismo número a todos los estudiantes empatados, pero deja un hueco en la numeración para el siguiente grupo.Por ejemplo, si 108 estudiantes obtienen la puntuación más alta, RANK() asignará el número 1 a los 108 estudiantes. El siguiente estudiante, aunque esté en la posición número 109, no recibirá el número 2, sino el número 109, porque RANK() salta el conteo para reflejar cuántos estudiantes ocuparon el puesto anterior.Esto ocurre porque RANK() considera que los primeros 108 estudiantes ocupan el mismo puesto.
DENSE_RANK(): Por otro lado, DENSE_RANK() también asigna el mismo número a todos los estudiantes empatados, pero no deja huecos en la numeración. Es decir, los siguientes estudiantes recibirán el número consecutivo.Siguiendo el mismo ejemplo, si 108 estudiantes obtienen la puntuación más alta, DENSE_RANK() también asignará el número 1 a los 108 estudiantes. Sin embargo, el siguiente estudiante, aunque esté en la posición física número 109, recibirá el rango 2, ya que no se saltan números.

🔀 LEAD() y LAG() – El futuro y el pasado en tus manos

Estas funciones permiten comparar una fila con una anterior o posterior. LAG() obtiene el valor de la fila anterior y LEAD() el valor de la fila siguiente.

Estructura:

SELECT 
    columna1,
    LAG(columnaX) OVER (ORDER BY columnaY) AS Previous_Value,
    LEAD(columnaX) OVER (ORDER BY columnaY) AS Next_Value
FROM tabla;

LAG(columnaX): Obtiene el valor de la fila anterior de la columna columnaX.
LEAD(columnaX): Obtiene el valor de la fila siguiente de la columna columnaX.
OVER(ORDER BY columnaY): Define cómo ordenar las filas para que LAG() y LEAD() puedan determinar cuál es la fila anterior y la siguiente.

Ejemplo:

SELECT 
    First_Name,
    Exam_Score,
    LAG(Exam_Score) OVER (ORDER BY Exam_Date) AS Previous_Score,
    LEAD(Exam_Score) OVER (ORDER BY Exam_Date) AS Next_Score
FROM student_data;

Resultado esperado: Este código muestra la puntuación de cada estudiante en un examen, junto con la puntuación de su examen anterior (Previous_Score) y el siguiente (Next_Score). El valor de Previous_Score será NULL para el primer examen, y el valor de Next_Score será NULL para el último examen en la lista.

¿Qué significa `OVER` y `PARTITION BY` en SQL?

OVER: Define el conjunto de filas sobre las que se aplicará la función. Puede incluir un ORDER BY para establecer cómo ordenar las filas y un PARTITION BY para agruparlas antes de aplicar la función.
PARTITION BY: Divide las filas en grupos (o particiones) para aplicar la función por separado en cada grupo. Es similar a GROUP BY, pero en lugar de devolver una fila por grupo, devuelve todas las filas con el cálculo aplicado dentro de cada grupo.

Ejemplo con PARTITION BY:

SELECT 
    First_Name,
    Last_Name,
    Exam_Score,
    Gender,
    RANK() OVER (PARTITION BY Gender ORDER BY Exam_Score DESC) AS Rank_in_School
FROM student_data;

Resultado esperado: Este código clasifica a los estudiantes por su puntuación de examen dentro de cada género (es decir, por la columna Gender). Esto significa que genero tendrá su propio ranking independiente.

En resumen:

Ejemplos y Ejercicios

Veamos ejemplos y ejercicios de cómo se pueden utilizar las Window Functions con las bases de datos de ejemplo.

Ejemplo 1: Clasificar Estudiantes por Puntuación de Examen en Cada Escuela

SELECT 
    School_ID,
    First_Name,
    Last_Name,
    Exam_Score,
    RANK() OVER (PARTITION BY School_ID ORDER BY Exam_Score DESC) AS Rank_in_School
FROM student_data;

Explicación:

RANK(): Asigna un número a cada fila, en este caso, basado en la puntuación de examen.
OVER(): Aquí estamos definiendo cómo se calcula el ranking. No reduce el número de filas, simplemente añade el ranking a cada una.
PARTITION BY School_ID: Divide los estudiantes en grupos según la escuela a la que pertenecen (School_ID). Cada grupo es independiente y el ranking se calcula por separado en cada escuela.
ORDER BY Exam_Score DESC: Ordena los estudiantes por su puntuación de mayor a menor dentro de cada escuela. DESC significa «descendente», es decir, de mayor a menor.
Rank_in_School: Es el alias de la columna que contiene el ranking de cada estudiante dentro de su escuela.

Resultado:

Ejemplo 2: Promedio de Puntuación de Examen en Cada Escuela

SELECT 
    First_Name,
    Last_Name,
    School_ID,
    Exam_Score,
    AVG(Exam_Score) OVER (PARTITION BY School_ID) AS Avg_Score_School
FROM student_data;

Explicación:

AVG(Exam_Score): Calcula el promedio de las puntuaciones de examen.
OVER(): Nos permite calcular el promedio sin reducir el número de filas. Cada estudiante verá el promedio de su escuela en una columna adicional.
PARTITION BY School_ID: Agrupa a los estudiantes por escuela (School_ID). El promedio se calcula dentro de cada grupo.
Avg_Score_School: Es el alias de la columna que contiene el promedio de puntuación de la escuela del estudiante.

Ejemplo 3: Encontrar las 5 Escuelas con el Mayor Promedio de Puntuación

WITH School_Averages AS (
    SELECT 
        School_ID,
        AVG(Exam_Score) AS Avg_Score
    FROM `student.data`
    GROUP BY School_ID
)
SELECT * FROM (
    SELECT 
        School_ID,
        Avg_Score,
        RANK() OVER (ORDER BY Avg_Score DESC) AS School_Rank
    FROM School_Averages
) Ranked_Schools
WHERE School_Rank <= 5;

Explicación:

WITH: Crea una consulta temporal llamada School_Averages que calcula los promedios de puntuación por escuela.
AVG(Exam_Score): Calcula el promedio de las puntuaciones.
GROUP BY School_ID: Agrupa los datos por escuela para calcular un promedio por cada una.
RANK() OVER (ORDER BY Avg_Score DESC): Clasifica a las escuelas de mayor a menor promedio de puntuación.
School_Rank: El alias que contiene la clasificación de cada escuela.
WHERE School_Rank <= 5: Filtra para mostrar solo las 5 escuelas con mejor promedio.

Ejemplo 4: Número de Estudiantes por Escuela

SELECT DISTINCT
    School_ID,
    COUNT(Student_ID) OVER (PARTITION BY School_ID) AS Total_Students
FROM student_data;
ORDER BY Total_Students DESC

Explicación:

SELECT DISTINCT: Aplicamos DISTINCT para evitar resultados duplicados.
COUNT(Student_ID): Cuenta cuántos estudiantes hay.
OVER (PARTITION BY School_ID): Aplica el conteo dentro de cada escuela.
Total_Students: Alias que contiene el total de estudiantes por escuela.
ORDER BY Total_Students DESC: Ordena los resultados de forma descendente de acuerdo al toal de estudiantes.

Ejemplo 5: Ranking de Escuelas por Programas Extracurriculares

SELECT 
    School_Name,
    Extracurricular_Programs,
    DENSE_RANK() OVER (ORDER BY Extracurricular_Programs DESC) AS Rank_Programs
FROM school_data;

Explicación:

DENSE_RANK() OVER (ORDER BY Extracurricular_Programs DESC): Clasifica las escuelas según el número de programas extracurriculares que ofrecen.
Rank_Programs: Alias que muestra el ranking de programas extracurriculares.

Ejemplo 6: Promedio de Asistencia en Cada Escuela

SELECT DISTINCT
    School_ID,
    AVG(Attendance) OVER (PARTITION BY School_ID) AS Avg_Attendance
FROM student_data;

Explicación:

AVG(Attendance) OVER (PARTITION BY School_ID): Calcula el promedio de asistencia en cada escuela.
Avg_Attendance: Alias que muestra el promedio de asistencia de los estudiantes en cada escuela.

Ejemplo 7: Ranking de Escuelas por Cantidad de Actividad Física de los Estudiantes

Este ejemplo muestra qué escuelas tienen a los estudiantes más activos en términos de actividad física.

SELECT 
    sd.School_Name,
    AVG(st.Physical_Activity) AS Avg_Physical_Activity,
    RANK() OVER (ORDER BY AVG(st.Physical_Activity) DESC) AS Activity_Rank
FROM student_data st
JOIN school_data sd ON st.School_ID = sd.School_ID
GROUP BY sd.School_Name;

Explicación:

AVG(st.Physical_Activity): Calcula el promedio de la actividad física de los estudiantes en cada escuela.
RANK() OVER (ORDER BY AVG(st.Physical_Activity) DESC): Clasifica a las escuelas según el nivel de actividad física promedio de sus estudiantes.
Activity_Rank: Alias que muestra el ranking de actividad física.

Preguntas Frecuentes (FAQ):

1. ¿Qué son las Window Functions en SQL?
Las Window Functions son funciones avanzadas de SQL que permiten realizar cálculos sobre un conjunto de filas relacionadas con la fila actual, sin reducir el número de filas en el resultado. Son útiles para crear rankings, promedios, totales acumulados, y más, sin necesidad de subconsultas complicadas.

2. ¿Cuál es la diferencia entre RANK(), DENSE_RANK() y ROW_NUMBER()?

RANK() asigna el mismo rango a filas con valores iguales, pero deja huecos en la numeración.
DENSE_RANK() también asigna el mismo rango a filas con valores iguales, pero no deja huecos en la numeración.
ROW_NUMBER() simplemente asigna un número consecutivo a cada fila, sin importar si hay empates.

3. ¿Cuándo debo usar LAG() y LEAD()?

LAG() se utiliza para obtener el valor de la fila anterior, útil para comparar una fila con la anterior en el conjunto de resultados.
LEAD() obtiene el valor de la fila siguiente, ideal para comparar una fila con la siguiente.

4. ¿Puedo usar Window Functions con GROUP BY?
No directamente. Las Window Functions se aplican después del GROUP BY. Si necesitas agrupar datos y luego aplicar una función de ventana, utiliza primero GROUP BY para agregar datos, y después aplica las Window Functions con una subconsulta o CTE.

5. ¿Qué errores comunes debo evitar al usar Window Functions?

Intentar usar un alias de una función de ventana en la cláusula WHERE. En su lugar, usa una subconsulta o HAVING.
No ordenar correctamente las filas dentro del OVER(), lo que puede llevar a resultados incorrectos.

6. ¿Las Window Functions afectan el rendimiento de las consultas?
Sí, en datasets grandes, el uso de Window Functions puede afectar el rendimiento. Para optimizar, asegúrate de tener índices adecuados y limitar el uso de estas funciones solo cuando sea necesario.

Anexos: Enlaces Útiles para Aprender SQL y Window Functions

Documentación oficial de SQL:
- W3Schools SQL Tutorial – Excelente para aprender lo básico y practicar consultas simples.
- SQL Window Functions Documentation – Documentación oficial de PostgreSQL, pero aplicable a otras bases de datos con Window Functions.
Cursos gratuitos:
- SQL for Data Science (Coursera) – Curso de Coursera ideal para aprender SQL y aplicarlo en análisis de datos.
- Introducción a SQL Datacamp– Tutorial interactivo que te enseña a usar SQL con ejercicios prácticos.
Herramientas de práctica:
- SQL Fiddle – Un entorno gratuito donde puedes escribir y ejecutar consultas SQL online.
- SQL Lite Online – nos permite ejecutar de forma online los diferentes tipos de sentencias SQL
Libros recomendados:
- SQL for Data Scientists por Renee M. P. Teate – Un libro que enseña SQL con un enfoque en el análisis de datos y uso avanzado de funciones como las Window Functions.
- SQL Performance Explained por Markus Winand – Ideal para aprender a optimizar consultas SQL, incluyendo el uso eficiente de Window Functions.
Foros y comunidades:
- Stack Overflow – SQL – Encuentra respuestas a preguntas complejas sobre SQL y funciones de ventana.
- Reddit – Learn SQL – Comunidad donde puedes resolver dudas, compartir recursos y mejorar tu conocimiento de SQL.

🎯 Conclusión

Las Window Functions son una herramienta poderosa que puede transformar la manera en que analizas datos. Como hemos visto en los ejemplos anteriores:

Simplifican cálculos complejos
Reducen la necesidad de subconsultas
Mejoran el rendimiento de las consultas
Permiten análisis más sofisticados

Recapitulación rápida:

Usa ROW_NUMBER() para numerar filas.
Aplica RANK() y DENSE_RANK() para manejar empates en tus datos.
Utiliza LEAD() y LAG() para comparar filas adyacentes.

Ahora es tu turno. Toma lo que has aprendido aquí y aplícalo en tu próxima consulta. Estoy seguro de que, en poco tiempo, te darás cuenta de lo poderosas que pueden ser estas herramientas para transformar tus datos en oro puro. 💎

¿Por qué deberías aprender sobre las Window Functions? 🎯

¡Manos a los Datos! La Clave para Aprender SQL es Practicar

Descargar archivos de ejemplo para practicar

Un paseo por los tipos de funciones

🏅 ROW_NUMBER() – Para numerar filas como un profesional

🏆 RANK() y DENSE_RANK() – El arte de los empates

Diferencia entre RANK() y DENSE_RANK() en caso de empates:

🔀 LEAD() y LAG() – El futuro y el pasado en tus manos

¿Qué significa OVER y PARTITION BY en SQL?

Ejemplos y Ejercicios

Ejemplo 1: Clasificar Estudiantes por Puntuación de Examen en Cada Escuela

Ejemplo 2: Promedio de Puntuación de Examen en Cada Escuela

Ejemplo 3: Encontrar las 5 Escuelas con el Mayor Promedio de Puntuación

Ejemplo 4: Número de Estudiantes por Escuela

Ejemplo 5: Ranking de Escuelas por Programas Extracurriculares

Ejemplo 6: Promedio de Asistencia en Cada Escuela

Ejemplo 7: Ranking de Escuelas por Cantidad de Actividad Física de los Estudiantes

Preguntas Frecuentes (FAQ):

Anexos: Enlaces Útiles para Aprender SQL y Window Functions

🎯 Conclusión

Recapitulación rápida:

Entradas relacionadas

Diferencia entre `RANK()` y `DENSE_RANK()` en caso de empates:

¿Qué significa `OVER` y `PARTITION BY` en SQL?