Kul med SQL: rekursiva CTE i Postgres

vanliga Tabelluttryck (CTE) är en kraftfull konstruktion inom SQL. I det dagliga samtalet kan du höra CTEs som kallas WITH klausuler. Du kan tänka på CTEs som liknar en vy som bara materialiseras medan den frågan körs och inte existerar utanför den frågan. CTEs kan vara mycket användbara byggstenar för att låta dina stora SQL-frågor vara mer läsbara. Men de kan också användas rekursivt så att du kan skapa några mycket komplexa frågor utan att behöva falla ner till ett procedurspråk som plpgsql eller plv8.

rekursiva CTEs tillåter sig att kallas tills något villkor är uppfyllt. Låt oss hoppa rätt in och utforska en rekursiv CTE—en grundläggande, och använda PostgreSQL självklart-och låt oss dissekera den rekursiva CTE lite längre för att se vad vi kan använda den för:

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

när ovanstående körs får vi följande resultat:

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

med ovanstående kan vi också enkelt göra detta med en generate_series. Men håll dig med oss så ser du de mer komplexa saker vi kan göra som inte är möjliga med generate_series. Låt oss först titta närmare på hur det fungerar.

den första delen du kommer att märka är WITH RECURSIVE. Detta berättar Postgres CTE kan rekursivt kalla sig. Nästa del du kommer att märka är att det tar några parametrar i den. I det här fallet (n) kan det också ta mer än en om du behöver.

flytta längre in i CTE, vi har den första frågan som körs, SELECT 10, som genererar det första värdet. Den andra delen är där allt roligt börjar. UNION ALL anger att vi ska returnera alla poster som produceras från slingan. Då kommer SELECT n+10 FROM tens WHERE n+10<= 100 att fortsätta ringa tens CTE som skapas tills villkoret är uppfyllt.

så det är grunderna, men den intressanta frågan är: när skulle du använda en rekursiv CTE? När du har ett träd eller en hierarkisk struktur för dina data kan rekursiva CTE göra livet mycket enklare än att ladda alla dina data och köra en slinga i din kod. För applikationer som handlar om e-handel och shoppingkategorier är rekursiva CTE en stor hjälp.

Låt oss försöka göra fördelarna med rekursiva CTEs i Postgres lite mer konkreta, med ett exempel. Först ska vi skapa en tabell över anställda, då ska vi ladda upp några exempel anställda. (Ja, du ser några av namnen från vårt team här på Citus Data på den här listan. De var de första Namnen som kom att tänka 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 ska jag skriva en fråga som ger mig alla rapporter som rullar upp till en viss organisation inom företaget. I det här fallet kommer jag att få mig själv och alla mina rapporter, tillsammans med varje persons chefs-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ästa gång du behöver göra någon rekursiv beräkning över dina data, överväga att göra det direkt i SQL i motsats till att ladda Alla data i din ansökan. För vidare läsning överväga att ta en titt på några av dessa användbara resurser:

• PostgreSQL docs på CTEs
• lösa resande säljare problemet med en CTE
• att få ett träd med alla sina barn

som en anteckning CTEs vid denna tidpunkt är en optimering staket i PostgreSQL, men det finns förhoppningar om att förändras i framtiden. Vanliga Tabeluttryck är ett otroligt användbart verktyg för rapportering. Ibland uppväger läsbarheten för CTEs prestandapåverkan, men överväga avvägningarna när du använder dem

• kul med sql
• Postgres
• tips