17th Mar 2023 Lectura de 34 minutos

Las 27 mejores preguntas de entrevista de SQL avanzado con respuestas

Índice

1. ¿Qué es un JOIN en SQL?
2. ¿Cuál es la diferencia entre INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL JOIN?
3. ¿Qué es un CROSS JOIN?
4. ¿Qué es un Self-Join en SQL?
5. Unir dos tablas utilizando un JOIN de dos columnas
6. Unir dos tablas con una unión no equitativa
7. ¿Qué hace DISTINCT?
8. ¿Qué hace GROUP BY en SQL?
9. ¿Cómo Filtrar Grupos GROUP BY?
10. ¿Cuál es la diferencia entre WHERE y HAVING?
11. ¿Qué devolverá la siguiente consulta que intenta filtrar NULLs?
12. Escriba una Consulta que Encuentre el Número de Canciones por Artista. Use LEFT JOIN y COUNT().
13. ¿Cuál es la diferencia entre JOIN y UNION?
14. ¿Cuál es la diferencia entre UNION y UNION ALL?
15. ¿Qué es una Subconsulta en SQL?
16. Escriba una Consulta para Devolver a los Vendedores y sus Datos de Ventas Mensuales por Encima de su Promedio Personal de Ventas. Utilice una Subconsulta Correlacionada.
17. ¿Qué es Funciones de ventana en SQL?
18. ¿Cuál es la diferencia entre Funciones de ventana y GROUP BY?
19. ¿Qué Funciones de ventana conoces?
20. ¿Cómo Crear un Ranking en SQL?
21. ¿Cuál es la diferencia entre RANK() y DENSE_RANK()?
22. Encontrar las N Filas Superiores en SQL Utilizando una Función de Ventana y una CTE.
23. Calcular la Diferencia entre Dos Filas (Delta) Utilizando Funciones de ventana
24. Utilice Funciones de ventana para calcular un total acumulado
25. Encontrar una media móvil utilizando Funciones de ventana
26. ¿Cuál es la diferencia entre ROWS y RANGE?
27. Utilice una Consulta Recursiva para Encontrar a Todos los Empleados Bajo un Gerente Dado.
¿Estás preparado para superar las preguntas de la entrevista de trabajo de SQL?

¿Dónde puede encontrar un profesional de SQL una guía completa de preguntas de entrevista de SQL avanzado? La respuesta más breve es: ¡aquí! Hemos seleccionado las 27 preguntas SQL más importantes y las hemos respondido para usted.

Prepararse para una entrevista SQL no es fácil, especialmente si tu trabajo requiere el conocimiento de SQL avanzado. Este artículo contiene las 27 preguntas avanzadas de SQL más frecuentes en las entrevistas y ofrece respuestas detalladas y recursos para seguir leyendo.

Repasaremos estos cuatro conceptos principales y algunos más:

JOINs
GROUP BY, WHERE, y HAVING
CTEs (Common Table Expressions) y consultas recursivas
Funciones de ventana

La mejor manera de refrescar sus conocimientos avanzados de SQL es seguir nuestro curso interactivo. SQL avanzado interactivo. Contiene más de 300 ejercicios prácticos sobre funciones de ventana, expresiones comunes de tabla, funciones recursivas y mucho más.

Ataquemos estas preguntas de frente, ¡sin más preámbulos!

1. ¿Qué es un JOIN en SQL?

JOIN es un comando SQL que le permite combinar dos o más tablas. Esto se hace a través de una columna común (es decir, una columna que tiene los mismos valores en ambas tablas), lo que permite utilizar datos de dos o más tablas al mismo tiempo. La unión de tablas en SQL es esencial debido a la naturaleza de las bases de datos relacionales: los datos se atomizan en tablas, y cada tabla contiene sólo una parte de los datos disponibles en la base de datos.

Utilizaremos dos tablas para mostrar cómo funciona esto. La primera tabla es football_players.

id	first_name	last_name	national_team_id	games_played
1	Gianfranco	Zola	1	35
2	Virgil	van Dijk	2	53
3	Marcus	Rashford	3	51
4	Kylian	Mbappé	5	66
5	Phil	Foden	3	22
6	Frenkie	de Jong	2	22
7	Mario	Balotelli	1	36
8	Erling	Haaland	6	23

La segunda es national_team.

id	country
1	Italy
2	Netherlands
3	England
4	Croatia

Esta es una consulta que une dos tablas:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Selecciona columnas de ambas tablas. Para unirlas, primero hacemos referencia a una tabla en la cláusula FROM. A continuación aparece JOIN, y después la segunda tabla. Utilizamos la cláusula ON para especificar la condición con la que se unirán las tablas: el national_team_id de la tabla football_players debe ser igual a la columna id de la tabla national_team tabla.

El resultado de la consulta es

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

INNER JOIN es una de las diversas uniones distintas de SQL. Su característica es que sólo devuelve datos de las tablas unidas cuando la condición de unión es verdadera. Aquí hay más detalles sobre cómo funciona el INNER JOIN en SQL.

2. ¿Cuál es la diferencia entre INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL JOIN?

Existen diferentes tipos de uniones en SQL. Las uniones más utilizadas son INNER JOIN, LEFT JOIN, RIGHT JOIN, y FULL JOIN. LEFT JOIN, RIGHT JOIN, y FULL JOIN son las llamadas uniones externas. JOIN (también conocida como INNER JOIN) es una unión interna. En este caso, "interna" significa que sólo devuelve las filas de ambas tablas que cumplen la condición de unión; las uniones externas devuelven todas las filas de una tabla, más las filas coincidentes de la(s) otra(s) tabla(s). La excepción es FULL JOIN, que devuelve todas las filas de ambas tablas.

Este es el resultado de INNER JOIN del ejemplo anterior. Volvamos a tenerlo aquí. Así será más fácil ver la diferencia entre las distintas uniones.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

LEFT JOIN devuelve todos los datos de la tabla izquierda (es decir, la primera tabla, que aparece antes\a la izquierda de la palabra clave JOIN ) y sólo las filas coincidentes de la tabla derecha (la segunda tabla, que aparece después\a la derecha de la palabra clave JOIN ). Si no hay datos coincidentes en la tabla derecha, los valores que faltan se muestran como NULLs. Aquí se muestra la misma consulta con LEFT JOIN sustituyendo a INNER JOIN:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
LEFT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

La tabla de la izquierda es football_playersy la derecha es national_team. Como era de esperar, el resultado es diferente:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23

Todos los futbolistas de la tabla de la izquierda están aquí. Sin embargo, Kylian Mbappe y Erling Haaland no tienen un país que coincida en la tabla derecha, por lo que hay NULLs en la columna country para esos jugadores. Estas filas no estaban presentes en el resultado de INNER JOIN. Fueron añadidas por LEFT JOIN.

