Explicación de las funciones de ventana de BigQuery

Mantenerse al día con las tendencias de análisis de datos proporciona a su organización - y a su CV - la vanguardia. En este artículo, analizaremos las funciones de ventana de BigQuery y cómo puedes utilizarlas para profundizar en tus datos.

BigQuery de Google, lanzado en 2010, está ganando terreno como opción popular entre las organizaciones que necesitan analizar grandes cantidades de información rápidamente y comparar sus propios datos con datos estadísticos de dominio público.

Desde que Google adaptó el lenguaje de recuperación de datos de BigQuery para ajustarlo al SQL estándar -e incluyó características avanzadas como las funciones de ventana SQL-, su popularidad ha aumentado. Muchas organizaciones incluyen ahora los conocimientos de BigQuery como imprescindibles, y esto significa que los conocimientos de SQL son más demandados que nunca. Este artículo explica por qué el conocimiento de SQL es esencial para trabajar con BigQuery

SQL sigue manteniéndose como la principal habilidad para cualquiera que necesite trabajar con datos. Si aún no es un gurú de SQL, puede que le interese nuestra ruta de aprendizajeCurso completo de SQL . Incluye 7 cursos que le llevarán desde el principiante absoluto hasta el experto en SQL. El curso contiene cientos de retos de codificación del mundo real y se completa en unas 84 horas. Como puedes acceder a las bases de datos de ejemplo a través de tu navegador, no necesitas instalar ningún software para empezar.

¿Qué es BigQuery?

Google BigQuery es un almacén de datos de alta velocidad ubicado en la Nube. Diseñado especialmente para albergar datos utilizados para análisis, puede procesar petabytes de datos en cuestión de minutos. Si aún no te has hecho a la idea de lo que son los petabytes, un petabyte es un cuatrillón de bytes (o un millón de gigabytes).

En BigQuery se paga por lo que se utiliza, por lo que el coste de almacenar y analizar grandes cantidades de datos suele ser mucho menor que invertir en muchos discos duros. Y como en BigQuery se almacenan públicamente todo tipo de datos estadísticos útiles de gobiernos y organizaciones mundiales, puede acceder a ellos para obtener información sobre cómo podría mejorarse el rendimiento de su organización.

¿Qué es SQL Funciones de ventana?

Las funciones de ventana también se conocen como funciones analíticas o funciones OVER. Se añadieron al estándar SQL en 2003, y la mayoría de los principales proveedores de bases de datos comenzaron a implementarlas a partir de 2010. Por lo tanto, son una incorporación bastante reciente a SQL.

Antes de que se incluyeran las funciones de ventana en SQL, se podían listar filas individuales o calcular agregados como totales y medias. No era fácil hacer ambas cosas en la misma consulta, a menos que se escribieran subconsultas complejas (y probablemente lentas e ineficaces).

Esto significa que podría tener una lista como esta ...

Student ID	Subject	Score
1	Math	63
1	Science	50
2	Math	59

... o podrías mostrar agregados como este:

Subject	Class Average
Math	52
Science	61
English	55
Overall Average	56

En este ejemplo, si quisiera saber cómo se compara la puntuación de un estudiante individual con la media de la clase, tendría que mirar los dos informes uno al lado del otro. Sería mucho mejor si pudiera ver la media de la clase en la misma fila que la puntuación de un alumno, como en este ejemplo:

Student ID	Subject	Score	Class Average
1	Math	63	62
1	Science	50	61
2	Math	59	52

Como ya he mencionado, podría lograrlo utilizando subconsultas. Pero las subconsultas son notoriamente lentas de ejecutar y pueden complicar mucho la consulta.

Este es el tipo de cosas que las funciones de ventana le permiten hacer fácil y eficientemente: incluir agregados junto con detalles en la misma fila.

¿Por qué se llaman Funciones de ventana?

Se denominan funciones ventana porque, mientras se observa una fila individual, también se puede "mirar a través de la ventana" y extraer información de todo el conjunto de datos o de las filas relacionadas con la fila actual.

Por ejemplo, veamos una tabla de calificaciones de alumnos:

Student ID	Teacher ID	Subject	Grade
1	1	Math	63
2	1	Math	80
3	2	Math	60
4	2	Math	45
5	1	Math	52
6	1	Math	70
7	2	Math	65
1	2	Science	70
2	2	Science	62
3	2	Science	90
4	2	Science	30
5	2	Science	53
1	5	English	59
3	5	English	70
5	5	English	45
6	5	English	62
1	12	History	55
3	12	History	67
4	12	History	58

Como en el ejemplo anterior, queremos mostrar la media de la clase junto a cada alumno, de forma que una sola fila tenga este aspecto:

Student ID	Subject	Grade	Class Average
4	Math	45	62

Para ello, tenemos que mirar la media de todos los demás alumnos cuando extraigamos esta fila y mostrar el resultado junto a los demás datos.

En SQL, las funciones de ventana utilizan una ventana deslizante de filas para extraer información adicional de todo el conjunto de datos o de un subconjunto relacionado con la fila actual.

En el diagrama anterior, la fila actual es un resultado de matemáticas, y la ventana incluye todas las calificaciones de matemáticas.

Cuando extraigamos esta fila...

Student ID	Subject	Grade	Class Average
1	Science	70	62

... necesitaremos que la ventana se "deslice" para que podamos ver todos los resultados de ciencias con el fin de calcular la media de ciencias.

Las funciones de ventana tienen muchas de las mismas capacidades que la cláusula GROUP BY, pero la diferencia es que nos permiten ver agregados y detalles uno al lado del otro.

Sintaxis de BigQuery Funciones de ventana

La cláusula OVER()

La cláusula OVER() indica que está utilizando una función de ventana. Escriba la consulta como de costumbre e incluya los agregados que desee junto a los demás nombres de columna. Cada agregado se identifica con la cláusula OVER(). Cuando se utiliza esta cláusula por sí sola, la "ventana" es todo el conjunto de datos. Más adelante hablaré de las ventanas deslizantes.

Por ejemplo, si quisieras extraer todos los resultados de matemáticas y mostrar el promedio de la clase y la calificación más alta y más baja contra la calificación de cada estudiante, tu consulta se vería así:

SELECT 
  student_id,
  grade,
  AVG(grade) OVER() AS average,
  MIN(grade) OVER() AS lowest,
  MAX(grade) OVER() AS highest
FROM exam_results
WHERE subject = 'Math';

Los resultados serían los siguientes

student_id	grade	average	lowest	highest
1	63	62	45	80
2	80	62	45	80
3	60	62	45	80
4	45	62	45	80
5	52	62	45	80
6	70	62	45	80
7	65	62	45	80

La cláusula PARTITION BY

Esta cláusula utiliza una ventana deslizante. En lugar de una ventana que contenga todo el conjunto de datos, sólo incluye una partición (o parte) del conjunto.

En el ejemplo anterior, incluí sólo los resultados matemáticos excluyendo todos los demás mediante la cláusula WHERE. Si quisiera un informe que mostrara los resultados de todas las asignaturas pero calculara la media utilizando sólo las filas en las que la asignatura coincidiera con la fila actual, utilizaría la cláusula PARTITION BY:

SELECT 
  student_id,
  subject,
  grade,
  AVG(grade) OVER(PARTITION BY subject) AS average
FROM exam_results;

Eche un vistazo a esta copia codificada por colores de la tabla de calificaciones de los alumnos para ver cómo funcionarán las particiones:

Student ID	Teacher ID	Subject	Grade
1	1	Math	63
2	1	Math	80
3	2	Math	60
4	2	Math	45
5	1	Math	52
6	1	Math	70
7	2	Math	65
1	2	Science	70
2	2	Science	62
3	2	Science	90
4	2	Science	30
5	2	Science	53
1	5	English	59
3	5	English	70
5	5	English	45
6	5	English	62
1	12	History	55
3	12	History	67
4	12	History	58

Al procesar cada fila, las filas incluidas en la ventana cambian en función del valor de la columna subject. Esto significa que la media se calcula sólo para la partición del conjunto de datos donde la asignatura coincide con la fila actual. Puede visualizarlo así:

Los resultados se verían así:

Student ID	Subject	Grade	Class Average
1	Math	63	62
2	Math	80	62
3	Math	60	62
4	Math	45	62
5	Math	52	62
6	Math	70	62
7	Math	65	62
1	Science	70	61
2	Science	62	61
3	Science	90	61
4	Science	30	61
5	Science	53	61
1	English	59	59
3	English	70	59
5	English	45	59
6	English	62	59
1	History	55	60
3	History	67	60
4	History	58	60

La cláusula ORDER BY

La cláusula ORDER BY dentro de la función OVER() utiliza un tipo diferente de ventana deslizante. Cuando se utiliza OVER(ORDER BY column_name), la ventana incluye sólo aquellas filas en las que el valor de la columna especificada es menor o igual que el valor de esa columna en la fila actual.

La cláusula ORDER BY es útil para calcular totales y medias móviles. Como ejemplo, utilizaré una tabla llamada monthly_transactions que contiene transacciones de cuentas bancarias:

