Geschiedenis van de vraag. De noodzaak voor lopende unieke tellingen ontstond vanuit analytische werklasten die metrics zoals cumulatieve unieke klantenacquisities of unieke SKU-invoeringen in de loop van de tijd volgden. Voor de ANSI SQL:2003 vensterfunctie-uitbreidingen waren analisten afhankelijk van zelf-joins of gecorreleerde subquery's, wat resulteerde in kwadratische tijdcomplexiteit die onacceptabel is voor moderne datavolumes. De standaardisering van vensterfuncties bood een lineaire-tijd, set-gebaseerde mechanisme om de lopende cardinaliteit te handhaven zonder procedurele lussen.
Het probleem. ANSI SQL staat expliciet het DISTINCT sleutelwoord niet toe binnen vensteraggregatiefuncties (bijv. COUNT(DISTINCT col) OVER (...)). Deze beperking voorkomt directe berekening van unieke waarden binnen een cumulatief of glijdend venster. De kernuitdaging ligt in het identificeren van de eerste verschijning van elke entiteit binnen de sorteerorde van de partitie en het accumuleren van deze binaire vlaggen (eerste verschijning = 1, anders = 0) geleidelijk.
De oplossing. De canonieke benadering combineert ROW_NUMBER() om eerste verschijningen te markeren met een voorwaardelijke SUM() vensterfunctie. Door ROW_NUMBER() te partitioneren op basis van de entiteitsidentificatie, krijgt de chronologisch eerste verschijning de waarde 1; volgende verschijningen ontvangen oplopende gehele getallen. Een externe query telt vervolgens een case-expressie die 1 alleen retourneert wanneer het rijnummer gelijk is aan 1, geëvalueerd over een ongebonden voorafgaand venster.
SELECT event_date, region_id, user_id, SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY region_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_unique_users FROM ( SELECT event_date, region_id, user_id, ROW_NUMBER() OVER ( PARTITION BY region_id, user_id ORDER BY event_date, event_id -- event_id als tie-breaker ) AS rn FROM user_activity ) flagged;
Probleembeschrijving. Een fintech-startup moest de naleving van regelgeving monitoren door cumulatieve unieke handelaren die per verkoopregio gedurende het fiscale jaar werden aangemeld. Hun merchant_signups tabel bevatte 120 miljoen rijen met region_code, merchant_id, en signup_timestamp. Bestaande Python batchtaken kostten 35 minuten om deze metrics 's nachts te berekenen, wat leidde tot rapportagevertragingen en verouderde dashboardgegevens. De vereiste was om real-time cumulatieve tellingen te produceren binnen strikte ANSI SQL voor draagbaarheid over cloud data warehouses.
Oplossing A: De zelf-join benadering. Deze methode voegt de tabel aan zichzelf toe op basis van overeenkomende regio en eerdere tijdstempels, en telt unieke handelaren per externe rij. Voordelen: Het vereist geen ondersteuning van vensterfuncties en werkt op legacy SQL-92 engines. Nadelen: Het algoritme vertoont O(n²) complexiteit; voor miljoenen rijen genereert dit tussenliggende cartesiaanse producten die terabytes aan tijdelijke opslag verbruiken en kan het niet binnen enkele uren worden voltooid, wat het operationeel onhaalbaar maakt.
Oplossing B: De geassocieerde scalar subquery. Hier embed de SELECT-clausule een subquery: (SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp). Voordelen: Het is declaratief en logisch transparant om te lezen. Nadelen: De subquery wordt eenmaal per rij uitgevoerd (120 miljoen keer), wat predicate pushdown voorkomt en enorme willekeurige I/O veroorzaakt; database-optimizers kunnen geen onderscheid maken in unieke aggregaten over variërende temporele reeksen, wat resulteert in geschatte uitvoeringstijden van meer dan 90 minuten.
Oplossing C: De ANSI SQL vensterfunctie techniek. Het gebruik van ROW_NUMBER() om eerste verschijningen te identificeren, gevolgd door een lopende SUM() zoals in het bovenstaande codevoorbeeld wordt weergegeven. Voordelen: Dit voert een enkele tabelscan met sortering uit, waarbij de optimalisatiefuncties voor venster spoelen worden benut voor O(n log n) complexiteit en beperkte geheugengebruik. Nadelen: Het vereist zorgvuldige behandeling van temporele gelijken; als twee aanmeldingen identieke tijdstempels delen, kan een niet-deterministische volgorde dubbele telling veroorzaken, tenzij een unieke tie-breaker (zoals event_id) aan de ORDER BY clausule wordt toegevoegd.
Gekozen oplossing en resultaat. Oplossing C werd geïmplementeerd. Door event_id op te nemen in de ORDER BY om deterministische detectie van eerste verschijning te waarborgen, werd de query uitgevoerd in 4 minuten op de bestaande cluster—een verbetering van 9x. Het resultaat maakte real-time nalevingsdashboards mogelijk, waardoor risicobeheerders de diversiteit van onboarding konden volgen zonder ETL-vertragingen, en de query was volledig draagbaar naar PostgreSQL, Snowflake, en BigQuery zonder wijziging.
Waarom veroorzaakt COUNT(DISTINCT column) OVER (ORDER BY ...) een syntaxisfout in strikte ANSI SQL?
De SQL-standaard staat expliciet het DISTINCT sleutelwoord niet toe binnen het argument van een vensteraggregatiefunctie zoals COUNT, SUM, of AVG. Hoewel specifieke leveranciers (bijv. PostgreSQL 16+, Oracle) dit als een propriëtaire extensie aanbieden, beperken ANSI SQL:2011 en eerdere versies vensteraggregaten tot het werken op alle rijen binnen het gedefinieerde venster. Deze beperking bestaat omdat het onderhouden van een distinct-set hash-tabel voor elk mogelijk vensterframe tijdens streamingevaluatie niet door de standaardgrammatica wordt vereist. Kandidaten moeten erkennen dat DISTINCT alleen is toegestaan in standaardaggregatiefuncties die geen OVER-clausules bevatten, of binnen inverse distributiefuncties zoals PERCENTILE_CONT, maar nooit als een vensterdistinct telling.
Hoe ga je om met dubbele tijdstempels bij het bepalen van de "eerste" verschijning van een entiteit?
ROW_NUMBER() kent willekeurige waarden toe onder gelijken, tenzij de ORDER BY-clausule een totale ordening specificeert. Als een handelaar twee vermeldingen heeft met identieke tijdstempels, kunnen beide rijen potentieel rn = 1 ontvangen als de ordening niet-deterministisch is, wat leidt tot een onterechte toename van de cumulatieve telling. De oplossing is om een unieke primaire sleutel of auto-increment ID aan de ORDER BY clausule toe te voegen: ORDER BY signup_timestamp, merchant_signup_id. Dit zorgt voor een deterministische sequencing waarbij de eerder toegewezen ID wordt beschouwd als de eerste verschijning, waardoor de wiskundige integriteit van de lopende unieke telling behouden blijft.
Kan deze techniek worden aangepast voor een bewegende unieke telling over een vast aantal rijen (bijv. laatste 100 transacties) in plaats van ongebonden voorafgaand?
Nee, niet efficiënt met pure ANSI SQL. De ongebonden voorafgaande methode slaagt omdat uniekheid monotoon is—eenmaal een entiteit verschijnt, blijft deze "geteld" voor altijd. In een glijdend venster (bijv. ROWS BETWEEN 100 PRECEDING AND CURRENT ROW) moet een entiteit die het venster verlaat de telling verlagen, wat vereist dat je weet of de vertrekkende rij het enige exemplaar van die entiteit binnen het huidige venster vertegenwoordigt. ANSI SQL mist array-aggregatie of set-differentiële operatoren binnen vensterframes om dergelijke uittredingen efficiënt bij te houden. Het implementeren hiervan vereist ofwel recursieve CTE's (die in dit scenario degradeert naar O(n²)) of propriëtaire extensies zoals ARRAY_AGG gecombineerd met setbewerkingen, wat beide strikt ANSI-naleving schendt.