Rekursive CTE ‘er i Postgres

almindelige Tabeludtryk (CTE’ er) er en kraftig konstruktion inden for kvm. I den daglige samtale kan du høre CTE ‘ er kaldet WITH klausuler. Du kan tænke på CTE ‘ er som ligner en visning, der kun materialiseres, mens forespørgslen kører og ikke findes uden for forespørgslen. CTE ‘ er kan være meget nyttige byggesten til at lade dine store forespørgsler være mere læsbare. Men de kan også bruges rekursivt, så du kan oprette nogle meget komplekse forespørgsler uden at skulle falde ned til et proceduremæssigt sprog som f.eks.

rekursive CTE ‘ er tillader sig at blive kaldt, indtil en eller anden betingelse er opfyldt. Lad os hoppe lige ind og udforske en rekursiv CTE—en grundlæggende, og brug selvfølgelig Postgraver-og lad os dissekere den rekursive CTE lidt længere for at se, hvad vi kan bruge den til:

WITH RECURSIVE tens (n) AS ( SELECT 10 UNION ALL SELECT n+10 FROM tens WHERE n+10<= 100 ) SELECT n FROM tens; 

når ovenstående køres, får vi følgende resultat:

 n ----- 10 20 30 40 50 60 70 80 90 100 (10 rows) 

med ovenstående kunne vi også nemt gøre dette med en generate_series. Men hold dig til os, og du vil se de mere komplekse ting, vi kan gøre, som ikke er mulige med generate_series. Lad os først se nærmere på, hvordan det fungerer.

den første del du vil bemærke er WITH RECURSIVE. Dette fortæller Postgres, at CTE rekursivt kan kalde sig selv. Den næste del, du vil bemærke, er, at det tager nogle parametre ind i det. I dette tilfælde (n) kan det også tage mere end en, hvis du har brug for det.

når vi går videre ind i CTE, har vi den første forespørgsel, der udføres, SELECT 10, som genererer den første værdi. Den anden del er, hvor alt det sjove begynder. UNION ALL angiver, at vi skal returnere alle de poster, der er produceret fra sløjfen. Derefter fortsætter SELECT n+10 FROM tens WHERE n+10<= 100 med at kalde tens CTE, der oprettes, indtil betingelsen er opfyldt.

så det er det grundlæggende, men det interessante spørgsmål er: Hvornår vil du bruge en rekursiv CTE? Når du har et træ eller en hierarkisk struktur til dine data, kan rekursive CTE ‘ er gøre livet meget lettere end at indlæse alle dine data og køre en løkke i din kode. For applikationer, der beskæftiger sig med e-handel og shoppingkategorier, er rekursive CTE ‘ er en stor hjælp.

lad os prøve at gøre fordelene ved rekursive CTE ‘ er i Postgres lidt mere konkrete med et eksempel. Først skal vi oprette en tabel over medarbejdere, så skal vi indlæse nogle eksempler på medarbejdere. (Ja, du kan se nogle af Navnene fra vores team her på Citus Data på denne liste. De var de første Navne, der kom til at tænke på.)

CREATE TABLE employees ( id serial, name varchar(255), manager_id int ); INSERT INTO employees VALUES (1, 'Umur', null); INSERT INTO employees VALUES (2, 'Craig', 1); INSERT INTO employees VALUES (3, 'Daniel', 2); INSERT INTO employees VALUES (4, 'Claire', 1); INSERT INTO employees VALUES (5, 'Lindsay', 2); INSERT INTO employees VALUES (6, 'Will', 2); INSERT INTO employees VALUES (7, 'Burak', 2); INSERT INTO employees VALUES (8, 'Eren', 2); INSERT INTO employees VALUES (9, 'Katie', 3); INSERT INTO employees VALUES (10, 'Teresa', 4); 

nu skal jeg skrive en forespørgsel, der giver mig alle de rapporter, der ruller op i en bestemt organisation i virksomheden. I dette tilfælde vil jeg få mig selv og alle mine rapporter sammen med hver persons manager-id:

WITH RECURSIVE managertree AS ( SELECT id, name, manager_id FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN managertree mtree ON mtree.id = e.manager_id ) SELECT * FROM managertree; id | name | manager_id ----+---------+------------ 2 | Craig | 1 3 | Daniel | 2 5 | Lindsay | 2 6 | Will | 2 7 | Burak | 2 8 | Eren | 2 9 | Katie | 3 (7 rows) 

næste gang du skal foretage en rekursiv beregning på tværs af dine data, skal du overveje at gøre det direkte i SDR i modsætning til at indlæse alle disse data i din applikation. For yderligere læsning overveje at tage et kig på nogle af disse nyttige ressourcer:

• postgraduate docs on CTEs
• løsning af det rejsende sælgerproblem med en CTE
• at få et træ med alle dets børn

som en note CTEs på dette tidspunkt er et optimeringshegn i postgraduate, selvom der er håb om, at det ændrer sig i fremtiden. Almindelige Tabeludtryk er et utroligt nyttigt værktøj til rapportering. Til tider opvejer læsbarheden af CTE ‘ er præstationspåvirkningen, men overvej afvejningerne, når du bruger dem

• sjov med kvm
• Postgres
• tips