Gegevens transformeren met denormalisatie en draaipunten

Het hervormen van gegevensstructuur is een belangrijk onderdeel van het bouwen van effectieve analyseoplossingen. Denormalization, pivoting en unpivoting zijn transformatietechnieken waarmee u gegevens kunt ordenen tussen genormaliseerde en geaggregeerde indelingen, die elk verschillende analytische en modelleringsbehoeften bieden.

In deze les leert u hoe u genormaliseerde tabellen kunt platmaken voor snellere query's, rijen kunt draaien in kolommen voor kruistabellenanalyse en dat proces kunt omkeren wanneer u brede gegevenssets moet normaliseren.

Inzicht in denormalisatie

Genormaliseerde databases verminderen gegevensredundantie door informatie over meerdere gerelateerde tabellen te splitsen. Hoewel dit ontwerp goed werkt voor transactionele systemen, creëert het uitdagingen voor analyses. Query's moeten veel tabellen samenvoegen, waardoor de prestaties worden vertraagd en de complexiteit wordt verhoogd.

Denormalisatie lost deze uitdagingen op door gerelateerde tabellen af te vlakken in minder, bredere tabellen. U combineert gegevens uit dimensietabellen rechtstreeks in feitentabellen, waardoor joins niet meer nodig zijn tijdens query's.

Overweeg een verkoopdatabase met afzonderlijke tabellen voor orders, klanten en producten. Voor een genormaliseerde query zijn meerdere joins vereist:

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

In een gedenormaliseerde tabel worden deze gegevens samen opgeslagen:

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

Query's voor de gedenormaliseerde tabel worden sneller uitgevoerd omdat er geen joins vereist zijn. Deze benadering is gebruikelijk bij het bouwen van stervormige schema's en datamarts voor business intelligence-hulpprogramma's. De afweging is meer opslagruimte en de noodzaak om gedenormaliseerde tabellen te vernieuwen wanneer de brongegevens worden gewijzigd.

Rijen in kolommen draaien

Pivoteren zet unieke waarden uit een kolom om in afzonderlijke kolommen, waardoor een 'lange' tabelindeling wordt omgezet naar een 'brede'. Deze transformatie is met name handig bij het voorbereiden van gegevens voor rapporten of dashboards in meerdere tabellen.

Voor de PIVOT component is een statistische functie vereist, omdat meerdere bronrijen kunnen worden toegewezen aan dezelfde cel in het gedraaide resultaat. U geeft op welke kolomwaarden nieuwe kolomnamen worden met behulp van de FOR en IN componenten.

Bekijk de kwartaalverkoopgegevens die zijn opgeslagen in long format.

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

Deze query retourneert omzet als afzonderlijke rijen voor elk kwartaal. Als u kwartalen naast elkaar wilt vergelijken, pivoteert u de gegevens:

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

Het resultaat toont de kwartaalomzet van elke regio als kolommen, waardoor vergelijkingen per jaar eenvoudig zijn:

U kunt meerdere statistische functies toepassen in één draaibewerking:

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

Hiermee worden kolommen gemaakt voor zowel de som als het gemiddelde van de omzet van elk kwartaal.

Kolommen omvormen tot rijen

Unpivoten voert de omgekeerde transformatie uit: het converteert kolommen weer naar rijen. Gebruik unpivoting wanneer u gegevens in een brede tabelindeling ontvangt die normalisatie nodig hebben voor analytische doeleinden of wanneer u gegevens voorbereidt voor systemen die verwachten dat de lange indeling.

De UNPIVOT clausule transformeert opgegeven kolommen in waardenaamparen. U definieert een kolom voor het opslaan van de waarden en een kolom voor het opslaan van de oorspronkelijke kolomnamen.

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

Het resultaat converteert elke maandkolom naar rijen:

UNPIVOT Standaard worden null-waarden uitgesloten. Als u deze wilt opnemen, geeft u het volgende op INCLUDE NULLS:

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

U kunt ook aangepaste aliassen toewijzen aan de niet-gedraaide kolomnamen.

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

De juiste transformatie kiezen

Elke transformatie dient specifieke analytische behoeften. Gebruik denormalisatie wanneer u snelle queryprestaties nodig hebt voor dashboards en rapporten. Kies pivot wanneer analisten naast elkaar vergelijkingen tussen categorieën nodig hebben. Voer een unpivot uit wanneer u breed gestructureerde data wilt normaliseren voor aggregatie- of machine learning-pijplijnen.

Deze transformaties werken vaak samen in gegevenspijplijnen. U kunt de brongegevens omzetten om de indeling te standaardiseren, transformaties toe te passen en vervolgens de resultaten te pivoteren voor de uiteindelijke presentatie, of de uitvoer denormaliseren in geoptimaliseerde tabellen voor downstream gebruikers.