5 construcciones SQL avanzadas que todo analista de datos debe aprender

¿Su trabajo implica el análisis de datos? Aquí tienes cinco ejemplos de cómo el SQL avanzado puede ayudarte en tu trabajo diario.

Soy analista de datos y tengo que decir que los analistas de datos pueden ser bastante extraños. Otras personas suelen tener miedo a las inmensas cantidades de datos; nosotros disfrutamos con ellas. Cuanto más nos adentramos en los datos, más felices somos. Una herramienta importante para adentrarse en los datos (y, por tanto, para ser un analista de datos más feliz) es SQL.

¿Recuerdas la primera vez que descubriste las funciones simples de SQL? ¿La alegría que sentiste cuando pudiste seleccionar rápidamente los datos, agruparlos y ordenarlos? Si ahora dominas esas funciones básicas, probablemente te estés preguntando qué es el SQL avanzado y cómo puede ayudarte a alcanzar nuevos niveles de manejo de datos y creación de informes geniales. Al fin y al cabo, los analistas de datos se aburren cuando trabajan y no juegan.

Bueno, no me detendré en la definición. La mejor manera de explicar el SQL avanzado es mostrarte algunas consultas avanzadas. Entonces verás lo útiles (y divertidas) que pueden ser.

Como voy a utilizar muchas funciones de ventana de SQL, es mejor que ya sepas qué son y cómo funcionan. Este artículo te pondrá al día si no estás familiarizado con ellas.

Consulta 1: Clasificación de los datos

A la dirección le gusta ver las clasificaciones de absolutamente todo: productos vendidos, salarios, empleados por departamento, dinero ganado por cualquier segmento imaginable... siempre pedirán verlo clasificado. Para mostrarte un ejemplo de cómo clasificar cosas en SQL, usaré la tabla sales tabla. Tiene las siguientes columnas:

• product - El nombre del producto.
• product_price - El precio del producto.
• items_sold - El número de artículos vendidos.

La idea es calcular los ingresos de cada producto y clasificarlos mediante la función RANK(). El código de abajo resolverá esta tarea:

SELECT	product,
		product_price,
		items_sold,
		product_price * items_sold AS revenue,
		RANK() OVER (ORDER BY product_price * items_sold DESC) AS revenue_rank
FROM sales;

El código anterior selecciona todas las columnas de la tabla. Para obtener los ingresos por producto, hay que multiplicar el precio por los artículos vendidos. Esto es exactamente lo que hará la consulta, y el resultado se mostrará en la nueva columna ingresos. Por último, está la función RANK(). Aquí, esta función clasificará todas las filas por la nueva columna ingresos (definida por product_price * items_sold). El rango se mostrará en la nueva columna revenue_rank.

Y aquí está el resultado:

RANK() es sólo una forma de clasificar los datos en SQL. Puedes ver las otras formas en este artículo. Y, si quieres profundizar en el tema, aquí tienes nuestra guía completa de funciones de clasificación.

Consulta 2: Cálculo de valores delta

Junto con la clasificación, el cálculo de los valores delta es probablemente una de las tareas más comunes de los analistas de datos. Suele ser necesario cuando se calculan cambios diarios, mes a mes, trimestre a trimestre o año a año. Ya sean ingresos, costes, cambios de precio, cambios de volumen o cualquier otra cosa imaginable, tendrá que calcular la diferencia entre las cifras. Para ello, lo que necesita es una consulta SQL avanzada con la función LAG(). Esta función se utiliza para recuperar los datos de una fila anterior. Te mostraré cómo funciona utilizando la tabla revenue tabla. La tabla consta de dos filas:

• month - El mes del año.
• revenue - Los ingresos de ese mes.

Su tarea consiste en calcular la diferencia entre los ingresos de cada mes y los del mes anterior (es decir, el delta de ingresos mensual). ¿Cómo lo harías? Si conoces la función LAG(), es muy fácil. Aquí está el código:

SELECT	month,
		revenue,
		revenue - LAG(revenue, 1) OVER (ORDER BY month) AS monthly_delta