account_id	tran_date	transaction	value
1	2023-09-01	Opening Balance	500.00
1	2023-09-03	Deposit	137.45
1	2023-09-12	Withdrawal	-200.00
1	2023-09-18	Withdrawal	-250.00
2	2023-09-01	Opening Balance	1200.00
2	2023-09-14	Deposit	900.00
2	2023-09-20	Purchase	-318.90

La siguiente consulta mostrará una lista de las transacciones de la Cuenta ID 1, mostrando un saldo actual.

SELECT 
  account_id,
  tran_date,
  transaction,
  value,
  SUM(value) OVER(ORDER BY tran_date) AS balance
FROM monthly_transactions
WHERE account_id = 1;

La inclusión de la cláusula ORDER BY dentro de la cláusula OVER controla una ventana móvil.

Si lo desea, también puede utilizar la cláusula habitual ORDER BY al final de la consulta para controlar el orden final de las filas en el informe. No es necesario que se muestren en el orden original.

Por defecto, el uso de ORDER BY dentro de la cláusula OVER hace que la ventana se deslice de forma que sólo vea las filas cuya fecha sea menor o igual que la fecha de la fila actual. Hay otras palabras clave que pueden cambiar este valor por defecto, pero están un poco más allá del alcance de este artículo.

Puede visualizarlo así:

Los resultados se verían así:

account_id	tran_date	transaction	value	balance
1	2023-09-01	Opening Balance	500.00	500.00
1	2023-09-03	Deposit	137.45	637.45
1	2023-09-12	Withdrawal	-200.00	437.45
1	2023-09-18	Withdrawal	-250.00	187.45
2	2023-09-01	Opening Balance	1200.00	1200.00
2	2023-09-14	Deposit	900.00	2100.00
2	2023-09-20	Purchase	-318.90	1781.10

Usando ORDER BY con la cláusula PARTITION BY

Si desea mostrar todas las cuentas con sus saldos corrientes, puede utilizar PARTITION BY y ORDER BY juntos:

SELECT 
  account_id,
  tran_date,
  transaction,
  value,
  SUM(value) OVER(PARTITION BY account_id ORDER BY tran_date) AS balance
FROM monthly_transactions
ORDER BY account_id, tran_date

La cláusula PARTITION BY hará que la ventana deslizante sólo incluya las filas en las que account_id coincida con la fila actual. La cláusula ORDER BY hará que sólo incluya filas dentro de esa partición donde la fecha sea menor o igual a la fecha de la fila actual.

Los resultados serían:

account_id	tran_date	transaction	value	balance
1	2023-09-01	Opening Balance	500.00	500.00
1	2023-09-03	Deposit	137.45	637.45
1	2023-09-12	Withdrawal	-200.00	437.45
1	2023-09-18	Withdrawal	-250.00	187.45
2	2023-09-01	Opening Balance	1200.00	1200.00
2	2023-09-14	Deposit	900.00	2100.00
2	2023-09-20	Purchase	-318.90	1781.10

Cada cuenta tiene su propio saldo.

SQL Funciones de ventana Disponible en BigQuery

Ya ha visto cómo las funciones comunes de agregación de SQL como SUM(), AVG(), MIN() y MAX() pueden utilizarse junto con la cláusula OVER para extraer agregados de una ventana de datos.

Google BigQuery, al igual que muchos otros dialectos de SQL, dispone de funciones adicionales que pueden ofrecer una visión más profunda de los datos. He aquí algunos ejemplos.

RANGO()

Esta función ordena el conjunto de datos de mayor a menor en una columna especificada. Puede responder a preguntas como

• ¿Qué lugar ocupó cada estudiante en la clase, según los resultados de los exámenes?
• ¿Qué productos fueron más rentables?
• ¿Qué clientes gastaron más dinero?
• ¿Qué almacén recibió más reclamaciones?

Utilizando la tabla de ejemplo que vimos antes, clasifiquemos a los estudiantes por resultados de exámenes utilizando esta consulta:

SELECT 
  student_id,
  subject,
  grade,
  RANK() OVER(PARTITION BY subject ORDER BY grade desc) AS place
FROM exam_results
ORDER BY subject, grade DESC;

El resultado sería:

student_id	subject	grade	place
3	English	70	1
6	English	62	2
1	English	59	3
5	English	45	4
3	History	67	1
4	History	58	2
1	History	55	3
2	Math	80	1
6	Math	70	2
7	Math	65	3
1	Math	63	4
3	Math	60	5
5	Math	52	6
4	Math	45	7
3	Science	90	1
1	Science	70	2
2	Science	62	3
5	Science	53	4
4	Science	30	5

