Historia pytania. Konieczność obliczeń zliczeń unikalnych pojawiła się w związku z zadań analitycznych śledzących metryki takie jak skumulowana liczba unikalnych pozyskanych klientów czy unikalnych wprowadzeń SKU w czasie. Przed rozszerzeniami funkcji okiennych ANSI SQL:2003 analitycy polegali na łączeniach wewnętrznych lub skorelowanych podzapytaniach, co skutkowało kwadratową złożonością czasową, niedopuszczalną dla nowoczesnych objętości danych. Standaryzacja funkcji okiennych zapewniła mechanizm oparty na zbiorach działający w czasie liniowym do utrzymania bieżącej liczności bez pętli proceduralnych.
Problem. ANSI SQL wyraźnie zabrania stosowania słowa kluczowego DISTINCT w agregatowych funkcjach okiennych (np. COUNT(DISTINCT col) OVER (...)). To ograniczenie uniemożliwia bezpośrednie obliczenie unikalnych wartości w ramach skumulowanej lub przesuwającej się ramki. Kluczowym wyzwaniem jest zidentyfikowanie pierwszego wystąpienia każdego podmiotu w porządku sortowania partycji i stopniowe sumowanie tych flag binarnych (pierwsze wystąpienie = 1, inaczej = 0).
Rozwiązanie. Kanoniczne podejście łączy ROW_NUMBER() do oznaczania pierwszych wystąpień z warunkową funkcją okienną SUM(). Poprzez partycjonowanie ROW_NUMBER() według identyfikatora podmiotu, chronologiczne pierwsze wystąpienie otrzymuje wartość 1; kolejne wystąpienia otrzymują rosnące liczby całkowite. Zapytanie zewnętrzne sumuje wyrażenie warunkowe, które zwraca 1 tylko wtedy, gdy numer wiersza jest równy 1, oceniane w ramach nieograniczonej ramki poprzedzającej.
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 jako rozstrzyganie w przypadku remisu ) AS rn FROM user_activity ) flagged;
Opis problemu. Startup fintechowy potrzebował monitorować zgodność z regulacjami, śledząc skumulowaną liczbę unikalnych sprzedawców pozyskanych w danym regionie sprzedaży przez cały rok fiskalny. Ich tabela merchant_signups zawierała 120 milionów wierszy z region_code, merchant_id i signup_timestamp. Istniejące zadania wsadowe w Pythonie trwały 35 minut na obliczenie tych metryk każdej nocy, co powodowało opóźnienia w raportowaniu i przestarzałe dane na tablicy rozdzielczej. Wymogiem było uzyskanie rzeczywistych skumulowanych zliczeń w ramach rygorystycznego ANSI SQL dla przenośności w różnych hurtowniach danych w chmurze.
Rozwiązanie A: Podejście z łączeniem wewnętrznym. Ta metoda łączy tabelę z samą sobą na podstawie dopasowanych regionów i wcześniejszych znaczników czasowych, zliczając unikalnych sprzedawców dla każdego wiersza zewnętrznego. Zalety: Nie wymaga wsparcia dla funkcji okiennych i działa na starszych silnikach SQL-92. Wady: Algorytm wykazuje złożoność O(n²); dla milionów wierszy generuje pośrednie produkty kartezjańskie zużywające terabajty pamięci tymczasowej i nie kończy się w ciągu kilku godzin, co czyni go niepraktycznym.
Rozwiązanie B: Skorelowane podzapytanie skalarne. Tutaj klauzula SELECT zawiera podzapytanie: (SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp). Zalety: Jest deklaratywne i logicznie przejrzyste. Wady: Podzapytanie wykonuje się raz na każdy wiersz (120 milionów razy), co uniemożliwia przesunięcie predykatu i powoduje ogromny losowy I/O; optymalizatory baz danych nie mogą dekorelować agregatów unikalnych w różnych zakresach czasowych, co prowadzi do szacowanych czasów wykonania przekraczających 90 minut.
Rozwiązanie C: Technika funkcji okiennych ANSI SQL. Wykorzystanie ROW_NUMBER() do identyfikacji pierwszych wystąpień, a następnie bieżącego SUM(), jak pokazano w powyższym przykładzie kodu. Zalety: Wykonuje pojedyncze skanowanie tabeli z sortowaniem, wykorzystując możliwości spooling funkcji optymalizatora dla złożoności O(n log n) i ograniczonego użycia pamięci. Wady: Wymaga ostrożnego traktowania powiązanych znaczników czasowych; jeśli dwa zapisy mają identyczne znaczniki czasowe, nieokreślone sortowanie może podliczyć je dwa razy, chyba że do klauzuli ORDER BY zostanie dodany unikalny czynnik rozstrzygania (np. event_id).
Wybrane rozwiązanie i wynik. Zastosowano rozwiązanie C. Poprzez dodanie event_id do ORDER BY, aby zapewnić deterministyczne wykrywanie pierwszego wystąpienia, zapytanie wykonano w 4 minuty na istniejącym klastrze — co stanowi 9-krotną poprawę. Wynik umożliwił rzeczywiste panele kontrolne zgodności, pozwalając pracownikom ds. ryzyka monitorować różnorodność onboardingu bez opóźnień ETL, a zapytanie było całkowicie przenośne do PostgreSQL, Snowflake i BigQuery bez modyfikacji.
Dlaczego COUNT(DISTINCT column) OVER (ORDER BY ...) generuje błąd składni w rygorystycznym ANSI SQL?
Standard SQL wyraźnie zabrania użycia słowa kluczowego DISTINCT w argumencie funkcji agregatowej okna, takiej jak COUNT, SUM lub AVG. Chociaż niektórzy dostawcy (np. PostgreSQL 16+, Oracle) oferują to jako rozszerzenie własnościowe, ANSI SQL:2011 i wcześniejsze wersje ograniczają agregaty okienne do działania na wszystkich wierszach w określonej ramce. Ograniczenie to wynika z tego, że utrzymanie hash table dla zbioru unikalnych wartości dla każdego możliwego okna w czasie strumieniowym nie jest wymagane przez standardową gramatykę. Kandydaci muszą zauważyć, że DISTINCT jest dozwolone tylko w standardowych funkcjach agregatowych bez klauzul OVER, lub w funkcjach dystrybucyjnych odwrotnych, takich jak PERCENTILE_CONT, ale nigdy w ramach okna z unikalnym zliczeniem.
Jak radzisz sobie z duplikującymi znacznikami czasowymi podczas ustalania „pierwszego” wystąpienia podmiotu?
ROW_NUMBER() przypisuje dowolne wartości w przypadku remisu, chyba że klauzula ORDER BY określa pełne porządkowanie. Jeśli sprzedawca ma dwa wpisy z identycznymi znacznikami czasowymi, oba wiersze mogą potencjalnie otrzymać rn = 1, jeśli porządkowanie jest nieokreślone, co prowadzi do błędnego zwiększenia skumulowanego zliczenia. Rozwiązaniem jest dodanie unikalnego klucza głównego lub autoinkrementowanego ID do klauzuli ORDER BY: ORDER BY signup_timestamp, merchant_signup_id. To zapewnia deterministyczne porządkowanie, w którym wcześniejszy przydzielony ID jest uznawany za pierwsze wystąpienie, zachowując matematyczną integralność skumulowanego zliczenia unikalnych wartości.
Czy tę technikę można dostosować do przesuwającego się zliczenia unikalnych wartości w ramach stałej liczby wierszy (np. ostatnich 100 transakcji) zamiast nieograniczonego poprzedzania?
Nie, nieefektywnie z czystym ANSI SQL. Metoda nieograniczonego poprzedzania odnosi sukces, ponieważ unikalność jest monotoniczna — gdy podmiot się pojawi, pozostaje „zliczony” na zawsze. W ramce przesuwającej się (np. ROWS BETWEEN 100 PRECEDING AND CURRENT ROW) podmiot opuszczający okno musi zmniejszyć zliczenie, co wymaga wiedzy, czy opuszczający wiersz reprezentuje jedyną instancję tego podmiotu w bieżącej ramce. ANSI SQL nie ma operatorów agregacji tablic ani różnicy zestawów w ramach okien do skutecznego śledzenia takich egress. Implementacja tego wymagałaby albo rekurencyjnych CTE (które pogarszają się do O(n²) w tym scenariuszu), albo rozszerzeń własnościowych, takich jak ARRAY_AGG w połączeniu z operacjami na zbiorach, co naruszałoby rygorystyczną zgodność z ANSI.