Historia: Przed wprowadzeniem funkcji analitycznych w ANSI SQL:2003, uzupełnianie rzadkich danych szeregów czasowych wymagało nieefektywnych auto-łączeń lub kursorów proceduralnych przetwarzających wiersze pojedynczo. Wzorzec LOCF pochodzi z pakietów statystycznych takich jak SAS i R, gdzie przenoszenie ostatnio znanej obserwacji jest standardową techniką oczyszczania danych. Dostawcy baz danych później zaimplementowali tę logikę w SQL poprzez funkcje okienne, z klauzulą IGNORE NULLS, które zostały sformalizowane w ANSI SQL:2011 specjalnie w celu obsługi takich luk deklaratywnie.
Problem: Sieci czujników i systemy handlu finansowego często generują wartości NULL z powodu awarii transmisji lub godzin niehandlowych. Proste funkcje LAG zawodzą, ponieważ zwracają natychmiastowego poprzednika, który również może być NULL, tworząc luki w obliczonych metrykach. Wyzwanie polega na przeszukiwaniu wstecz przez uporządkowaną partycję, aż napotka się najnowszą nie-nullową wartość, bez użycia auto-łączeń, które obniżają wydajność kwadratowo.
Rozwiązanie: Użyj funkcji okiennej LAST_VALUE z opcją IGNORE NULLS oraz specyfikacją ramki rozszerzającą się od początku partycji do bieżącego wiersza. Ta konfiguracja nakazuje silnikowi utrzymywanie bufora bieżących nie-nullowych wartości, skutecznie przeszukując NULL-e, aby odzyskać ostatnią ważną obserwację. Dla systemów nieobsługujących IGNORE NULLS, obejście wykorzystuje COUNT nie-nullów do tworzenia stabilnych grup, chociaż technicznie wiąże się to z podzapytaniem.
SELECT device_id, reading_time, temperature, LAST_VALUE(temperature IGNORE NULLS) OVER ( PARTITION BY device_id ORDER BY reading_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS locf_temperature FROM sensor_readings;
Platforma analityki zdrowotnej monitoruje ciągłe poziomy glukozy u pacjentów diabetyków przy użyciu urządzeń noszonych. Z powodu zakłóceń Bluetooth, około 12% pomiarów przychodzi jako NULL, ale klinicyści wymagają pełnych krzywych do obliczeń dawek insuliny, gdzie interpolacja mogłaby być medycznie niebezpieczna. Dokładna logika LOCF jest niezbędna, ponieważ brakujące wartości podczas snu lub posiłków mogą wywołać fałszywe alarmy hipoglikemii.
Rozwiązanie A: Proceduralna aktualizacja oparta na kursorach. Procedura składowa PL/SQL iteruje przez rekordy pacjentów w porządku chronologicznym, utrzymując zmienną sesyjną do przechowywania ostatniego ważnego pomiaru glukozy i natychmiast aktualizując wiersze NULL. Plusy: kompatybilne z starszymi wersjami Oracle, które nie obsługują funkcji okiennych; łatwe do zrozumienia dla programistów o doświadczeniu w programowaniu imperatywnym. Minusy: przetwarzanie wiersz po wierszu generuje nadmierne I/O i blokowanie tabeli; przetwarzanie 10 milionów wierszy trwa 45 minut, co uniemożliwia tworzenie pulpitów na żywo.
Rozwiązanie B: Auto-łączenie z podzapytaniem skorelowanym. Zapytanie wykonuje lewy join, aby znaleźć maksymalny znacznik czasu mniejszy niż bieżący wiersz, gdzie glukoza NIE JEST NULL, efektywnie szukając poprzedniej wartości dla każdej luki. Plusy: deklaratywne SQL bez kodu proceduralnego; działa w systemach zgodnych z ANSI SQL-92. Minusy: złożoność O(n²) powoduje wykładnicze spowolnienie; zapytanie czasowo kończy się po 6 godzinach na danych produkcyjnych z powodu powtarzających się pełnych skanów tabeli.
Rozwiązanie C: Funkcja okienna z IGNORE NULLS. Realizuje LAST_VALUE(glucose IGNORE NULLS) podzieloną według pacjenta i uporządkowaną według czasu, wykorzystując pojedyncze przejście przez indeks. Plusy: złożoność O(n log n) wykonuje się w 28 sekund w tym samym zbiorze danych 10 milionów wierszy; minimalny ślad pamięci i brak problemów z blokowaniem. Minusy: wymaga wsparcia ANSI SQL:2011, co wymaga aktualizacji bazy danych z istniejącej instancji PostgreSQL 9.5.
Zespół wybrał Rozwiązanie C po stwierdzeniu, że koszt aktualizacji bazy danych był uzasadniony przez 99% poprawę wydajności. Implementacja umożliwiła powiadomienia o glukozie w czasie rzeczywistym i zmniejszyła wykorzystanie CPU serwera o 94%. W konsekwencji klinika pomyślnie monitorowała 50,000 pacjentów jednocześnie bez opóźnień ani braków krytycznych wzrostów glukozy.
Pytanie 1: Dlaczego LAST_VALUE bez IGNORE NULLS zwraca NULL, nawet gdy poprzednie nie-nullowe wartości istnieją w partycji?
Domyślnie LAST_VALUE ocenia ramkę obejmującą bieżący wiersz. Kiedy bieżący wiersz zawiera NULL, a ramka obejmuje CURRENT ROW, funkcja widzi ten NULL jako ostatnią wartość w oknie. Kandydaci błędnie zakładają, że funkcja przeszukuje wstecz w nieskończoność; jednak bez IGNORE NULLS traktuje NULL jako wartości ważne. Ramka okna ROWS UNBOUNDED PRECEDING obejmuje bieżący wiersz, co sprawia, że LAST_VALUE jest równoważne wartości bieżącego wiersza, chyba że wyraźnie poleci się zignorować NULL-e.
Pytanie 2: Jak można zaimplementować LOCF w pre-2011 ANSI SQL bez IGNORE NULLS, i jaki jest błąd logiczny w używaniu różnic ROW_NUMBER zamiast COUNT?
Możesz użyć COUNT(non_null_col) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) do stworzenia identyfikatora grupującego, który zwiększa się tylko podczas napotkania wartości nie-null. Wszystkie następne NULL-e dzielą ten licznik, tworząc grupę noszącą. Kandydaci czasami próbują odejmować ROW_NUMBER() OVER (ORDER BY ...) od ROW_NUMBER() OVER (PARTITION BY non_null_flag ORDER BY ...). To nie działa, ponieważ tworzy nowe grupy dla każdej luki pomiędzy nie-nullami, zamiast rozszerzać poprzednią grupę do przodu. Metoda COUNT działa, ponieważ produkuje stabilny identyfikator dla całego okresu ostatniej znanej wartości.
Pytanie 3: Kiedy używasz RANGE zamiast ROWS dla ramki LOCF na znacznikach czasu z duplikatami, dlaczego wyniki mogą stać się niedeterministyczne?
Ramka RANGE grupuje wiersze o identycznych wartościach ORDER BY w grupy rówieśników, traktując je jako jedną jednostkę. Jeśli wiele pomiarów czujników ma ten sam znacznik czasu w milisekundach, RANGE UNBOUNDED PRECEDING nie jest w stanie rozróżnić ich fizycznego porządku. Kiedy niektóre duplikaty zawierają NULL-e, a inne wartości, funkcja okienna może losowo wybierać z grupy rówieśników, w zależności od planu wykonania. Ramka ROWS gwarantuje deterministyczne wyniki poprzez przetwarzanie fizycznego porządku wierszy, zapewniając, że konkretna sekwencja wstawień określa, która wartość jest przenoszona do przodu. To rozróżnienie ma kluczowe znaczenie w przypadku danych handlu o wysokiej częstotliwości, gdzie liczy się każda mikrosekunda.