25 Ejemplos de Consultas SQL Avanzadas

Una de las mejores formas de aprender SQL avanzado es estudiando ejemplos de consultas. En este artículo, mostraremos 25 ejemplos de consultas SQL avanzadas de complejidad media a alta. Puedes utilizarlos para refrescar tus conocimientos de SQL avanzado o para repasarlos antes de una entrevista SQL.

Muchos de los ejemplos de este artículo se basarán en la siguiente employee tabla. Sólo unos pocos ejemplos se basarán en otras tablas; en estos casos, las tablas se explicarán junto con el ejemplo.

employee_id	first_name	last_name	dept_id	manager_id	salary	expertise
100	John	White	IT	103	120000	Senior
101	Mary	Danner	Account	109	80000	junior
102	Ann	Lynn	Sales	107	140000	Semisenior
103	Peter	O'connor	IT	110	130000	Senior
106	Sue	Sanchez	Sales	107	110000	Junior
107	Marta	Doe	Sales	110	180000	Senior
109	Ann	Danner	Account	110	90000	Senior
110	Simon	Yang	CEO	null	250000	Senior
111	Juan	Graue	Sales	102	37000	Junior

Incluso para las personas con experiencia en SQL, un buen curso interactivo de SQL en línea puede ser una verdadera ayuda. Puede encontrar el conjunto más completo de cursos interactivos de SQL en nuestra pista Curso completo de SQL . Contiene 7 cursos interactivos de SQL con más de 850 ejercicios organizados de forma lógica para llevarle desde un principiante hasta un usuario avanzado de SQL. Los cursos para principiantes cubren los fundamentos de SQL y son una forma perfecta de repasar y refrescar sus conocimientos básicos de SQL. Los cursos avanzados de SQL le enseñarán conceptos como funciones de ventana, consultas recursivas e informes SQL complejos. Cree una cuenta gratuita en LearnSQL.es y pruebe nuestros cursos interactivos sin tener que gastar dinero. Después, si te gusta lo que estás aprendiendo, puedes comprar el acceso completo.

Bien, ¡ahondemos en nuestras consultas SQL avanzadas!

25 SQL avanzado Ejemplos de Consultas con Explicaciones

Ejemplo #1 - Clasificación de Filas Basada en un Criterio de Orden Específico

Algunas veces necesitamos crear una consulta SQL para mostrar un ranking de filas basado en un criterio de orden específico. En este ejemplo de consulta, mostraremos una lista de todos los empleados ordenados por salario (primero el salario más alto). El informe incluirá la posición de cada empleado en el ranking.

Aquí está el código:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  salary, 
  RANK() OVER (ORDER BY salary DESC) as ranking
FROM employee
ORDER BY ranking

En la consulta anterior, utilizamos la función RANK(). Se trata de una función de ventana que devuelve la posición de cada fila en el conjunto de resultados, basándose en el orden definido en la cláusula OVER (1 para el salario más alto, 2 para el segundo más alto, y así sucesivamente). Necesitamos utilizar una cláusula de clasificación ORDER BY al final de la consulta para indicar el orden en el que se mostrará el conjunto de resultados.

Si desea saber más sobre las funciones de clasificación en SQL, le recomiendo nuestro artículo ¿Qué es la función RANK() en SQL y cómo se utiliza?

Ejemplo #2 - Listar las Primeras 5 Filas de un Conjunto de Resultados

La siguiente consulta SQL crea un informe con los datos de los empleados para los 5 salarios más altos de la compañía. Este tipo de informe debe ser ordenado en base a un criterio dado; en nuestro ejemplo, el criterio de orden será nuevamente salary DESC:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking

La cláusula WITH de la consulta anterior crea una CTE llamada employee_ranking, que es una especie de tabla virtual que se consume en la consulta principal. La subconsulta en la CTE utiliza la función RANK() para obtener la posición de cada fila en el ranking. La cláusula OVER (ORDER BY salary DESC) indica cómo debe calcularse el valor RANK(). La función RANK() para la fila con el salario más alto devolverá 1, y así sucesivamente.

