Tablas Dinámicas SQL: Tutorial Paso a Paso

¿Eres analista de datos y quieres mejorar tus habilidades? ¿O quizás quieras descubrir qué herramientas necesitas para tener éxito en este papel? Hoy vamos a ver una de las herramientas más importantes para los analistas de datos: la tabla dinámica SQL.

Una tabla dinámica SQL es una herramienta esencial para organizar y resumir datos rápidamente. Con sólo unas pocas líneas de código, ayuda a los analistas a identificar rápida y eficazmente patrones y tendencias en los datos. Esto se consigue pivotando o rotando las filas y columnas de la tabla y aplicando un cálculo agregado a los datos subyacentes.

Si es la primera vez que crea informes SQL, consulte nuestro curso Cómo crear informes básicos con SQL curso. Aprenderás a escribir consultas complejas en SQL. Entre las muchas funciones que se enseñan en este curso está la sintaxis CASE WHEN, que es esencial para construir tablas pivotantes.

Antes de entrar en el tema de las tablas dinámicas, asegúrate de tener a mano nuestra hoja de trucos de SQL para análisis de datos. Puede utilizarla para refrescar algunas de las funciones que vamos a tratar.

Cómo funcionan las tablas dinámicas SQL en el análisis de datos

Las tablas dinámicas SQL son una herramienta útil para reorganizar, resumir y analizar rápidamente grandes cantidades de información. Es posible que ya haya trabajado con herramientas como Microsoft Excel y Google Sheets, que ofrecen la funcionalidad de tablas dinámicas integradas, lo que le permite realizar fácilmente este tipo de transformaciones.

Pongamos un ejemplo. Imagina que tienes una tabla que muestra los datos de streaming de música de un artista en varias plataformas musicales y países.

Tabla:

La disposición de estos datos dificulta la comparación, sobre todo cuando aumenta el número de países y plataformas.

Imaginemos que pudiéramos estructurar los datos de forma que los streams de cada país se desglosaran por plataforma y se sumaran colectivamente. Entonces podríamos responder a preguntas como "¿Qué plataforma es más popular en cada país?" y "¿En qué plataforma y país deberíamos centrar más esfuerzos?" Esta nueva estructura podría tener este aspecto:

Resultado:

El nuevo formato de la tabla hace que sea mucho más fácil comparar los streams del artista entre plataformas y países. Ahora puedes ver de un vistazo qué plataforma funciona mejor en cada país: Spotify en Francia e Irlanda, y SoundCloud en Alemania. El nuevo formato permite un análisis y una comprensión más rápidos, mostrando el rendimiento geográfico y específico de cada plataforma sin tener que rebuscar manualmente entre las filas de datos.

Los ingredientes de una tabla dinámica SQL

Primero vamos a desglosar los componentes básicos de la tabla dinámica definiéndolos con un ejemplo. Utilizaremos la misma tabla que en la sección anterior para ilustrarlo:

• Identificador de fila: Este es el primer ingrediente; es una columna seleccionada de su conjunto de datos para representar las filas de la tabla pivotante (es decir, los registros individuales). En el ejemplo anterior, hemos utilizado la columna platform como identificador de fila, por lo que cada fila contendrá los datos de una plataforma.
• Identificador de columna: El segundo ingrediente es una columna que se selecciona para ser la parte vertical de la tabla pivotante. En nuestro ejemplo, hemos utilizado la columna país como identificador de columna; los países se enumerarán horizontalmente, como cabeceras en la parte superior de la tabla de resultados.
• Agregación: Ahora que tenemos la parte vertical y horizontal de nuestra tabla, la agregación es el ingrediente que determina cómo interactúan los datos subyacentes en la fila y el identificador de columna. Se suelen utilizar funciones de agregación como SUM(), AVG() y MAX(). En nuestro ejemplo, hemos utilizado SUM() para calcular los flujos totales de cada plataforma en cada uno de los países.
• Tabla dinámica: La tabla dinámica es el plato final. Resume los datos introducidos pivotando filas en columnas con una función de agregación. Al resumir eficazmente los datos, la tabla dinámica nos ayuda a visualizar lo que estamos analizando de una forma más estructurada. En el ejemplo anterior, vemos que el resultado final de nuestras entradas muestra los flujos por cada plataforma en los países enumerados en nuestra tabla original.

Tablas dinámicas nativas

Dependiendo de la base de datos que esté utilizando, puede tener una tabla dinámica nativa . Básicamente, esto significa que existe una función integrada que la base de datos utiliza para crear tablas pivotantes SQL. SQL Server es un gran ejemplo de una base de datos que ofrece una función PIVOT().

