Storia della domanda. La necessità di conteggi distintivi continui è emersa da carichi di lavoro analitici che tracciano metriche come le acquisizioni cumulative di clienti unici o l’introduzione di SKU distinti nel tempo. Prima delle estensioni delle funzioni di finestra ANSI SQL:2003, gli analisti si affidavano a self-join o sottoquery correlate, comportando una complessità temporale quadratica inaccettabile per i volumi di dati moderni. La standardizzazione delle funzioni di finestra ha fornito un meccanismo basato su set e lineare per mantenere la cardinalità corrente senza cicli procedurali.
Il problema. ANSI SQL vieta esplicitamente la parola chiave DISTINCT all'interno delle funzioni aggregate delle finestre (ad es., COUNT(DISTINCT col) OVER (...)). Questa restrizione impedisce il calcolo diretto dei valori distintivi all'interno di un frame cumulativo o mobile. La sfida principale risiede nell'identificare il primo apparire di ciascuna entità nell'ordine di ordinamento della partizione e sommare progressivamente questi segnali binari (primo apparire = 1, altrimenti = 0).
La soluzione. L'approccio classico combina ROW_NUMBER() per contrassegnare le prime occorrenze con una funzione di finestra condizionale SUM(). Suddividendo ROW_NUMBER() per l'identificatore dell'entità, la prima occorrenza cronologicamente riceve valore 1; le occorrenze successive ricevono interi in aumento. Una query esterna somma quindi un'espressione di caso che emette 1 solo quando il numero di riga è uguale a 1, valutato su un frame di precedenti illimitati.
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 come criterio di rottura ) AS rn FROM user_activity ) flagged;
Descrizione del problema. Una startup fintech aveva bisogno di monitorare la conformità normativa tracciando i commercianti unici cumulativi registrati per regione di vendita durante l'anno fiscale. La loro tabella merchant_signups conteneva 120 milioni di righe con region_code, merchant_id e signup_timestamp. Le attuali batch job Python impiegavano 35 minuti per calcolare queste metriche ogni notte, causando ritardi nei report e dati di dashboard obsoleti. Il requisito era di produrre conteggi cumulativi in tempo reale all'interno di un rigoroso SQL ANSI per la portabilità tra i magazzini di dati cloud.
Soluzione A: L'approccio self-join. Questo metodo unisce la tabella a se stessa in base alla corrispondenza tra regione e timestamp precedenti, contando i commercianti distinti per ogni riga esterna. Pro: Non richiede supporto per le funzioni di finestra e funziona su motori SQL-92 legacy. Contro: L'algoritmo presenta una complessità O(n²); per milioni di righe, questo genera prodotti cartesiani intermedi che consumano terabyte di spazio di archiviazione temporaneo e non riesce a completarsi in ore, rendendolo operativamente non fattibile.
Soluzione B: La sottoquery scalare correlata. Qui, la clausola SELECT incorpora una sottoquery: (SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp). Pro: È dichiarativa e logicamente trasparente da leggere. Contro: La sottoquery viene eseguita una volta per riga (120 milioni di volte), prevenendo il pushdown dei predicati e causando enormi I/O casuali; gli ottimizzatori di database non possono decorrelare aggregati distintivi su intervalli temporali variabili, risultando in tempi di esecuzione stimati superiori a 90 minuti.
Soluzione C: La tecnica delle funzioni di finestra ANSI SQL. Utilizzando ROW_NUMBER() per identificare le prime apparizioni seguita da un SUM in esecuzione come mostrato nell'esempio di codice sopra. Pro: Questo esegue una singola scansione della tabella con ordinamento, sfruttando le capacità di spooling della finestra dell'ottimizzatore per una complessità O(n log n) e un utilizzo della memoria limitato. Contro: Richiede una gestione attenta dei legami temporali; se due registrazioni condividono timestamp identici, un ordinamento non deterministico potrebbe contare due volte a meno che non venga aggiunto un criterio di rottura unico (come event_id) alla clausola ORDER BY.
Soluzione scelta e risultato. La soluzione C è stata implementata. Includendo event_id nella ORDER BY per garantire un rilevamento deterministico della prima apparizione, la query è stata eseguita in 4 minuti sul cluster esistente, un miglioramento di 9 volte. Il risultato ha abilitato dashboard di conformità in tempo reale, consentendo ai funzionari del rischio di monitorare la diversità dell'onboarding senza ritardi ETL, e la query era completamente portabile su PostgreSQL, Snowflake e BigQuery senza modifiche.
Perché COUNT(DISTINCT column) OVER (ORDER BY ...) genera un errore di sintassi in SQL ANSI rigoroso?
Lo standard SQL vieta esplicitamente la parola chiave DISTINCT all'interno dell'argomento di una funzione aggregata di finestra come COUNT, SUM o AVG. Anche se specifici fornitori (ad es., PostgreSQL 16+, Oracle) offrono questo come un'estensione proprietaria, ANSI SQL:2011 e versioni precedenti limitano le aggregazioni di finestra a operare su tutte le righe all'interno del frame definito. Questa limitazione esiste perché mantenere una tabella hash di set distinti per ogni possibile frame di finestra durante la valutazione in streaming non è richiesto dalla grammatica standard. I candidati devono riconoscere che DISTINCT è consentito solo nelle funzioni aggregate standard che non hanno clausole OVER, o all'interno di funzioni di distribuzione inverse come PERCENTILE_CONT, ma mai come un conteggio distintivo in finestra.
Come gestisci i timestamp duplicati quando si determina la "prima" occorrenza di un'entità?
ROW_NUMBER() assegna valori arbitrari tra i legami a meno che la clausola ORDER BY non specifichi un ordinamento totale. Se un commerciante ha due registrazioni con timestamp identici, entrambe le righe potrebbero ricevere rn = 1 se l'ordinamento è non deterministico, causando l'incremento del conteggio cumulativo due volte in modo errato. La risoluzione è appendere una chiave primaria unica o un ID autoincrementante alla clausola ORDER BY: ORDER BY signup_timestamp, merchant_signup_id. Questo assicura una sequenza deterministica in cui l'ID assegnato precedentemente è considerato la prima occorrenza, preservando l'integrità matematica del conteggio distintivo corrente.
Questa tecnica può essere adattata per un conteggio distintivo mobile su un frame con conteggio fisso di righe (ad es., ultime 100 transazioni) piuttosto che precedenti illimitati?
No, non in modo efficiente con puro SQL ANSI. Il metodo di precedenti illimitati ha successo perché la distintività è monotona; una volta che un'entità appare, rimane "contata" per sempre. In una finestra mobile (ad es., ROWS BETWEEN 100 PRECEDING AND CURRENT ROW), un'entità che esce dalla finestra deve decrementare il conteggio, richiedendo la conoscenza se la riga in uscita rappresenta l'unica istanza di quell'entità all'interno del frame corrente. SQL ANSI non ha operatori di aggregazione di array o di differenza di set all'interno dei frame di finestra per tenere traccia di tale uscita in modo efficiente. Implementare ciò richiede o CTE ricorsivi (che degradano a O(n²) per questo scenario) o estensioni proprietarie come ARRAY_AGG combinate con operazioni di set, entrambe violando la conformità rigorosa ad ANSI.