FROM revenue;

El principio para calcular el delta es deducir el mes anterior del mes actual. El código anterior hace exactamente eso. Primero, selecciona las columnas mes e ingresos. Después, deduce el importe de los ingresos del mes actual del mes anterior. Esto está definido por la función LAG(). Los valores que ponemos en el paréntesis de la función (revenue, 1) indican que el valor de la columna ingresos se deducirá del valor anterior de esa columna. Por eso está el número 1; define cuántas filas retrocede la función para realizar la operación.

En teoría, LAG(revenue) hará lo mismo, ya que retroceder una fila es el valor por defecto de la función LAG(). Sin embargo, quería mostrarte esto explícitamente. Es más fácil de entender y sabrás qué hacer cuando necesites retroceder más de una fila.

Los deltas deben ser calculados secuencialmente, no por meses al azar; por eso existe ORDER BY month. El delta se mostrará en la nueva columna monthly_delta. Ejecute el código y obtendrá la tabla resultante:

Consulta 3: Cálculo de totales corridos

Los totales corridos (también conocidos como sumas acumulativas) se utilizan ampliamente en el análisis de datos. Suelen utilizarse con datos de series temporales para ver cómo evolucionan (o evolucionarán) ciertos indicadores de rendimiento a lo largo del tiempo. Al igual que otros conceptos avanzados de SQL, los totales acumulados tienen un uso práctico muy amplio. Se utilizan para controlar las ventas, los ingresos, los costes, los beneficios y los presupuestos. Aquí hay un artículo que explica muy bien los totales corrientes y cómo calcularlos en SQL.

Por ahora, te mostraré cómo funciona una suma acumulada para los presupuestos. Vamos a utilizar una tabla llamada de forma muy imaginativa budget. Está formada por estas columnas:

• month - El mes del flujo de caja.
• client - El nombre del cliente.
• cash_flow - El flujo de caja presupuestado.

Hay tres clientes. El presupuesto contiene proyecciones mensuales del flujo de caja anual que su empresa cobrará de ellos. Tiene que calcular el flujo de caja acumulado para cada cliente. Es posible que ya sepa intuitivamente que tiene que utilizar la función SUM(), pero con algún tipo de giro. He aquí cómo calcular los totales acumulados:

SELECT	month,
		client,
		cash_flow,
		SUM (cash_flow) OVER (PARTITION BY client ORDER BY month) AS running_total

FROM budget;

Esta es una pequeña y simple consulta que hace maravillas. Selecciona las columnas mes, cliente y cash_flow. Para calcular el total acumulado, hay que resumir los flujos de caja. Esto se define en SUM (cash_flow).

Sin embargo, no te interesa tener totales corridos a nivel de tabla. Necesitas de alguna manera hacer que se resuman los flujos de caja mes a mes para el primer cliente, y luego reiniciar y empezar de nuevo para el segundo cliente. Para ello, necesita OVER (PARTITION BY client ORDER BY month). Aquí la partición es definida por el cliente de la columna, lo que significa que cada conjunto de datos es definido por los diferentes clientes. Además, la operación se realizará sólo dentro de la partición, no en toda la tabla. De esta manera, se obtiene un total de ejecución para cada cliente por separado.

Por supuesto, los flujos de caja tienen que ser resumidos secuencialmente; por eso está ordenado por la columna del mes. El total acumulado aparecerá en la nueva columna total_corrido.

Esta es la tabla resultante:

He utilizado funciones de ventana en los tres últimos ejemplos. Si quieres aprender más sobre este tema, una buena manera es el curso Funciones de ventana , uno de nuestros cursos de SQL avanzado.

Algo que también puede ser muy útil, especialmente si eres nuevo en las funciones de ventana o las usas sólo ocasionalmente, es esta hoja de trucos de SQL Funciones de ventana . La usaré la próxima vez que escriba sobre funciones de ventana, ¡seguro!

Consulta 4: Creación de un informe basado en múltiples condiciones

