Práctica SQL: 11 Ejercicios de Práctica de Funciones de Ventana SQL con Soluciones Detalladas

En este artículo, presentamos 11 ejercicios de práctica relacionados con las funciones ventana de SQL, junto con soluciones y explicaciones detalladas.

Las funciones ventana de SQL son una potente característica que nos permite extraer información significativa de nuestros datos fácilmente, sin embargo, pocos cursos de SQL ofrecen ejercicios de funciones ventana de SQL. Esto dificulta bastante la práctica de las funciones ventana. En este artículo, te daremos 11 ejercicios de práctica que incluyen funciones ventana.

Todos los ejercicios mostrados en este artículo provienen de nuestros cursos interactivos Funciones de ventana y Funciones de ventana Practice Set. El curso Funciones de ventana es un tutorial en profundidad con más de 200 ejercicios. Recomendamos este curso para aprender o repasar los conocimientos sobre las funciones de ventana de SQL. Funciones de ventana Practice Set es un curso de práctica para aquellos que conocen las funciones de ventana de SQL y buscan más práctica.

Funciones de ventana en pocas palabras

Las funciones ventana de SQL son herramientas que ayudan a analizar datos de diferentes maneras. Desde el cálculo de totales y medias móviles, la comparación de datos dentro de subconjuntos específicos y la identificación de los datos de mayor rendimiento, hasta la realización de clasificaciones y particiones complejas, estas funciones nos permiten extraer información más profunda de nuestros datos y realizar tareas complejas con facilidad.

Las funciones de ventana de SQL ofrecen un versátil conjunto de herramientas para mejorar el análisis de datos. Este conjunto de herramientas incluye:

• ROW_NUMBER(), RANK(), DENSE_RANK() y NTILE() para clasificar datos.
• SUM() AVG(), , , y para agregaciones. COUNT() MAX() MIN()
• LEAD() y LAG() para comparar datos entre filas.
• FIRST_VALUE() y LAST_VALUE() para extraer valores límite.

Además, la cláusulaOVER() permite particionar y ordenar los datos con precisión dentro de estas funciones, lo que permite a los usuarios realizar cálculos complejos en subconjuntos definidos de datos.

El dominio de las funciones de ventana de SQL es cada vez más importante para los profesionales, analistas e ingenieros de datos. No sólo les capacita para resolver eficazmente complejos retos analíticos, sino que también les proporciona una comprensión más profunda de los datos. Practicar las funciones de ventana de SQL mejorará su capacidad para crear consultas avanzadas y le ayudará a descubrir nuevas perspectivas a partir de los datos. Se trata de una habilidad vital en el mundo actual, centrado en los datos.

Antes de abordar los ejercicios, es posible que desee echar un vistazo a nuestra hoja de trucos de funciones de ventana, que le recordará la lista de funciones de ventana y su sintaxis.

SQL Funciones de ventana Ejercicios prácticos: Tienda de películas online

Antes de comenzar con los ejercicios, veamos el conjunto de datos que utilizaremos.

Conjunto de Datos

Los siguientes ejercicios utilizan la base de datos de la tienda de películas online, que contiene seis tablas.

• La tabla customer almacena información sobre todos los clientes registrados. Las columnas son id, first_name, last_name, join_date, y country.
• La tabla movie contiene registros de todas las películas disponibles en la tienda. Las columnas son id, title, release_year, genre, y editor_ranking.
• La tabla review almacena las valoraciones de los clientes sobre las películas. Las columnas son id, rating, customer_id (hace referencia a la tabla customer tabla), y movie _id (hace referencia a la movie tabla).
• La tabla single_rental almacena información sobre las películas que los clientes alquilaron durante un periodo de tiempo determinado. Las columnas son id, rental_date, rental_period, platform, customer_id (hace referencia a la tabla), (hace referencia a la tabla) y (hace referencia a la tabla). customer tabla), movie _id (hace referencia a la movie tabla), payment_date, y payment_amount.
• La tabla subscription almacena los registros de todos los clientes suscritos a la tienda. Las columnas son id, length (en días), start_date, platform, payment_date, payment_amount, y customer_id (hace referencia a la customer tabla).
• La tabla giftcard contiene información sobre las tarjetas regalo adquiridas. Las columnas son id, amount_worth, customer_id (hace referencia a la customer tabla), payment_date, y payment_amount.