Por último, en la WHERE de la consulta principal pedimos aquellas filas con un valor de ranking menor o igual que 5. Esto nos permite obtener sólo las 5 primeras filas por valor de clasificación. De nuevo, utilizamos una cláusula ORDER BY para mostrar el conjunto de resultados, que se ordena por rango de forma ascendente.

Ejemplo nº 3 - Listar las 5 últimas filas de un conjunto de resultados

Esta consulta es similar a la consulta top 5, pero queremos las últimas 5 filas. Sólo necesitamos introducir un cambio en el tipo de orden, es decir, utilizar ASC en lugar de DESC. En el CTE, crearemos una columna de clasificación basada en un orden ascendente de salario (el salario más bajo primero):

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary ASC) as ranking
  FROM employee
)
SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking

En la consulta principal, utilizamos WHERE ranking <= 5 para filtrar las filas con los 5 salarios más bajos. Después, utilizamos ORDER BY ranking para ordenar las filas del informe por valor de ranking.

Ejemplo #4 - Listar la segunda fila más alta de un conjunto de resultados

Supongamos que queremos obtener los datos del empleado con el segundo salario más alto de la empresa. Podemos aplicar un enfoque similar a nuestra consulta anterior:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking = 2

La condición WHERE ranking = 2 se utiliza para filtrar las filas con el salario en la posición 2. Tenga en cuenta que podemos tener más de un empleado en la posición 2 si tienen el mismo salario.

En este punto, es importante entender el comportamiento de la función RANK() así como otras funciones disponibles como ROW_NUMBER() y DENSE_RANK(). Este tema se cubre en detalle en nuestro Resumen de las Funciones de Ranking en SQL. Le recomiendo leer este artículo si necesita trabajar con diferentes tipos de rankings.

Ejemplo #5 - Listar el Segundo Salario Más Alto por Departamento

Agreguemos una variación a la consulta SQL anterior. Como cada uno de nuestros empleados pertenece a un departamento, ahora queremos un reporte que muestre el ID del departamento y el nombre del empleado con el segundo salario más alto en este departamento. Queremos un registro por cada departamento de la empresa. Esta es la consulta:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary, 
    dept_id
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  dept_id, 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking = 2
ORDER BY dept_id, last_name

El principal cambio introducido en esta consulta es la cláusula PARTITION BY dept_id en OVER. Esta cláusula agrupa las filas con el mismo dept_id, ordenando las filas de cada grupo por salario DESC. A continuación, se calcula la función RANK() para cada departamento.

En la consulta principal, devolvemos el dept_id y los datos de los empleados en la posición 2 de su clasificación departamental.

Para aquellos lectores que quieran saber más sobre cómo encontrar la enésimafila más alta de un grupo, les recomiendo el artículo Cómo encontrar el enésimo salario más alto por departamento con SQL.

Ejemplo #6 - Listar las Primeras 50% Filas en un Conjunto de Resultados

En algunos casos, podríamos estar interesados en obtener el primer 50% del conjunto de resultados, (o cualquier otro porcentaje). Para este tipo de informe, existe una función SQL llamada NTILE() que recibe un parámetro entero que indica el número de subconjuntos en los que queremos dividir todo el conjunto de resultados. Por ejemplo NTILE(2) divide el conjunto de resultados en 2 subconjuntos con la misma cantidad de elementos; para cada fila, devuelve un 1 o un 2 dependiendo del subconjunto donde se encuentre la fila.

Ésta es la consulta

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    NTILE(2) OVER (ORDER BY salary ) as ntile
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ntile = 1
ORDER BY salary 

La consulta anterior devuelve sólo las filas de la primera mitad de un informe de empleados ordenados por salario en orden ascendente. Utilizamos la condición ntile = 1 para filtrar sólo las filas de la primera mitad del informe. Si está interesado en la función de ventana NTILE(), consulte el artículo SQL común Funciones de ventana: Uso de particiones con funciones de clasificación.

Ejemplo #7 - Listar el último 25% de filas en un conjunto de resultados

Al igual que en la consulta anterior, en este ejemplo utilizaremos NTILE(4) para dividir el conjunto de resultados en 4 subconjuntos; cada subconjunto tendrá el 25% del conjunto de resultados total. Utilizando la función NTILE(), generaremos una columna llamada ntile con los valores 1, 2, 3 y 4:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    NTILE(4) OVER (ORDER BY salary) as ntile
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ntile = 4
ORDER BY salary 