Una de las principales tareas de los analistas de datos es hacer que los datos sean más amigables para otros usuarios. Al darles los datos en una forma que puedan utilizar fácilmente, les facilitamos el trabajo. Para crear informes útiles, un analista de datos tiene que combinar la información del negocio con su conocimiento de los datos. Una de las herramientas que puede ayudarle a conseguirlo es la sentencia CASE, que es otro concepto avanzado de SQL.

Para dar un ejemplo, imaginemos el siguiente escenario. Trabajas en un banco y tus compañeros te piden que crees un informe. Hay una tabla llamada debt que muestra los clientes del banco y los detalles sobre su deuda. La tabla consta de las siguientes columnas:

• client - El nombre del cliente.
• date_due - El día de vencimiento de la deuda.
• amount_due - El importe de la deuda vencida.

Lo que tienes que hacer es crear un informe a partir del 30.4.2020. De alguna manera necesitas calcular el número de días de vencimiento a partir de la fecha del informe. Además, necesitas asignar al cliente a un determinado bucket de tiempo, de acuerdo con el número de días que su cuenta está vencida.

La consulta está justo debajo. No tengas miedo - lo analizaré por ti. No es tan temible como parece.

SELECT	client,
		date_due,
		amount_due,
		DATEDIFF ('2020-04-30', date_due) AS days_due,
		CASE
	WHEN  DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 90 AND DATEDIFF ('2020-04-30', date_due) <=180 THEN '91-180 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 180 AND DATEDIFF ('2020-04-30', date_due) <=365 THEN '181-365 days'
	ELSE '> 365 days'
END AS time_bucket

FROM debt;

En primer lugar, hay que especificar la parte de la consulta SELECT. He seleccionado las columnas existentes cliente, fecha_deuda y cantidad_deuda.

A continuación, tienes que calcular los días de vencimiento. Para ello, hay que restar la fecha de vencimiento a la fecha de notificación. Esto es exactamente lo que he hecho con DATEDIFF ('2020-04-30', date_due) AS days_due. He utilizado la función DATEDIFF() para calcular la diferencia requerida. Cuando se utiliza esta función, primero hay que especificar qué fechas se quieren restar. En nuestro caso, se trata de la fecha de notificación y la fecha de vencimiento. A continuación, tiene que especificar cómo quiere que se muestre el resultado, es decir, en años, meses o días. En este caso necesitas días, así que pones día como último valor en DATEDIFF().

Ahora viene la parte emocionante: crear las condiciones que he utilizado en la sentencia CASE. Esta sentencia se abre con CASE y termina con END. Entre medias, tienes que definir las condiciones que crearán el informe que tus compañeros quieren. Para ello, utilizarás WHEN y THEN.

Digamos que el primer cubo de los días de vencimiento es de 0 a 30 días. La primera condición en la sentencia CASE es WHEN DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'. Ya que necesita asignar clientes a un cubo de tiempo de acuerdo con los días de vencimiento, esta parte del código hace exactamente eso. Se lee así: si la diferencia entre la fecha de reporte y la fecha de vencimiento es menor o igual a 30 días, entonces este cliente será asignado al cubo de tiempo 0-30 días.

El siguiente bucket de tiempo es el de 31-90 días, y esta es la parte del código que lo define:

WHEN DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'

No es tan complicado, ¿verdad?

El mismo principio funciona para los dos tramos restantes: 91-180 días y 181-365 días. Todas las deudas que han vencido hace más de 365 días pertenecen al cubo de tiempo de 365 días. Esto se define en ELSE '> 365 days'. Esto simplemente define los criterios para la presentación de informes: si el valor es este, haz esto; si no lo es, haz esto. Esencialmente, es una versión más compleja de la sentencia IF.

Tenga en cuenta que hay una forma más elegante de escribir este código: Podría haber declarado una variable que contenga el valor '2020-04-30' en lugar de escribir '2020-04-30' manualmente en todo el código. Sin embargo, no quería confundirte si no estás familiarizado con las variables.