Al calcular estos resultados, la cláusula PARTITION BY subject hace que SQL mire sólo los resultados de la misma asignatura que la fila actual. La cláusula ORDER BY grade DESC los ordena en orden descendente de calificación. A continuación, la función RANK() clasifica a los alumnos en este orden.

Como el alumno 3 tiene la nota más alta en Inglés, su rango es 1; el alumno 6, el siguiente más alto, tiene el rango 2 en esta asignatura. El alumno 2 tiene la nota más alta en Matemáticas y está clasificado como 1.

DENSE_RANK()

DENSE_RANK() se utiliza con el mismo propósito que RANK. La diferencia entre ambos se explica mejor observando los resultados de esta consulta, que representan puntuaciones en una competición de tiro.

Utilizando RANK, la consulta sería:

SELECT 
  competitor_no,
  score,
  RANK() OVER(ORDER BY score desc) AS rank
FROM match_results
ORDER BY score DESC;

Los resultados son:

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	2
9	81	4
2	76	5
6	76	5
7	72	7
3	70	8
8	68	9
1	62	10

Los competidores 5 y 10 empatan en el segundo puesto, y ambos reciben una clasificación de 2. El competidor 9 es el siguiente más alto, y se clasifica como 4. El tercer puesto queda fuera.

Utilizando DENSE_RANK, la consulta es:

SELECT 
  competitor_no,
  score,
  DENSE RANK() OVER(ORDER BY score desc) AS rank
FROM match_results
ORDER BY score DESC;

Los resultados son:

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	2
9	81	3
2	76	4
6	76	4
7	72	5
3	70	6
8	68	7
1	62	8

Los concursantes 5 y 10 siguen teniendo una clasificación de 2, pero el tercer puesto no queda excluido: El competidor 9 tiene ahora una clasificación de 3.

Ambas funciones tienen la misma sintaxis. Si quisiéramos recodificar la consulta anterior sobre las calificaciones de los alumnos utilizando la función DENSE_RANK(), quedaría así:

SELECT 
  student_id,
  subject,
  grade,
  DENSE_RANK() OVER(PARTITION BY subject ORDER BY grade desc) AS class_place
FROM exam_results
ORDER BY subject, grade DESC

Pero como no hay valores de empate, el resultado sería el mismo.

ROW_NUMBER()

La función ROW_NUMBER() funciona de forma similar a las dos funciones anteriores, pero las filas se numeran simplemente en orden. Si las filas tienen el mismo valor, se numerarán consecutivamente, dependiendo de cuál se encontró primero. A continuación se muestran los resultados de la consulta de la competición de tiro utilizando ROW_NUMBER() en lugar de RANK() o DENSE_RANK():

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	3
9	81	4
2	76	5
6	76	6
7	72	7
3	70	8
8	68	9
1	62	10

LAG()

Esta función permite comparar los datos de la fila anterior del conjunto de resultados con los datos de la fila actual. Es ideal para realizar comparaciones interanuales, lo que permite descubrir tendencias e identificar problemas de rendimiento empresarial.

LAG() y la función relacionada LEAD() sólo puede utilizarse junto con la cláusula OVER(ORDER BY).

Tomemos como ejemplo la siguiente tabla, que contiene datos de ventas de una pequeña empresa:

year	sales_value	sales_quantity	profit
2019	54000	900	8000
2020	75000	1200	11000
2021	30000	450	1000
2022	60000	10000	7000

La consulta de ejemplo para comparar las cifras interanuales es:

SELECT 
  year,
  sales_value,
  sales_quantity,
  profit,
  LAG(sales_value) OVER(ORDER BY year) as ly_value,
  LAG(sales_quantity) OVER(ORDER BY year) as ly_qty,
  LAG(profit) OVER(ORDER BY year) as ly_profit,
  profit - LAG(profit) OVER(ORDER BY year) as inc_dec
FROM annual_sales
ORDER BY year;

Los resultados de esta consulta son:

year	sales_value	sales_quantity	profit	ly_value	ly_qty	ly_profit	inc_dec
2019	54000	900	8000	NULL	NULL	NULL	NULL
2020	75000	1200	11000	54000	900	8000	3000
2021	30000	450	1000	75000	1200	11000	-10000
2022	60000	10000	7000	30000	450	1000	6000

Analicemos esta línea de la consulta y veamos qué ha hecho realmente:

LAG(sales_value) OVER(ORDER BY year) as ly_value

En esta fila del resultado...

year	sales_value	sales_quantity	profit	ly_value	ly_qty	ly_profit	inc_dec
2020	75000	1200	11000	54000	900	8000	3000