La condición WHERE ntile = 4 filtra sólo las filas del último trimestre del informe. La última cláusula ORDER BY salary ordena el conjunto de resultados que devolverá la consulta, mientras que OVER (ORDER BY salary) ordena las filas antes de dividirlas en 4 subconjuntos utilizando NTILE(4).

Ejemplo #8 - Numerar las filas de un conjunto de resultados

A veces queremos crear una clasificación que asigne a cada fila un número que indique la posición de esa fila en la clasificación: 1 a la primera fila, 2 a la segunda, y así sucesivamente. SQL ofrece varias formas de hacerlo. Si queremos una simple secuencia de números del 1 al N, podemos utilizar la función ROW_NUMBER(). Sin embargo, si queremos una clasificación que permita dos filas en la misma posición (es decir, porque comparten el mismo valor) podemos utilizar la función RANK() o DENSE_RANK(). La siguiente consulta crea un informe donde cada fila tiene un valor de posición:

SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary,
  ROW_NUMBER() OVER (ORDER BY employee_id) as ranking_position
FROM employee

Si desea conocer diferentes funciones de clasificación avanzadas, le recomiendo el artículo Visión general de las funciones de clasificación en SQL.

Ejemplo #9 - Listar Todas las Combinaciones de Filas de Dos Tablas

En algunos casos, podríamos necesitar una unión que incluya todas las combinaciones posibles de filas de dos tablas. Supongamos que tenemos una empresa de alimentación que vende 3 tipos de cereales: copos de maíz, copos de maíz azucarados y copos de arroz. Todos estos cereales se venden en 3 tamaños de envase diferentes: 1 libra, 3 libras y 5 libras. Como ofrecemos 3 productos en 3 tamaños de envase diferentes, ofrecemos nueve combinaciones distintas.

Tenemos una product tabla con 3 registros (copos de maíz, copos de maíz azucarados y copos de arroz) y otra tabla llamada box_size con 3 registros, uno para 1 libra y dos registros para 3 y 5 libras, respectivamente. Si queremos crear un informe con la lista de precios de nuestras nueve combinaciones, podemos utilizar la siguiente consulta:

SELECT
  grain.product_name,
  box_size.description, 
  grain.price_per_pound * box_size.box_weight
FROM product
CROSS JOIN	box_sizes

El resultado de la consulta será:

product	package_size	price
Corn flake	1 pound box	2.43
Corn flake	3 pound box	7.29
Corn flake	5 pound box	12.15
Sugared corn flake	1 pound box	2.85
Sugared corn flake	3 pound box	8.55
Sugared corn flake	5 pound box	14.25
Rice flake	1 pound box	1.98
Rice flake	3 pound box	5.94
Rice flake	5 pound box	9.90

La cláusula CROSS JOIN sin ninguna condición produce una tabla con todas las combinaciones de filas de ambas tablas. Observe que calculamos el precio basándonos en el precio por libra almacenado en la tabla product y el peso de box_sizes con la expresión:

    grain.price_per_pound * box_size.box_weight

Puede encontrar más información sobre CROSS JOIN en Guía Ilustrada del CROSS JOIN de SQL.

Ejemplo #10 - Unir una Tabla a Sí Misma

En algunos casos, necesitamos unir una tabla consigo misma. Piense en la employee tabla. Cada fila tiene una columna llamada manager_id con el ID del gerente que supervisa a este empleado. Utilizando una auto-unión podemos obtener un informe con las columnas employee_name y manager_name; esto nos mostrará quién gestiona a cada empleado. He aquí la consulta:

SELECT 	
  e1.first_name ||’ ‘|| e1.last_name AS manager_name,
  e2.first_name ||’ ‘|| e2.last_name AS employee_name
FROM employee e1
JOIN employee e2 
ON e1.employee_id = e2.manager_id

En la consulta anterior, podemos ver que la tabla employee es referenciada dos veces como e1 y e2, y la condición de unión es e1.employee_id = e2.manager_id. Esta condición enlaza cada fila de empleado con la fila de manager. El artículo ¿Qué es un Self Join en SQL? Una Explicación con Siete Ejemplos le dará más ideas sobre cuando puede aplicar autouniones en sus consultas SQL.

