Storia: Nella data warehousing temporale, la tecnica Last Observation Carried Forward (LOCF) domina l'imputazione dei valori mancanti, utilizzando i record validi precedenti per riempire le lacune. Tuttavia, specifici domini analitici—come l'applicazione di riconciliazioni di fine giornata a transazioni finanziarie intraday o la retropropagazione delle conferme di laboratorio a diagnosi provvisorie precedenti—richiedono l'approccio inverso Next Observation Carried Backward (NOCB). Storicamente, NOCB è stato implementato tramite subquery correlate o cursori procedurali, entrambi con complessità O(n²) e incapaci di sfruttare ottimizzatori moderni basati su set.
Il Problema: Data una sequenza totalmente ordinata (ad es., event_time), ogni valore NULL deve essere sostituito con il valore non-NULL più vicino che si verifica dopo di esso nella sequenza. I NULL consecutivi che precedono un record valido dovrebbero ricevere lo stesso valore successivo. Le funzioni standard come LEAD() accedono solo alla riga immediatamente successiva, fallendo quando esistono più NULL consecutivi prima di un ancoraggio non-NULL. I self-join e i CTE ricorsivi sono proibiti dalle limitazioni di prestazione.
La Soluzione: La soluzione sfrutta la semantica di ignoranza di NULL di COUNT(espressione). Contando i valori non-NULL dalla riga corrente fino alla fine della partizione (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), generiamo un "identificatore di bucket" stabile che è identico per tutte le righe tra due ancoraggi non-NULL. All'interno di ciascun bucket, MAX(val)—che ignora anche i NULL—recupera il valore di ancoraggio e lo trasmette a tutte le righe in quel gruppo.
WITH bucketed AS ( SELECT record_id, event_time, status_code, COUNT(status_code) OVER ( ORDER BY event_time, record_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS bucket_id FROM audit_log ) SELECT record_id, event_time, COALESCE( MAX(status_code) OVER ( PARTITION BY bucket_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), 'UNKNOWN' ) AS confirmed_status FROM bucketed;
Contesto e descrizione del problema: Una società di trading ad alta frequenza mantiene una tabella execution che cattura scambi di azioni a livello di microsecondi. A causa dei protocolli di reporting degli scambi, il "prezzo consolidato" finale per qualsiasi minuto—verificato dalla camera di compensazione—arriva 30 secondi dopo la fine del minuto e viene contrassegnato solo al confine (ad es., 14:30:00.000). Per i calcoli TWAP (Time-Weighted Average Price) normativi, ogni millisecondo di quel minuto deve riflettere il prezzo consolidato finale, richiedendo di riempire retroattivamente tutti i record 14:29:00.000 - 14:29:59.999. Il volume giornaliero supera i 50 milioni di righe, e la finestra batch è di 10 minuti.
Soluzione 1: Subquery scalare correlata. Questo approccio utilizza una subquery scalare per ogni riga per individuare il MIN(event_time) delle righe future in cui consolidated_price IS NOT NULL, quindi si unisce per recuperare quel prezzo.
Pro: Concettualmente semplice per gli sviluppatori con esperienze procedurali.
Contro: Esegue O(n²) confronti. Su dati di produzione, il tempo di esecuzione della query ha superato i 45 minuti, violando la finestra batch. Gestire più NULL consecutivi richiede logica aggiuntiva per saltare in avanti, aumentando la complessità e i tassi di errore.
Soluzione 2: Traversata CTE ricorsiva. Un CTE ricorsivo itera all'indietro riga per riga, portando il prezzo non-NULL all'indietro fino a incontrare un altro non-NULL.
Pro: Garantito per funzionare su qualsiasi database conforme a ANSI SQL.
Contro: CTE ricorsivi elaborano le righe sequenzialmente in molti motori (ad es., PostgreSQL), risultando in esecuzione multi-threaded e potenziale overflow dello stack su partizioni profonde. I benchmark hanno mostrato un tempo di esecuzione di 20 minuti con alta pressione di memoria, rendendolo inadatto per SLA di produzione.
Soluzione 3: Bucketizzazione con funzione di finestra (Scelta). Implementare il pattern COUNT e MAX. Il COUNT retrospettivo crea bucket identici per tutte le righe che richiedono lo stesso valore futuro, mentre MAX propaga quel valore all'interno del bucket.
Pro: Completamente basato su set, parallelizzabile e si esegue in tempo O(n log n) a causa dell'operazione di ordinamento. Si scala linearmente con il volume e utilizza standard ANSI SQL portabili tramite PostgreSQL, SQL Server, Oracle e DB2.
Contro: Richiede due passaggi sui dati (il CTE e la query esterna), sebbene gli ottimizzatori moderni spesso fondano questi. Richiede un ordinamento totale; i timestamp duplicati richiedono una colonna di tie-breaker per garantire la determinismo.
Risultato: Il tempo di esecuzione della pipeline è sceso da 45 minuti a 8 secondi sul set di dati di 50 milioni di righe. L'azienda ha eliminato un fragile script di riempimento Python, riducendo la complessità dell'infrastruttura e garantendo che i report normativi vengano generati entro la finestra di conformità.
Perché COUNT(column) deve essere utilizzato invece di COUNT(*) o ROW_NUMBER() quando si costruisce la chiave di raggruppamento?
Molti candidati utilizzano intuitivamente COUNT(*) o ROW_NUMBER(), credendo che questi possano segmentare i dati. COUNT(*) conta ogni riga indipendentemente dai NULL, producendo un valore unico e monotonico per ogni riga nel frame retrospettivo, il che impedisce la formazione di gruppi stabili. ROW_NUMBER() assegna un identificatore unico a ciascuna riga, distruggendo analogamente il raggruppamento. Solo COUNT(column) incrementa esclusivamente quando incontra valori non-NULL, assegnando quindi lo stesso "ID bucket" a tutti i NULL precedenti fino al successivo confine non-NULL. Questa distinzione è cruciale perché sfrutta la semantica di ignoranza di NULL delle funzioni di finestra aggregate per simulare un "look-ahead" senza logica procedurale.
Come si comporta la query se la partizione termina con valori NULL finali, e quale modifica garantisce la gestione deterministica quando non esiste alcuna osservazione futura?
Se le righe finali nella partizione ordinata sono NULL, COUNT(status_code) viene valutato a zero per quelle righe. Di conseguenza, MAX(status_code) restituisce NULL, che è logicamente corretto—non esiste alcuna osservazione futura da riportare all'indietro. I candidati spesso dimenticano di gestire ciò nella logica aziendale downstream. Per fornire un valore predefinito (ad es., un segnaposto statico o un valore da una ricerca esterna), è necessario racchiudere il risultato in COALESCE. Inoltre, per distinguere tra "NULL riempiti" e "NULL non riempibili" per il monitoraggio della qualità dei dati, si dovrebbe confrontare i valori originali e riempiti: CASE WHEN status_code IS NULL AND bucket_id = 0 THEN 'UNCONFIRMED' END.
Quale problema di determinismo sorge se la clausola ORDER BY contiene valori duplicati, e perché il passaggio da ROWS a RANGE esacerba il problema?
Quando le chiavi di ordinamento contengono duplicati (pareggi), la definizione del frame della finestra diventa ambigua. Utilizzando ROWS (offset fisici) assegna gruppi in base all'ordine fisico della tabella, che è arbitrario a meno che non sia fornito un secondo ordinamento unico. Passare a RANGE (intervalli di valori logici) tratta tutte le righe con lo stesso valore di ordinamento come pari, causando loro di condividere lo stesso frame. In questa soluzione, se più righe condividono lo stesso event_time, RANGE potrebbe erroneamente raggruppare righe NULL con righe non-NULL dello stesso timestamp o dividere i gruppi in modo imprevedibile. I candidati devono garantire un ordinamento totale aggiungendo una chiave unica (ad es., record_id) alla clausola ORDER BY: ORDER BY event_time, record_id per garantire l'assegnazione deterministica dei bucket attraverso tutte le implementazioni di ANSI SQL.