Ahora que ya estamos familiarizados con el conjunto de datos, pasemos a los ejercicios prácticos de SQL.

Ejercicio 1: Ordenar Alquileres por Precio

Ejercicio:

Para cada alquiler individual, muestre rental_date, el título de la película alquilada, su género, el importe del pago y la clasificación del alquiler en función del precio pagado (el alquiler más caro debería tener una clasificación = 1). La clasificación debe crearse por separado para cada género de película. Permita el mismo rango para varias filas y deje espacios en la numeración.

Solución:

SELECT
  rental_date,
  title,
  genre,
  payment_amount,
  RANK() OVER(PARTITION BY genre ORDER BY payment_amount DESC)
FROM movie
JOIN single_rental
  ON single_rental.movie_id = movie.id;

Explicación de la solución:

La instrucción nos dice que mostremos cierta información sobre alquileres individuales y películas. Por lo tanto, unimos la tabla single_rental con la tabla movie en su columna común (es decir, la columna movie_id ).

A continuación, necesitamos clasificar todos los alquileres en función del precio pagado por alquiler. Para ello, utilizamos RANK(). A continuación, en la cláusula OVER(), ordenamos los datos por la columna payment_amount en orden descendente, de forma que el alquiler más caro tenga el rango 1.

Como la clasificación debe crearse por separado para cada género de película, en la cláusula OVER(), dividimos los datos por la columna de género.

¿Por qué elegimos RANK() en lugar de DENSE_RANK() o ROW_NUMBER()? La instrucción dice que se permite el mismo rango para varias filas; por lo tanto, reducimos las opciones a RANK() y DENSE_RANK(). La función ROW_NUMBER() asigna números consecutivos como rangos a filas sucesivas; no permite múltiples filas con el mismo rango.

Los huecos en la numeración de filas están permitidos, por lo que necesitamos la función RANK(). DENSE_RANK() no omite ningún número en una secuencia, incluso si varias filas tienen el mismo rango. En la tabla siguiente se presentan estas funciones de clasificación y cómo funcionan a partir de una lista de valores de datos:

VALUE	ROW_NUMBER()	RANK()	DENSE_RANK()
Apple	1	1	1
Apple	2	1	1
Apple	3	1	1
Carrot	4	4	2
Banana	5	5	3
Banana	6	5	3
Peach	7	7	4
Tomato	8	8	5

Consulte este artículo para obtener más información sobre las distintas funciones de clasificación.

Ejercicio 2: Encontrar al^2º cliente que compra tarjetas regalo

Ejercicio:

Mostrar el nombre y apellidos del cliente que compró la segunda tarjeta regalo más reciente, junto con la fecha en la que se realizó el pago. Asuma que se asigna un rango único para cada compra de tarjeta regalo.

Solución:

WITH ranking AS (
  SELECT
    first_name,
    last_name,
    payment_date,
    ROW_NUMBER() OVER(ORDER BY payment_date DESC) AS rank
  FROM customer
  JOIN giftcard
    ON customer.id = giftcard.customer_id
)

SELECT
  first_name,
  last_name,
  payment_date
FROM ranking
WHERE rank = 2;

Explicación de la solución:

Vamos a mostrar información sobre los clientes y sus compras con tarjeta regalo, por lo que necesitamos unir la tabla customer con la tabla giftcard tabla en su columna común, (customer_id).

La instrucción dice que busquemos al cliente que compró la segunda tarjeta regalo más reciente. Para ello, primero clasifiquemos las compras con tarjeta regalo utilizando la función ROW_NUMBER(); suponemos que se asigna un rango único a cada compra con tarjeta regalo.

La sentencia interna SELECT selecciona la información del cliente y las fechas de sus compras con tarjeta regalo. A continuación, clasificamos las filas utilizando la función ROW_NUMBER() para marcar la segunda compra con tarjeta regalo más reciente (es decir, el valor de rango 2).