Ejemplo #11 - Mostrar Todas las Filas con un Valor por Encima del Promedio

Necesitamos un informe que muestre todos los empleados con un salario superior a la media de la empresa. Primero podemos crear una subconsulta para obtener el salario promedio de la compañía, y luego comparar el salario de cada empleado con el resultado de la subconsulta. Esto se muestra en el siguiente ejemplo:

SELECT 
  first_name, 
  last_name, 
  salary
FROM employee  
WHERE salary > ( SELECT AVG(salary) FROM employee )

Puede ver la subconsulta que obtiene el salario medio en la cláusula WHERE. En la consulta principal, seleccionamos el nombre del empleado y el salario. Puede leer más sobre subconsultas en el artículo Cómo practicar subconsultas SQL.

Ejemplo #12 - Empleados con Salarios Mayores al Promedio de su Departamento

Supongamos que queremos obtener los registros de los empleados con salarios superiores al salario medio de sus departamentos. Esta consulta es diferente a la anterior porque ahora necesitamos una subconsulta para obtener el salario medio del departamento del empleado actual en lugar de toda la empresa Esto se llama subconsulta correlacionada porque dentro de la subconsulta hay una referencia a una columna de la fila actual de la tabla principal de la consulta.

He aquí el código:

SELECT
  first_name, 
  last_name, 
  salary
FROM employee e1 
WHERE salary > 
    (SELECT AVG(salary) 
     FROM employee e2 
     WHERE e1.departmet_id = e2.department_id)

En la subconsulta, podemos ver una referencia a la columna e1.department_id, que es una columna a la que se hace referencia en la consulta principal. La condición e1.departmet_id = e2.department_id es la clave en la subconsulta porque nos permite obtener la media de todos los empleados del departamento de la fila actual. Una vez obtenido el salario medio departamental, lo comparamos con el salario del empleado y filtramos en consecuencia.

Ejemplo #13 - Obtener Todas las Filas Donde un Valor Está en el Resultado de una Subconsulta

Supongamos que Juan Pérez gestiona varios departamentos y queremos obtener una lista de todos los empleados de esos departamentos. Utilizaremos una subconsulta para obtener los ID de los departamentos gestionados por Juan Pérez. A continuación, utilizaremos el operador IN para encontrar a los empleados que trabajan en esos departamentos:

SELECT 	
  first_name, 
  last_name
FROM employee e1 
WHERE department_id IN (
   SELECT department_id 
   FROM department
   WHERE manager_name=‘John Smith’)

La subconsulta anterior es una subconsulta de varias filas: devuelve más de una fila. De hecho, devolverá varias filas porque Juan Pérez gestiona muchos departamentos. Cuando se trabaja con subconsultas de varias filas, es necesario utilizar operadores específicos (como IN) en la condición WHERE que involucra a la subconsulta.

Ejemplo #14 - Encontrar Filas Duplicadas en SQL

Si una tabla tiene filas duplicadas, puede encontrarlas con SQL. Utilice una consulta con una cláusula GROUP BY que incluya todas las columnas de la tabla y una cláusula HAVING para filtrar las filas que aparecen más de una vez. He aquí un ejemplo:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
FROM employee
GROUP BY 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
HAVING COUNT(*) > 1

Las filas que no estén duplicadas tendrán un COUNT(*) igual a 1, pero aquellas filas que existan muchas veces tendrán un COUNT(*) devolviendo el número de veces que existe la fila. Te sugiero el artículo Cómo Encontrar Valores Duplicados en SQL si quieres encontrar más detalles sobre esta técnica.

Ejemplo #15 - Contar Filas Duplicadas

Si quieres contar filas duplicadas, puedes usar la siguiente consulta. Es similar a la anterior, pero añadimos un COUNT(*) en la lista SELECT para mostrar cuántas veces aparece cada fila duplicada en la tabla:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary, 
  COUNT(*) AS number_of_rows
FROM employee
GROUP BY
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
HAVING COUNT(*) > 1

