0Pricing
SQL Interview Prep · Lekcja

Rozkład skumulowany i procent całości

Obliczanie procentów narastająco i udziału w całości w obrębie partycji

Rozkład skumulowany i procent całości to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 4 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.

Pytanie o udział w całości

To częste pytanie podczas rozmów kwalifikacyjnych dotyczących raportowania: „Jaki procent całkowitego przychodu stanowi każda kategoria?” oraz jego narastający odpowiednik: „Jaki jest narastający udział w całości?”

Trik polega na podzieleniu wartości każdego wiersza przez agregat okna obliczony dla całej partycji. Sprawdzana jest właśnie wiedza o tym, że sumę całkowitą można umieścić w funkcji okna bez konieczności korzystania ze złączenia tabeli z samą sobą.

SUM w funkcji okna bez ORDER BY = suma całkowita

Najważniejszy element to SUM(amount) OVER (): puste OVER, bez ORDER BY, zwraca sumę całego zbioru wyników i powtarza ją w każdym wierszu.

Ponieważ nie ma ORDER BY, nie ma też ramki narastającej, więc domyślna ramka obejmuje całą partycję. Ta sama suma w każdym wierszu jest dokładnie mianownikiem potrzebnym do obliczenia udziału procentowego w całości.

SELECT
  category,
  amount,
  SUM(amount) OVER () AS grand_total
FROM category_sales;

Obliczanie udziału procentowego w całości

Podziel wartość wiersza przez całkowitą sumę obliczoną za pomocą funkcji okna i pomnóż wynik przez 100. Rzutuj wartość na typ dziesiętny, aby dzielenie całkowitoliczbowe nie obcięło wyniku do zera.

To zapytanie wykonywane w jednym przebiegu zastępuje starszy wzorzec, w którym podzapytanie obliczające sumę było łączone z powrotem ze szczegółowymi wierszami. Jest krótsze, szybsze i czytelne.

SELECT
  category,
  amount,
  ROUND(
    100.0 * amount / SUM(amount) OVER (),
    2
  ) AS pct_of_total
FROM category_sales;

Pułapka dzielenia całkowitoliczbowego

To klasyczna pułapka na rozmowie kwalifikacyjnej: w wielu bazach danych wyrażenie amount / total dla kolumn całkowitoliczbowych wykonuje dzielenie całkowitoliczbowe, więc wynik mniejszy od 1 staje się równy 0.

Można temu zapobiec, najpierw mnożąc przez 100.0 (literał numeryczny), albo rzutując jeden z operandów: amount::numeric / total. Pominięcie tego kroku zwraca kolumnę zer, co osoby prowadzące rozmowę zauważają natychmiast.

SELECT
  category,
  amount * 1.0 / SUM(amount) OVER () AS share,
  CAST(amount AS DECIMAL) / SUM(amount) OVER () AS share_alt
FROM category_sales;

Udział procentowy w całości w obrębie grupy

Dodaj PARTITION BY, aby udział każdego wiersza odnosił się do jego grupy, a nie do całej tabeli. Na przykład może to być procent sprzedaży każdego produktu w obrębie jego własnego regionu.

Mianownik SUM(amount) OVER (PARTITION BY region) jest teraz resetowany dla każdego regionu, więc wartości procentowe w obrębie każdego regionu sumują się do 100.

SELECT
  region,
  product,
  amount,
  ROUND(
    100.0 * amount / SUM(amount) OVER (PARTITION BY region),
    2
  ) AS pct_of_region
FROM regional_sales;

Narastający udział w całości

Połącz licznik narastający ze stałym mianownikiem, aby uzyskać narastający udział w całości, czyli informację o tym, jaka część całkowitej sumy zgromadziła się do danego wiersza.

Licznik używa ORDER BY (suma narastająca), a mianownik — pustego OVER () (suma całkowita). Ostatni wiersz zawsze osiąga 100%.

SELECT
  sale_date,
  amount,
  ROUND(
    100.0 * SUM(amount) OVER (ORDER BY sale_date)
          / SUM(amount) OVER (),
    2
  ) AS running_pct
FROM daily_sales;

CUME_DIST: rozkład skumulowany

SQL ma wbudowaną funkcję obliczającą rozkład skumulowany: CUME_DIST(). Zwraca ona ułamek wierszy, których wartość ORDER BY jest mniejsza lub równa wartości bieżącego wiersza, czyli liczbę z przedziału (0, 1].

W przeciwieństwie do ręcznie obliczanego narastającego udziału kwoty, CUME_DIST dotyczy pozycji wiersza i odpowiada na pytanie: „Jaka część wierszy ma wartość nie większą od tej wartości?”. Jest przydatna w raportowaniu opartym na percentylach.

