Obliczenia opierają się na metodzie najmniejszych kwadratów. Nachylenie (β) jest definiowane jako kowariancja zmiennej niezależnej X i zmiennej zależnej Y podzielona przez wariancję X. Wyraz wolny (α) pochodzi z średniej Y minus iloczyn nachylenia i średniej X. W ANSI SQL wdraża się te definicje algebraiczne przy użyciu agregatów SUM, AVG i COUNT, zwykle w klauzuli GROUP BY lub jako funkcje okienne z klauzulą OVER. Zapytanie musi wyraźnie obliczać sumę iloczynów cross-products (Σ(X - X̄)(Y - Ȳ)) oraz sumę odchyleń kwadratowych dla X (Σ(X - X̄)²), aby rozwiązać końcowe współczynniki.
Zespół analityki detalicznej potrzebował określić elastyczność cenową popytu dla każdej kategorii produktów, aby zoptymalizować strategie dynamicznego ustalania cen. Dysponowali tabelą transakcji zawierającą unit_price i quantity_sold, i potrzebowali linii trendu, która kwantyfikowałaby, jak ilość sprzedana zmieniała się wraz z ceną dla każdego unikalnego category_id.
Jedno z proponowanych rozwiązań polegało na eksportowaniu dziennych agregatów do zewnętrznego skryptu Python z wykorzystaniem scikit-learn do dopasowania modeli regresji. To podejście oferowało prostotę wdrożenia i dostęp do bogatej diagnostyki statystycznej. Jednak wprowadzało to znaczną latencję danych, naruszało ścisłe zasady zarządzania danymi, tworząc zewnętrzne kopie wrażliwych danych dotyczących sprzedaży i uniemożliwiało aktualizacje panelu na żywo wymagane dla zautomatyzowanych algorytmów ustalania cen.
Inną rozważaną opcją było utworzenie funkcji agregacyjnej zdefiniowanej przez użytkownika (UDAF) w silniku bazy danych, co pozwoliłoby na składnię taką jak REGRESS_SLOPE(price, quantity). Choć eleganckie i wielokrotnego użytku, poświęcało to przenośność pomiędzy różnymi systemami baz danych i wymagało podwyższonych uprawnień administracyjnych do wdrożenia, co czyniło je nieodpowiednim dla zablokowanego środowiska multi-tenant w hurtowni danych w chmurze.
Wybrane rozwiązanie wdrażało bezpośrednio wzory algebraiczne w ANSI SQL przy użyciu standardowych agregatów. Zespół wykorzystał funkcje okienne SUM i AVG podzielone według category_id, aby obliczyć niezbędne terminy kowariancji i wariancji w jednym przejściu nad danymi. To podejście utrzymywało obliczenia zlokalizowane z danymi, eliminowało opóźnienia dotyczące ekstrakcji-transformacji-ładowania (ETL) i ściśle przestrzegało standardów ANSI SQL bez własnych rozszerzeń. Efektem było zestawienie elastyczności cenowej o opóźnieniu poniżej sekundy, które automatycznie aktualizowało się w miarę napływu nowych transakcji, umożliwiając algorytmom ustalania cen dostosowywanie marż w czasie rzeczywistym.
Jak radzisz sobie z wartościami NULL w X lub Y, nie unieważniając obliczeń całej grupy?
Kandydaci często zapominają, że podczas gdy funkcje agregujące ANSI SQL ignorują NULL-e, operacje arytmetyczne z udziałem NULL-ów zwracają NULL. Przy obliczaniu terminu kowariancji SUM((x - avg_x) * (y - avg_y)), jeśli zarówno x, jak i y są NULL dla konkretnego wiersza, iloczyn staje się NULL i ten wiersz zostaje wyłączony z sumy. To skutecznie wykonuje usunięcie parami, co jest zazwyczaj pożądane, ale trzeba upewnić się, że COUNT, używane dla stopni swobody w obliczeniach wariancji, odzwierciedla liczbę par nie-NULL, a nie wszystkich wierszy. Rozwiązaniem jest filtracja WHERE x IS NOT NULL AND y IS NOT NULL w podzapytaniu lub użycie COUNT(x) (które równa się COUNT(y) po filtracji) zamiast COUNT(*), zapewniając spójne mianowniki w wszystkich terminach agregacyjnych.
Jaka jest różnica między obliczaniem regresji dla populacji a próbki i jak wpływa to na twoje zapytanie SQL?
Wielu kandydatów stosuje wzór na wariancję próby (dzieląc przez n - 1) niespójnie z wzorem kowariancji. W ANSI SQL wbudowane funkcje takie jak VAR_POP i VAR_SAMP obsługują tę różnicę, ale przy ręcznym obliczaniu wariancji jako SUM(POWER(x - avg_x, 2)) / COUNT(*), musisz świadomie wybrać mianownik. Dla obliczenia nachylenia, jeśli ręcznie obliczasz wariancję X w mianowniku, musisz dopasować ją do mianownika obliczenia kowariancji. Mieszanie ich (np. kowariancja próby podzielona przez wariancję populacji) prowadzi do stronniczego nachylenia. Skorygowane podejście to zdecydowanie o ramie statystycznej (populacja vs. próbka) i zastosowanie tej samej logiki mianownikowej (lub n, lub n-1) zarówno do licznika kowariancji, jak i do mianownika wariancji.
Jak obliczyłbyś współczynnik determinacji (R²), aby zmierzyć dopasowanie w ramach tego samego zapytania?
Kandydaci często pomijają metryki walidacyjne. R² oblicza się jako 1 - (SS_res / SS_tot), gdzie SS_res to suma kwadratów reszt (Σ(y - ŷ)²), a SS_tot to całkowita suma kwadratów (Σ(y - ȳ)²). Obliczenie ŷ (przewidywanej y) wymaga nachylenia i wyrazu wolnego obliczonych w poprzednich krokach. W ANSI SQL można to obliczyć za pomocą zagnieżdżonych wyrażeń wspólnych (CTEs): najpierw obliczyć średnie, następnie obliczyć nachylenie i wyraz wolny w drugim CTE, a na końcu obliczyć kwadratowe różnice między rzeczywistymi a przewidywanymi wartościami w zapytaniu zewnętrznym. Częstym błędem jest próba odwołania się do obliczonego nachylenia w ramach tego samego poziomu agregacji, w którym jest ono obliczane, co narusza kolejność przetwarzania logicznego. Rozwiązaniem jest oddzielenie logiki w sekwencyjnych CTE, aby obliczone współczynniki mogły być ponownie używane jako stałe w końcowej agregacji dla R².