RIGHT JOIN hace lo contrario: devuelve todos los datos de la tabla derecha y sólo los datos coincidentes de la tabla izquierda. Cuando no hay datos coincidentes en la tabla izquierda, los valores que faltan se muestran como NULLs.

Este es el código:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
RIGHT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Todo sigue igual, excepto que usamos RIGHT JOIN en lugar de LEFT JOIN. Este es el resultado:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
NULL	NULL	NULL	NULL	Croatia	NULL

Ahora tenemos todas las selecciones nacionales y sus jugadores. Pero puedes ver que hay un país (Croacia) que no tiene jugadores en la tabla de la izquierda. Las columnas de jugadores de Croacia se rellenan con NULLs.

FULL JOIN saca todos los datos de todas las tablas unidas. De nuevo, si no hay datos coincidentes en la tabla correspondiente, los valores que falten aparecerán como NULL.

Una vez más, cambiamos el tipo de unión en la consulta:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
FULL JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Devolverá todos los datos de ambas tablas. Cualquier dato no coincidente se sustituye por NULLs. Todos los jugadores están en el resultado, aunque no tengan un país correspondiente en la otra tabla. Todos los países están en el resultado, aunque no tengan jugadores en la tabla football_player tabla. El resultado FULL JOIN es la unión de LEFT JOIN y RIGHT JOIN:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23
NULL	NULL	NULL	NULL	Croatia	NULL

Puedes encontrar más información en el artículo sobre los diferentes tipos de JOIN. También puede ver nuestra Tipos de JOIN en SQL Cheat Sheet para un rápido repaso.

3. ¿Qué es un CROSS JOIN?

Un CROSS JOIN es otro tipo de unión disponible en SQL. Devuelve un producto Cartesiano. Esto significa que CROSS JOIN devolverá cada fila de la primera tabla combinada con cada fila de la segunda tabla.

No se utiliza muy a menudo. Pero si está tentado de utilizarlo, piénselo dos veces. Devolver todas las combinaciones de filas puede llevar algún tiempo, ¡si es que la consulta termina!

Como ejemplo, utilicemos las tablas de las dos preguntas anteriores. Para escribir la consulta, utilice la palabra clave CROSS JOIN. Como se trata de un tipo join que devuelve todas las combinaciones de filas de todas las tablas, no hay cláusula ON. Eche un vistazo:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
CROSS JOIN national_team nt;

Este es el resultado. Todos los jugadores de la tabla football_players con todos los países de la tabla national_team tabla.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Italy	53
3	Marcus	Rashford	3	Italy	51
4	Kylian	Mbappé	5	Italy	66
5	Phil	Foden	3	Italy	22
6	Frenkie	de Jong	2	Italy	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	Italy	23
1	Gianfranco	Zola	1	Netherlands	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	Netherlands	51
4	Kylian	Mbappé	5	Netherlands	66
5	Phil	Foden	3	Netherlands	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Netherlands	36
8	Erling	Haaland	6	Netherlands	23
1	Gianfranco	Zola	1	England	35
2	Virgil	van Dijk	2	England	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	England	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	England	22
7	Mario	Balotelli	1	England	36
8	Erling	Haaland	6	England	23
1	Gianfranco	Zola	1	Croatia	35
2	Virgil	van Dijk	2	Croatia	53
3	Marcus	Rashford	3	Croatia	51
4	Kylian	Mbappé	5	Croatia	66
5	Phil	Foden	3	Croatia	22
6	Frenkie	de Jong	2	Croatia	22
7	Mario	Balotelli	1	Croatia	36
8	Erling	Haaland	6	Croatia	23

Puede aprender más sobre CROSS JOIN en esta guía ilustrada sobre el CROSS JOIN en SQL.

4. ¿Qué es un Self-Join en SQL?

Como probablemente sospeche, una auto-unión ocurre cuando la tabla se une consigo misma. Es importante notar que no es un comando distinto en SQL: cualquier tipo de JOIN puede ser usado para unir una tabla consigo misma.

La unión se realiza como cualquier otra JOIN, pero esta vez hará referencia a la misma tabla a ambos lados de la palabra clave JOIN. La autounión es especialmente útil cuando una tabla tiene una clave externa que hace referencia a su clave principal. Esto permite consultar datos jerárquicos, como árboles genealógicos o la jerarquía organizativa de una empresa. También es útil para buscar pares de valores. En el ejemplo siguiente, buscamos jugadores de la misma selección nacional:

SELECT 
  fp1.id,
  fp1.first_name,
  fp1.last_name,
  fp1.national_team_id,
  fp2.id AS id_2,
  fp2.first_name AS first_name_2,
  fp2.last_name AS last_name_2,
  fp2.national_team_id as national_team_id_2
FROM football_players fp1 
JOIN football_players fp2
ON fp1.id <> fp2.id
AND fp1.national_team_id = fp2.national_team_id;

Autounión significa que, en lugar de dos tablas, está especificando la misma tabla dos veces: una en la cláusula FROM y otra después de la cláusula JOIN. Dado que está utilizando la misma tabla dos veces, debe utilizar alias para las tablas. Cada aparición de la tabla debe tener un alias distinto (fp1, fp2 en nuestra consulta) para que quede claro a qué aparición de la tabla nos estamos refiriendo.

Estamos uniendo jugadores de la misma selección nacional (sus valores national_team_id son iguales). Sin embargo, no queremos listar a un jugador consigo mismo, así que excluimos el caso en que fp1.id y fp2.id sean iguales.

El resultado de la consulta es el siguiente:

id	first_name	last_name	national_team_id	id_2	first_name_2	last_name_2	national_team_id_2
1	Gianfranco	Zola	1	7	Mario	Balotelli	1
2	Virgil	van Dijk	2	6	Frenkie	de Jong	2
3	Marcus	Rashford	3	5	Phil	Foden	3
5	Phil	Foden	3	3	Marcus	Rashford	3
6	Frenkie	de Jong	2	2	Virgil	van Dijk	2
7	Mario	Balotelli	1	1	Gianfranco	Zola	1

Puedes utilizar las columnas national_team_id de ambas tablas para confirmar que, realmente, Gianfranco Zola y Mario Balotelli jugaron en el mismo equipo.

Puede obtener más información en nuestro artículo sobre ejemplos de autounión.

5. Unir dos tablas utilizando un JOIN de dos columnas

Se le dan dos tablas. La primera es employeeque tiene los siguientes datos:

id	first_name	last_name
1	Steve	Bergman
2	Steve	Johnson
3	Steve	King

La segunda tabla es customerque tiene los siguientes datos:

id	first_name	last_name
1	Ann	Coleman
2	Steve	Bergman
3	Steve	Young
4	Donna	Winter
5	Steve	King

