Transformera data med kopplingar och ange operatorer

Datateknikarbetsflöden kräver ofta att du kombinerar information från flera datauppsättningar. Kopplingar kombinerar tabeller vågrätt genom att matcha rader i relaterade kolumner, medan mängdoperatorer kombinerar tabeller lodrätt genom att lägga till, korsa eller exkludera rader. Att förstå när du ska använda varje metod hjälper dig att skapa effektiva transformeringspipelines.

I den här lektionen får du lära dig hur du kopplar tabeller baserat på relationer och tillämpar uppsättningsoperatorer för att kombinera datauppsättningar med matchande scheman.

Kombinera tabeller med kopplingar

Kopplar samman rader från två tabeller baserat på en relaterad kolumn. Kopplingstypen avgör vilka rader som visas i resultatet och hur omatchade rader hanteras.

Förstå kopplingstyper

Varje kopplingstyp har ett specifikt syfte vid datatransformering:

Sammanfogningstyp	Description	Användningsfall
INNER	Returnerar endast matchande rader från båda tabellerna	Länka beställningar till giltiga kunder
LEFT	Returnerar alla vänstra tabellrader; lägger till NULL:er för höger tabell när det inte finns någon matchning	Inkludera alla kunder, även utan beställningar
RIGHT	Returnerar alla högra tabellrader; lägger till NULL-värden för den vänstra tabellen när ingen matchning finns.	Inkludera alla avdelningar, även utan anställda
FULL	Returnerar alla rader från båda tabellerna och lägger till NULL-värden för icke-matchningar.	Kombinera datauppsättningar för att hitta luckor på båda sidor
SEMI	Returnerar vänstra tabellrader som har matchningar i den högra tabellen	Filtrera kunder som har gjort beställningar
ANTI	Returnerar vänster tabellrader som inte har någon matchning i den högra tabellen	Hitta kunder som aldrig gjort beställningar
CROSS	Returnerar kartesisk produkt av alla rader	Generera alla möjliga kombinationer

Koppla tabeller med SQL

JOIN Satsen kombinerar tabeller baserat på ett villkor som anges med ON eller USING. I följande exempel används tabeller för anställda och avdelningar:

CREATE TEMP VIEW employee(id, name, deptno) AS
    VALUES (105, 'Chloe', 5),
           (103, 'Paul',  3),
           (101, 'John',  1),
           (102, 'Lisa',  2),
           (104, 'Evan',  4),
           (106, 'Amy',   6);

CREATE TEMP VIEW department(deptno, deptname) AS
    VALUES (3, 'Engineering'),
           (2, 'Sales'),
           (1, 'Marketing');

Om du vill matcha anställda med deras avdelningar använder du en inre join:

-- Inner join: only employees with matching departments
SELECT e.id, e.name, e.deptno, d.deptname
FROM employee e
INNER JOIN department d ON e.deptno = d.deptno;

En inre koppling returnerar bara tre rader – anställda vars avdelningsnummer finns i avdelningstabellen. Anställda på avdelningarna 4, 5 och 6 visas inte eftersom dessa avdelningar inte finns i avdelningstabellen.

När du behöver inkludera alla anställda oavsett avdelningsmatchningar använder du en vänsterkoppling:

-- Left join: all employees, NULL for missing departments
SELECT e.id, e.name, e.deptno, d.deptname
FROM employee e
LEFT JOIN department d ON e.deptno = d.deptno;

Detta returnerar alla sex anställda. De som saknar matchande avdelningar visar NULL i deptname kolumnen.

Ansluta DataFrames till PySpark

PySpark-metoden join() har samma funktioner. Parametern how anger kopplingstypen:

df_employee = spark.table("employee")
df_department = spark.table("department")

# Inner join
df_inner = df_employee.join(
    df_department,
    on=df_employee.deptno == df_department.deptno,
    how="inner"
)
display(df_inner)

För vänster, höger eller fullständiga yttre sammanfogningar, ändra parametern how:

# Left join: keep all employees
df_left = df_employee.join(
    df_department,
    on="deptno",
    how="left"
)

# Full outer join: keep all rows from both tables
df_full = df_employee.join(
    df_department,
    on="deptno",
    how="full"
)

När kopplingskolumnen har samma namn i båda DataFrames skickar du kolumnnamnet som en sträng. Den här metoden undviker dubbletter av kolumner i resultatet.

Filtrera data med semi- och antikopplingar

Semi- och anti-joins filtrerar data baserat på förekomsten i en annan tabell. En semikoppling returnerar rader från den vänstra tabellen som har matchningar, utan att inkludera kolumner från den högra tabellen:

-- Find employees who belong to listed departments
SELECT *
FROM employee
LEFT SEMI JOIN department ON employee.deptno = department.deptno;

En antikoppling returnerar motsatsen – rader utan matchningar:

-- Find employees in departments not listed
SELECT *
FROM employee
LEFT ANTI JOIN department ON employee.deptno = department.deptno;

I PySpark använder du how="semi" eller how="anti".

# Employees with matching departments
df_semi = df_employee.join(df_department, on="deptno", how="semi")

# Employees without matching departments
df_anti = df_employee.join(df_department, on="deptno", how="anti")

Kombinera rader med uppsättningsoperatorer

Mängdoperatorer fungerar lodrätt och kombinerar rader från frågor med samma kolumnstruktur. Till skillnad från kopplingar matchar inte uppsättningsoperatorer villkor – de staplar eller jämför hela resultatuppsättningar.

Förstå uppsättningsoperatorer

Azure Databricks har stöd för tre uppsättningsoperatorer:

Operator	Description	Beteende
UNION	Kombinerar rader från båda frågorna	Tar bort dubbletter som standard. använda ALL för att behålla dem
INTERSECT	Returnerar rader som finns i båda frågorna	Endast matchande rader visas
EXCEPT	Returnerar rader från den första frågan inte i den andra	Tar bort dubbletter som standard

Båda frågorna måste ha samma antal kolumner med kompatibla datatyper.

Slå samman datauppsättningar med UNION

UNION lägger till rader från en fråga till en annan. Som standard tar den bort dubbletter:

-- Combine active and archived customers (no duplicates)
SELECT customer_id, name, email FROM active_customers
UNION
SELECT customer_id, name, email FROM archived_customers;

Om du vill bevara alla rader, inklusive dubbletter, använder du UNION ALL:

-- Combine all rows, keeping duplicates
SELECT customer_id, name, email FROM active_customers
UNION ALL
SELECT customer_id, name, email FROM archived_customers;

Använd UNION ALL när du vet att det inte finns några dubbletter eller när dubbletter är meningsfulla. Den fungerar bättre eftersom den hoppar över dedupliceringssteget.

I PySpark använder du union() för att kombinera DataFrames. Observera att PySpark union() fungerar som UNION ALL– den behåller dubbletter:

df_active = spark.table("active_customers")
df_archived = spark.table("archived_customers")

# Combine both tables (keeps duplicates)
df_combined = df_active.union(df_archived)

# Remove duplicates if needed
df_distinct = df_active.union(df_archived).distinct()

Hitta vanliga rader med INTERSECT

INTERSECT returnerar endast rader som visas i båda frågorna. Detta hjälper dig att identifiera överlappande data:

-- Find customers in both active and archived tables
SELECT customer_id FROM active_customers
INTERSECT
SELECT customer_id FROM archived_customers;

Den här frågan identifierar kunder som på något sätt visas i båda tabellerna – användbara för datakvalitetskontroller eller identifiering av poster som behöver avstämning.

Exkludera rader med EXCEPT

EXCEPT returnerar rader från den första frågan som inte finns i den andra. Detta hjälper dig att identifiera luckor eller unika poster:

-- Find active customers not in the archived table
SELECT customer_id FROM active_customers
EXCEPT
SELECT customer_id FROM archived_customers;

Du kan också använda MINUS som synonym för EXCEPT:

-- Same result using MINUS syntax
SELECT customer_id FROM active_customers
MINUS
SELECT customer_id FROM archived_customers;

Som UNION, både INTERSECT och EXCEPT ta bort dubbletter som standard. Lägg till ALL för att bevara dubbletter vid behov.

Välj mellan kopplingar och ange operatorer

Valet mellan sammanfogningar och mängdoperatorer beror på transformationsmålet. Sammanfogningar kombinerar vågrätt kolumner från relaterade tabeller, medan mängdoperatorer kombinerar lodrätt rader.

Använd sammanfogningar när du behöver:

• Berika poster genom att lägga till kolumner från relaterade tabeller
• Matcha poster baserat på nyckelrelationer
• Filtrera data baserat på förekomsten i en annan tabell (semi/antikopplingar)

Använd uppsättningsoperatorer när du behöver:

• Lägga till poster från flera källor med identiska scheman
• Hitta gemensamma poster mellan datauppsättningar
• Identifiera poster som är unika för ett dataset

Båda metoderna utgör grunden för effektiva pipelines för datatransformering. I nästa lektion utforskar du hur du läser in transformerade data i måltabeller i Unity Catalog.