... nuestra fila actual corresponde al año 2020. La función LAG() junto con ORDER BY año hace que SQL busque en la fila del año anterior (2019) y extraiga de ella el valor de las ventas bajo el título ly_value.

Observará que en la primera fila, las columnas calculadas por la función LAG() contienen un valor nulo, ya que no existe ningún registro anterior.

LEAD()

La función LEAD() es la inversa de LAG(): obtiene los datos de la fila siguiente a la actual, en lugar de la anterior.

Para comparar el beneficio entre el año actual, el anterior y el posterior utilizando la misma tabla de muestra, la consulta sería:

SELECT 
  year,
  profit,
  LAG(profit) OVER(ORDER BY year) as ly_profit,
  profit - LAG(profit) OVER(ORDER BY year) as ly_inc_dec,
  LEAD(profit) OVER(ORDER BY year) as ny_profit,
  LEAD(profit) OVER(ORDER BY year) - profit as ny_inc_dec
FROM annual_sales
ORDER BY year;

Los resultados de esta consulta serían:

year	profit	ly_profit	ly_inc_dec	ny_profit	ny_inc_dec
2019	8000	NULL	NULL	11000	3000
2020	11000	8000	3000	1000	-10000
2021	1000	11000	-10000	7000	6000
2022	7000	1000	6000	NULL	NULL

Las columnas ny_profit y ny_inc_dec utilizan la función LEAD(), que hace que SQL busque en la siguiente fila de la secuencia para extraer estos campos. Si la fila actual corresponde a 2019, estos dos campos se extraerán de la fila de 2020.

Esto debería haberle dado una idea de algunas de las útiles funciones de ventana SQL disponibles en BigQuery. Encontrará una lista completa de funciones en la documentación SQL de BigQuery.

Para ver más ejemplos de funciones de ventana, consulte el artículo Ejemplos de funciones de ventana SQL. También puede encontrar una guía de referencia rápida para la sintaxis de las funciones de ventana SQL en nuestra Funciones de ventana Cheat Sheet.

Usos prácticos de BigQuery Funciones de ventana

En el mundo real, hay muchas formas en las que las funciones de ventana de BigQuery pueden ofrecerle información que ayude a su organización a funcionar mejor. Son muy potentes y permiten elaborar informes complejos con gran rapidez.

He aquí algunas ideas de cómo pueden utilizarse las funciones de ventana de BigQuery:

• Clasificar a sus empleados por rendimiento para dar recompensas motivadoras. Consulte este artículo para obtener más información sobre la clasificación de filas.
• Averiguar el rendimiento de cada línea de productos en comparación con otros artículos similares.
• Saber cómo afectan los productos individuales a la media móvil de beneficios. Puede leer más sobre el cálculo de medias móviles en SQL aquí.
• Comparar datos interanuales para descubrir tendencias. Obtenga más información sobre la preparación de comparaciones interanuales en SQL en este artículo.
• Utilizar totales acumulados para ver exactamente cuántas ventas se han realizado en un momento dado. Obtenga más información sobre el cálculo de totales acumulados en SQL aquí.

Próximos pasos con BigQuery Funciones de ventana

Ahora que ha visto lo que las funciones de ventana SQL pueden hacer en BigQuery y otros sistemas de gestión de bases de datos, es hora de pensar en mejorar sus habilidades en esta importante área.

Un buen lugar para empezar es LearnSQL.es's SQL Funciones de ventana course. Aprenderás paso a paso cómo utilizar las técnicas que has visto en este artículo, con una explicación completa de cada tema. También tendrás mucha práctica, con más de 200 ejercicios interactivos para asegurarte de que sabes cómo extraer información compleja en situaciones del mundo real. Dispondrás de ayuda cuando te atasques y podrás acceder a bases de datos de ejemplo a través de tu navegador. El curso dura unas 20 horas.

Si realmente desea convertirse en un experto, puede obtener más práctica en la resolución de problemas complejos trabajando con nuestro Funciones de ventana Practice Set. Abordarás 100 ejemplos de informes complejos utilizando tres bases de datos diferentes.

También puedes leer este artículo sobre cómo practicar las funciones de ventana, y puedes obtener más información sobre la sintaxis de BigQuery aquí.

Si estás buscando trabajo para un puesto de alto nivel en análisis o ciencia de datos, es muy probable que en la entrevista te pidan que demuestres tus conocimientos de las funciones de ventana de SQL. Para hacerte una idea del tipo de preguntas que te pueden hacer (y cómo responderlas), aquí tienes un artículo que analiza las principales preguntas de la entrevista sobre las funciones de ventana de SQL.

Da hoy mismo los primeros pasos para llevar tus conocimientos de análisis de datos al siguiente nivel.