SELECT
  score,
  CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM exam_results;

PERCENT_RANK i jego różnica

Bliskim odpowiednikiem jest PERCENT_RANK(), zdefiniowane jako (rank - 1) / (total_rows - 1), przyjmujące wartości od 0 do 1.

Różnica istotna podczas rozmowy kwalifikacyjnej jest następująca: CUME_DIST uwzględnia bieżący wiersz w liczniku („w tej wartości lub poniżej”), natomiast PERCENT_RANK oznacza względną rangę zaczynającą się od 0 dla pierwszego wiersza. Funkcje zwracają różne wartości, a ich pomylenie jest częstym błędem.

SELECT
  score,
  CUME_DIST()    OVER (ORDER BY score) AS cd,
  PERCENT_RANK() OVER (ORDER BY score) AS pr
FROM exam_results;

Analiza Pareto / 80–20

Narastający udział w całości umożliwia przeprowadzenie analizy Pareto: „Którzy klienci z czołówki generują 80% przychodu?”. Posortuj dane malejąco według amount, oblicz narastający udział, a następnie odfiltruj wiersze, w których udział skumulowany po raz pierwszy przekracza 80%.

Ponieważ wyników funkcji okna nie można umieścić w WHERE, należy opakować obliczenie w CTE i filtrować w zapytaniu zewnętrznym — jest to ta sama zasada, która dotyczy każdej funkcji okna.

WITH ranked AS (
  SELECT
    customer_id,
    revenue,
    SUM(revenue) OVER (ORDER BY revenue DESC)
      / SUM(revenue) OVER () AS running_share
  FROM customer_revenue
)
SELECT *
FROM ranked
WHERE running_share <= 0.80;

Zaokrąglanie i uzgadnianie

Uwaga: zaokrąglenie każdego procentu do 2 miejsc po przecinku może sprawić, że suma kolumny wyniesie 99.99 lub 100.01 zamiast dokładnie 100. Podczas rozmowy kwalifikacyjnej może paść pytanie, jak zagwarantować, że części sumują się do całości.

Typowe odpowiedzi to: zaokrąglać tylko na potrzeby wyświetlania, zachować pełną precyzję w obliczeniach albo zastosować korektę metodą największych reszt do jednego wiersza. Ważniejsze od samego rozwiązania jest nazwanie problemu.

Najważniejsze punkty podsumowania rozmowy

Najważniejsze wnioski, które należy umieć wyjaśnić:

  • SUM(x) OVER () bez ORDER BY oznacza sumę całkowitą w każdym wierszu.
  • Należy pomnożyć przez 100.0, aby uniknąć dzielenia całkowitoliczbowego.
  • PARTITION BY służy do obliczania udziałów w poszczególnych grupach.
  • Skumulowany licznik podzielony przez mianownik będący sumą całkowitą daje udział narastający.
  • CUME_DIST i PERCENT_RANK służą do analizy rozkładu; należy znać różnice między nimi.
  • Należy użyć CTE, aby filtrować dane według analizy Pareto lub progu.

Szybkie sprawdzenie

Jak uzyskać sumę całkowitą całego zbioru wyników w każdym wierszu?

Podsumowanie: rozkład i procent całości

Procent całości uzyskuje się, dzieląc wartość wiersza przez SUM(x) OVER (), czyli sumę całkowitą zwracaną w każdym wierszu; należy zawsze pomnożyć przez 100.0, aby uniknąć dzielenia całkowitoliczbowego, oraz dodać PARTITION BY, aby obliczać udziały w poszczególnych grupach. Skumulowany licznik podzielony przez mianownik będący sumą całkowitą daje udział narastający, który kończy się na poziomie 100% i stanowi podstawę analizy Pareto.

Do analizy rozkładu opartej na pozycji należy użyć CUME_DIST i PERCENT_RANK, pamiętając o kwestii uzgadniania zaokrągleń. Na tym kończy się omówienie sum narastających i średnich ruchomych.

Często zadawane pytania

Czy lekcja „Rozkład skumulowany i procent całości” jest bezpłatna?

Tak — pełny tekst „Rozkład skumulowany i procent całości” 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 „Rozkład skumulowany i procent całości”?

Obliczanie procentów narastająco i udziału w całości w obrębie partycji Ć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 4 z 4.

Ile czasu zajmuje lekcja „Rozkład skumulowany i procent całości”?

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

  1. Sumy narastające z ramkami okienkowymi
  2. Ramki ROWS a RANGE
  3. Średnie kroczące w przesuwanym oknie
  4. Rozkład skumulowany i procent całości
← Powrót do SQL Interview Prep