Transformera data med avnormalisering och pivoter

Att omforma datastrukturen är en viktig del av att skapa effektiva analyslösningar. Denormalisering, pivotering och opivotering är teknik för datatransformering som hjälper dig att omorganisera data mellan normaliserade och aggregerade format – var och en tillgodoser olika analytiska och modelleringsbehov.

I den här lektionen får du lära dig att platta ut normaliserade tabeller för snabbare frågor, rotera rader i kolumner för korstabellanalys och vända processen när du behöver normalisera breda datamängder.

Förstå avnormalisering

Normaliserade databaser minskar dataredundansen genom att dela information i flera relaterade tabeller. Även om den här designen fungerar bra för transaktionssystem skapar den utmaningar för analys. Frågor måste ansluta många tabeller, vilket minskar prestandan och ökar komplexiteten.

Denormalisering hanterar dessa utmaningar genom att slå samman relaterade tabeller till färre, mer omfattande tabeller. Du kombinerar data från dimensionstabeller direkt till faktatabeller, vilket eliminerar behovet av kopplingar vid frågetillfället.

Överväg en försäljningsdatabas med separata tabeller för beställningar, kunder och produkter. En normaliserad fråga kräver flera kopplingar:

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

En avnormaliserad tabell lagrar den här informationen tillsammans:

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

Frågor mot den avnormaliserade tabellen körs snabbare eftersom inga kopplingar krävs. Den här metoden är vanlig när du skapar star-scheman och data marts för Business Intelligence-verktyg. Kompromissen är ökad lagring och behovet av att uppdatera avnormaliserade tabeller när källdata ändras.

Pivotera rader till kolumner

Pivotering omvandlar unika värden från en kolumn till separata kolumner och konverterar ett "långt" tabellformat till ett "brett" . Den här omvandlingen är särskilt användbar när du förbereder data för kors-tabellrapporter eller dashboards.

PIVOT Satsen kräver en mängdfunktion eftersom flera källrader kan mappas till samma cell i det pivoterade resultatet. Du anger vilka kolumnvärden som ska bli nya kolumnnamn med hjälp av FOR-satserna IN.

Överväg kvartalsvisa försäljningsdata som lagras i ett långt 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;

Den här frågan returnerar intäkter som separata rader för varje kvartal. För att jämföra kvartal sida vid sida, vänd data:

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

Resultatet visar varje regions kvartalsintäkter som kolumner, vilket gör jämförelser från år till år enkla:

Du kan använda flera mängdfunktioner i en enda pivotåtgärd:

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

Detta skapar kolumner för både summan och genomsnittet av varje kvartals intäkter.

Avpivotera kolumner i rader

Avpivoting utför den omvända omvandlingen – den konverterar kolumner tillbaka till rader. Använd unpivoting när du tar emot bredformatsdata som behöver normalisering för analys eller när du förbereder data för system som förväntar sig ett långt format.

Satsen UNPIVOT omvandlar angivna kolumner till värdenamnspar. Du definierar en kolumn som ska innehålla värdena och en annan för att lagra de ursprungliga kolumnnamnen.

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

Resultatet konverterar varje månadskolumn till rader:

Som standard UNPIVOT exkluderar null-värden. Om du vill inkludera dem anger du INCLUDE NULLS:

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

Du kan också tilldela anpassade alias till de opivoterade kolumnnamnen:

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

Välj rätt transformation

Varje transformering betjänar specifika analytiska behov. Använd avnormalisering när du behöver snabba frågeprestanda för instrumentpaneler och rapporter. Välj pivot när analytiker behöver jämförelser sida vid sida mellan kategorier. Använd unpivot när du behöver normalisera breda data för aggregerings- eller maskininlärningspipelines.

Dessa transformeringar fungerar ofta tillsammans i datapipelines. Du kan avpivotera källdata för att standardisera dess format, tillämpa transformeringar och sedan pivotera resultat för slutlig presentation – eller avnormalisera utdata till optimerade tabeller för underordnade konsumenter.