Esta sentencia interna SELECT es una expresión común de tabla (CTE). Está envuelta dentro de la cláusula WITH y se denomina ranking. Seleccionamos los datos relevantes de esta CTE y establecemos una condición en la cláusula WHERE para que sólo aparezca la fila con un rango igual a 2.

¿Por qué necesitamos definir un CTE y luego consultarlo? Porque no podemos utilizar la columna de rango en la cláusula WHERE de la consulta interna SELECT. La razón es el orden de ejecución, que es: FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, y LIMIT. Por lo tanto, la columna de rango aún no está definida en el momento en que se ejecutaría la cláusula WHERE del SELECT interno.

Ejercicio 3: Calcular el total acumulado de los pagos

Ejercicio:

Para cada alquiler individual, muestre el id, rental_date, payment_amount y el total corrido de payment_amounts de todos los alquileres desde el más antiguo (en términos de rental_date) hasta la fila actual.

Solución:

SELECT
  id,
  rental_date,
  payment_amount,
  SUM(payment_amount) OVER(
    ORDER BY rental_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM single_rental;

Explicación de la solución:

En primer lugar, seleccionamos la información sobre cada alquiler individual de la single_rental tabla.

A continuación, vamos a encontrar el total acumulado de los importes de pago de todos los alquileres utilizando la función SUM() (que toma la columna payment_amount como argumento) con la cláusula OVER(). Aquí hay un artículo que explica los detalles sobre el total acumulado y cómo calcularlo en SQL.

La instrucción dice que se busque el total acumulado desde la fecha de alquiler más antigua hasta la fecha de la fila actual. Entonces, en la cláusula OVER(), necesitamos ordenar los datos por la columna rental_date y luego definir ROWS para ser contada en el total corrido, desde la fecha más antigua (BETWEEN UNBOUNDED PRECEDING) hasta la fecha actual (AND CURRENT ROW).

SQL Funciones de ventana Ejercicios Prácticos: Clínica de Salud

Conjunto de datos

Los siguientes ejercicios utilizan una base de datos de una clínica de salud que contiene dos tablas.

• La tabla doctor almacena información sobre los médicos. Las columnas son id, first_name, last_name, y age.
• La tabla procedure contiene información sobre los procedimientos realizados por los médicos a los pacientes. Las columnas son id, procedure_date, doctor_id (hace referencia a la doctor tabla), patient_id, category, name, price, y score.

Ahora que estamos familiarizados con el conjunto de datos, procedamos con los ejercicios de práctica de SQL.

Ejercicio 4: Calcular la Media Móvil de las Puntuaciones

Ejercicio:

Para cada procedimiento, muestre la siguiente información: procedure_date, doctor_id, category, name, score y la puntuación media de los procedimientos de la misma categoría que se incluyen en el siguiente marco de ventana: las dos filas anteriores, la fila actual y las tres filas siguientes en función de la fecha del procedimiento.

Solución:

SELECT
  procedure_date,
  doctor_id,
  category,
  name,
  score,
  AVG(score) OVER(
    PARTITION BY category
    ORDER BY procedure_date
    ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING)
FROM procedure;

Explicación de la solución:

Vamos a mostrar la información de cada procedimiento seleccionando de la procedure tabla.

Luego hallaremos la puntuación media de los procedimientos de la misma categoría. Para ello, utilizamos la función AVG() con la columna puntuación como argumento. A esto le sigue la cláusula OVER(), en la que particionamos el conjunto de datos en categorías.

Además, debemos considerar sólo las filas incluidas en el siguiente marco de ventana: las dos filas anteriores, la fila actual y las tres filas siguientes en cuanto a la fecha del procedimiento. Definimos este marco de datos en la cláusula OVER(). En primer lugar, ordenamos el conjunto de datos por la columna procedure_date para que los procedimientos aparezcan cronológicamente. A continuación, definimos las filas que se tendrán en cuenta para calcular el valor medio de la puntuación: dos filas anteriores (BETWEEN 2 PRECEDING) y tres filas siguientes (AND 3 FOLLOWING), incluida la fila actual.

Esto se conoce como media móvil. Puede obtener más información en Qué es una media móvil y cómo calcularla en SQL.

Ejercicio 5: Encontrar la Diferencia entre los Precios de los Procedimientos

Ejercicio:

Para cada procedimiento, muestre la siguiente información: id, procedure_date, name, price, price del procedimiento anterior (en términos del id) y la diferencia entre estos dos valores. Nombra las dos últimas columnas previous_price y difference.

Solución:

SELECT
  id,
  procedure_date,
  name,
  price,
  LAG(price) OVER(ORDER BY id) AS previous_price,
  price - LAG(price) OVER(ORDER BY id) AS difference
FROM procedure;

Explicación de la solución:

De nuevo, empezamos seleccionando información sobre cada procedimiento de la procedure tabla.

La instrucción dice que mostremos el precio del procedimiento anterior. Para ello, utilizamos la función LAG(), que devuelve el valor de la fila anterior para su argumento (aquí, para la columna price ). Para asegurarnos de que elegimos el precio del procedimiento anterior en términos de id, ordenamos el conjunto de datos por la columna id en la cláusula OVER(). Le ponemos el alias previous_price.

Ahora que tenemos el valor del precio y el valor del precio anterior, podemos seleccionar la diferencia entre estos dos valores. Simplemente restamos la función LAG() de la columna price y le ponemos el alias difference.

Consulte Cómo Calcular la Diferencia entre Dos Filas en SQL para obtener más información.

Ejercicio 6: Encontrar la Diferencia entre el Precio Actual y el Mejor Precio

Ejercicio:

Para cada procedimiento, muestre el:

• procedure_date
• name
• price
• category
• score
• Precio del mejor procedimiento (en términos de puntuación) de la misma categoría (columna best_procedure).
• Diferencia entre este price y el best_procedure (columna difference).

Solución:

SELECT 
  procedure_date, 
  name, 
  price,
  category,
  score, 
  FIRST_VALUE(price) OVER(PARTITION BY category ORDER BY score DESC)
     AS best_procedure,
  price - FIRST_VALUE(price) OVER(PARTITION BY category 
     ORDER BY score DESC) AS difference
FROM procedure;

Explicación de la solución:

Comenzamos seleccionando la información sobre cada procedimiento de la tabla de procedimientos.

El siguiente paso es encontrar el precio del mejor procedimiento. Utilizamos la función FIRST_VALUE(), que devuelve el primer valor de una partición ordenada de un conjunto de resultados. Para obtener el precio del mejor procedimiento de la misma categoría, debemos particionar el conjunto de datos por la columna category. Y para obtener el precio del mejor procedimiento en términos de puntuación, debemos ordenar el conjunto de datos por la columna de puntuación en orden descendente. Esta expresión se denomina best_procedure.

Por último, hallamos la diferencia entre price y best_procedure restando la función FIRST_VALUE() de la columna de precio.

Ejercicio 7: Encontrar el mejor médico por procedimiento

Ejercicio:

Averiguar qué médico es el mejor en cada procedimiento. Para cada procedimiento, seleccione el nombre del procedimiento y el nombre y apellidos de todos los médicos que hayan obtenido puntuaciones altas (superiores o iguales a la puntuación media de este procedimiento). Clasifique a los médicos por procedimiento en función del número de veces que lo han realizado. A continuación, muestre los mejores médicos para cada procedimiento, es decir, los que tienen una puntuación de 1.

Solución:

WITH cte AS (
  SELECT
    name,
    first_name,
    last_name,
    COUNT(*) c,
    RANK() OVER(PARTITION BY name ORDER BY count(*) DESC) AS rank
  FROM procedure p 
  JOIN doctor d
    ON p.doctor_id = d.id
  WHERE score >= (SELECT avg(score) 
                  FROM procedure pl 
                  WHERE pl.name = p.name)
  GROUP BY name, first_name, last_name
)

SELECT 
  name,
  first_name,
  last_name
FROM cte
WHERE rank = 1;

Explicación de la solución:

En primer lugar, seleccionamos el nombre del procedimiento y la información sobre los médicos, por lo que unimos la tabla procedure con la tabla doctor en su columna común (doctor_id).

Queremos seleccionar todos los médicos que obtuvieron puntuaciones altas (superiores o iguales a la puntuación media de este procedimiento). Para ello, definimos la condición de la cláusula WHERE para la columna de puntuación. La columna score debe almacenar un valor igual o superior a la puntuación media del procedimiento de la fila actual.

Vamos a clasificar a los médicos por procedimiento. Utilizaremos la función RANK() con la cláusula OVER(), en la que particionaremos el conjunto de datos por el nombre del procedimiento. Además, debemos clasificar en función del número de veces que el médico realizó este procedimiento. Para obtener el número de veces que el médico realizó este procedimiento, debemos COUNT(*) mientras agrupamos por el nombre del procedimiento y el nombre y apellido del médico (es decir, estamos agrupando por todas las columnas enumeradas en la sentencia SELECT ).

Todo lo que hemos hecho hasta ahora es definir una Expresión de Tabla Común (CTE), que es la sentencia interna SELECT encerrada por la cláusula WITH y llamada cte.

Ahora seleccionamos las columnas relevantes de esta CTE. Para obtener los mejores médicos para cada procedimiento (los que tienen un rango de 1), definimos la cláusula WHERE con la condición para la columna rank.

¿Por qué tenemos que definir una EQT y luego consultarla? Porque no podemos utilizar la columna rank en la cláusula WHERE de la consulta interna SELECT. La razón es el orden de ejecución, que es: FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, y LIMIT. La columna rank no ha sido definida cuando la cláusula WHERE es ejecutada.

SQL Funciones de ventana Ejercicios Prácticos: Campeonatos de Atletismo

Conjunto de datos

Los siguientes ejercicios utilizan la base de datos de campeonatos de atletismo que contiene ocho tablas.

• La tabla competition almacena información sobre las competiciones. Las columnas son id, name, start_date, end_date, year, y location.
• La tabla de disciplinas almacena información de todas las disciplinas de carrera (desde las carreras de corta distancia (por ejemplo, los 100 metros) hasta las carreras de larga distancia (por ejemplo, el maratón)). Las columnas son id, name, is_men, y distance.
• La tabla event almacena información sobre la competición y la disciplina de cada prueba. Las columnas son id, competition_id (hace referencia a la competition tabla), y discipline_id (referencia a la discipline tabla).
• La tabla round almacena las rondas de cada prueba. Las columnas son id, event_id (hace referencia a la event tabla), round_name, round_number, y is_final.
• La tabla race tabla almacena los datos de cada carrera de cada ronda. Las columnas son id, round_id (hace referencia a la round tabla), round_name (igual que en la round tabla), race_number, race_date, is_final (igual que en la round tabla), y wind.
• La tabla athlete tabla almacena información sobre los atletas que participan en la competición. Las columnas son id, first_name, last_name, nationality_id (hace referencia a la nationality tabla) y birth_date.
• La tabla nationality almacena información sobre los países de origen de los atletas. Las columnas son id, country_name, y country_abbr.
• La tabla result almacena información sobre todos los participantes en una prueba determinada. Las columnas son race_id (hace referencia a la race tabla), athlete_id (hace referencia a la athlete tabla), result, place, is_dsq, is_dns, y is_dnf.

Ahora que ya estamos familiarizados con el conjunto de datos, pasemos a los ejercicios prácticos de SQL.

Ejercicio 8: Calcular la Diferencia entre Promedios Diarios de Velocidad del Viento

Ejercicio:

Para cada fecha en la que hubo una regata, muestre el race_date, el viento medio en esta fecha redondeado a tres decimales, y la diferencia entre la velocidad media del viento en esta fecha y la velocidad media del viento en la fecha anterior, también redondeada a tres decimales. Las columnas deben llamarse race_date, avg_wind, y avg_wind_delta.

Solución:

SELECT
  race_date,
  ROUND(AVG(wind), 3) AS avg_wind,
  ROUND(AVG(wind) - LAG(AVG(wind)) OVER(ORDER BY race_date), 3) 
     AS avg_wind_delta
FROM race
GROUP BY race_date;

Explicación de la solución:

Vamos a mostrar información de la regata para cada fecha de regata, por lo que seleccionamos los datos de la race tabla.

Para encontrar la velocidad media del viento en esta fecha redondeada a tres decimales, utilizamos la función AVG() con la columna wind como argumento. A continuación, la encerramos dentro de la función ROUND() y la redondeamos a tres decimales. Nótese que debemos agrupar por la columna race_date, ya que utilizamos la función agregada AVG().

Podemos obtener la media del viento en la fecha anterior utilizando la función LAG() con el valor AVG(wind) como argumento. La cláusula OVER() define que ordenemos todo el conjunto de datos por la columna race_date para tener las filas de datos ordenadas cronológicamente.

Como queremos ver la diferencia entre la velocidad media del viento en esta fecha y la velocidad media del viento en la fecha anterior, restamos LAG(AVG(wind)) de AVG(wind). Y para redondearlo a tres decimales, utilizamos de nuevo la función ROUND().

Ejercicio 9: Comparar los mejores resultados con los anteriores

Ejercicio:

Para cada mujer que corrió en la ronda final del maratón femenino en Río, muestre la siguiente información:

• El puesto que consiguieron en la carrera.
• Su nombre.
• Su apellido.
• comparison_to_best - La diferencia entre su tiempo y el mejor tiempo en esta final.
• comparison_to_previous - La diferencia entre su tiempo y el resultado de la atleta que obtuvo el siguiente mejor tiempo.

Ordena las filas por la columna place.

Solución:

SELECT
  place,
  first_name,
  last_name,
  result - FIRST_VALUE(result) OVER (ORDER BY result) 
     AS comparison_to_best,
  result - LAG(result) OVER(ORDER BY result) 
     AS comparison_to_previous
FROM competition
JOIN event
  ON competition.id = event.competition_id
JOIN discipline
  ON discipline.id = event.discipline_id
JOIN round
  ON event.id = round.event_id
JOIN race
  ON round.id = race.round_id
JOIN result
  ON result.race_id = race.id 
JOIN athlete
  ON athlete.id = result.athlete_id
WHERE competition.name = 'Rio de Janeiro Olympic Games'
  AND discipline.name = 'Women''s Marathon'
  AND round.is_final IS TRUE
ORDER BY place;

Explicación de la solución:

Vamos a utilizar información sobre competiciones, disciplinas, rondas, atletas y resultados. Por lo tanto, debemos unir todas estas tablas en sus columnas comunes, como se menciona en la introducción del conjunto de datos.

La instrucción dice que mostremos la información de cada mujer que corrió en la ronda final del maratón femenino de Río. Lo cubrimos en la cláusula WHERE que contiene las siguientes condiciones:

• El nombre de la competición debe ser Rio de Janeiro Olympic Games.
• El nombre de la disciplina debe ser Women's Marathon.
• La ronda debe ser la ronda final.

A continuación seleccionamos la columna place de la tabla result y las columnas first_name y last_name de la tabla athlete de la tabla.

Para hallar la diferencia entre su tiempo y el mejor tiempo en esta final, utilizamos la función FIRST_VALUE() con la columna result como argumento. A ésta le sigue la cláusula OVER(), que ordena el conjunto de datos por la columna result. A continuación, restamos esta función FIRST_VALUE() de la fila actual result. La llamamos comparison_to_best.

Para hallar la diferencia entre su tiempo y el resultado del atleta que obtuvo el siguiente mejor puesto, utilizamos la función LAG() con la columna result como argumento para obtener el resultado anterior. Una vez más, a esto le sigue la cláusula OVER() para ordenar el conjunto de datos por la columna result (para asegurarnos de que obtenemos el siguiente mejor resultado). A continuación, restamos esta función LAG() de la fila actual result. La llamamos comparison_to_previous.

Por último, ordenamos las filas por la columna lugar utilizando la cláusula ORDER BY.

SQL Funciones de ventana Ejercicios Prácticos: Estadísticas de sitios web

Conjunto de datos

Los siguientes ejercicios utilizan la base de datos de estadísticas de sitios web que contiene dos tablas.

• La tabla website almacena información sobre sitios web. Las columnas son id, name, budget, y opened.
• La tabla statistics Las columnas son website_id (hace referencia a la tabla), , , , . website tabla), day, users, impressions, clicks, y revenue.