Vamos a crear una tabla pivotante utilizando la función PIVOT() de SQL Server para resumir datos de flujo. Estos son los datos que utilizaremos almacenados en una tabla llamada streams_table:

Consulta:

SELECT platform, France, Ireland, Germany
FROM
(
    SELECT platform, country, streams
    FROM streams_table
) AS SourceTable
PIVOT
(
    SUM(streams)
    FOR country IN (France, Ireland, Germany)
) AS PivotTable;

Vamos a desglosar esta consulta. Empezaremos con la subconsulta.

1. Definir la tabla de origen

SELECT platform, country, streams
FROM streams_table

Las líneas 4-6 (inmediatamente después del paréntesis abierto) definen la tabla SourceTable a través de una subconsulta. La tabla fuente tiene tres columnas indicadas en la sentencia SELECT (platform, country y streams) que proceden de la consulta inicial streams_table.

2. Remodelación de los datos mediante operaciones pivotantes

SUM(streams)
FOR country IN (France, Ireland, Germany)

A continuación - dentro de los paréntesis de la función PIVOT() - definimos nuestro PivotTable aplicando la función agregada SUM() sobre la columna streams. Esto suma el número de flujos de cada plataforma. A continuación, especificamos sobre qué columna pivotar: country.

Obsérvese que los valores de los países se indican sin comillas. Tenga en cuenta también que en SQL Server, los nombres de columna que contienen espacios o caracteres especiales o que empiezan por un número deben ir entre corchetes. Si tuviéramos una columna que contuviera años, tendría el siguiente aspecto:

SUM(streams)
FOR stream_year IN ([2022], [2023])

3. Selección de las columnas finales

SELECT platform, France, Ireland, Germany

Por último, volvemos al principio de la consulta y seleccionamos las columnas de salida. Esto incluye la columna platform y las columnas recién formadas para cada país.

Resultado:

No todos los lenguajes SQL tienen incorporadas las funciones PIVOT(). Cuando esta función no está disponible en tu base de datos, hay otra técnica sencilla que podemos aplicar para evitarlo. Vamos a explorarla.

Creación de pivotes SQL sin tablas PIVOT() nativas

Como se mencionó anteriormente, no todas las bases de datos-incluyendo las bien conocidas como MySQL y PostgreSQL-tienen capacidades nativas de pivote. Tal vez recuerde que en nuestra introducción mencionamos la sintaxis CASE WHEN. Utilizando esta sintaxis, podemos obtener exactamente el mismo resultado.

Dado que MySQL y PostgreSQL son bases de datos ampliamente utilizadas, la técnica CASE WHEN es un método ampliamente utilizado para crear tablas pivote. Veamos algunos ejemplos:

Ejemplo 1: Pivoteando Datos en Flujo

Utilizaremos el mismo ejemplo para demostrar cómo el enfoque CASE WHEN puede producir los mismos resultados:

Consulta:

SELECT platform,
    SUM(CASE WHEN country = 'France' THEN streams END) AS france_streams,
    SUM(CASE WHEN country = 'Ireland' THEN streams END) AS ireland_streams,
    SUM(CASE WHEN country = 'Germany' THEN streams END) AS germany_streams
FROM streams_table
GROUP BY platform;

Las tablas pivotantes SQL resultantes muestran los flujos totales por plataforma para los países enumerados.

• Identificador de fila: Dado que seleccionamos la columna platform, cada plataforma aparece como una columna en el resultado. También observará que incluimos platform en nuestro GROUP BY para separar nuestras plataformas en grupos.
• Identificador de columna: Para especificar los países como columnas verticales, utilizamos la estructura CASE WHEN country = 'France'. Si el flujo es de Francia, la sentencia CASE WHEN devuelve el flujo. Luego se suma con la función SUM(). Si el flujo no es de Francia, entonces la sentencia CASE WHEN devuelve NULL y los valores no se cuentan en esa suma y la consulta pasa a la siguiente CASE WHEN. Consulte nuestro artículo sobre Cómo utilizar CASE WHEN en GROUP BY para obtener una explicación más detallada.
• Agregación: Al igual que antes, utilizamos SUM() como función de agregación. La lógica de nuestra sentencia case establece que los flujos se acumulan si el país es el mismo que el país dado; si no, no se acumula nada.

Utilicemos la misma tabla para la demostración, pero invirtamos los identificadores de fila y columna y veamos qué ocurre. T

Consulta:

SELECT country,
    SUM(CASE WHEN platform = 'Apple Music' THEN streams END) AS apple_music_streams,
    SUM(CASE WHEN platform = 'SoundCloud' THEN streams END) AS soundcloud_streams,
    SUM(CASE WHEN platform = 'Spotify' THEN streams END) AS spotify_streams
FROM streams_table
GROUP BY country;

Resultado:

Como puede ver, el resultado de la agregación sigue siendo el mismo. El único cambio es la forma en que se muestran los datos:

• Identificador de fila: Esta vez, utilizamos country como identificador de fila, por lo que cada país aparece como una fila horizontal en el resultado. También incluimos country en la cláusula GROUP BY para asegurarnos de que los resultados se agrupan correctamente por país, lo que da lugar a filas separadas para los datos de flujo totales de cada país.
• Identificador de columna: Modificamos nuestra identificación de columna a plataforma, para que las plataformas aparezcan como columnas verticales en el resultado. Esto se consigue con la función SUM(CASE WHEN platform =...)
• Agregación: Al igual que en la última consulta, los resultados numéricos son los mismos porque hemos utilizado el mismo método de agregación (SUM()).

Para ver más ejemplos de la agregación CASE WHEN con SUM(), consulte nuestro artículo Cómo utilizar CASE WHEN con SUM() en SQL.

Ejemplo 2: Pivoteo de Datos de Clientes

A continuación, veamos cómo las tablas pivotantes pueden ayudarnos a descubrir segmentos de mercado objetivo.

En customer_table describe datos de ventas de clientes con cuatro columnas: customer, age_category, country y purchases:

Consulta:

SELECT age_category,
    SUM(CASE WHEN country = 'France' THEN purchases END) AS France,
    SUM(CASE WHEN country = 'Spain' THEN purchases END) AS Spain,
    SUM(CASE WHEN country = 'Italy' THEN purchases END) AS Italy
FROM customer_table
GROUP BY age_category;

Resultado:

Las tablas dinámicas SQL resultantes muestran las compras totales por categoría de edad en Francia, España e Italia donde:

• El identificador de fila es la columna age_category.
• El identificador de columna es country, especificando France, Spain y Italy.
• La función agregada es SUM().

Ejemplo 3: Operaciones pivotantes de datos

Veamos ahora un ejemplo de cómo el uso de tablas pivotantes SQL puede descubrir posibles cuellos de botella o fallos en las operaciones.

La tabla siguiente, cake_baking_datadescribe los datos de la panadería utilizando cuatro columnas: order_number, stage, order_day y mins_taken:

Tabla:

Supongamos que queremos encontrar el tiempo medio que tarda cada tarea en cada día. Esta es la consulta que utilizaríamos.

Consulta:

SELECT 
    order_day,
    AVG(CASE WHEN stage = 'Mixing' THEN mins_taken END) AS avg_mixing_time,
    AVG(CASE WHEN stage = 'Baking' THEN mins_taken END) AS avg_baking_time,
    AVG(CASE WHEN stage = 'Decorating' THEN mins_taken END) AS avg_decorating_time
FROM cake_baking_data
GROUP BY order_day;

Resultado:

La tabla dinámica resultante muestra los tiempos medios por día de pedido para mezclar, hornear y decorar donde:

• El identificador de fila es la columna order_day.
• El identificador de columna es la etapa, especificando mixing, baking, o decorating.
• La función agregada es AVG().

Como puede ver, la solución de CASE WHEN le permite permanecer dentro de las populares bases de datos MySQL y PostgreSQL y aún así conseguir las mismas tablas pivotantes SQL.

Unas palabras finales sobre las tablas pivotantes SQL

En resumen, está claro por qué las tablas dinámicas SQL son tan importantes. Ser capaz de pivotar los datos es una herramienta importante en su caja de herramientas de análisis de datos. La capacidad de transformar y resumir conjuntos de datos de forma rápida y eficaz le permite descubrir tendencias y tomar decisiones más inteligentes.

En este artículo, nos hemos centrado principalmente en el pivotaje para analistas de datos. Si te interesa la carrera de análisis de datos, te recomiendo que le eches un vistazo a nuestro artículo 25 preguntas de entrevista SQL para analistas de datos. Te ayudará a entender lo que necesitas saber para tener éxito en este campo.

La mejor manera de mejorar tu SQL es practicar, practicar y practicar. Recomendamos nuestro curso Cómo crear informes básicos con SQL para ello; no sólo podrás practicar lo que hemos aprendido hoy, sino que también aprenderás a crear informes significativos. Así que échale un vistazo y sigue mejorando tus habilidades con las tablas dinámicas SQL.