Tu tarea es devolver todos los empleados que también son clientes de la empresa. Desafortunadamente, no puedes utilizar la columna id porque es el ID del empleado en una tabla y el ID del cliente en otra. En otras palabras, no hay ninguna columna en una tabla que haga referencia a la otra.

La solución es unir las tablas por el nombre y el apellido, es decir, utilizar una columna de dos columnas JOIN.

El código siguiente unirá primero las tablas por el nombre. Después, la palabra clave AND establece la segunda condición de unión, que es el apellido. De esta forma, obtendrá datos de ambas tablas en las que la combinación nombre/apellido sea la misma. Si utilizáramos sólo una de estas columnas, podríamos haber obtenido datos erróneos, ya que los empleados y los clientes pueden tener el mismo nombre pero apellidos diferentes (o viceversa). Esta es la consulta:

SELECT 
  e.first_name, 
  e.last_name
FROM employee e
JOIN customer c
ON e.first_name = c.first_name
AND e.last_name = c.last_name;

Aquí está la salida de código.:

first_name	last_name
Steve	Bergman
Steve	King

El resultado muestra que Steve Bergman y Steve King son empleados y clientes de la empresa.

6. Unir dos tablas con una unión no equitativa

Hasta ahora, hemos estado utilizando uniones equitativas: uniones en las que hay un signo de igualdad en la condición ON. Por el contrario, la unión no-equi es una unión que tiene una condición de no-igualdad en la cláusula ON.

En esta ocasión, tenemos datos sobre usuarios de móviles y su consumo de datos. La primera tabla es mobile_userque muestra los usuarios de móviles y su límite mensual de datos móviles en MB:

id	first_name	last_name	mobile_data_limit
1	Michael	Watson	5,000
2	Nicole	Gomez	10,000
3	Sam	Stone	8,000

La segunda tabla es data_usageque muestra el uso de datos mensual real del usuario en MB:

id	mobile_user_id	data_used	period
1	1	4,987	2022_10
2	2	6,875	2022_10
3	3	12,547	2022_10
4	1	5,037	2022_11
5	2	11,111	2022_11
6	3	4,897	2022_11

La tarea consiste en encontrar todos los datos en los que el uso real fue superior al límite mensual. Queremos ver el nombre y apellidos del usuario, el límite mensual, los datos reales utilizados y el periodo de tiempo.

La solución es utilizar la unión no equitativa, como se muestra a continuación:

SELECT 
  first_name,
  last_name,
  mobile_data_limit,
  data_used,
  period
FROM mobile_user mu
JOIN data_usage du
ON mu.id = du.mobile_user_id
AND mobile_data_limit < data_used;

La consulta selecciona toda la información necesaria de dos tablas. Las tablas se unen mediante INNER JOIN. Primero las unimos cuando el ID de usuario es el mismo. A continuación, añadimos la segunda condición después de la palabra clave AND. Aquí tenemos una condición de no-igualdad que nos dará datos donde el límite está por debajo del uso mensual.

Puedes ver el resultado más abajo:

first_name	last_name	mobile_data_limit	data_used	period
Sam	Stone	8,000	12,547	2022_10
Michael	Watson	5,000	5,037	2022_11
Nicole	Gomez	10,000	11,111	2022_11

Si le interesa, aquí tiene más ejemplos de uniones no equitativas.

7. ¿Qué hace DISTINCT?

DISTINCTEl propósito de DISTINCT, en general, es eliminar valores duplicados. O, dicho de otro modo, mostrar valores únicos en el resultado de la consulta.

Imagine que trabaja con esta tabla loans que muestra los ID de los préstamos y su duración en meses.

loan_id	loan_duration
100112	60
100205	60
100208	48
100333	120
100357	48
100398	120

Diferentes préstamos pueden tener la misma duración, por lo que desea extraer la lista de posibles duraciones de préstamos. Para ello, utilice DISTINCT:

SELECT DISTINCT loan_duration
FROM loans
ORDER BY loan_duration;

La salida muestra que hay préstamos con duraciones de 48, 60 y 120 meses:

loan_duration
48
60
120

DISTINCT puede utilizarse en SELECT con una columna para mostrar sólo los valores únicos de esa columna, como en el ejemplo anterior. Si se utiliza en SELECT pero con varias columnas, la salida mostrará las combinaciones únicas de todas esas columnas.

También puede utilizar DISTINCT con funciones agregadas. Si lo hace, su consulta eliminará los resultados de agregación duplicados.

Puedes ver ejemplos de estos usos en nuestro artículo sobre el papel de DISTINCT en SQL.

8. ¿Qué hace GROUP BY en SQL?

GROUP BY GROUP BY es una cláusula SQL utilizada para organizar datos en grupos basados en un valor o valores comunes. Se utiliza más comúnmente con funciones agregadas; esta combinación devolverá datos agregados para cada grupo. Sin embargo, es importante saber que el uso de funciones de agregación dentro de la cláusula GROUP BY no está permitido.

La sintaxis general de GROUP BY es la siguiente:

SELECT 
  column_1,
  column_2,
  …,
FROM table_name
WHERE …
GROUP BY column_1, column_2
HAVING …
ORDER BY column_1, column_2;

Supongamos que existe la tabla salaries:

id	first_name	last_name	salary	department
1	Nicholas	Poirot	4,798.44	IT
2	Samantha	Wolf	5,419.24	IT
3	Stewart	Johnsons	5,419.24	IT
4	Jackie	Biden	8,474.54	Sales
5	Mark	Hamilton	10,574.84	Sales
6	Mariana	Costa	9,747.54	Sales
7	Paul	Stewart	3,498.12	Accounting
8	Mary	Rutte	4,187.23	Accounting
9	Thomas	Schwarz	3,748.55	Accounting

Usaremos GROUP BY y AVG() para encontrar el salario medio por departamento:

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department;

Queremos ver los departamentos, así que seleccionamos esta columna. Para calcular el salario medio, aplicamos la función AVG() a la columna salary.

Todas las columnas listadas en GROUP BY definen los grupos de datos. En nuestro ejemplo, los grupos están definidos por la columna departamento: calculamos el salario medio de cada departamento.

Nuestra agrupación y agregación de datos tiene el siguiente aspecto:

department	average_salary
Accounting	3,811.30
Sales	9,598.97
IT	5,212.31

El salario medio en Contabilidad es de 3.811,30. Los salarios medios de los otros dos departamentos son 9.598,97 y 5.212,31, respectivamente.

Al escribir una consulta, GROUP BY siempre tiene que ir después de WHERE pero antes de la cláusula HAVING. Puedes aprender más sobre esto en este artículo sobre GROUP BY en SQL.

9. ¿Cómo Filtrar Grupos GROUP BY?

Una vez que obtenga los grupos que especificó en GROUP BY, a veces querrá filtrarlos. La clave para hacerlo se encuentra en la sintaxis de la pregunta anterior. La cláusula que le permite filtrar grupos es HAVING.

