Las 7 consultas SQL avanzadas más importantes para el análisis de datos

Explore las consultas SQL avanzadas esenciales para el análisis de datos.

El lenguaje de consulta estructurado, o SQL, es una herramienta indispensable para los analistas de datos. La mayoría de las personas aprenden el lenguaje con relativa rapidez y pueden empezar a realizar análisis de datos en profundidad tras unas pocas lecciones. Por ello, muchos analistas de datos tienden a quedarse en el nivel principiante/intermedio del uso de SQL. Este nivel de comprensión permite "hacer el trabajo", pero puede no ser la forma más eficiente de escribir una consulta.

Dominar las técnicas avanzadas de SQL puede mejorar enormemente sus capacidades de análisis de datos, permitiéndole profundizar en sus conjuntos de datos y obtener información valiosa. En este artículo, exploraremos siete consultas SQL avanzadas que pueden resultar útiles para el análisis de datos. Para cada ejemplo, presentaremos la consulta y sus resultados, discutiremos la sintaxis utilizada y proporcionaremos una breve explicación de cómo la base de datos calcula el resultado.

Si desea mejorar sus conocimientos de SQL, no deje de consultar nuestro curso de SQL Reporting. Es una excelente manera de aprender y practicar funciones SQL más avanzadas.

7 SQL avanzado Consultas que los analistas de datos deben conocer

1. 1. Agrupación de datos por período de tiempo

En este primer ejemplo, vamos a examinar los datos de la tabla sales. Este conjunto de datos incluye un ID para cada producto, la fecha de la transacción y el importe total de la transacción.

Un escenario muy común de análisis de datos es tomar datos brutos como estos y agregarlos por periodos de fechas específicos, como un mes o un año. Hagámoslo ejecutando la siguiente consulta:

SELECT
  EXTRACT(YEAR FROM sale_date) AS year, 
  EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY year, month
ORDER BY year, month;

Esta consulta arrojará los siguientes resultados:

En esta consulta, utilizamos la función EXTRACT() para extraer el año y el mes del campo sale_date. A continuación, agrupamos los datos por los nuevos campos de año y mes y calculamos las ventas totales de cada grupo utilizando la función SUM().

Tenga en cuenta que necesitamos agrupar por año y mes para obtener resultados precisos. Si sólo introdujéramos el mes, los resultados combinarían los valores de meses específicos de todos los años (de modo que todos los meses de enero de todos los años se combinarían en una fila, todos los meses de febrero se combinarían, etc.). Si agrupamos los resultados por año y mes, los meses de los distintos años se colocan en filas separadas.

A continuación, ordenamos las filas por año y mes para obtener resultados ordenados.

Los diferentes motores de bases de datos suelen tener funciones diferentes y a veces mejores para lograr el mismo resultado, como DATE_TRUNC() en PostgreSQL o TRUNC() en Oracle.

2. Crear Múltiples Niveles de Agrupación Usando ROLLUP

Algunas veces puede querer agrupar datos por múltiples niveles en una sola consulta. Digamos que desea agregar las ventas totales (a través de todos los años) así como las ventas totales en cada año al ejemplo anterior. Puede añadir filas adicionales para el total general y los totales de cada año utilizando la palabra clave ROLLUP.

Utilizando el mismo conjunto de datos, ejecutaremos esta consulta:

SELECT
 EXTRACT(YEAR FROM sale_date) AS year,
 EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(year,month)
ORDER BY year, month;

Resultado:

En nuestro ejemplo, ROLLUP añade tres filas adicionales: una para el total general de todas las ventas y dos para las ventas totales de cada año. En nuestro conjunto de resultados, la tercera fila es el total anual de 2021: el valor de la columna year es 2021 y el valor de la columna month es NULL. La sexta fila es el total para 2022: el valor en la columna del año es 2022 y el valor en la columna month es NULL. La última fila es el total para ambos años: tiene NULL en las columnas año y month. Estas filas fueron añadidas por ROLLUP. Los valores NULL en las columnas año y month indican las filas agregadas.

3. Clasificación de datos Funciones de ventana

La clasificación de datos es un requisito común en el análisis avanzado de datos. Por ejemplo, puede clasificar sus productos según las ventas más altas para saber qué productos generan más ingresos o clasificar las tiendas según las ventas más bajas para saber qué tiendas son las que menos rinden.

Puede utilizar funciones de ventana como RANK() o DENSE_RANK() para asignar rangos a las filas basándose en el valor de una columna específica. En primer lugar, echemos un vistazo a la función RANK():

SELECT
  product_id,
  SUM(amount) AS total_sales,
  RANK() OVER(ORDER BY SUM(amount) DESC) AS rank
FROM sales
GROUP BY id;

Resultado:

Esta consulta calcula primero las ventas totales de cada product_id y luego las clasifica en orden descendente. La función RANK() es una función de ventana utilizada para calcular la clasificación. La cláusula OVER() es la sintaxis utilizada con las funciones ventana. La función RANK() asigna un rango único a cada fila dentro de un conjunto de resultados, basándose en un orden específico. La cláusula ORDER BY de la cláusula OVER() especifica el orden en el que se asignará el rango, basándose en una o más columnas. En nuestro ejemplo, ordenamos las filas por el importe total de las ventas.

Se puede utilizar la misma sintaxis para la función DENSE_RANK(). La diferencia entre ambas estriba en el caso de que dos valores sean iguales. Si dos valores empatan en el primer puesto, RANK() saltará a 3 para la tercera fila, mientras que DENSE_RANK() asignaría la tercera fila como 2.

Para obtener más información sobre las funciones de clasificación, lea nuestro artículo en profundidad Visión general de las funciones de clasificación en SQL.

También puede filtrar las X filas superiores utilizando una expresión común de tabla (CTE) y una cláusula WHERE. Por ejemplo, podemos ejecutar lo siguiente para obtener sólo la fila con la clasificación más baja:

WITH sales_cte AS (
  SELECT
    product_id,
    SUM(amount) AS total_sales,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
  FROM sales
  GROUP BY id 
) 
SELECT * 
FROM sales_cte
WHERE rank = 1;

En esta consulta, definimos una CTE llamada sales_cteque proporciona el rango de cada product_id en función de sus ventas totales. A continuación, consultamos este CTE, extrayendo únicamente las filas en las que el rango = 1. Esto nos permite obtener únicamente las filas con el rango más alto. Esto nos permite devolver sólo la fila superior.

4. Cálculo de la diferencia (delta) entre filas

A veces es necesario comparar los valores de filas consecutivas. Un ejemplo sería obtener las ventas de un mes anterior para compararlas con las del mes actual. Tener tanto las ventas del mes actual como las del mes anterior le permite calcular la diferencia entre los dos valores.

Puede utilizar funciones de ventana como LEAD() y LAG() para lograrlo. Esta vez utilizaremos una tabla diferente: sales_agg. Esta tabla contiene las ventas agregadas de cada año y mes.

Calculemos la diferencia de ventas mes a mes utilizando LAG():

SELECT
  year, 
  month,
  total_sales,
  LAG(total_sales) OVER(ORDER BY year, month) AS previous_month,
  total_sales – LAG(total_sales) OVER(ORDER BY year, month) AS            sales_difference
FROM sales_agg
ORDER BY year, month;

Esta consulta utiliza la función LAG() para calcular la diferencia entre las ventas del mes actual y las ventas del mes anterior:

La función LAG() es una función de ventana. Extrae el valor total_sales de la fila anterior, ordenado por año y mes. Asignamos a esa columna el alias previous_month.

A continuación, la columna sales_difference se calcula tomando el valor total_sales de cada fila y restándole el valor previous_month. La primera fila tiene valores NULL, ya que no hay fila anterior.

Si necesitara extraer las ventas del mes siguiente, utilizaría la función de ventana LEAD() en lugar de LAG(). LAG() compara la fila actual con los valores anteriores; LEAD() compara la fila actual con los valores posteriores.

How to Calculate the Difference Between Two Rows in SQL da múltiples ejemplos reales de uso de esta sintaxis para calcular la diferencia entre dos filas.

5. Cálculo de un total acumulado

Un total acumulado calcula la suma de una secuencia de números. También se conoce como total acumulado o suma acumulada; añade cada nuevo valor al total anterior.

Los totales corridos son útiles para calcular el efecto acumulativo de los puntos de datos a lo largo del tiempo. Por ejemplo, es posible que desee calcular el número acumulado de usuarios que han visitado su sitio web hasta un momento determinado para comprender el crecimiento de usuarios de su sitio web.

Volvamos a la función SUM() y veamos cómo podemos utilizarla para calcular una suma de ventas acumulada. Al igual que en el ejemplo anterior, tendremos que utilizar SUM() como función ventana para obtener los resultados deseados.

SELECT
  year,
  month,
  total_sales,
  SUM(total_sales) OVER(ORDER BY year, month) AS running_total
FROM sales_agg
ORDER BY year, month;

Este total acumulado se calcula utilizando la función SUM() con la cláusula OVER(). Esto añade el total_sales de la fila actual a todas las filas anteriores en el orden especificado.

En la segunda fila, el total acumulado se calcula sumando el valor total_sales de la primera y la segunda fila. En nuestro ejemplo, la segunda fila corresponde a febrero de 2021. El valor running_total es la suma de la primera fila (para enero de 2021) y la segunda fila (para febrero de 2021).

En la tercera fila, el total acumulado se calcula sumando los valores de las filas primera a tercera. Este mismo patrón continúa en cada fila.

Para obtener más información sobre el cálculo de totales acumulados en SQL, consulte nuestro artículo ¿Qué es un total acumulado en SQL y cómo se calcula?

6. Cálculo de una media móvil

