Moro MED SQL :Rekursive CTEs I Postgres

Common Table Expressions (CTEs) er en kraftig konstruksjon innen SQL. I dag til dag samtale, kan du høre CTEs referert til som WITH klausuler. Du kan tenke På Cte-Er som ligner på en visning som bare materialiseres mens spørringen kjører og ikke finnes utenfor spørringen. CTEs kan være svært nyttige byggesteiner for å la dine store SQL-spørringer være mer lesbare. Men de kan også brukes rekursivt slik at du kan lage noen svært komplekse spørringer uten å måtte slippe ned til et prosessspråk som plpgsql eller plv8.

Rekursive Cte tillater seg å bli kalt til noen betingelse er oppfylt. La oss hoppe rett inn og utforske en rekursiv CTE—en grunnleggende, og bruk PostgreSQL selvfølgelig-og la oss dissekere rekursiv CTE litt lenger for å se hva vi kan bruke 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 ovennevnte kjøres, får vi følgende resultat:

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

med det ovenfor kan vi ogsa enkelt gjore dette med en generate_series. Men hold deg til oss, og du vil se de mer komplekse tingene vi kan gjøre som ikke er mulige med generate_series. La oss først se nærmere på hvordan det fungerer.

den første delen du vil legge merke til er WITH RECURSIVE. Dette forteller Postgres AT CTE kan rekursivt kalle seg selv. Den neste delen du vil legge merke til er at det tar noen parametere inn i den. I dette tilfellet (n) kan det også ta mer enn en bør du trenger.

Flytter videre inn I CTE, vi Har den første spørringen som utføres, SELECT 10, som genererer den første verdien. Den andre delen er hvor all moroa begynner. UNION ALL angir at vi skal returnere alle postene som er produsert fra løkken. Deretter vil SELECT n+10 FROM tens WHERE n+10<= 100 fortsette å ringe tens CTE som er opprettet til betingelsen er oppfylt.

så det er grunnleggende, men det interessante spørsmålet er: når vil du bruke en rekursiv CTE? Når du har et tre eller hierarkisk struktur til dataene dine, kan rekursive CTEs gjøre livet mye enklere enn å laste alle dataene dine og kjøre en sløyfe i koden din. For programmer som omhandler e-handel og shopping kategorier, rekursive CTEs er en stor hjelp.

La oss prøve å gjøre fordelene med rekursive CTEs I Postgres litt mer konkret, med et eksempel. Først skal vi lage et bord med ansatte, så skal vi laste opp noen eksempelansatte. (Ja, du vil se noen av navnene fra teamet vårt her På Citus Data på denne listen. De var de første navnene som kom til tankene.)

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

Nå skal jeg skrive en spørring som gir meg alle rapportene som ruller opp i en bestemt org i selskapet. I dette tilfellet skal jeg få meg selv og alle mine rapporter, sammen med hver persons leder 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) 

Neste gang du trenger å gjøre noen rekursiv beregning på tvers av dataene dine, bør du vurdere å gjøre det direkte i SQL i motsetning til å laste inn alle dataene i søknaden din. For videre lesing vurdere å ta en titt på noen av disse nyttige ressurser:

• PostgreSQL docs on CTEs
• Løse det reisende salgsproblemet med En CTE
• Å Få et tre med alle det er barn

Som et notat CTEs på dette tidspunktet er et optimaliseringsgjerde I PostgreSQL, selv om det er håp om at det endrer seg i fremtiden. Vanlige Tabelluttrykk er et utrolig nyttig verktøy for rapportering. Til tider oppveier Lesbarheten Av CTEs ytelsen, men vurder avveiningene når du bruker dem

• moro med sql
• Postgres
• tips