Transformar datos con desnormalización y pivotes

La reformación de la estructura de datos es una parte clave de la creación de soluciones de análisis eficaces. La desnormalización, la pivotación y la despivotación son técnicas de transformación que ayudan a reorganizar los datos entre formatos normalizados y agregados, cada uno de los cuales sirve a diferentes necesidades analíticas y de modelado.

En esta unidad, aprenderá a aplanar las tablas normalizadas para consultas más rápidas, girar filas en columnas para el análisis entre tabulares e invertir ese proceso cuando necesite normalizar conjuntos de datos anchos.

Comprender la desnormalización

Las bases de datos normalizadas reducen la redundancia de datos dividiendo información entre varias tablas relacionadas. Aunque este diseño funciona bien para los sistemas transaccionales, crea desafíos para el análisis. Las consultas deben combinar muchas tablas, lo que ralentiza el rendimiento y aumenta la complejidad.

La desnormalización aborda estos desafíos al reducir las tablas relacionadas a menos tablas más anchas. Los datos de las tablas de dimensiones se combinan directamente en tablas de hechos, lo que elimina la necesidad de combinaciones en el momento de la consulta.

Considere una base de datos de ventas con tablas independientes para pedidos, clientes y productos. Una consulta normalizada requiere varias combinaciones:

-- Normalized query with multiple joins
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Una tabla desnormalizada almacena esta información juntas:

-- Create a denormalized orders table
CREATE OR REPLACE TABLE sales.orders_denormalized AS
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Las consultas en la tabla desnormalizada se ejecutan más rápido porque no se requieren combinaciones. Este enfoque es común al crear esquemas de estrella y data marts para herramientas de inteligencia empresarial. El inconveniente es aumentar el almacenamiento y la necesidad de actualizar las tablas desnormalizadas cuando cambian los datos de origen.

Transformar filas en columnas

La pivotación transforma valores únicos de una columna en columnas independientes, convirtiendo un formato de tabla largo en uno ancho. Esta transformación es especialmente útil al preparar datos para informes de tablas cruzadas o tableros.

La PIVOT cláusula requiere una función de agregado porque varias filas de origen podrían asignarse a la misma celda en el resultado pivotado. Especifique qué valores de columna se convierten en nuevos nombres de columna mediante las cláusulas FOR y IN.

Considere los datos de ventas trimestrales almacenados en un formato largo:

-- Create sample sales data
CREATE OR REPLACE TEMP VIEW quarterly_sales(year, quarter, region, revenue) AS
VALUES (2024, 1, 'East', 150000),
       (2024, 2, 'East', 175000),
       (2024, 3, 'East', 160000),
       (2024, 4, 'East', 200000),
       (2024, 1, 'West', 180000),
       (2024, 2, 'West', 165000),
       (2024, 3, 'West', 190000),
       (2024, 4, 'West', 210000),
       (2025, 1, 'East', 165000),
       (2025, 2, 'East', 185000);

-- View data in long format
SELECT * FROM quarterly_sales;

Esta consulta devuelve ingresos como filas independientes para cada trimestre. Para comparar trimestres uno al lado del otro, pivotee los datos:

-- Pivot quarters into columns
SELECT year, region, Q1, Q2, Q3, Q4
FROM quarterly_sales
PIVOT (
  SUM(revenue)
  FOR quarter IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4)
);

El resultado muestra los ingresos trimestrales de cada región como columnas, lo que hace que las comparaciones de año a año sean sencillas:

Puede aplicar múltiples funciones de agregación en una sola operación de pivote:

-- Pivot with multiple aggregations
SELECT year, Q1_total, Q1_avg, Q2_total, Q2_avg
FROM (SELECT year, quarter, revenue FROM quarterly_sales)
PIVOT (
  SUM(revenue) AS total,
  AVG(revenue) AS avg
  FOR quarter IN (1 AS Q1, 2 AS Q2)
);

Esto crea columnas para la suma y el promedio de los ingresos de cada trimestre.

Despivotar columnas en filas

La despivotación realiza la transformación inversa: vuelve a convertir las columnas en filas. Utilice la despivotación cuando reciba datos de formato ancho que necesiten normalización para su análisis o cuando prepare datos para sistemas que esperan un formato largo.

La UNPIVOT cláusula transforma las columnas especificadas en pares de nombre de valor. Defina una columna para contener los valores y otro para contener los nombres de columna originales.

-- Create wide-format data
CREATE OR REPLACE TEMP VIEW regional_targets(region, jan, feb, mar, apr) AS
VALUES ('North', 50000, 55000, 52000, 58000),
       ('South', 45000, 48000, 51000, 53000),
       ('East',  60000, 62000, 65000, 68000);

-- Unpivot monthly columns into rows
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (jan, feb, mar, apr)
);

El resultado convierte cada columna mensual en filas:

De forma predeterminada, UNPIVOT excluye valores NULL. Para incluirlos, especifique INCLUDE NULLS:

-- Unpivot while keeping null values
SELECT *
FROM regional_targets
UNPIVOT INCLUDE NULLS (
  target_amount FOR month IN (jan, feb, mar, apr)
);

También puede asignar alias personalizados a los nombres de columnas no pivotadas:

-- Unpivot with custom month labels
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (
    jan AS `January`,
    feb AS `February`,
    mar AS `March`,
    apr AS `April`
  )
);

Elección de la transformación correcta

Cada transformación atiende necesidades analíticas específicas. Use la desnormalización cuando necesite un rendimiento rápido de las consultas para los paneles e informes. Elija pivote cuando los analistas necesiten comparaciones lado a lado entre categorías. Aplique unpivot cuando necesite normalizar datos amplios para flujos de trabajo de agregación o aprendizaje automático.

Estas transformaciones suelen trabajar conjuntamente en canalizaciones de datos. Puede desnormalizar los datos de origen para estandarizar su formato, aplicar transformaciones y luego pivotar los resultados para la presentación final, o desnormalizar la salida en tablas optimizadas para los consumidores posteriores.