Una vez especificados los criterios de filtrado en HAVING, la consulta devolverá todos los datos que cumplan los criterios. Todos los demás datos se filtrarán.

A continuación se muestra cómo funciona con los datos de la pregunta anterior si tuviéramos que mostrar sólo los departamentos con un salario medio inferior a 5.500 dólares.

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department
HAVING AVG(salary) < 5500;

El código es muy similar al de la pregunta anterior. La diferencia es la cláusula HAVING. La utilizamos para filtrar los resultados y mostrar sólo los departamentos con salarios inferiores a 5.500.

Esto es lo que devuelve el código:

department	average_salary
Accounting	3,811.30
IT	5,212.31

El departamento que falta en la salida es Ventas porque su salario medio es de 9.598,97.

Puedes encontrar más ejemplos prácticos de esta cláusula en este artículo sobre HAVING en SQL.

10. ¿Cuál es la diferencia entre WHERE y HAVING?

Si conoces las respuestas a las dos preguntas anteriores, probablemente conozcas la respuesta a esta pregunta.

La principal diferencia es que WHERE se utiliza para filtrar datos antes de agruparlos. Su posición en la sentencia SELECT lo demuestra: viene antes que GROUP BY. Debido a su propósito, no se permiten funciones agregadas en WHERE.

HAVING, por el contrario, se utiliza para filtrar datos después de agruparlos; por eso se utiliza después de GROUP BY. Además, HAVING permite condiciones que incluyen funciones agregadas.

La mejor manera de aprender la distinción es leer este artículo sobre WHERE vs. HAVING en SQL.

11. ¿Qué devolverá la siguiente consulta que intenta filtrar NULLs?

A menudo te harán este tipo de pregunta en tu entrevista de SQL avanzado: te darán un código y tendrás que describir el resultado de la consulta. Mientras que escribir y leer código SQL van de la mano, todavía se siente diferente cuando tienes que analizar el código que alguien más escribió.

Tienes datos en la tabla contributors:

id	first_name	last_name	start_date	termination_date
1	Valeria	Bogdanov	2022-10-11	NULL
2	Nicholas	Bertolucci	2022-04-07	2022-11-11
3	Mathilde	Bauman	2022-05-25	2022-10-01
4	Trevor	Trucks	2022-01-28	NULL
5	Maria	Szabo	2022-03-15	NULL

¿Qué devolverá este código?

SELECT 
  first_name,
  last_name,
  start_date,
  termination_date
FROM contributors
WHERE termination_date != '2022-10-01';

Si respondes que devolverá todas las filas excepto ID = 3, ¡te equivocas! Se trata de una pregunta trampa. Al leer la condición WHERE, podrías leerla como: devolver todos los datos en los que la fecha de finalización sea diferente de 2022-10-01. Mirando la tabla, pensarías que son todas las filas menos una.

Lo es, ¡pero no para SQL! Como puede ver, hay tres filas con valores NULL. Para SQL, NULL no es igual a un valor; es un no-valor. Así que cuando configure la condición en WHERE de esa manera, estará excluyendo todas las fechas que no sean iguales a los valores 2022-10-01 y NULL.

Aquí está la salida como prueba:

first_name	last_name	start_date	termination_date
Nicholas	Bertolucci	2022-04-07	2022-11-11

Puede aprender más sobre este y otros operadores de comparación utilizados con NULL.

12. Escriba una Consulta que Encuentre el Número de Canciones por Artista. Use LEFT JOIN y COUNT().

Suponga que le dan dos tablas artist y song.

Estos son los artist datos:

id	artist_name
1	Prince
2	Jimi Hendrix
3	Santana
4	Otis Redding
5	Lou Rawls

A continuación están los song datos:

id	artist_id	song_title
1	1	Purple Rain
2	2	Purple Haze
3	3	Europa
4	1	Cream
5	1	Bambi
6	1	Why You Wanna Treat Me So Bad?
7	2	Spanish Castle Magic
8	3	Taboo
9	3	Incident at Neshabur
10	3	Flor D' Luna

Necesitas usar LEFT JOIN y COUNT() para encontrar todos los artistas, sus IDs, y el número de sus canciones en la base de datos.

Podrías tener la tentación de sugerir esta solución:

SELECT 
  a.id,
  artist_name,
  COUNT(*) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Veamos el resultado:

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	1
5	Lou Rawls	1

La salida muestra todos los artistas; eso está bien. Sin embargo, el número de canciones de Otis Redding y Lou Rawls es uno, ¡lo cual es incorrecto! Echa un vistazo a la tabla songy verás que no hay ningún ID de artista igual a 4 o 5.

¿Qué ha fallado? Al utilizar COUNT(*) con LEFT JOIN, la función de agregado contará todos los valores no coincidentes (NULLs). Por eso el resultado mostraba una canción para Otis Redding y Lou Rawls, aunque no tuvieran canciones en la tabla.

COUNT(*) se utiliza para contar todas las filas. Para dar una respuesta correcta, deberías usar COUNT(song_title) en su lugar.

SELECT 
  a.id,
  artist_name,
  COUNT(song_title) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Este código le dará la salida correcta:

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	0
5	Lou Rawls	0

El número de canciones de Prince, Jimi Hendrix y Santana permanece igual que en la salida anterior. Sin embargo, el número de canciones de los otros dos artistas es ahora cero, y esa es la cuenta correcta.

13. ¿Cuál es la diferencia entre JOIN y UNION?

JOIN es una cláusula SQL que se utiliza para unir dos o más tablas. Permite utilizar datos de todas las tablas unidas. En otras palabras, las columnas de todas las tablas se muestran unas junto a otras, lo que significa que los datos se apilan horizontalmente.

UNION es un operador de conjunto que se utiliza para combinar los resultados de dos o más sentencias SELECT. Los datos se apilan verticalmente. Uno de los requisitos al utilizar UNION es que tiene que haber el mismo número de columnas en todas las sentencias SELECT unidas. Además, todas las columnas seleccionadas deben ser del mismo tipo de datos.

14. ¿Cuál es la diferencia entre UNION y UNION ALL?

Lo que tienen en común es que ambos son operadores de conjunto. Además, ambos operadores se utilizan con el mismo fin: fusionar datos de dos o más sentencias SELECT.

Los requisitos relativos al número de columnas y su tipo de datos también son los mismos.

Ahora bien, la diferencia es que UNION sólo devuelve registros únicos. En cambio, UNION ALL devuelve todos los registros, incluidos los duplicados.

Normalmente, UNION ALL es más rápido porque no ordena el resultado para eliminar los duplicados. La regla general es utilizar UNION ALL por defecto. Utilice UNION sólo si necesita resultados únicos o si está absolutamente seguro de que su consulta no devolverá datos duplicados.

Puedes aprender más sobre su sintaxis y uso en este artículo sobre las diferencias entre UNION y UNION ALL.