Además, con respecto a la función DATEDIFF(), ten en cuenta que he utilizado la función y la sintaxis de MySQL. Dependiendo del motor de base de datos que utilices, es posible que tengas que adaptar la sintaxis en consecuencia.

Todos esos buckets de tiempo se mostrarán en la nueva columna time_bucket. Como quieres que tus datos se vean bien, ordenarás tu tabla por days_due de forma ascendente. Ejecute el código y obtendrá una bonita tabla. ¡Y probablemente un café gratis de tus colegas!

Emocionante, ¿verdad? Si quieres más emoción como ésta, Cómo crear informes básicos con SQL es para ti. Allí aprenderá más sobre CASE WHEN y los matices de GROUP BY.

Consulta 5: Añadir subtotales a un informe

Una petición muy común es mostrar subtotales y totales en el mismo informe. La cláusula ROLLUP hace esto mucho más fácil. Es una extensión de la cláusula GROUP BY. Le permite añadir subtotales y totales generales a sus datos.

Así es como se usa ROLLUP. Tienes la tabla warehouse con las siguientes columnas:

• warehouse - El nombre del almacén.
• brand - La marca del producto.
• product - El nombre del producto.
• quantity - La cantidad de este producto en el almacén.

Hay dos marcas diferentes con cinco productos entre ellas. Y hay dos almacenes. Tu tarea es calcular la cantidad total de productos de ambas marcas en ambos almacenes. También necesita el total general de todos los productos en ambos almacenes. Y por último, necesitas hacerlo todo en una sola tabla con una sola consulta. ¿Cómo lo harías? El código es:

SELECT	warehouse,
		brand,
		SUM (quantity) AS sum_product

FROM warehouse
GROUP BY ROLLUP (warehouse, brand);

En primer lugar, selecciona las columnas almacén y marca de la tabla. También quieres la suma de la columna cantidad, que se mostrará en la nueva tabla sum_product. ¿Cuál es el siguiente paso? ¡Aquí es cuando entra ROLLUP! Se utiliza para obtener los totales de múltiples niveles de agrupación de datos. La parte GROUP BY ROLLUP (warehouse, brand) hará exactamente eso. Agrupará los datos por las columnas de almacén y marca. Después, sumará los datos de acuerdo a cada agrupación. El resultado es:

La tabla contiene los totales de las marcas Brando y Ostap en los almacenes de Ámsterdam y Berlín y un total general. El subtotal de ambos productos en el almacén de Ámsterdam aparece en la primera fila con el valor de la marca NULL. Asciende a 64 039, la suma de las dos filas anteriores.

A continuación, se muestran los totales de ambas marcas en el almacén de Berlín. A continuación, hay otra línea con el valor de la marca NULL; se trata en realidad del subtotal de Berlín, que asciende a 80 807. La última fila muestra el total general de todos los productos en todos los almacenes, que es de 144 846.

¿Por qué hay valores NULL en algunas filas? Porque SQL no sabe cómo nombrar las marcas y los almacenes cuando están agrupados y se muestra un subtotal o un gran total. Para encontrar más detalles divertidos sobre otras extensiones de GROUP BY, consulte nuestro curso de extensiones GROUP BY.

Esta es la última consulta SQL avanzada por ahora.

¿Le han resultado útiles estos temas de Ejercicio de SQL?

Ejercicio de SQL como lo usan los científicos de datos, los analistas de datos y otros es un tema realmente amplio. Existen enormes posibilidades en el uso de SQL para el análisis de datos. Este tema de SQL Avanzado te dará una idea de lo que puedes hacer con SQL como analista de datos.

En este artículo, he intentado mostrarte algunos de los usos más comunes de SQL avanzado. Los ejemplos son prácticos y están sacados de mi experiencia, así que espero que sean útiles. He intentado que las consultas sean lo más fáciles (y comprensibles) posible. Puedes adaptar estas consultas a tus informes y conjuntos de datos particulares. No dude en utilizarlas.

Si tienes alguna pregunta o comentario, házmelo saber en la sección de comentarios.