SQL Interview Prep · Lekcja

NTILE do tworzenia przedziałów

Dzielenie wierszy na kwartyle i przedziały percentylowe

NTILE do tworzenia przedziałów to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 3 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Gdy potrzebne są koszyki o równej liczebności

Rekruterzy pytają: „Podziel klientów na cztery grupy o jednakowych rozmiarach według wydatków” albo „W którym decylu znajduje się każdy wiersz?” Służy do tego NTILE.

NTILE(n) rozdziela uporządkowane wiersze między n koszyków tak równomiernie, jak to możliwe, i nadaje każdemu wierszowi numer koszyka od 1 do n. W tej lekcji omówimy sposób podziału, obsługę nierównej liczby wierszy oraz różnice między NTILE a funkcjami rankingowymi.

Podstawowa składnia NTILE

Zastosowanie NTILE(4) do wierszy uporządkowanych według wartości tworzy kwartyle. Jak każda funkcja okna wymaga ona klauzuli OVER; zawarte w niej ORDER BY określa, które wiersze trafią do koszyków z niższymi, a które z wyższymi wartościami.

Porządek rosnący umieszcza najmniejsze wartości w koszyku 1, a porządek malejący odwraca tę kolejność.

SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customers;

Jak NTILE rozdziela wiersze

Przy 12 wierszach i NTILE(4) każdy koszyk otrzymuje dokładnie 12 / 4 = 3 wiersze. Koszyk 1 zawiera 3 najniższe wartości, a koszyk 4 — 3 najwyższe.

Najważniejsza zasada: NTILE dzieli według liczby wierszy, a nie według przedziałów wartości. Dwa koszyki mogą obejmować zupełnie różne zakresy wartości, o ile zawierają taką samą liczbę wierszy.

Nierówny podział

Co się dzieje, gdy liczby wierszy nie można podzielić równo? Przy 10 wierszach i NTILE(4) otrzymujemy 10 / 4 = 2 i resztę 2. NTILE przydziela wcześniejszym koszykom dodatkowe wiersze.

Koszyk 1: 3 wiersze
Koszyk 2: 3 wiersze
Koszyk 3: 2 wiersze
Koszyk 4: 2 wiersze

Rozmiary koszyków różnią się więc najwyżej o jeden, a większe koszyki znajdują się na początku. Ta dokładna zasada jest częstym szczegółem sprawdzanym podczas rozmów.

NTILE ignoruje remisy wartości

Istotna pułapka: NTILE nie umieszcza równych wartości w tym samym koszyku. Wypełnia koszyki według pozycji, więc dwa wiersze z identycznym total_spend mogą trafić do różnych koszyków wyłącznie ze względu na kolejność wierszy.

Jeśli firma wymaga, aby równe wartości należały do tego samego poziomu, NTILE jest niewłaściwym narzędziem; potrzebne jest podejście oparte na wartościach. Rekruterzy celowo zastawiają tę pułapkę.

Decyle i percentyle

Liczba koszyków to po prostu liczba przekazana jako argument. NTILE(10) tworzy decyle, a NTILE(100) przedziały percentylowe. W ten sposób analitycy dzielą użytkowników na poziomy wyników lub przedziały ryzyka.

Wynikiem jest numer przedziału, więc wartość w koszyku 9 funkcji NTILE(10) znajduje się w drugim najwyższym decylu.

SELECT
  user_id,
  score,
  NTILE(10) OVER (ORDER BY score DESC) AS decile
FROM leaderboard;

Koszyki tworzone w obrębie grup

Dodaj PARTITION BY, aby niezależnie dzielić dane na koszyki w obrębie każdej grupy, na przykład tworzyć kwartyle wydatków dla każdego regionu. Każdy region zaczyna numerację od koszyka 1.

Pozwala to odpowiedzieć na pytania takie jak „którzy klienci należą do górnego kwartylu w każdym regionie?”, ponieważ nawet region o niskich łącznych wydatkach ma własny koszyk 4.

SELECT
  region,
  customer_id,
  total_spend,
  NTILE(4) OVER (
    PARTITION BY region
    ORDER BY total_spend DESC
  ) AS regional_quartile
FROM customers;

Filtrowanie konkretnego poziomu

