Prestandaoptimering och frågeoptimering i PostgreSQL · Lektion

Rekursiva CTE:er och graf frågeuttryck

Utforska hur frågor som använder rekursiva CTE:er kan optimeras för hierarkiska data och graftraversering.

Lektion 2 av 411 steg

Rekursiva CTE:er och graf frågeuttryck är en gratis lektion i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit. Detta är lektion 2 av 4. Du kan läsa vilka 3 lektioner som helst i den här lärvägen kostnadsfritt i sin helhet – därefter låser CoddyKit PRO upp alla lektioner, plus praktisk övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Den ingår i lärvägen för Prestandaoptimering och frågeoptimering i PostgreSQL, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Vad är hierarkiska data?

Många verkliga datamängder har en naturlig hierarki. Tänk på ett organisationsschema där medarbetare rapporterar till chefer, eller en materialförteckning där komponenter består av underkomponenter.

Vanliga SQL-frågor kan ha svårt att effektivt navigera i dessa relationer över flera nivåer utan komplexa, nästlade underfrågor eller kopplingar. Det är här rekursiva Common Table Expressions verkligen kommer till sin rätt!

Möt rekursiva CTE:er

En Common Table Expression (CTE) fungerar som en tillfällig, namngiven resultatuppsättning som Ni kan referera till i en enda SQL-sats. De förbättrar läsbarheten och strukturerar komplexa frågor.

En rekursiv CTE är speciell eftersom den kan referera till sig själv, vilket gör att den upprepade gånger kan köras för att bearbeta hierarkiska eller grafliknande data. Den passar perfekt för problem av typen ”hitta alla efterkommande” eller ”följ en väg”.

Utgångspunkten: basmedlemmen

Varje rekursiv CTE har två huvuddelar som kombineras med UNION ALL. Den första är basmedlemmen.

Denna icke-rekursiva del definierar den ursprungliga uppsättningen rader för rekursionen. Det är ”roten” eller startpunkten för genomgången. Tänk på den som det första steget på Er väg genom data.

Vi använder en tabell med namnet employees, med employee_id, name och manager_id.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

WITH RECURSIVE subordinates AS (
  SELECT employee_id, name, manager_id, 0 AS level
  FROM employees
  WHERE employee_id = 1
)
SELECT * FROM subordinates;

Iteration med den rekursiva medlemmen

Den andra delen är den rekursiva medlemmen. Denna del refererar till själva CTE:n (subordinates i vårt exempel) och kopplar ihop den med bastabellen (employees) för att hitta nästa datanivå.

Den körs upprepade gånger och bearbetar resultaten från föregående iteration tills inga nya rader returneras. Detta är den ”steg för steg”-del av genomgången.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

