Plezier met SQL: recursieve CTE ’s in Postgres

Common Table Expressions (CTE’ s) zijn een krachtige constructie binnen SQL. In het dagelijkse gesprek kunt u CTE ‘ s horen die WITH worden genoemd. Je kunt CTE ‘ s zien als vergelijkbaar met een weergave die alleen wordt gematerialiseerd terwijl die query draait en niet buiten die query bestaat. CTE ’s kunnen zeer nuttige bouwstenen zijn voor het toestaan van uw grote SQL-query’ s om leesbaarder te zijn. Maar, ze kunnen ook recursief worden gebruikt zodat u een aantal zeer complexe query ‘ s te maken zonder te vallen naar een procedurele taal zoals plpgsql of plv8.

recursieve CTE ‘ s laten zich aanroepen totdat aan een bepaalde voorwaarde is voldaan. Laten we er meteen in springen en een recursieve CTE verkennen—een basic, en met behulp van PostgreSQL natuurlijk – en dan laten we de recursieve CTE een beetje verder ontleden om te zien waarvoor we het kunnen gebruiken:

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

wanneer het bovenstaande wordt uitgevoerd krijgen we het volgende resultaat:

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

met het bovenstaande kunnen we dit ook gemakkelijk doen met een generate_series. Maar blijf bij ons en je ziet de meer complexe dingen die we kunnen doen die niet mogelijk zijn met generate_series. Laten we eerst eens kijken hoe het werkt.

het eerste deel dat u ziet is WITH RECURSIVE. Dit vertelt Postgres dat de CTE zichzelf recursief kan aanroepen. Het volgende gedeelte dat je zult merken is dat het een aantal parameters in het neemt. In dit geval (n) kan er ook meer dan één nodig zijn.

verder in de CTE, hebben we de eerste query die wordt uitgevoerd, SELECT 10, die de eerste waarde genereert. Het tweede deel is waar al het plezier begint. De UNION ALL geeft aan dat we alle records gaan retourneren die uit de lus worden geproduceerd. Dan zal SELECT n+10 FROM tens WHERE n+10<= 100 de CTE tens blijven aanroepen totdat aan de voorwaarde is voldaan.

dat zijn de basisprincipes, maar de interessante vraag is: wanneer zou je een recursieve CTE gebruiken? Wanneer u een boom of hiërarchische structuur aan uw gegevens, recursieve CTEs kan het leven veel gemakkelijker dan het laden van al uw gegevens en het uitvoeren van een lus in uw code. Voor toepassingen die zich bezighouden met E-commerce en winkelen categorieën, recursieve CTE ‘ s zijn een grote hulp.

laten we proberen de voordelen van recursieve CTE ‘ s in Postgres wat concreter te maken, met een voorbeeld. Eerst maken we een tabel met werknemers, dan laden we wat voorbeeldwerknemers op. (Ja, u ziet enkele namen van ons team hier bij Citus Data op deze lijst. Dat waren de eerste Namen die in me opkwamen.)

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 ga ik een query schrijven die me alle rapporten geeft die oprollen in een bepaalde organisatie binnen het bedrijf. In dit geval ga ik mezelf en al mijn rapporten te krijgen, samen met elke persoon 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) 

de volgende keer dat u een aantal recursieve berekening over uw gegevens te doen, overwegen om het te doen direct in SQL in tegenstelling tot het laden van al die gegevens in uw toepassing. Voor verder lezen overwegen het nemen van een kijkje op een aantal van deze nuttige middelen:

• PostgreSQL-documenten over CTE ‘s
• het oplossen van het probleem van de reizende verkoper met een CTE
• het verkrijgen van een boom met alle kinderen

als noot CTE’ s zijn op dit moment een optimalisatie in PostgreSQL, hoewel er hoop is dat dat in de toekomst zal veranderen. Gemeenschappelijke Tabeluitdrukkingen zijn een ongelooflijk nuttig hulpmiddel voor rapportage. Soms weegt de leesbaarheid van CTE ‘ s op tegen het effect op de prestaties, maar overweeg de afwegingen bij het gebruik ervan

• fun met SQL
• Postgres
• tips