Transformieren von Daten mit Denormalisierung und Pivots

Die Neugestaltung der Datenstruktur ist ein wichtiger Bestandteil der Erstellung effektiver Analyselösungen. Denormalisierung, Pivoting und Unpivoting sind Transformationstechniken, die Ihnen helfen, Daten zwischen normalisierten und aggregierten Formaten neu zu organisieren – jeweils für unterschiedliche analyse- und modellierungsanforderungen.

In dieser Lektion erfahren Sie, wie Sie normalisierte Tabellen für schnellere Abfragen verkleinern, Zeilen in Spalten für tabellarische Analysen drehen und diesen Prozess umkehren, wenn Sie breite Datasets normalisieren müssen.

Grundlegendes zur Denormalisierung

Normalisierte Datenbanken reduzieren Datenredundanz, indem Informationen auf mehrere verknüpfte Tabellen aufgeteilt werden. Dieses Design eignet sich zwar gut für Transaktionssysteme, schafft aber Herausforderungen für Analysen. Abfragen müssen viele Tabellen verknüpfen, was die Leistung verlangsamt und die Komplexität erhöht.

Die Denormalisierung behebt diese Herausforderungen, indem verwandte Tabellen in weniger, breitere Tabellen vereinfacht werden. Sie kombinieren Daten aus Dimensionstabellen direkt in Faktentabellen, wodurch die Notwendigkeit von Verknüpfungen zur Abfragezeit wegfällt.

Erwägen Sie eine Vertriebsdatenbank mit separaten Tabellen für Bestellungen, Kunden und Produkte. Eine normalisierte Abfrage erfordert mehrere Verknüpfungen:

-- 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;

Eine denormalisierte Tabelle speichert diese Informationen zusammen:

-- 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;

Abfragen für die denormalisierte Tabelle werden schneller ausgeführt, da keine Verknüpfungen erforderlich sind. Dieser Ansatz ist beim Erstellen von Sternschemas und Data Marts für Business Intelligence-Tools üblich. Der Kompromiss ist erhöhter Speicher und die Notwendigkeit, denormalisierte Tabellen zu aktualisieren, wenn sich Quelldaten ändern.

Pivotieren von Zeilen in Spalten

Beim Pivoting werden eindeutige Werte aus einer Spalte in separate Spalten umgewandelt, wobei ein "langes" Tabellenformat in eine "breite" konvertiert wird. Diese Transformation ist besonders hilfreich bei der Vorbereitung von Daten für Kreuztabellenberichte oder Dashboards.

Die PIVOT Klausel erfordert eine Aggregatfunktion, da mehrere Quellzeilen der gleichen Zelle im pivotierten Ergebnis zugeordnet werden können. Mit den FOR Und-Klauseln IN geben Sie an, welche Spaltenwerte zu neuen Spaltennamen werden.

Betrachten Sie vierteljährliche Umsatzdaten, die in einem langen Format gespeichert sind:

-- 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;

Diese Abfrage gibt Umsatz als separate Zeilen für jedes Quartal zurück. Um Quartale nebeneinander zu vergleichen, pivotieren Sie die Daten:

-- 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)
);

Das Ergebnis zeigt den vierteljährlichen Umsatz der einzelnen Regionen als Spalten an, wodurch der Vergleich von Jahresumsätzen vereinfacht wird.

Sie können mehrere Aggregatfunktionen in einem einzelnen Pivotvorgang anwenden:

-- 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)
);

Dadurch werden Spalten für die Summe und den Durchschnitt des Umsatzes jedes Quartals erstellt.

Spalten in Zeilen umwandeln

Durch das Aufheben der Pivotierung wird die Umgekehrte Transformation ausgeführt. Sie konvertiert Spalten wieder in Zeilen. Verwenden Sie das Aufheben der Pivotierung, wenn Sie Breitformatdaten empfangen, die eine Normalisierung für die Analyse oder beim Vorbereiten von Daten für Systeme benötigen, die ein langes Format erwarten.

Die UNPIVOT Klausel wandelt die angegebenen Spalten in Wertenamenpaare um. Sie definieren eine Spalte, die die Werte enthält, und eine weitere, um die ursprünglichen Spaltennamen zu enthalten.

-- 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)
);

Das Ergebnis konvertiert jede Monatsspalte in Zeilen:

Schließt standardmäßig UNPIVOT NULL-Werte aus. Geben Sie Folgendes an INCLUDE NULLS, um sie einzuschließen:

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

Sie können auch benutzerdefinierte Aliase den Namen der nicht pivotierten Spalten zuweisen:

-- 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`
  )
);

Auswählen der richtigen Transformation

Jede Transformation dient speziellen analytischen Anforderungen. Verwenden Sie die Denormalisierung, wenn Sie eine schnelle Abfrageleistung für Dashboards und Berichte benötigen. Wählen Sie Pivot aus, wenn Analysten parallele Vergleiche über Kategorien hinweg benötigen. Wenden Sie die Entpivotung an, wenn Sie breite Daten für Aggregations- oder Machine Learning-Pipelines normalisieren müssen.

Diese Transformationen arbeiten häufig in Datenpipelines zusammen. Sie können die Quelldaten entpivotieren, um das Format zu standardisieren, Transformationen anzuwenden und dann Ergebnisse für die endgültige Präsentation zu pivotieren – oder die Ausgabe in optimierte Tabellen für nachgelagerte Verbraucher zu denormalisieren.