WITH RECURSIVE subordinates AS (
  -- Base Member
  SELECT employee_id, name, manager_id, 0 AS level
  FROM employees
  WHERE employee_id = 1

  UNION ALL

  -- Recursive Member
  SELECT e.employee_id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates WHERE level = 1; -- Just showing the first recursive step

Så avslutas rekursionen

En rekursiv CTE behöver ett sätt att stoppa! Rekursionen avslutas automatiskt när den rekursiva medlemmen inte producerar några nya rader. Om den fortsatte att hitta nya rader för alltid skulle Ni få en oändlig loop!

Det är viktigt att kopplingsvillkoret och filtren i den rekursiva medlemmen så småningom slutar matcha rader, så att frågan kan slutföras. I vårt exempel upphör den när det inte finns fler medarbetare vars manager_id matchar ett employee_id som hittats hittills.

Följ ett organisationsschema

Nu sätter vi ihop allt för att hitta alla underställda till ”Alice” (medarbetar-ID 1), tillsammans med deras rapporteringsnivå.

Basmedlemmen börjar med Alice. Den rekursiva medlemmen hittar sedan Alices direktrapporter (nivå 1), därefter deras rapporter (nivå 2) och så vidare tills inga fler underställda hittas.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

WITH RECURSIVE subordinates AS (
  SELECT employee_id, name, manager_id, 0 AS level
  FROM employees
  WHERE employee_id = 1

  UNION ALL

  SELECT e.employee_id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT employee_id, name, level
FROM subordinates
ORDER BY level, employee_id;

`UNION ALL` för bättre prestanda

Ni kanske undrar varför vi använder UNION ALL och inte bara UNION.

UNION ALL: Kombinerar alla rader från båda resultatuppsättningarna, inklusive dubbletter. Det är vanligtvis snabbare eftersom dubbletter inte behöver identifieras och tas bort.
UNION: Kombinerar rader och tar bort alla dubbletter. I en rekursiv CTE kan kontrollen av dubbletter medföra betydande kostnader och är ofta onödig om logiken säkerställer unika vägar eller element på varje nivå.

Vid rekursiva genomgångar föredras UNION ALL nästan alltid, såvida Ni inte specifikt behöver ta bort dubbletter som logiken kan skapa.

Navigera i grafer: vänners vänner

Rekursiva CTE:er är också kraftfulla för genomgång av grafer. Föreställ Er att Ni hittar alla kopplingar i ett socialt nätverk eller följer beroenden.

Vi använder en enkel tabell med namnet connections för att hitta alla personer som är kopplade till ”Alice” (ID 1) upp till två nivåer bort.

CREATE TABLE connections (
  person_id INT,
  connected_to_id INT
);

INSERT INTO connections (person_id, connected_to_id) VALUES
(1, 2), -- Alice -> Bob
(1, 3), -- Alice -> Charlie
(2, 4), -- Bob -> David
(3, 5), -- Charlie -> Eve
(4, 6), -- David -> Frank
(5, 7); -- Eve -> Grace

WITH RECURSIVE path_finder AS (
  SELECT person_id AS start_node,
         connected_to_id AS end_node,
         1 AS depth
  FROM connections
  WHERE person_id = 1

  UNION ALL

  SELECT pf.start_node, c.connected_to_id, pf.depth + 1
  FROM connections c
  JOIN path_finder pf ON c.person_id = pf.end_node
  WHERE pf.depth < 2 -- Limit depth to avoid infinite loops or excessive recursion
)
SELECT DISTINCT start_node, end_node, depth
FROM path_finder
ORDER BY depth, end_node;

Optimera rekursiva frågor

Rekursiva CTE:er kan vara kraftfulla, men också resurskrävande om de inte hanteras väl. Här följer några tips:

Begränsa djupet: Inkludera alltid ett avslutningsvillkor för djupet (som level < max_depth) för att förhindra oändliga loopar eller överdrivet långa frågor.
Indexera nycklar: Säkerställ att kolumner som används i kopplingsvillkor (till exempel employee_id, manager_id, person_id och connected_to_id) är indexerade.
Filtrera tidigt: Tillämpa filter i basmedlemmen för att minska den ursprungliga datamängden.
Undvik cykler: Om data kan innehålla cykler (till exempel A -> B -> A) kan Ni behöva spåra den väg som tagits (till exempel en array med besökta noder) för att förhindra oändliga loopar. PostgreSQL 14+ erbjuder satsen CYCLE för detta.

Rekursiv CTE-struktur

Anta att en rekursiv CTE används för att hitta alla delar i en stycklista, med utgångspunkt från en färdig produkt. CTE:n heter bom_path.

Vilket av följande beskriver korrekt struktur och syfte för den rekursiva medlemmen i denna CTE?

Besvara frågan i den interaktiva lektionen

Rekursiva CTE:er: repetition

Ni har utforskat kraften i rekursiva CTE:er!

De är nödvändiga för att fråga hierarkiska data (till exempel organisationsscheman) och genomföra graftraverseringar (till exempel för att hitta vägar eller kopplingar).
En rekursiv CTE består av en basmedlem (startpunkt) och en rekursiv medlem (iterativt steg), som kombineras med UNION ALL.
Rekursionen avslutas när den rekursiva medlemmen inte producerar några nya rader, men det är ofta god praxis att lägga till en djupbegränsning.
Överväg alltid att indexera relevanta kolumner och filtrera tidigt för bästa prestanda.

När ni behärskar rekursiva CTE:er öppnas nya möjligheter att fråga komplexa och sammanlänkade dataset i PostgreSQL!

Gratis att börja

Lär dig SQL med en AI-lärare – gratis

Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.

Kurser: 22
Lektioner: 88

Börja lära dig gratis Visa hela kursen

Vanliga frågor

Är lektionen ”Rekursiva CTE:er och graf frågeuttryck” gratis?

Ja – du kan läsa vilka 3 lektioner som helst i lärvägen Prestandaoptimering och frågeoptimering i PostgreSQL, inklusive ”Rekursiva CTE:er och graf frågeuttryck”, kostnadsfritt i sin helhet här på webben. Därefter låser CoddyKit PRO upp alla lektioner, plus interaktiv övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Vad lär jag mig i ”Rekursiva CTE:er och graf frågeuttryck”?

Utforska hur frågor som använder rekursiva CTE:er kan optimeras för hierarkiska data och graftraversering. Ni övar på Prestandaoptimering och frågeoptimering i PostgreSQL med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.

Behöver jag någon erfarenhet för att börja lära mig Prestandaoptimering och frågeoptimering i PostgreSQL?

Du behöver inga förkunskaper. Utbildningen i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 2 av 4.

Hur lång tid tar lektionen ”Rekursiva CTE:er och graf frågeuttryck”?

De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.

Kan jag skriva och köra kod i den här Prestandaoptimering och frågeoptimering i PostgreSQL-lektionen?

Ja. Varje Prestandaoptimering och frågeoptimering i PostgreSQL-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.

Alla lektioner i den här kursen

Optimera aggregat och fönsterfunktioner
Rekursiva CTE:er och graf frågeuttryck
Använda materialiserade vyer för bättre prestanda
Optimera frågor med FILTER och villkorsstyrd aggregering

← Tillbaka till Prestandaoptimering och frågeoptimering i PostgreSQL