15. ¿Qué es una Subconsulta en SQL?

Una subconsulta es una consulta escrita dentro de otra consulta SQL. La 'otra' consulta se llama consulta principal, mientras que una subconsulta a veces también se llama consulta anidada.

Las subconsultas pueden utilizarse en las sentencias SELECT, INSERT, UPDATE, y DELETE. También pueden utilizarse en cláusulas como FROM o WHERE, que es el uso más común.

He aquí un ejemplo. La tabla es productsy almacena información sobre nombres de productos, cantidades y categorías:

id	product_name	quantity	product_category
1	Apple MacBook Air (2020) MGN63N/A Space Gray	319	Laptop
2	Fairphone 4 128GB Green 5G	208	Mobile phone
3	Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver	157	Desktop
4	HP 17-cp0971nd	487	Laptop
5	Huawei P30 Pro - 128GB - Blue	148	Mobile phone
6	Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC	514	Desktop
7	Toshiba Dynabook Satellite Pro E10-S-101 Notebook	207	Laptop
8	Samsung Galaxy S23 5G - 256GB - Phantom Black	56	Mobile phone
9	Intel Compleet PC \| Intel Core i7-10700	459	Desktop

Utilizaremos una subconsulta y mostraremos la cantidad total por categoría de producto, pero sólo para los productos individuales cuya cantidad sea superior a la cantidad media de todos los productos.

Esta es la solución:

SELECT 
  product_category,
  SUM(quantity) AS product_quantity
FROM products
WHERE quantity > (SELECT AVG(quantity)
			FROM products)
GROUP BY product_category;

La consulta selecciona la categoría de producto y suma la cantidad utilizando la función de agregado SUM(). Hay una condición en WHERE que dice que sólo los productos individuales con una cantidad superior a la media se incluirán en la suma. Utilizamos la subconsulta y la función AVG() para obtener esta media.

La consulta devuelve dos filas:

product_category	product_quantity
Laptop	806
Desktop	973

Falta una categoría porque no cumple los criterios de filtrado: teléfonos móviles.

Existen diferentes tipos de subconsultas, como las escalares, las de varias filas y las correlacionadas. Puede aprender más sobre ellos en nuestro artículo sobre tipos de subconsultas.

16. Escriba una Consulta para Devolver a los Vendedores y sus Datos de Ventas Mensuales por Encima de su Promedio Personal de Ventas. Utilice una Subconsulta Correlacionada.

Una subconsulta correlacionada es un tipo de subconsulta que utiliza valores de la consulta externa. Se comprueba una vez por cada fila que devuelve la consulta externa, lo que puede ralentizar el rendimiento.

Sin embargo, la pregunta insiste en utilizarla, así que veamos los datos.

La primera tabla es salesperson:

id	first_name	last_name
1	Nina	Lee
2	Carolina	Green
3	Mick	Johnson

La otra tabla es sales:

id	salesperson_id	monthly_sales	period
1	1	1,200.47	2021_10
2	2	5,487.22	2021_10
3	3	700.47	2021_10
4	1	15,747.54	2021_11
5	2	16,700.87	2021_11
5	3	14,322.87	2021_11
6	1	9,745.55	2021_12
7	2	9,600.97	2021_12
8	3	6,749.58	2021_12

Su tarea consiste en utilizar una subconsulta correlacionada y devolver el nombre completo del vendedor, sus ventas mensuales y los periodos en los que sus ventas superan su media personal.

He aquí la solución:

SELECT 
  first_name,
  last_name,
  monthly_sales,
  period
FROM salesperson sp
JOIN sales s
ON sp.id = s.salesperson_id
WHERE monthly_sales > 
   (SELECT AVG(monthly_sales)
    FROM sales
    WHERE salesperson_id = sp.id);

La consulta selecciona todas las columnas necesarias. Estos datos proceden de ambas tablas, así que las hemos unido.

Ahora viene la parte crucial. Para filtrar los datos, utilizamos la cláusula WHERE. La condición dice que la consulta debe devolver todos los datos en los que las ventas mensuales sean superiores a las ventas medias de cada vendedor. ¿Cómo calculamos estas ventas medias individuales? Utilizando la función AVG() en la subconsulta que escribimos en la cláusula WHERE.

Este es el resultado:

first_name	last_name	monthly_sales	period
Nina	Lee	15,747.54	2021_11
Carolina	Green	16,700.87	2021_11
Mick	Johnson	14,322.87	2021_11
Nina	Lee	9,745.55	2021_12

Hay más ejemplos en este artículo sobre subconsultas correlacionadas.

17. ¿Qué es Funciones de ventana en SQL?

Las funciones de ventana de SQL reciben su nombre del hecho de que se aplican a una ventana de datos. Esta ventana es simplemente un conjunto de filas relacionadas con la fila actual.

Las funciones de ventana son iniciadas por la cláusula OVER(). Otra cláusula importante es PARTITION BY, que define particiones de datos dentro de un marco de ventana. Cuando se omite esta cláusula, la partición es toda la tabla de resultados. Cuando se utiliza PARTITION BY, se pueden definir una o más columnas por las que se particionarán los datos. Se puede considerar como GROUP BY para las funciones de ventana.

Otra cláusula importante es ORDER BY. Ordena los datos dentro de la ventana. En el contexto de las funciones de ventana, esta cláusula da instrucciones sobre el orden en que se ejecutará la función.

Para saber más, consulta este artículo sobre funciones de ventana.

18. ¿Cuál es la diferencia entre Funciones de ventana y GROUP BY?

La única similitud que comparten es que tanto GROUP BY como las funciones ventana pueden utilizarse -y muy a menudo se utilizan- con las funciones de agregado, y ambas trabajan sobre un conjunto de filas.

Sin embargo, cuando se utiliza GROUP BY, la salida se muestra como grupos, y no se pueden ver las filas individuales que forman el grupo.

Las funciones de ventana no tienen estos problemas. Una de sus características es que no contraen las filas individuales cuando muestran datos agregados. Esto significa que es posible mostrar datos agregados y no agregados simultáneamente.

Las funciones de ventana se utilizan para algo más que para agregar datos, como verás en la siguiente pregunta. Pero si quieres saber más sobre el tema actual, tenemos un artículo que explica las funciones ventana frente a GROUP BY.

19. ¿Qué Funciones de ventana conoces?

Las funciones ventana de SQL se pueden dividir generalmente en cuatro categorías:

Funciones de Clasificación
Funciones de Distribución
Funciones Analíticas
Funciones Agregadas

Las funciones de clasificación son:

ROW_NUMBER() - Devuelve un número único para cada fila dentro de una partición; los valores empatados tienen números de fila diferentes.
RANK() - Ordena los datos dentro de una partición; los valores empatados tienen el mismo rango y hay un espacio después de los empates (por ejemplo, 1, 2, 3, 3, 5).
DENSE_RANK() - Clasifica los datos dentro de una partición; los valores empatados tienen el mismo rango y no hay diferencia en la clasificación (por ejemplo, 1, 2, 3, 3, 4).

