🔀 MERGE & SCD

Scalanie danych, Slowly Changing Dimensions Typ 1, 2, 3

⚡ Kluczowe fakty do zapamiętania

→MERGE = UPSERT (Update + Insert). W jednym poleceniu: sprawdza czy rekord istnieje (ON klucz), jeśli tak — UPDATE (WHEN MATCHED), jeśli nie — INSERT (WHEN NOT MATCHED). Bez MERGE trzeba by pisać dwa osobne zapytania z IF/ELSE.
→Tylko jeden WHEN MATCHED i jeden WHEN NOT MATCHED. Nie możesz mieć dwóch WHEN MATCHED. Logikę warunkową umieść wewnątrz sekcji — np. CASE w UPDATE SET, lub WHERE warunek po INSERT.
→DELETE WHERE w WHEN MATCHED usuwa wiersze PO wykonaniu UPDATE. Warunek DELETE dotyczy wartości PO aktualizacji, nie przed. Przydatne do 'soft delete' — zaktualizuj status, a potem usuń nieaktywne.
→SCD Typ 1 = nadpisz, historia ginie. Najprościej. Stara wartość jest bezpowrotnie zastąpiona. Używaj gdy historia zmian jest nieważna (np. poprawka literówki).
→SCD Typ 2 = pełna historia w nowych wierszach. Stary wiersz dostaję date_to = dziś i is_current = 0. Nowy wiersz ma date_from = dziś, date_to = 9999-12-31, is_current = 1. Wymaga SURROGATE KEY (sekwencja), bo jeden klient biznesowy może mieć wiele wierszy.
→SCD Typ 3 = tylko jedna poprzednia wartość. Tabela ma kolumny: current_dept i prev_dept. Przy zmianie: przesuń current → prev, wpisz nowe do current. Historia ograniczona — pamiętasz tylko jedną wersję wstecz.

MERGE — składnia

MERGE (zwany też "UPSERT") sprawdza czy rekord istnieje i w zależności od wyniku wykonuje UPDATE lub INSERT. Wszystko w jednym poleceniu.

sql

MERGE INTO tabela_docelowa d
USING tabela_zrodlowa s
ON (d.klucz = s.klucz)            -- warunek dopasowania
WHEN MATCHED THEN                  -- wiersz istnieje w docelowej
    UPDATE SET d.kol1 = s.kol1,
               d.kol2 = s.kol2
    [DELETE WHERE warunek]         -- opcjonalny DELETE pasujących
WHEN NOT MATCHED THEN              -- wiersz NIE istnieje w docelowej
    INSERT (kol1, kol2)
    VALUES (s.kol1, s.kol2)
    [WHERE warunek];               -- opcjonalny filtr dla INSERT

Przykład podstawowy

sql

-- Synchronizacja tabeli docelowej ze źródłem:
MERGE INTO hr.employees_target t
USING hr.employees_source s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET t.salary       = s.salary,
               t.department_id = s.department_id
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, salary, department_id)
    VALUES (s.employee_id, s.first_name, s.last_name, s.salary, s.department_id);

MERGE z DELETE

sql

-- Usuń dopasowane wiersze, które są już nieaktywne:
MERGE INTO employees_active t
USING employees_updates s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET t.status = s.status
    DELETE WHERE t.status = 'INACTIVE'   -- usuwa wiersze po UPDATE, gdy status = INACTIVE
WHEN NOT MATCHED THEN
    INSERT (employee_id, status) VALUES (s.employee_id, s.status);

⚠️

MERGE — tylko jeden WHEN MATCHED i jeden WHEN NOT MATCHED

Oracle pozwala na dokładnie jeden WHEN MATCHED THEN i jeden WHEN NOT MATCHED THEN. Nie można mieć dwóch WHEN MATCHED. Logikę warunkową umieść wewnątrz sekcji (np. CASE w UPDATE SET).

Slowly Changing Dimensions (SCD) — typy

Typ	Strategia	Historia	Złożoność
Typ 1	Nadpisanie starej wartości	Brak (stara wartość znika)	Prosta
Typ 2	Nowy wiersz z datą ważności	Pełna (wszystkie wersje)	Średnia
Typ 3	Dodatkowa kolumna "poprzednia wartość"	Tylko poprzednia wersja	Prosta

⚖️ Porównanie wyników— SCD Typ 1 vs Typ 2 vs Typ 3 — jak wygląda tabela wymiaru po zmianie działu klienta

Typ 1 — nadpisanie (historia ginie)

CUST_ID	NAME	DEPT	UPDATED
5	Nowak	Marketing	2024-01-15

Typ 2 — nowy wiersz (pełna historia)

KEY	CUST_ID	NAME	DEPT	DATE_FROM	DATE_TO	CURRENT
1	5	Nowak	Sales	2020-01-01	2024-01-14	N
2	5	Nowak	Marketing	2024-01-15	9999-12-31	Y

💡 Typ 1: jeden wiersz, stara wartość (Sales) ZNIKNĘŁA bezpowrotnie. Typ 2: dwa wiersze — stary z DATE_TO i CURRENT=N, nowy z CURRENT=Y. Historia zachowana. Typ 3 (nie pokazany): jeden wiersz z kolumnami current_dept='Marketing' i prev_dept='Sales'.

