Meerdere CTE's aan elkaar koppelen

Echte problemen uit sollicitatiegesprekken passen zelden in één stap. Door CTE's te koppelen bouw je een pijplijn met benoemde stappen, waarbij elke stap de uitvoer van de vorige stap transformeert. Dit weerspiegelt hoe een ervaren ontwikkelaar een moeilijke query opsplitst in beheersbare onderdelen.

In plaats van subquery's drie niveaus diep te nesten, schrijf je elke stap één keer, geef je die een naam en laat je latere stappen ernaar verwijzen.

Om meerdere CTE's te definiëren schrijf je WITH één keer en scheid je elk benoemd blok met een komma. Je herhaalt het sleutelwoord WITH dus niet.

• Eén WITH bovenaan.
• Een komma tussen elke CTE-definitie.
• Geen komma vóór de uiteindelijke hoofdquery.

Hier zit de kracht van koppelen: een CTE kan gegevens lezen uit elke CTE die ervoor is gedefinieerd. Dankzij deze zichtbaarheid in één richting kun je een afhankelijkheidsketen opbouwen.

Een eerdere CTE kan een latere niet zien, dus de volgorde is belangrijk. Rangschik je stappen van onbewerkte gegevens naar de uiteindelijke vorm.

Vraag: wat is de gemiddelde besteding van klanten die meer dan 1000 dollar hebben uitgegeven? Deel het op in drie stappen: bereken de totale besteding per klant, filter de grote besteders en bereken daarna hun gemiddelde.

Elke CTE-naam beschrijft het doel, zodat een beoordelaar de opbouw meteen begrijpt.

Omdat de zichtbaarheid alleen vooruit werkt, moet een CTE die afhankelijk is van een andere na die afhankelijkheid worden vermeld. Als je verwijst naar een naam die nog niet is gedefinieerd, geeft de database een foutmelding: 'relatie bestaat niet'.

Een goede gewoonte: lees je lijst met CTE's van boven naar beneden en controleer of elke gebruikte naam al erboven staat.

Eén CTE kan gegevens leveren aan meerdere volgende CTE's. Hier zie je waarom koppelen beter werkt dan geneste subquery's: je berekent één keer een basisresultaat en vertakt daar vanaf.

Hier lezen zowel active als recent uit base, waardoor dubbele logica wordt vermeden.

Gekoppelde CTE's worden in de hoofdquery vaak samengevoegd. Bereken beide kanten afzonderlijk en combineer ze daarna. Zo blijft elke berekening op zichzelf staan en blijft het samenvoegen eenvoudig.

Hieronder berekenen we onafhankelijk het aantal bestellingen en het aantal terugbetalingen en voegen we die daarna per klant samen.

Vergelijk een subquery die drie niveaus diep is genest met een pijplijn van drie CTE's. Bij de geneste versie moet de lezer de structuur mentaal van binnen naar buiten uitpakken. De CTE-versie leest in uitvoervolgorde, van boven naar beneden.

Interviewers waarderen de CTE-aanpak omdat dit de vorm is die ze in productie zouden willen onderhouden. Elke stap een naam geven is documentatie die nooit veroudert.

Beginners zetten vaak een komma na de laatste CTE, direct vóór de hoofdquery met SELECT. Die afsluitende komma veroorzaakt een syntaxisfout.

• Komma's staan alleen tussen CTE-definities.
• Na het laatste sluithaakje volgt direct de hoofdquery, zonder komma.

Een andere valkuil: vergeten dat elke CTE een volledige SELECT tussen de haakjes nodig heeft.

Een genuanceerd punt voor sollicitatiegesprekken: logisch gezien leest de pijplijn als een reeks afzonderlijke stappen, maar de optimizer kan ze samenvoegen tot één uitvoeringsplan. In de meeste databases hoef je niet te betalen voor het materialiseren van tussenresultaten.

Door CTE's te koppelen kun je de query dus beter begrijpen zonder dat dit noodzakelijkerwijs ten koste gaat van de prestaties. Noem dit om diepgang te tonen.

Goede namen voor stappen maken van een query zelfdocumenterende code. Kies bij voorkeur namen die de uitvoer van elke stap beschrijven, niet de bewerking.

• spend en big_spenders zijn beter dan step1 en step2.
• Een lezer moet de volledige opbouw alleen uit de CTE-namen kunnen afleiden.
• Consistente naamgeving in alle stappen maakt het samenvoegen in de hoofdquery vanzelfsprekend.

Tijdens een sollicitatiegesprek laat een duidelijke naamgeving van stappen zien dat je onderhoudbare SQL voor productie schrijft.

Je hebt geleerd pijplijnen te bouwen: één WITH, CTE-definities gescheiden door komma's en zichtbaarheid die alleen vooruit werkt, waarbij elke stap eerdere stappen kan lezen.

• Orden CTE's van onbewerkte gegevens naar het uiteindelijke resultaat.
• Gebruik één basis-CTE opnieuw in meerdere volgende stappen.
• Geen afsluitende komma vóór de hoofdquery.
• Koppelen verbetert de leesbaarheid zonder noodzakelijkerwijs de prestaties te schaden.

Volgende stap: CTE's vergelijken met subquery's en tijdelijke tabellen.