De nuevo, puede encontrar información valiosa sobre cómo gestionar registros duplicados en el artículo Cómo encontrar registros duplicados en SQL.

Ejemplo #16 - Encontrar Registros Comunes entre Tablas

Si tenemos dos tablas con el mismo esquema o si dos tablas tienen un subconjunto de columnas en común, podemos obtener las filas que aparecen en ambas tablas con el operador de conjunto INTERSECT. Supongamos que tenemos una instantánea de la tabla employee tomada en enero de 2020 llamada employee_2020_jan y queremos obtener la lista de empleados que existen en ambas tablas. Podemos hacerlo con esta consulta:

SELECT 
  last_name, 
  first_name 
FROM employee
INTERSECT
SELECT 
  last_name, 
  first_name 
FROM employee_2020_jan

Como resultado obtendremos una lista de empleados que aparecen en ambas tablas. Quizás tengan valores diferentes en las columnas como salary o dept_id. Es decir, estamos obteniendo aquellos empleados que trabajaban para la empresa en Ene 2020 y que siguen trabajando para la empresa.

Si te interesa saber más sobre los operadores de conjuntos, te sugiero el artículo Introducción a los operadores de conjuntos de SQL: Unión, Unión Todo, Menos e Intersección.

Ejemplo #17 - Agrupación de Datos con ROLLUP

La cláusula GROUP BY en SQL se utiliza para agregar filas en grupos y aplicar funciones a todas las filas del grupo, devolviendo un único valor de resultado. Por ejemplo, si queremos obtener un informe con el importe total del salario por departamento y nivel de experiencia, podemos hacer la siguiente consulta:

SELECT 	
  dept_id, 
  expertise, 
  SUM(salary) total_salary
FROM	employee
GROUP BY dept_id, expertise

GROUP BY tiene la cláusula opcional ROLLUP, que permite incluir agrupaciones adicionales en una consulta. Añadiendo la cláusula ROLLUP a nuestro ejemplo podríamos obtener la suma total de los salarios de cada departamento (sin importar el nivel de especialización del empleado) y la suma total de los salarios de toda la tabla (sin importar el departamento y el nivel de especialización del empleado). La consulta modificada es:

SELECT 
  dept_id, 
  expertise, 
  SUM(salary) total_salary
FROM employee
GROUP BY ROLLUP (dept_id, expertise)

Y el resultado será:

dept_id	expertise	total_salary
Account	Senior	90000
Account	Junior	80000
Account	NULL	170000
CEO	Senior	250000
CEO	NULL	250000
IT	Senior	250000
IT	NULL	250000
Sales	Junior	110000
Sales	Semisenior	140000
Sales	Senior	180000
Sales	NULL	430000
NULL	NULL	1100000

Las filas del conjunto de resultados con NULL son las filas adicionales añadidas por la cláusula ROLLUP. Un valor NULL en la columna expertise significa un grupo de filas para un valor específico de dept_id pero sin un valor específico de expertise. En otras palabras, es la cantidad total de salarios para cada dept_id. Del mismo modo, la última fila del resultado que tiene un NULL para las columnas dept_id y expertise significa el total general para todos los departamentos de la empresa.

Si quieres aprender más sobre la cláusula ROLLUP y otras cláusulas similares como CUBE, el artículo Grouping, Rolling, and Cubing Data tiene muchos ejemplos.

Ejemplo #18 - Sumatoria Condicional

En algunos casos, necesitamos resumir o contar valores basados en alguna(s) condición(es). Por ejemplo, si queremos obtener el total de salarios en los departamentos de Ventas y Recursos Humanos combinados y en los departamentos de IT y Soporte combinados, podemos ejecutar la siguiente consulta:

SELECT 
  SUM (CASE
    WHEN dept_id IN (‘SALES’,’HUMAN RESOURCES’) 
    THEN salary
    ELSE 0 END) AS total_salary_sales_and_hr,
  SUM (CASE
    WHEN dept_id IN (‘IT’,’SUPPORT’) 
    THEN salary
    ELSE 0 END) AS total_salary_it_and_support
FROM employee