Cuando se observan tendencias de ventas en el análisis de datos, a menudo resulta útil utilizar una media móvil en lugar de cada punto de datos individual. Una media móvil (también conocida como media móvil) calcula la media del valor actual y un número determinado de valores inmediatamente anteriores.

Esta técnica ayuda a suavizar los datos e identificar tendencias, sobre todo cuando los datos tienen una gran volatilidad. 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.

Por ejemplo, puede que necesite analizar las ventas diarias de un restaurante que tiene ventas altas el fin de semana pero bajas de lunes a miércoles. Si trazara cada punto de datos individual, vería valores muy altos y muy bajos uno al lado del otro, lo que haría más difícil ver las tendencias a largo plazo. Si utilizamos una media móvil de 3 días, obtendremos la media de los 3 últimos días, lo que equilibra los máximos y los mínimos.

Para este ejemplo, modificaremos nuestra tabla sales_agg.

Ahora vamos a calcular una media móvil de 3 meses de las ventas:

SELECT
  year, 
  month, 
  total_sales, 
  AVG(total_sales) OVER(ORDER BY year, month ROWS BETWEEN 2 PRECEDING and CURRENT ROW) AS moving_average
FROM aales_agg
ORDER BY year, month;

Esta consulta calcula la media móvil utilizando la función AVG() con la cláusula OVER(). La cláusula ROWS BETWEEN especifica el rango de filas a incluir en el cálculo de la media móvil - en este caso, la fila actual y las dos filas precedentes.

El resultado:

Como no hay filas anteriores, la media móvil de la primera fila es sólo el valor total de las ventas. La media móvil de la segunda fila es la media de 300 y 700. Para la tercera fila, ahora tenemos las 2 filas anteriores, tal y como se definen en nuestra consulta; la media móvil se calcula promediando 300, 700 y 500. Este patrón continúa para el resto de las filas.

Qué es una media móvil y cómo calcularla en SQL es un excelente recurso para obtener más información sobre este tema.

7. 7. Recuento de elementos en categorías personalizadas mediante SUM() y CASE WHEN

Puede contar elementos en categorías personalizadas combinando SUM() con CASE WHEN. Esto se utiliza cuando se necesita crear lógica de negocio que no existe en los datos. Por ejemplo, es posible que desee agrupar ubicaciones específicas por regiones personalizadas y, a continuación, calcular métricas basadas en dichas regiones.

CASE WHEN Las sentencias le permiten realizar lógica condicional en las consultas. La sintaxis está estructurada de la siguiente manera:

CASE 
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
END

Las condiciones se evalúan de arriba abajo. La primera condición que se evalúa como verdadera determina qué resultado se devuelve.

En nuestro ejemplo, vamos a crear un desglose de categorías de ventas basado en los importes de las ventas:

SELECT
  SUM(CASE WHEN total_sales < 400 THEN 1 ELSE 0 END) AS low_sales,  
  SUM(CASE WHEN total_sales >= 400 AND total_sales < 700 THEN 1 ELSE 0 END) AS medium_sales,
  SUM(CASE WHEN total_sales >= 700 THEN 1 ELSE 0 END) AS high_sales
FROM sales_agg

En la consulta anterior, utilizamos la expresión CASE WHEN para clasificar cada mes en las categorías low_sales, medium_sales o high_sales. Cuando se cumple la condición de la expresión CASE WHEN, devuelve un valor de 1; de lo contrario, devuelve 0.

A continuación, se utiliza la función SUM() para sumar los 1 y los 0 de cada categoría, contando así el número de meses que pertenecen a cada categoría. El resultado es una única fila con los recuentos de low_sales, medium_sales y high_sales meses.

Este método permite crear categorías personalizadas y contar los elementos de cada categoría con una sola consulta. La combinación de SUM() y CASE WHEN es versátil y puede adaptarse a diferentes casos de uso.

Si desea utilizar esta construcción en su consulta, Cómo utilizar CASE WHEN con SUM() en SQL le proporcionará más detalles.

Potencie su Análisis de Datos con las Consultas SQL avanzado

Dominar las consultas SQL avanzadas es esencial para un análisis de datos eficaz y preciso. En este artículo, hemos tratado siete potentes técnicas SQL que pueden ayudarle a obtener una visión más profunda de sus datos.

Si aprende a agrupar datos por periodos de tiempo, a utilizar ROLLUP para varios niveles de agrupación, a clasificar datos con funciones de ventana, a calcular diferencias entre filas, a calcular totales y medias móviles y a contar elementos en categorías personalizadas, estará bien equipado para abordar tareas complejas de análisis de datos.

A medida que siga perfeccionando sus conocimientos de SQL, encontrará aún más formas de aprovechar esta potente herramienta para liberar todo el potencial de sus conjuntos de datos e impulsar una mejor toma de decisiones en su organización.

Para obtener más práctica y profundizar aún más en estos temas, inscríbase en nuestro curso de SQL Reporting. Es una excelente manera de dominar estas funciones avanzadas de SQL.