Het exponentieel voortschrijdend gemiddelde (EMA) is ontstaan in technische analyse in het midden van de 20e eeuw als een manier om gegevens te glätten, waarbij meer gewicht aan recente waarnemingen wordt gegeven. In tegenstelling tot eenvoudige voortschrijdende gemiddelden heeft de berekening van de EMA een recursieve wiskundige eigenschap waarbij elke waarde afhankelijk is van de eerder berekende EMA, wat een afhankelijkheidsketen creëert die weerstand biedt tegen vectorisatie. Deze eigenschap maakt het berucht moeilijk om te implementeren in set-gebaseerde SQL, omdat standaard venstereigenschappen werken op statische frames in plaats van op cumulatieve resultaten. Interviewers stellen deze vraag om het begrip van de kandidaat van de recursieve mogelijkheden van ANSI SQL te beoordelen en hun vermogen om iteratieve algoritmen om te zetten in declaratieve setlogica.
Wiskundig gezien is de EMA op tijd t gedefinieerd als: EMAt = α × Prijs_t + (1-α) × EMA{t-1}, waarbij α de gladheidsfactor is (typisch 2/(N+1) voor een N-periode gemiddeld). De basisgeval gebruikt de prijs van de eerste periode als de initiële EMA. In een databasecontext staan we voor de uitdaging om deze doorlopende berekening over miljoenen rijen te behouden die zijn geordend op tijdstempel, waarbij elke rij toegang vereist tot de eerder berekende waarde van de vorige rij. Standaard ANSI SQL aggregatiefuncties zoals SUM of AVG kunnen deze recursieve afhankelijkheid niet uitdrukken, en venstereigenschappen met ROWS of RANGE clausules hebben alleen toegang tot onbewerkte invoerwaarden, niet tot berekende uitvoer van eerdere rijen.
We implementeren dit met behulp van een recursieve CTE (Common Table Expression) die de geordende dataset sequentieel doorloopt. Eerst stellen we een deterministische rijvolgorde vast met ROW_NUMBER() om hiaten of onregelmatige tijdstempels te behandelen. Het anker lid selecteert de initiële rij voor elke partition (bijvoorbeeld aandelen symbool), waarbij de initiële EMA gelijk is aan de eerste prijs. Het recursieve lid voegt de CTE samen met de volgende sequentiële rij (waar rij_nummer = vorige + 1) en past de EMA-formule toe met behulp van de eerder berekende waarde. Deze benadering voldoet strikt aan de ANSI SQL:1999 normen en wordt uitgevoerd als een enkele set-gebaseerde bewerking.
WITH RECURSIVE genummerde_handelingen AS ( SELECT symbool, prijs, handels_tijd, ROW_NUMBER() OVER (PARTITION BY symbool ORDER BY handels_tijd) AS rn FROM handelingen ), ema_reeks AS ( -- Anker: eerste rij voor elk symbool SELECT symbool, prijs, rn, prijs AS ema -- Initiële EMA is gelijk aan de eerste prijs FROM genummerde_handelingen WHERE rn = 1 UNION ALL -- Recursief: bereken EMA voor volgende rijen SELECT t.symbool, t.prijs, t.rn, 0.2 * t.prijs + 0.8 * e.ema AS ema -- α = 0.2 voor 9-periode EMA FROM ema_reeks e JOIN genummerde_handelingen t ON t.symbool = e.symbool AND t.rn = e.rn + 1 ) SELECT symbool, prijs, ema, rn FROM ema_reeks ORDER BY symbool, rn;
Een kwantitatief handelsbedrijf moest EMA-indicatoren voor vijf jaar historische tick-gegevens terugvullen over 5.000 aandelen symbolen om een nieuw algoritme te valideren. De dataset bevatte 250 miljoen rijen van gegevens over de markt met hoge frequentie, en de bestaande Python Pandas-oplossing vereiste het overdragen van gigabytes aan gegevens over het netwerk, wat leidde tot frequente time-outs en geheugenfouten op de analysetool tijdens piekperiodes van marktvolatiliteit.
Het team overweegde eerst het implementeren van een Python preprocessor script met de Pandas ewm() methode. Deze benadering bood snelle prototyping en een bekende syntaxis voor de kwantitatieve analisten, en het handhaafde de recursieve berekening op een native manier met behulp van geoptimaliseerde C-uitbreidingen. Echter, het introduceerde aanzienlijke overhead bij de overdracht van gegevens tussen de PostgreSQL database en de applicatieserver, vereiste het laden van miljoenen rijen in het geheugen, en necessiteerde complexe chunking-logica om symbolen in batches te verwerken zonder het continuïteit van de EMA-berekening over batchgrenzen heen te verliezen.
Ten tweede keken ze naar een puur set-gebaseerde benadering met behulp van een SELF JOIN met exponentiële gewichtsberekeningen, waarbij elke rij verbinding maakt met alle eerdere rijen binnen een 200-periode terugkijkperiode en geometrische gewichten toepast. Deze methode vermijdde volledig recursie en zou theoretisch de database-optimalisator in staat stellen de operatie te paralelliseren. Echter, het had te maken met een O(n²) complexiteit ten opzichte van de periode van de terugblik, waardoor enorme tussenresultaatssets ontstonden die de tempdb overweldigden bij de verwerking van gegevens over de markt met hoge frequentie, en het bood alleen een benadering van de werkelijke EMA vanwege de eindige window truncatie.
Ten derde evalueerden ze de recursieve CTE oplossing met behulp van ANSI SQL standaard syntaxis. Deze benadering werd volledig binnen de database-engine uitgevoerd, verminderde netwerkoverdracht overhead, en berekende de wiskundig exacte EMA door strikt de recursieve definitie te volgen. Hoewel het risico liep de limieten van de recursiediepte te overschrijden op extreem lange symboolgeschiedenis en het meestal single-threaded per symbool uitvoerde in de meeste ANSI SQL implementaties, bleek het geheugen-efficiënt en vermijdde het de kwadratische explosie van de zelf-join-methode.
Ze kozen de recursieve CTE benadering omdat deze de gegevensbeweging elimineerde, zorgde voor numerieke precisie die identiek was aan Pandas, en kon worden gepland als een database-native verversing van een gematerialiseerde weergave zonder externe afhankelijkheden. De DBA configureerde de max_recursive_iterations parameter om rekening te houden met de langste symboolgeschiedenis (ongeveer 50.000 ticks per symbool).
De implementatie verwerkte de gehele dataset van 250 miljoen rijen in ongeveer 12 minuten. De resulterende EMA waarden kwamen overeen met Pandas-berekeningen tot binnen de precisie van de drijvende komma, en valideerden de wiskundige juistheid van de SQL-implementatie. Het bedrijf produceerde vervolgens de query als een nachtelijke verversing van de gematerialiseerde weergave, waardoor de noodzaak voor externe Python scripts werd geëlimineerd en hun datastromencomplexiteit aanzienlijk werd verminderd.
Hoe ga je om met de berekening wanneer de bron tabel hiaten in de volgorde bevat of onregelmatige tijdstempels die geen perfecte gehele getallenreeks vormen?
Veel kandidaten veronderstellen dat handels_tijd of een ID-kolom een dichte reeks biedt die geschikt is voor rn = e.rn + 1 joins. In werkelijkheid creëren gemiste ticks of verwijderde records hiaten die de recursieketen onderbreken. De oplossing vereist het materialiseren van een dichte rang met behulp van ROW_NUMBER() of DENSE_RANK() vóór de recursieve CTE, waardoor opeenvolgende gehele getallen worden gegarandeerd ongeacht tijdstempelhiaten. Dit ontkoppelt de logische volgorde van fysieke sleutelwaarden, waardoor de recursie ononderbroken kan doorgaan terwijl de juiste temporele volgorde behouden blijft.
Waarom kan een recursieve CTE-benadering falen voor extreem lange tijdreeksen (bijv. 100.000+ rijen per symbool), en hoe verlicht je dit binnen de beperkingen van ANSI SQL?
Kandidaten vergeten vaak dat de ANSI SQL standaard geen oneindige recursiediepte voorschrijft, en implementaties zoals PostgreSQL standaard zijn beperkt tot 1000 iteraties terwijl SQL Server standaard is ingesteld op 100. Het overschrijden van deze limieten maakt de query ongeldig. De verlichting omvat batchverwerking met behulp van een controle tabel of iteratieve benadering, maar binnen strikte ANSI SQL moet je ofwel de sessielimiet voor recursie verhogen (niet-ANSI) of een hybride benadering implementeren met behulp van venstereigenschappen voor benaderende EMA over vaste terugblikperioden (bijv. 200 periodes) waarbij de exponentiële verval de oudere bijdragen verwaarloosbaar maakt. Voor exacte berekeningen moet je ervoor zorgen dat de recursielimiet van het platform groter is dan je maximale sequentielengte of een opgeslagen procedure loop gebruiken (verboden in de beperkingen van deze vraag).
Hoe voorkom je kruisbesmetting bij het berekenen van EMA's voor meerdere onafhankelijke tijdreeksen (bijv. verschillende aandelen symbolen) gelijktijdig in een enkele recursieve query?
Een veelgemaakte fout is het weglaten van de partitie sleutel uit de recursieve join-voorwaarde. Kandidaten schrijven t.rn = e.rn + 1 zonder t.symbool = e.symbool op te nemen, wat ertoe leidt dat de recursie tussen verschillende symbolen sprongen maakt wanneer de rijnummers overeenkomen. De juiste implementatie vereist het doorgeven van de partitie sleutel (symbool) door zowel het anker als de recursieve leden, en strikt joins op zowel het incrementele volgnummer als de partitie gelijkheid. Dit zorgt ervoor dat de recursiebomen per symbool geïsoleerd blijven, waardoor effectieve afzonderlijke berekeningscontexten binnen de enkele CTE-uitvoering worden gecreëerd.