La consulta devuelve una única fila con dos columnas. La primera columna muestra el salario total de los departamentos de Ventas y Recursos Humanos. Este valor se calcula utilizando la función SUM() en la columna salary - pero sólo cuando el empleado pertenece al departamento de Ventas o Recursos Humanos. Se añade un cero a la suma cuando el empleado pertenece a cualquier otro departamento. La misma idea se aplica para la columna total_salary_it_and_support.

Los artículos Patrones SQL útiles: Resumen condicional con CASE y Cómo usar CASE WHEN con SUM() en SQL SQL proporcionan más detalles sobre esta técnica.

Ejemplo #19 - Agrupar Filas por un Rango

En el siguiente ejemplo de consulta, crearemos los rangos de salario low, medium, y high. Luego contaremos cuantos empleados hay en cada rango salarial:

SELECT 
  CASE
    WHEN salary <= 750000 THEN ‘low’
    WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’
    WHEN salary > 100000 THEN ‘high’
  END AS salary_category, 
  COUNT(*) AS number_of_employees
FROM	employee
GROUP BY 
  CASE
    WHEN salary <= 750000 THEN ‘low’
    WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’
    WHEN salary > 100000 THEN ‘high’
END

En esta consulta, utilizamos CASE para definir el rango salarial de cada empleado. Puede ver la misma sentencia CASE dos veces. La primera define los rangos, como acabamos de decir; la segunda en el GROUP BY agrega registros y aplica la función COUNT(*) a cada grupo de registros. Puede utilizar la sentencia CASE del mismo modo para calcular recuentos o sumas para otros niveles definidos a medida.

Cómo Utilizar CASE en SQL explica otros ejemplos de sentencias CASE como la utilizada en esta consulta.

Ejemplo #20 - Calcular un Total Corrido en SQL

Un total corrido es un patrón SQL muy común, uno que se usa frecuentemente en finanzas y en análisis de tendencias.

Cuando se tiene una tabla que almacena cualquier métrica diaria, como una tabla sales con las columnas day y daily_amount, se puede calcular el total acumulado como la suma acumulada de todos los valores anteriores de daily_amount. SQL proporciona una función de ventana llamada SUM() para hacer precisamente eso.

En la siguiente consulta, calcularemos las ventas acumuladas de cada día:

SELECT 
  day,
  daily_amount,
  SUM (daily_amount) OVER (ORDER BY day) AS running_total
FROM sales

La función SUM() utiliza la cláusula OVER() para definir el orden de las filas; todas las filas anteriores al día actual se incluyen en SUM(). A continuación se muestra un resultado parcial:

day	daily_amount	running_total
Jan 30, 2023	1000.00	1000.00
Jan 31, 2023	800.00	1800.00
Feb 1, 2023	700.00	2500.00

Las dos primeras columnas day y daily_amount son valores tomados directamente de la tabla sales. La columna running_total se calcula mediante la expresión:

SUM (daily_amount) OVER (order by day)

Puede ver claramente cómo la running_total es la suma acumulada de las daily_amounts anteriores .

Si desea profundizar en este tema, le sugiero el artículo ¿Qué es un total móvil SQL y cómo se calcula?, que incluye muchos ejemplos clarificadores.

Ejemplo 21 - Calcular una Media Móvil en SQL

Un promedio móvil es una técnica de series de tiempo para analizar tendencias en datos. Se calcula como el promedio del valor actual y un número específico de valores inmediatamente anteriores para cada punto en el tiempo. La idea principal es examinar cómo se comportan estas medias a lo largo del tiempo en lugar de examinar el comportamiento de los puntos de datos originales o brutos.

Calculemos la media móvil de los últimos 7 días utilizando la tabla sales del ejemplo anterior:

SELECT 
  day,
  daily_amount,
  AVG (daily_amount) OVER (ORDER BY day ROWS 6 PRECEDING)
    AS moving_average
FROM sales

En la consulta anterior, utilizamos la función de ventana AVG() para calcular la media utilizando la fila actual (hoy) y las 6 filas anteriores. Como las filas están ordenadas por días, la fila actual y las 6 filas anteriores definen un periodo de 1 semana.

El artículo Qué es un Promedio Móvil y Cómo Calcularlo en SQL entra en detalle sobre este tema; consúltelo si desea aprender más.

