Przekształcanie danych za pomocą denormalizacji i przestawnych tabel

Zmiana struktury danych jest kluczowym elementem tworzenia skutecznych rozwiązań analitycznych. Denormalizacja, przestawianie i odprzestawianie to techniki przekształcania, które ułatwiają reorganizację danych między znormalizowanymi i zagregowanymi formatami, spełniające różne potrzeby analityczne i modelowania.

W tej lekcji dowiesz się, jak spłaszczać znormalizowane tabele w celu szybszego wykonywania zapytań, obracać wiersze w kolumny na potrzeby analizy krzyżowej i odwracać ten proces, gdy trzeba znormalizować szerokie zestawy danych.

Zrozumienie denormalizacji

Znormalizowane bazy danych zmniejszają nadmiarowość danych, dzieląc informacje między wieloma powiązanymi tabelami. Chociaż ten projekt działa dobrze w przypadku systemów transakcyjnych, stwarza wyzwania dla analizy. Zapytania SQL muszą łączyć wiele tabel, co spowalnia wydajność systemu i zwiększa złożoność.

Denormalizacja rozwiązuje te wyzwania, spłaszczając powiązane tabele w mniej, szersze tabele. Dane z tabel wymiarów można łączyć bezpośrednio w tabele faktów, eliminując potrzebę sprzężeń w czasie wykonywania zapytań.

Rozważmy bazę danych sprzedaży z oddzielnymi tabelami zamówień, klientów i produktów. Znormalizowane zapytanie wymaga wielu sprzężeń:

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

Zdenormalizowana tabela przechowuje te informacje razem:

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

Zapytania względem tabeli zdenormalizowanej działają szybciej, ponieważ nie są wymagane żadne sprzężenia. Takie podejście jest typowe podczas tworzenia schematów gwiazd i składnic danych dla narzędzi do analizy biznesowej. Kompromisem jest zwiększone zapotrzebowanie na przestrzeń magazynową oraz konieczność odświeżania zdenormalizowanych tabel, gdy zmieniają się dane źródłowe.

Przekształcanie wierszy na kolumny

Przestawianie przekształca unikalne wartości z kolumny w oddzielne kolumny, zmieniając format tabeli z "długiego" na "szeroki". Ta transformacja jest szczególnie przydatna podczas przygotowywania danych do raportów tabelarycznych lub pulpitów nawigacyjnych.

Klauzula PIVOT wymaga funkcji agregującej, ponieważ wiele wierszy źródłowych może zostać zmapowanych do tej samej komórki w wyniku analizy przestawnej. Określasz, które wartości kolumn stają się nowymi nazwami kolumn przy użyciu klauzuli FOR oraz IN.

Rozważ kwartalne dane sprzedaży przechowywane w długim formacie:

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

To zapytanie wyświetla przychód jako oddzielne wiersze dla każdego kwartału. Aby porównać kwartały obok siebie, przestawić dane:

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

Wynik przedstawia kwartalny przychód każdego regionu jako kolumny, co sprawia, że porównania rok do roku są proste:

W jednej operacji przestawnej można zastosować wiele funkcji agregujących.

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

Spowoduje to utworzenie kolumn zarówno dla sumy, jak i średniej przychodu każdego kwartału.

Przekształć kolumny na wiersze

Cofanie przestawiania wykonuje transformację odwrotną — konwertuje kolumny z powrotem na wiersze. Usuń przestawność podczas odbierania danych w szerokim formacie, które wymagają normalizacji na potrzeby analizy lub przygotowywania danych dla systemów, które oczekują długiego formatu.

Klauzula UNPIVOT przekształca określone kolumny w pary value-name. Zdefiniuj kolumnę do przechowywania wartości, a drugą do przechowywania oryginalnych nazw kolumn.

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

Wynik konwertuje kolumnę każdego miesiąca na wiersze:

Domyślnie UNPIVOT wyklucza wartości null. Aby je uwzględnić, określ INCLUDE NULLS:

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

Możesz również przypisać niestandardowe aliasy do nazw kolumn bez przestawnych:

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

Wybieranie właściwej transformacji

Każda transformacja spełnia określone potrzeby analityczne. Użyj denormalizacji, gdy potrzebujesz szybkiej wydajności zapytań dla pulpitów nawigacyjnych i raportów. Wybierz pozycję przestawną, gdy analitycy potrzebują porównań równoległych między kategoriami. Zastosuj odprzegubowanie, kiedy musisz znormalizować szerokie dane dla potoków agregacji lub uczenia maszyn.

Te przekształcenia często współpracują ze sobą w potokach danych. Możesz wycofać przestawienie danych źródłowych w celu standaryzacji ich formatu, zastosowania przekształceń, a następnie przestawić wyniki do ostatecznej prezentacji lub zdenormalizować dane wyjściowe do zoptymalizowanych tabel dla użytkowników końcowych.