Las funciones de distribución son:

PERCENT_RANK() - Devuelve el rango relativo dentro de una partición.
CUME_DIST() - Devuelve la distribución acumulativa dentro de una partición.

Las funciones analíticas son:

LEAD() - Permite acceder a valores de una fila posterior en relación con la fila actual.
LAG() - Permite acceder a valores de una fila anterior en relación con la fila actual.
NTILE() - Divide las filas de una partición en grupos aproximadamente iguales.
FIRST_VALUE() - Permite acceder a los valores de la primera fila de una partición.
LAST_VALUE() - Permite acceder a los valores de la última fila de una partición.
NTH_VALUE() - Permite acceder a la n-ésima fila de una partición.

Por último, las funciones de agregación son:

AVG() - Devuelve un valor medio de las filas de una partición.
COUNT() - Devuelve el número de valores de las filas de una partición.
MAX() - Devuelve el valor máximo de las filas de una partición.
MIN() - Devuelve el valor mínimo de las filas de una partición.
SUM() - Devuelve el valor de la suma de las filas de una partición.

Nuestra Funciones de ventana Cheat Sheet le proporcionará más información sobre todas estas funciones.

20. ¿Cómo Crear un Ranking en SQL?

La forma más sencilla de clasificar datos en SQL es utilizar una de las tres funciones de ventana de clasificación:

ROW_NUMBER()
RANK()
DENSE_RANK()

Se le da un conjunto de datos llamado album_sales con los siguientes datos:

id	artist	album	copies_sold
1	Eagles	Hotel California	42,000,000
2	Led Zeppelin	Led Zeppelin IV	37,000,000
3	Shania Twain	Come On Over	40,000,000
4	Fleetwood Mac	Rumours	40,000,000
5	AC/DC	Back in Black	50,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Michael Jackson	Thriller	70,000,000
8	Pink Floyd	The Dark Side of the Moon	45,000,000
9	Whitney Houston	The Bodyguard	45,000,000
10	Eagles	Their Greatest Hits (1971-1975)	44,000,000

Estas son las ventas de los diez álbumes más vendidos de la historia. Como puede ver, los álbumes no están clasificados. Eso es lo que haremos aquí: ordenarlos del más vendido al menos vendido utilizando funciones de ventana.

Si utiliza ROW_NUMBER(), la consulta tendrá este aspecto:

SELECT 
  ROW_NUMBER() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

La sintaxis es sencilla. Primero se elige la función ventana. A continuación, utilice la cláusula obligatoria OVER() que indica que se trata de una función ventana. En ORDER BY, se ordenan los datos de forma descendente. Esto significa ahora que los números de fila se asignarán según las copias vendidas de mayor a menor.

Por supuesto, enumere todas las demás columnas que necesite y haga referencia a la tabla para obtener el mismo resultado:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
4	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
8	Fleetwood Mac	Rumours	40,000,000
9	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,000,000

Como puedes ver, los álbumes están clasificados del uno al diez. Dos álbumes vendieron 45 millones de copias. Sin embargo, están clasificados de forma diferente (tercero y cuarto) según criterios aleatorios. Lo mismo ocurre con tres álbumes que vendieron 40 millones de copias.

Si utiliza RANK(), la sintaxis es la misma, salvo que utiliza una función de ventana diferente:

SELECT 
  RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

Sin embargo, la salida es diferente:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
7	Fleetwood Mac	Rumours	40,000,000
7	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,000,000

Puede ver que los álbumes empatados se clasifican en tercer lugar (dos veces). El siguiente álbum no empatado ocupa el quinto lugar. Lo mismo ocurre con los álbumes que ocupan el séptimo lugar.

Veamos qué ocurre si utilizamos DENSE_RANK():

SELECT 
  DENSE_RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

Éste es el resultado:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
4	Eagles	Their Greatest Hits (1971-1975)	44,000,000
5	Eagles	Hotel California	42,000,000
6	Shania Twain	Come On Over	40,000,000
6	Fleetwood Mac	Rumours	40,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Led Zeppelin	Led Zeppelin IV	37,000,000

Los primeros álbumes empatados se clasifican como terceros, que es lo mismo que en el resultado anterior. Pero la diferencia es que el siguiente álbum no empatado es el cuarto, lo que significa que no se salta la clasificación.

Los otros tres álbumes empatados ocupan ahora el sexto lugar, no el séptimo como antes. Además, el puesto más alto es el séptimo, no el décimo.

Como puede ver, cada método da resultados diferentes. Debes utilizar el que mejor se adapte a tus datos y a lo que quieras conseguir con la clasificación. Para saber más, lee el artículo sobre clasificación de filas en SQL.

21. ¿Cuál es la diferencia entre RANK() y DENSE_RANK()?

Ya hemos tocado la diferencia en la pregunta anterior. Allí la viste en un ejemplo práctico, y ahora vamos a formularla para responder a esta pregunta.

RANK() asigna el mismo rango a las filas con los mismos valores. Cuando llega a la siguiente fila no empatada, se salta el rango por el número de rangos empatados.

DENSE_RANK() también asigna el mismo rango a los valores empatados. Sin embargo, no se salta el rango cuando llega a la siguiente fila no vinculada. En otras palabras, DENSE_RANK() ordena los datos secuencialmente.

En este artículo se explican con más detalle las diferencias entre RANK() y DENSE_RANK().

22. Encontrar las N Filas Superiores en SQL Utilizando una Función de Ventana y una CTE.

Esta es una pregunta común y puede ser resuelta de varias maneras. Utilizaremos la función ventana en una CTE para devolver el resultado deseado.

Los datos disponibles se almacenan en la tabla salary tabla:

id	first_name	last_name	salary	department
1	Tim	Thompson	10,524.74	Sales
2	Martina	Hrabal	7,895.14	Accounting
3	Susan	Truman	15,478.69	Sales
4	Ciro	Conte	8,794.41	Accounting
5	Jorge	De Lucia	7,489.15	Sales
6	Carmen	Lopez	10,479.15	Accounting
7	Catherine	Molnar	8,794.89	Sales
8	Richard	Buchanan	12,487.69	Accounting
9	Mark	Wong	9,784.19	Sales
10	Silvia	Karelias	9,748.64	Accounting

La tarea aquí es devolver los tres empleados mejor pagados de cada departamento, junto con su salario y departamento.

El planteamiento es el siguiente:

WITH ranking AS (
  SELECT 
    first_name,
    last_name,
    salary,
    department,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM salary
)

SELECT *
FROM ranking
WHERE salary_rank <= 3
ORDER BY department, salary_rank;