Ejemplo #22 - Calcular una Diferencia (Delta) entre Dos Columnas en Diferentes Filas

Hay más de una forma de calcular la diferencia entre dos filas en SQL. Una forma de hacerlo es utilizando las funciones de ventana LEAD() y LAG(), como lo haremos en este ejemplo.

Supongamos que queremos obtener un informe con la cantidad total vendida cada día, pero también queremos obtener la diferencia (o delta) respecto al día anterior. Podemos utilizar una consulta como ésta:

SELECT 
  day,
  daily_amount,
  daily_amount - LAG(daily_amount) OVER (ORDER BY day)
    AS delta_yesterday_today
FROM sales

La expresión clave de esta consulta es:

daily_amount - LAG(daily_amount) OVER (ORDER BY day)

Ambos elementos de la diferencia aritmética proceden de filas diferentes. El primer elemento procede de la fila actual y LAG(daily_amount) procede de la fila del día anterior. LAG() devuelve el valor de cualquier columna de la fila anterior (en función del ORDER BY especificado en la cláusula OVER ).

Si quieres leer más sobre LAG() y LEAD(), te sugiero el artículo Cómo Calcular la Diferencia entre Dos Filas en SQL.

Ejemplo 23 - Calcular una diferencia interanual

Las comparaciones año sobre año (YOY) o mes a mes son una forma popular y efectiva de evaluar el desempeño de varios tipos de organizaciones. Puede calcular la comparación como un valor o como un porcentaje.

En este ejemplo, utilizaremos la tabla sales que contiene datos con una granularidad diaria. Primero tenemos que agregar los datos al año o al mes, lo que haremos creando un CTE con importes agregados por año. Esta es la consulta:

WITH year_metrics AS (
  SELECT 
    extract(year from day) as year,
    SUM(daily_amount) as year_amount
  FROM sales 
  GROUP BY year)
SELECT 
  year, 
  year_amount,
  LAG(year_amount) OVER (ORDER BY year) AS revenue_previous_year,
  year_amount - LAG(year_amount) OVER (ORDER BY year) as yoy_diff_value,
  ((year_amount - LAG(year_amount) OVER (ORDER BY year) ) /
     LAG(year_amount) OVER (ORDER BY year)) as yoy_diff_perc
FROM year_metrics
ORDER BY 1

La primera expresión a analizar es la que se utiliza para calcular yoy_diff_value:

year_amount - LAG(year_amount ) OVER (ORDER BY year)

Se utiliza para calcular la diferencia (como valor) entre el importe del año actual y el del año anterior utilizando la función de ventana LAG() y ordenando los datos por año.

En la siguiente expresión, calculamos la misma diferencia como porcentaje. Este cálculo es un poco más complejo porque tenemos que dividir por el importe del año anterior. (Nota: Utilizamos el año anterior como base para el cálculo del porcentaje, por lo que el año anterior es 100 por ciento).

((year_amount-LAG(year_amount ) OVER(ORDER BY year))/LAG(year_amount ) OVER(ORDER BY year))

En el artículo Cómo calcular las diferencias interanuales en SQL SQL, puede encontrar varios ejemplos de cálculo de diferencias de año a año y de mes a mes.

Ejemplo 24 - Utilizar Consultas recursivas y expresiones de tabla comunes para Gestionar Jerarquías de Datos

Algunas tablas en SQL pueden tener un tipo implícito de jerarquía de datos. Por ejemplo, nuestra tabla employee tiene un manager_id para cada empleado. Tenemos un gerente que está a cargo de otros gerentes, quienes a su vez tienen otros empleados a su cargo, y así sucesivamente.

Cuando tenemos este tipo de organización, podemos tener una jerarquía de varios niveles. En cada fila, la columna manager_id hace referencia a la fila del nivel inmediatamente superior en la jerarquía. En estos casos, una petición frecuente es obtener una lista de todos los empleados que dependen (directa o indirectamente) del director general de la empresa (que, en este caso, tiene el employee_id de 110). La consulta a utilizar es:

WITH RECURSIVE subordinate AS (
 SELECT  
   employee_id,
   first_name,
   last_name,
   manager_id
  FROM employee
  WHERE employee_id = 110 -- id of the top hierarchy employee (CEO)
 
  UNION ALL
 
  SELECT  
    e.employee_id,
    e.first_name,
    e.last_name,
    e.manager_id
  FROM employee e 
  JOIN subordinate s 
  ON e.manager_id = s.employee_id
)
SELECT 	
  employee_id,
  first_name,
  last_name,
  manager_id
FROM subordinate ;

En esta consulta, creamos un CTE recursivo llamado subordinate. Es la parte clave de esta consulta porque recorre la jerarquía de datos yendo de una fila a las filas de la jerarquía inmediatamente inferior.

Hay dos subconsultas conectadas por un UNION ALL; la primera subconsulta devuelve la fila superior de la jerarquía y la segunda devuelve el siguiente nivel, añadiendo esas filas al resultado intermedio de la consulta. A continuación, la segunda subconsulta se ejecuta de nuevo para devolver el siguiente nivel, que de nuevo se añadirá al conjunto de resultados intermedios. Este proceso se repite hasta que no se añaden nuevas filas al resultado intermedio.

Finalmente, la consulta principal consume los datos de la CTE subordinate y devuelve los datos de la forma que esperamos. Si desea aprender más sobre consultas recursivas en SQL, le sugiero el artículo Cómo Encontrar Todos los Empleados Bajo Cada Gerente en SQL.

Ejemplo #25 - Encontrar la Longitud de una Serie Usando Funciones de ventana

Supongamos que tenemos una tabla con datos de registro de usuarios. Almacenamos información sobre cuantos usuarios se registraron en cada fecha. Definimos una serie de datos como la secuencia de días consecutivos en que los usuarios se registraron. Un día en el que no se registra ningún usuario rompe la serie de datos. Para cada serie de datos, queremos encontrar su longitud.

La siguiente tabla muestra las series de datos:

id	day	Registered users
1	Jan 25 2023	51
2	Jan 26 2023	46
3	Jan 27 2023	41
4	Jan 30 2023	59
5	Jan 31 2023	73
6	Feb 1 2023	34
7	Feb 2 2023	56
8	Feb 4 2023	34

Hay 3 series de datos diferentes que se muestran en distintos colores. Buscamos una consulta para obtener la longitud de cada serie de datos. La primera serie de datos comienza el 25 de enero y tiene una longitud de 3 elementos, la segunda comienza el 30 de enero y su longitud es 4, y así sucesivamente.

La consulta es la siguiente:

WITH data_series AS (
  SELECT  	
    RANK() OVER (ORDER BY day) AS row_number,
    day, 
    day - RANK() OVER (ORDER BY day) AS series_id
 FROM	user_registration )
SELECT	
  MIN(day) AS series_start_day,
  MAX(day) AS series_end_day,
  MAX(day) - MIN (day) + 1 AS series_length
FROM	data_series
GROUP BY series_id
ORDER BY series_start_date

En la consulta anterior, el CTE tiene la columna series_id, que es un valor destinado a ser utilizado como ID para las filas de la misma serie de datos. En la consulta principal, la cláusula GROUP BY series_id se utiliza para agregar filas de la misma serie de datos. A continuación, podemos obtener el inicio de la serie con MIN(day) y su final con MAX(day). La longitud de la serie se calcula con la expresión:

      MAX(day) - MIN (day) + 1

Si quieres profundizar en este tema, en el artículo Cómo calcular la longitud de una serie con SQL encontrarás una explicación detallada de esta técnica.

Practica SQL avanzado con los cursos de LearnSQL.com

SQL es un lenguaje potente y fácil de aprender. En este artículo, mostramos 25 ejemplos de consultas SQL avanzadas. Todos ellos pueden explicarse en unos 5 minutos, lo que demuestra que SQL es un lenguaje accesible incluso cuando necesitas realizar informes o consultas complejas.

Si quieres seguir aprendiendo SQL, te sugiero nuestros cursos de SQL avanzado: Funciones de ventana, Consultas recursivas y expresiones de tabla comunes y Modificadores de GROUP BY en SQL. Todos ellos cubren áreas complejas del lenguaje SQL con palabras sencillas y abundantes ejemplos. ¡Aumente su destreza e invierta en sí mismo con SQL!