Nie można umieścić NTILE(...) bezpośrednio w klauzuli WHERE; funkcje okna są obliczane po wykonaniu WHERE. Należy opakować zapytanie w CTE lub podzapytanie, a następnie filtrować według kolumny zawierającej numer koszyka.

Ten wzorzec — „pokaż mi osoby wydające najwięcej, należące do górnego kwartylu” — jest najczęstszym zastosowaniem NTILE w rzeczywistych rozwiązaniach.

WITH q AS (
  SELECT customer_id, total_spend,
         NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
  FROM customers
)
SELECT customer_id, total_spend
FROM q
WHERE quartile = 1;

NTILE a percentyle oparte na wartościach

NTILE tworzy koszyki o równej liczbie wierszy. Jeśli zamiast tego potrzebują Państwo prawdziwego percentyla statystycznego, czyli wartości znajdującej się na przykład na 90. percentylu, należy użyć PERCENTILE_CONT lub PERCENTILE_DISC.

NTILE(100): określa, w którym przedziale percentylowym znajduje się wiersz, na podstawie pozycji w rankingu.
PERCENTILE_CONT(0.9): zwraca rzeczywistą wartość na 90. percentylu.

Znajomość tej różnicy odróżnia pewną odpowiedź od zgadywania.

Gdy ORDER BY ma znaczenie

NTILE wymaga ORDER BY wewnątrz OVER; bez określonej kolejności koszyki nie mają znaczenia. Kierunek sortowania decyduje o tym, który kraniec danych trafi do koszyka 1.

Jeśli remisy powodują niejednoznaczność przydziału, a dokładny koszyk wiersza granicznego ma znaczenie, należy dodać kolumnę rozstrzygającą do porządkowania, aby uzyskać deterministyczne i powtarzalne wyniki.

Nadawanie koszykom nazw

Surowe numery koszyków (1, 2, 3, 4) rzadko są końcowym rezultatem. Analitycy zazwyczaj mapują je na etykiety biznesowe, takie jak Niski, Średni, Wysoki i Najwyższy, za pomocą wyrażenia CASE stosowanego do wyniku NTILE.

Oblicz NTILE w CTE, a następnie przekształć numer w zewnętrznym zapytaniu. Dzięki temu logika funkcji okna pozostaje przejrzysta, a wynik jest gotowy do prezentacji.

WITH q AS (
  SELECT customer_id, total_spend,
         NTILE(4) OVER (ORDER BY total_spend) AS bucket
  FROM customers
)
SELECT customer_id, total_spend,
  CASE bucket
    WHEN 1 THEN 'Low'
    WHEN 2 THEN 'Medium'
    WHEN 3 THEN 'High'
    WHEN 4 THEN 'Top'
  END AS spend_tier
FROM q;

Szybkie sprawdzenie

Sprawdź zasadę nierównego rozdziału.

Podsumowanie

NTILE rozdziela uporządkowane wiersze na koszyki o równej liczebności:

NTILE(n) nadaje wierszom etykiety od 1 do n; NTILE(4) tworzy kwartyle, a NTILE(10) decyle.
Dzieli według liczby wierszy, a nie zakresu wartości, i przydziela dodatkowe wiersze wcześniejszym koszykom.
Nie umieszcza remisujących wartości w tym samym koszyku i nie można użyć go bezpośrednio w WHERE.
Aby uzyskać rzeczywistą wartość percentyla, należy użyć PERCENTILE_CONT.

W następnej części: pobieranie wartości granicznych za pomocą FIRST_VALUE, LAST_VALUE i krawędzi ramek.

Często zadawane pytania

Czy lekcja „NTILE do tworzenia przedziałów” jest bezpłatna?

Tak — pełny tekst „NTILE do tworzenia przedziałów” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „NTILE do tworzenia przedziałów”?

Dzielenie wierszy na kwartyle i przedziały percentylowe Ćwiczysz SQL Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 3 z 4.

Ile czasu zajmuje lekcja „NTILE do tworzenia przedziałów”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Interview Prep?

Tak. Każda lekcja SQL Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

LAG i LEAD dla sąsiednich wierszy
Zmiany okres do okresu
NTILE do tworzenia przedziałów
FIRST_VALUE, LAST_VALUE i krawędzie ramki

← Powrót do SQL Interview Prep