Ahora que ya estamos familiarizados con el conjunto de datos, pasemos a los ejercicios prácticos de SQL.

Ejercicio 10: Mirar hacia adelante con la función LEAD()

Ejercicio:

Tome las estadísticas del sitio web con id = 2 entre el 1 y el 14 de mayo de 2016 y muestre el día, el número de usuarios y el número de usuarios 7 días después.

Tenga en cuenta que las últimas 7 filas no tienen un valor en la última columna. Esto se debe a que no se pueden encontrar filas '7 días a partir de ahora' para ellos. Para estos casos, muestre -1 en lugar de NULL si no se encuentra ningún valor LEAD().

Solución:

SELECT
  day,
  users,
  LEAD(users, 7, -1) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 2
  AND day BETWEEN '2016-05-01' AND '2016-05-14';

Explicación de la solución:

Vamos a mostrar el día, el número de usuarios y el número de usuarios dentro de 7 días. Los dos primeros valores proceden de la tabla statistics son las columnas día y usuarios. El último valor debe calcularse utilizando la función LEAD().

Queremos ver el valor de la columna usuarios después de siete días; por lo tanto, pasamos la columna users como primer argumento y el valor de 7 como segundo argumento a la función LEAD(). Y para asegurarnos de que mostramos -1 en lugar de NULL si no se encuentra ningún valor LEAD(), pasamos el tercer argumento como -1.

La función LEAD() va seguida de la cláusula OVER(). Esta cláusula contiene la condición para ordenar el conjunto de datos por la columna del día, ya que las estadísticas deben ordenarse cronológicamente.

Para mostrar las estadísticas del sitio web con id = 2 entre el 1 y el 14 de mayo de 2016, debemos definir las condiciones pertinentes en la cláusula WHERE.

Ejercicio 11: Mirar hacia atrás con la función LAG()

Ejercicio:

Muestre las estadísticas del sitio web con id = 3 que incluyen el día, los ingresos y los ingresos de 3 días antes. Muestre -1.00 para las filas sin ingresos 3 días antes.

Solución:

SELECT
  day,
  revenue,
  LAG(revenue, 3, -1.00) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 3;

Explicación de la solución:

Vamos a mostrar el día, los ingresos y los ingresos 3 días antes. Los dos primeros valores provienen de la tabla statistics - son las columnas día e ingresos. El último valor debe calcularse utilizando la función LAG().

Queremos ver el valor de la columna de ingresos de tres días antes de la fila actual; por lo tanto, pasamos la columna de ingresos como primer argumento y el valor de 3 como segundo argumento a la función LAG(). Y para asegurarnos de que mostramos -1.00 para las filas sin valor de ingresos 3 días antes, pasamos el tercer argumento como -1.00.

A la función LAG() le sigue la cláusula OVER(). Ésta contiene la condición para ordenar el conjunto de datos por la columna day, ya que las estadísticas deben ordenarse cronológicamente.

Para mostrar las estadísticas del sitio web con id = 3, necesitamos definir una condición en la cláusula WHERE.

Más SQL Funciones de ventana Práctica

Los ejercicios de práctica de las funciones de ventana de SQL presentados en este artículo proporcionan una plataforma completa para perfeccionar sus conocimientos de SQL y análisis de datos consulta a consulta. Estos ejercicios provienen de nuestros cursos; para encontrar ejercicios de práctica adicionales, visite los cursos enlazados a continuación.

1. Funciones de ventana
2. Funciones de ventana Conjunto de prácticas

Si desea aprender o refrescar sus conocimientos sobre las funciones de ventana, le sugerimos que empiece por el curso Funciones de ventana que ofrece una exploración exhaustiva de este tema. Para quienes deseen perfeccionar sus conocimientos sobre las funciones de ventana, explore nuestro Conjunto de prácticas Funciones de ventana. Contiene 100 ejercicios estructurados en tres partes distintas, cada una de las cuales utiliza un conjunto de datos diferente.

Regístrese ahora y empiece gratis. Mucha suerte.