SCD Typ 1 — nadpisanie (MERGE)

sql

-- Gdy pracownik zmienia dział — po prostu aktualizuj:
MERGE INTO dim_employees t
USING employees_changes s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET t.department_name = s.department_name,
               t.salary = s.salary       -- stara wartość przepada!
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, department_name, salary)
    VALUES (s.employee_id, s.first_name, s.department_name, s.salary);

SCD Typ 2 — historia przez nowe wiersze

Każda zmiana tworzy nowy wiersz. Stary wiersz jest "zamykany" (data końca = dziś). Nowy wiersz ma date_from = dziś, date_to = NULL (lub 9999-12-31).

sql

-- Tabela z kolumnami historii:
-- dim_employees: employee_key (surrogate PK), employee_id (biznesowy),
--                department_name, salary, date_from, date_to, is_current

-- Krok 1: Zamknij stare wiersze (UPDATE):
UPDATE dim_employees
SET date_to   = SYSDATE - 1/86400,  -- sekundy wcześniej
    is_current = 0
WHERE employee_id IN (SELECT employee_id FROM employees_changes)
  AND is_current = 1;

-- Krok 2: Wstaw nowe wiersze:
INSERT INTO dim_employees
    (employee_key, employee_id, department_name, salary, date_from, date_to, is_current)
SELECT
    dim_employees_seq.NEXTVAL,
    s.employee_id,
    s.department_name,
    s.salary,
    SYSDATE,
    TO_DATE('9999-12-31', 'YYYY-MM-DD'),
    1
FROM employees_changes s;

ℹ️

SCD Typ 2 — klucz zastępczy (surrogate key)

Tabela wymiarów musi mieć surrogate key (np. employee_key z sekwencji), bo może istnieć wiele wierszy dla tego samego employee_id. Tabela faktów łączy się przez surrogate key, nie przez klucz biznesowy.

SCD Typ 3 — poprzednia wartość w kolumnie

sql

-- Tabela z kolumną "poprzednia":
-- dim_employees: employee_id, department_name, prev_department_name

-- Przy zmianie działu: przesuń current → prev, wstaw nowy current:
UPDATE dim_employees
SET prev_department_name = department_name,  -- archiwizuj starą
    department_name      = 'Nowy Dział'       -- wstaw nową
WHERE employee_id = 101;

-- Tylko JEDNA poprzednia wartość jest pamiętana — starsze historycznie znikają

Kiedy który typ?

sql

-- Typ 1: zmiana nazwy z powodu literówki — historia nieważna
-- Typ 2: zmiana działu pracownika — ważne do analiz historycznych
-- Typ 3: kategoria produktu z możliwością porównania "teraz vs poprzednio"

Pytania egzaminacyjne

🔤

Pytanie ABCD

Co robi MERGE z klauzulą DELETE WHERE status = 'INACTIVE' w sekcji WHEN MATCHED?

🔤

Pytanie ABCD

Ile sekcji WHEN MATCHED może być w jednym poleceniu MERGE?

🔤

Pytanie ABCD

SCD Typ 2 wymaga surrogate key (klucz zastępczy) ponieważ...

🔤

Pytanie ABCD

Jaka jest główna różnica między SCD Typ 1 a SCD Typ 2?

🔍 Czy to się wykona?

MERGE INTO employees_target t
USING employees_source s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary
WHEN MATCHED THEN
    UPDATE SET t.department_id = s.department_id;

🔍 Czy to się wykona?

MERGE INTO employees_target t
USING employees_source s
ON (t.employee_id = s.employee_id)
WHEN NOT MATCHED THEN
    INSERT (employee_id, salary)
    VALUES (s.employee_id, s.salary)
    WHERE s.salary > 3000;

🔎 Znajdź błąd — wskaż niepoprawną linię

1MERGE INTO employees_target t

2USING employees_source s

3ON (t.employee_id = s.employee_id)

4WHEN MATCHED THEN

5 DELETE WHERE t.salary < 3000

6WHEN NOT MATCHED THEN

7 INSERT (employee_id, salary) VALUES (s.employee_id, s.salary);

Ćwiczenia

💪

Ćwiczenie 1 — MERGE: upsert pracowników

Masz tabelę employees_staging z nowymi/zmienionymi danymi. Wykonaj MERGE do hr.employees_copy: jeśli pracownik istnieje — zaktualizuj salary i department_id; jeśli nie — wstaw nowy rekord.

💪

Ćwiczenie 2 — SCD Typ 2: historia zmian działu

Pracownik 101 zmienił dział z 'Sales' na 'Marketing'. Pokaż SQL dla SCD Typ 2: zamknij stary wiersz (ustaw date_to na wczoraj, is_current=0) i wstaw nowy wiersz z aktualnym działem.

💪

Ćwiczenie 3 — MERGE z DELETE

Wykonaj MERGE, który: aktualizuje pensję gdy pracownik istnieje, usuwa pracownika jeśli po aktualizacji ma status='INACTIVE', wstawia nowych pracowników gdy nie istnieją.