La primera parte del código es una Expresión de Tabla Común, o CTE. Se inicia utilizando la palabra clave WITH. La CTE se denomina clasificación. Después de la palabra clave AS, escribimos la definición de la CTE como una sentencia SELECT entre paréntesis.

Después de seleccionar todas las columnas requeridas viene ranking, usamos la función DENSE_RANK(). Si lo desea, puede utilizar cualquier otra función de ventana de clasificación.

La sintaxis es familiar. Para obtener los rankings por departamento, necesitamos particionar los datos por esa columna. Además, queremos ordenar los salarios de mayor a menor. En otras palabras, los datos de la partición deben ordenarse por salario en orden descendente.

La segunda sentencia SELECT (es decir, la consulta externa) selecciona todas las columnas del CTE y establece la condición en la cláusula WHERE para filtrar sólo los tres salarios más altos por departamento. Por último, la salida se ordena por departamento y rango salarial.

Este es el resultado:

first_name	last_name	salary	department	salary_rank
Richard	Buchanan	12,487.69	Accounting	1
Carmen	Lopez	10,479.15	Accounting	2
Silvia	Karelias	9,748.64	Accounting	3
Susan	Truman	15,478.69	Sales	1
Tim	Thompson	10,524.74	Sales	2
Mark	Wong	9,784.19	Sales	3

23. Calcular la Diferencia entre Dos Filas (Delta) Utilizando Funciones de ventana

Este problema se resuelve más elegantemente utilizando la función de ventana LAG(). Recuerde, esta es una función que accede al valor de la fila anterior.

Los datos del ejemplo se encuentran en la tabla revenue:

id	actual_revenue	period
1	8,748,441.22	2022_07
2	10,487,444.59	2022_08
3	7,481,457.15	2022_09
4	7,497,441.89	2022_10
5	8,697,415.36	2022_11
6	12,497,441.56	2022_12

Necesita mostrar los ingresos reales, el periodo de tiempo y la diferencia mensual (delta) entre el mes real y el anterior.

He aquí cómo hacerlo.

SELECT 
  actual_revenue,
  actual_revenue - LAG(actual_revenue) OVER (ORDER BY period ASC) AS monthly_revenue_change,	 
 period
FROM revenue
ORDER BY period;

Un delta se calcula restando el mes anterior del mes real. Eso es exactamente lo que hace esta consulta. Para obtener los ingresos del mes anterior, la función LAG() resulta muy útil. La columna actual_revenue es el argumento de la función, ya que queremos acceder a los datos de ingresos de la fila anterior. Como en toda función de ventana, existe una cláusula OVER(). En ella, ordenamos los datos por periodo de forma ascendente porque es lógico calcular el delta cronológicamente.

Esta es la salida de la consulta:

actual_revenue	monthly_revenue_change	period
8,748,441.22	NULL	2022_07
10,487,444.59	1,739,003.37	2022_08
7,481,457.15	-3,005,987.44	2022_09
7,497,441.89	15,984.74	2022_10
8,697,415.36	1,199,973.47	2022_11
12,497,441.56	3,800,026.20	2022_12

La primera muestra el cambio de ingresos como NULL. Esto es lo esperado porque no hay un mes anterior del que deducir. En 2022_08, hubo un aumento de ingresos de 1.739.003,37 = ingresos del mes actual - ingresos del mes anterior = 10.487.444,59 - 8.748.441,22. La misma lógica se aplica a todos los demás resultados.

Puede encontrar ejemplos similares en el artículo sobre el cálculo de la diferencia entre dos filas en SQL.

24. Utilice Funciones de ventana para calcular un total acumulado

Un total actual o acumulado es la suma de una secuencia de números. El total acumulado se actualiza cada vez que se añade un nuevo valor a la secuencia. Piense en los ingresos mensuales: los ingresos totales del mes en curso incluirán la suma de los ingresos del mes en curso y de todos los meses anteriores.

La función de ventana perfecta para calcular un total corrido (suma acumulada) es SUM().

Veamos el método con los mismos datos que en la pregunta anterior. El objetivo es calcular los ingresos acumulados de todos los meses disponibles en 2022.

He aquí la solución:

SELECT 
  actual_revenue,
  SUM(actual_revenue) OVER (ORDER BY period ASC) AS cumulative_revenue,
  period
FROM revenue;

La suma acumulada es la suma de los ingresos del mes actual y la suma de los ingresos de todos los meses anteriores. La función de ventana SUM() aplica esta lógica. El argumento de la función son los ingresos reales, porque eso es lo que estamos sumando. Para que sume todos los ingresos anteriores y los ingresos actuales, ordene los datos de forma ascendente por periodo. De nuevo, es lógico calcular un total acumulado desde el mes más antiguo hasta el más reciente.

Esto es lo que devuelve el código:

actual_revenue	cumulative_revenue	period
8,748,441.22	8,748,441.22	2022_07
10,487,444.59	19,235,885.81	2022_08
7,481,457.15	26,717,342.96	2022_09
7,497,441.89	34,214,784.85	2022_10
8,697,415.36	42,912,200.21	2022_11
12,497,441.56	55,409,641.77	2022_12

Los ingresos acumulados en la primera fila son los mismos que los ingresos reales. Para la segunda fila, el acumulado es de 19.235.885,81 = 8.748.441,22 + 10.487.444,59. En septiembre, el acumulado es de 26.717.342,96 = 8.748.441,22 + 10.487.444,59 + 7.481.457,15.

La misma lógica se aplica al resto de la tabla.

Puede obtener más información sobre el total móvil y cómo calcularlo aquí.

25. Encontrar una media móvil utilizando Funciones de ventana

Una media móvil se utiliza cuando se analiza una serie. Puede encontrarla con otros nombres, como media móvil, media móvil o media corrida. Es un promedio del valor actual y el número definido de valores precedentes. Por ejemplo, una media móvil de 7 días es la media del día actual y los seis días anteriores.

Para mostrarle cómo calcularla, utilizaremos la tabla eur_usd_rate tabla:

id	exchange_rate	date
1	1.0666	2022-12-30
2	1.0683	2023-01-02
3	1.0545	2023-01-03
4	1.0599	2023-01-04
5	1.0601	2023-01-05
6	1.0500	2023-01-06
6	1.0696	2023-01-09
7	1.0723	2023-01-10
8	1.0747	2023-01-11
9	1.0772	2023-01-12
10	1.0814	2023-01-13

Calcularemos la media móvil de 3 días de la siguiente manera:

SELECT 
  exchange_rate,
  AVG(exchange_rate) OVER (ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS eur_usd_moving_average,
  date
FROM eur_usd_rate;

Utilizamos la función de ventana AVG() en la columna exchange_rate. En la cláusula OVER(), los datos se ordenan por fecha en orden ascendente. ¡Ahora viene la parte importante! Recuerda que necesitamos una media móvil de 3 días que incluya la fila actual y las dos anteriores. Lo especificamos en la cláusula BETWEEN: le decimos a la función que incluya dos filas anteriores y la fila actual.

Veamos el resultado:

exchange_rate	eur_usd_moving_average	date
1.0666	1.0666	2022-12-30
1.0683	1.0675	2023-01-02
1.0545	1.0631	2023-01-03
1.0599	1.0609	2023-01-04
1.0601	1.0582	2023-01-05
1.0500	1.0567	2023-01-06
1.0696	1.0599	2023-01-09
1.0723	1.0640	2023-01-10
1.0747	1.0722	2023-01-11
1.0772	1.0747	2023-01-12
1.0814	1.0778	2023-01-13

La media móvil de la primera fecha es igual al tipo de cambio porque 1.0666/1 = 1.0666. Para 2023-01-02, se calcula así: (1,0666 + 1,0683)/2 = 1,0675.

En 2023-01-03, por fin tendremos tres fechas: (1,0666 + 1,0683 + 1,0545)/3 = 1,0631. Esta lógica se aplica al resto de las fechas.

Puedes encontrar más ejemplos en este artículo sobre el cálculo de medias móviles en SQL.

26. ¿Cuál es la diferencia entre ROWS y RANGE?

Tanto ROWS como RANGE son cláusulas utilizadas para definir un marco de ventana. Limitan el rango de datos utilizado en una función de ventana dentro de una partición.

La cláusula ROWS limita las filas. Se utiliza para especificar un número fijo de filas que preceden y siguen a la fila actual. El valor de las filas no se tiene en cuenta.

La cláusula RANGE limita el rango de datos de forma lógica. En otras palabras, limita los datos teniendo en cuenta los valores de las filas precedentes y siguientes en relación con la fila actual. No tiene en cuenta el número de filas.

¿Cómo se utilizan en la práctica? Lea nuestro artículo sobre FILAS y RANGO para más detalles.

27. Utilice una Consulta Recursiva para Encontrar a Todos los Empleados Bajo un Gerente Dado.

Una consulta recursiva es un tipo especial de CTE que se referencia a sí misma hasta llegar al final de la recursión. Es ideal para consultar datos gráficos o estructuras jerárquicas.

Un ejemplo de esto último es la estructura organizativa de la empresa, que se muestra en la tabla company_organization tabla:

employee_id	first_name	last_name	manager_id
5529	Jack	Simmons	5125
5238	Maria	Popovich	5329
5329	Dan	Jacobsson	5125
5009	Simone	Gudbois	5329
5125	Albert	Koch	NULL
5500	Jackie	Carlin	5529
5118	Steve	Nicks	5952
5012	Bonnie	Presley	5952
5952	Harry	Raitt	5529
5444	Sean	Elsam	5329

Esta tabla muestra todos los empleados y el ID de su responsable directo.

La tarea aquí es utilizar la recursividad y devolver todos los subordinados directos e indirectos de Jack Simmons. Además, vamos a añadir una columna que se puede utilizar para distinguir los diferentes niveles de organización. Aquí está el código:

WITH RECURSIVE subordinates AS (
	SELECT
  employee_id,
	  first_name,
	  last_name,
	  manager_id,
	  0 AS level
	FROM company_organization
	WHERE employee_id= 5529

	UNION ALL

	SELECT
 	  co.employee_id, 
	  co.first_name,
	  co.last_name,
	  co.manager_id,
	  level + 1
	FROM company_organization co 
JOIN subordinates s 
ON co.manager_id = s.employee_id
)

SELECT
  s.employee_id,
  s.first_name AS employee_first_name,
  s.last_name AS employee_last_name,
  co.employee_id AS direct_manager_id,
  co.first_name AS direct_manager_first_name,
  co.last_name AS direct_manager_last_name,
  s.level
FROM subordinates s 
JOIN company_organization co 
ON s.manager_id = co.employee_id
ORDER BY level;

Iniciamos la recursión utilizando WITH RECURSIVE. (Si está trabajando en MS SQL Server, utilice sólo WITH.)

El primer SELECT de un CTE se denomina miembro de anclaje. En él, hacemos referencia al conjunto de datos y seleccionamos todas las columnas necesarias. Además, creamos una nueva columna con el valor cero y filtramos los datos en la cláusula WHERE. ¿Por qué utilizar esta condición exacta en WHERE? Porque el ID de empleado de Jack Simmons es 5529, y queremos mostrarle a él y a sus subordinados.

A continuación viene UNION ALL, que combina los resultados de la consulta ancla y la consulta recursiva, es decir, la segunda sentencia SELECT.

Queremos que la recursividad llegue hasta el final de la estructura organizativa. En la consulta recursiva, unimos el CTE con la tabla company_organization tabla. Volvemos a enumerar todas las columnas necesarias de esta última tabla. Además, queremos añadir un nivel organizativo con cada recursión.

Por último, llegamos a la consulta que utiliza el CTE. Esta consulta sirve para obtener datos tanto del CTE como de la tabla. company_organization tabla. Utilizamos el CTE para mostrar los datos de los empleados. La otra tabla se utiliza para mostrar la información del responsable directo.

Ejecutando el código obtendremos este resultado:

employee_id	employee_first_name	employee_last_name	direct_manager_id	direct_manager_first_name	direct_manager_last_name	level
5529	Jack	Simmons	5125	Albert	Koch	0
5952	Harry	Raitt	5529	Jack	Simmons	1
5500	Jackie	Carlin	5529	Jack	Simmons	1
5012	Bonnie	Presley	5952	Harry	Raitt	2
5118	Steve	Nicks	5952	Harry	Raitt	2

La tabla de arriba muestra que el jefe directo de Jack Simmons es Albert Koch. Los subordinados directos de Simmons son Harry Raitt y Jackie Carlin. Los subordinados indirectos son Bonnie Presley y Steve Nicks. Su jefe directo es Harry Raitt.

Puede encontrar otras variaciones de esta tarea en el artículo sobre CTE recursivas.

Puedes aprender más sobre las funciones ventana en este artículo dedicado a las preguntas de entrevista sobre funciones ventana en SQL.

¿Estás preparado para superar las preguntas de la entrevista de trabajo de SQL?

Escribir esta guía no ha sido fácil. Pero vale la pena cuando pensamos en hacer más fácil tu entrevista de trabajo de SQL avanzado.

Por supuesto, estas no son todas las preguntas que te podrían hacer en la entrevista. Sin embargo, creemos que esta selección te proporcionará una base firme de los conceptos de SQL avanzado más importantes. Además, esta guía es lo suficientemente breve como para que puedas repasarla rápidamente antes de la entrevista y refrescar la memoria.

Para más refrescos sobre temas avanzados de SQL, prueba nuestro Funciones de ventana curso o SQL avanzado curso.

Tags: