Histogram w SQL: od zamówień do rozkładu klientów

Kiedy widzę w raporcie średnią liczbę zamówień na klienta, od razu chcę zajrzeć pod spód. Ta sama średnia może opisywać grupę podobnych osób albo mieszaninę klientów kupujących raz i kilku bardzo aktywnych. Biznesowo to dwie różne sytuacje, choć na kafelku dashboardu zmieści się ta sama liczba.

W tym artykule buduję rozkład liczby zamówień w SQL. Najpierw liczę zamówienia każdego klienta, potem klientów z daną liczbą zakupów, a na końcu tworzę czytelne przedziały. Po drodze pokazuję podzapytanie, CTE i CASE WHEN. Wszystko na małym zbiorze, który można sprawdzić ręcznie bez przygotowywania ekspedycji do hurtowni danych.

Przykłady używają składni działającej w PostgreSQL. Dane są syntetyczne, a tabele tymczasowe służą do ćwiczenia w osobnej sesji. Celowo dokładam klienta bez zakupów, anulowane zamówienie i zakup spoza okresu. To właśnie takie szczegóły decydują, czy wynik odpowiada na pytanie, które ktoś zadał.

Co właściwie chcę policzyć

Pytanie brzmi: ilu zarejestrowanych klientów złożyło w styczniu 2025 roku zero, jedno, od dwóch do pięciu, od sześciu do dziesięciu oraz ponad dziesięć zakończonych zamówień? Słowo „zarejestrowanych” jest ważne. Jeśli zacznę wyłącznie od tabeli zamówień, nie zobaczę osób, które niczego nie zamówiły.

Populację ustalam tutaj jako sześciu klientów istniejących w całej próbce. W prawdziwej analizie doprecyzowałbym, kto kwalifikuje się do obserwacji: osoby zarejestrowane przed końcem miesiąca, aktywne konta, konkretny kraj. Bez tego mianownik może zmieniać się wraz z techniczną zawartością tabeli.

Używam potocznego określenia histogram, bo buduję tabelę częstości wartości liczbowej. Dla dokładnej liczby zamówień jest to rozkład dyskretny. Po zebraniu wyników w nierówne przedziały otrzymuję segmenty, które najczytelniej pokazać jako wykres słupkowy z nazwami kategorii. W klasycznym histogramie szerokość przedziału ma znaczenie dla interpretacji powierzchni słupka, co wyjaśnia materiał NIST.

W praktyce nie rozciągałbym kategorii „11+” na numerycznej osi o udawanej szerokości. Ta kategoria nie ma górnej granicy. W SQL przygotowuję jej liczebność, a w narzędziu BI nadaję jej czytelną etykietę. Rysunek powinien pomagać zrozumieć dane, zamiast wymagać obrony na seminarium ze statystyki.

Przygotowuję mały zbiór danych

Poniższy blok uruchamiam raz w nowej sesji. Tworzę sześciu klientów i 27 zamówień. Dwadzieścia pięć pasuje do analizy, jedno jest anulowane, a jedno pochodzi z lutego. Identyfikator zamówienia jest unikalny, więc każdy wiersz tabeli orders opisuje jedno zamówienie.

-- example: fixture
CREATE TEMP TABLE customers (
    customer_id INTEGER PRIMARY KEY
);
CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    ordered_at DATE NOT NULL,
    status TEXT NOT NULL
);
INSERT INTO customers (customer_id) VALUES (1), (2), (3), (4), (5), (6);
INSERT INTO orders (order_id, customer_id, ordered_at, status) VALUES
    (1, 2, '2025-01-03', 'completed'),
    (2, 3, '2025-01-03', 'completed'),
    (3, 3, '2025-01-04', 'completed'),
    (4, 4, '2025-01-03', 'completed'),
    (5, 4, '2025-01-04', 'completed'),
    (6, 4, '2025-01-05', 'completed'),
    (7, 4, '2025-01-06', 'completed'),
    (8, 4, '2025-01-07', 'completed'),
    (9, 5, '2025-01-03', 'completed'),
    (10, 5, '2025-01-04', 'completed'),
    (11, 5, '2025-01-05', 'completed'),
    (12, 5, '2025-01-06', 'completed'),
    (13, 5, '2025-01-07', 'completed'),
    (14, 5, '2025-01-08', 'completed'),
    (15, 6, '2025-01-03', 'completed'),
    (16, 6, '2025-01-04', 'completed'),
    (17, 6, '2025-01-05', 'completed'),
    (18, 6, '2025-01-06', 'completed'),
    (19, 6, '2025-01-07', 'completed'),
    (20, 6, '2025-01-08', 'completed'),
    (21, 6, '2025-01-09', 'completed'),
    (22, 6, '2025-01-10', 'completed'),
    (23, 6, '2025-01-11', 'completed'),
    (24, 6, '2025-01-12', 'completed'),
    (25, 6, '2025-01-13', 'completed'),
    (26, 2, '2025-01-15', 'cancelled'),
    (27, 2, '2025-02-01', 'completed');

Klient pierwszy nie ma żadnego zamówienia. Drugi ma jedno pasujące do okresu i statusu, mimo że w tabeli występuje trzy razy. Kolejni mają dwa, pięć, sześć i jedenaście. Zapisuję te oczekiwania przed tworzeniem zapytania. W przeciwnym razie łatwo uznać pierwszy wynik, który się wykonał, za wynik poprawny.

Tabela z rzeczywistego systemu może mieć inny poziom szczegółowości. Jeśli jeden wiersz oznacza produkt w zamówieniu, samo COUNT(*) policzy pozycje. Jeśli dane zawierają historię statusów, jedno zamówienie pojawi się wielokrotnie. Zanim zmienię funkcję agregującą, sprawdzam znaczenie wiersza i kluczy.

Liczę zamówienia, a potem klientów

Na początek celowo ograniczam się do klientów, którzy mają pasujące zamówienia. Filtruję styczeń przed agregacją i biorę wyłącznie status completed. Zakres dat zapisuję z domkniętym początkiem i otwartym końcem. Ten sposób łatwo rozszerzyć na znaczniki czasu, bez zgadywania ostatniej sekundy miesiąca.

-- example: active-customers
SELECT customer_id, COUNT(*) AS order_cnt
FROM orders
WHERE ordered_at >= '2025-01-01'
  AND ordered_at < '2025-02-01'
  AND status = 'completed'
GROUP BY customer_id
ORDER BY customer_id;

Otrzymuję klientów 2, 3, 4, 5 i 6 z wynikami odpowiednio 1, 2, 5, 6 i 11. Jeden wiersz oznacza teraz jednego klienta. Nie mam już tabeli pojedynczych zdarzeń, tylko podsumowanie na poziomie osoby. Warto zatrzymać się przy tej zmianie, bo ona tłumaczy resztę rozwiązania.

Aby odpowiedzieć, ilu klientów złożyło daną liczbę zamówień, potrzebuję drugiej agregacji. Wewnętrzne zapytanie liczy zakupy osoby, a zewnętrzne zlicza wiersze dla danej liczby zakupów. Tutaj podzapytanie daje mi nazwany wynik pośredni, na którym mogę pracować jak na tabeli.

-- example: subquery
SELECT order_cnt, COUNT(*) AS customer_cnt
FROM (
    SELECT customer_id, COUNT(*) AS order_cnt
    FROM orders
    WHERE ordered_at >= '2025-01-01'
      AND ordered_at < '2025-02-01'
      AND status = 'completed'
    GROUP BY customer_id
) AS orders_per_customer
GROUP BY order_cnt
ORDER BY order_cnt;

W próbce każda z liczb 1, 2, 5, 6 i 11 ma jednego klienta. Przy większym zbiorze poszczególne liczebności będą różne. Nie sumuję tutaj order_cnt, ponieważ pytam o klientów. Sumowanie odpowiedziałoby na pytanie o zamówienia przypadające na grupę, czyli inną miarę.

To dobry moment na kontrolę. Suma kolumny customer_cnt powinna wynosić pięć, bo tylu klientów ma zakupy w zakresie. Suma iloczynów order_cnt * customer_cnt powinna wynosić 25. Druga kontrola pozwala zobaczyć, czy agregacja zachowała liczbę pasujących zamówień.

Dodaję CTE i klientów bez zakupów

Lubię CTE, gdy zapytanie ma kilka etapów, którym warto nadać nazwy. WITH pozwala mi wyodrębnić fragment przygotowujący liczbę zamówień. To ułatwienie czytania, a nie automatyczna obietnica szybszego działania. Szczegóły przetwarzania opisuje dokumentacja PostgreSQL dotycząca CTE.

Teraz zmieniam punkt startowy na tabelę klientów. LEFT JOIN zachowuje klienta nawet wtedy, kiedy nie ma dopasowanego zamówienia. Filtry dat i statusu umieszczam w ON, tak aby określały, które zamówienia pasują, bez usuwania samego klienta z wyniku.

-- example: all-customers
WITH orders_per_customer AS (
    SELECT c.customer_id, COUNT(o.order_id) AS order_cnt
    FROM customers AS c
    LEFT JOIN orders AS o
      ON o.customer_id = c.customer_id
     AND o.ordered_at >= '2025-01-01'
     AND o.ordered_at < '2025-02-01'
     AND o.status = 'completed'
    GROUP BY c.customer_id
)
SELECT customer_id, order_cnt
FROM orders_per_customer
ORDER BY customer_id;

Wynik to sześć wierszy z liczbami 0, 1, 2, 5, 6 i 11. Celowo używam COUNT(o.order_id). Funkcja pomija NULL, więc klient bez dopasowania otrzymuje zero. COUNT(*) policzyłby jego zachowany wiersz i dał jeden. To bardzo mała zmiana zapisu i bardzo duża zmiana odpowiedzi.

Gdybym przeniósł warunek statusu do WHERE, wiersz bez zamówienia nie spełniłby porównania. Klient z zerem zniknąłby z wyniku. Tak można przypadkowo ograniczyć rezultat lewego złączenia do dopasowanych rekordów. Opis złączeń w PostgreSQL pokazuje, skąd biorą się wartości NULL po stronie bez dopasowania.

W tym modelu COUNT(o.order_id) wystarcza, bo klucz zamówienia jest unikalny i nie dokładam tabeli pozycji. COUNT(DISTINCT o.order_id) przydaje się w niektórych szerszych złączeniach, ale nie traktuję go jak plastra na każdy problem. Rozmnożone kwoty sprzedaży nadal pozostaną rozmnożone, nawet jeśli licznik zamówień uda się uratować.

Buduję przedziały z CASE WHEN

Dokładne liczby zakupów są przydatne do kontroli, lecz przy szerokim rozkładzie tworzą długą tabelę. Grupuję je więc w pięć kategorii. Każdy klient ma trafić dokładnie do jednej, a zestaw warunków ma pokryć wszystkie wartości możliwe w tym modelu, czyli nieujemne liczby całkowite.

Warunki CASE są sprawdzane po kolei. Po obsłużeniu zera i jedynki warunek order_cnt <= 5 obejmuje już tylko pozostałe wartości od dwóch do pięciu. Podobnie kolejny warunek obejmuje sześć do dziesięciu. Dokumentacja CASE opisuje właśnie ten wybór pierwszego pasującego warunku.

-- example: buckets
WITH orders_per_customer AS (
    SELECT c.customer_id, COUNT(o.order_id) AS order_cnt
    FROM customers AS c
    LEFT JOIN orders AS o
      ON o.customer_id = c.customer_id
     AND o.ordered_at >= '2025-01-01'
     AND o.ordered_at < '2025-02-01'
     AND o.status = 'completed'
    GROUP BY c.customer_id
), classified AS (
    SELECT customer_id,
        CASE
            WHEN order_cnt = 0 THEN 0
            WHEN order_cnt = 1 THEN 1
            WHEN order_cnt <= 5 THEN 2
            WHEN order_cnt <= 10 THEN 3
            ELSE 4
        END AS bucket_id
    FROM orders_per_customer
), bins (bucket_id, bucket_label) AS (
    VALUES (0, '0'), (1, '1'), (2, '2-5'), (3, '6-10'), (4, '11+')
)
SELECT b.bucket_label, COUNT(c.customer_id) AS customer_cnt
FROM bins AS b
LEFT JOIN classified AS c ON c.bucket_id = b.bucket_id
GROUP BY b.bucket_id, b.bucket_label
ORDER BY b.bucket_id;

Osobny identyfikator przedziału rozwiązuje problem sortowania etykiet. Tekstowe 11+ nie powinno wskoczyć przed 2-5 tylko dlatego, że komputer poprawnie posortował napisy. Oddzielam kolejność biznesową od tego, co zobaczy odbiorca.

CTE bins przechowuje komplet kategorii. Dzięki lewemu złączeniu widzę również przedział bez klientów, z wynikiem zero. Gdybym grupował wyłącznie tabelę classified, pusta kategoria zniknęłaby całkowicie. W raporcie porównującym miesiące takie znikanie utrudnia czytanie i może zmieniać układ wykresu.

Granice wybrałem dla tego przykładu. W realnej pracy wynikałyby z pytania i rozkładu danych. Jeśli niemal wszyscy klienci trafiają do 11+, potrzebuję dokładniejszego podziału tej części. Jeśli każda kategoria ma jedną osobę, raport może być zbyt szczegółowy dla posiadanej próbki.

Sprawdzam wynik i przypadki brzegowe

Najpierw kontroluję sumę klientów: 1 + 1 + 2 + 1 + 1 daje sześć, zgodnie z populacją wejściową. Sprawdzam też, czy każdy klient ma jedną kategorię. Każda utrata albo podwójne przypisanie oznacza, że trzeba wrócić do definicji lub złączeń.

Granice testuję osobno: zero, jeden, dwa, pięć, sześć, dziesięć i jedenaście. W próbce mam większość z nich, a dziesięć mogę uzyskać przez zmianę liczby zamówień klienta szóstego. Szczególnie pilnuję wartości na styku przedziałów. Właśnie tam najłatwiej zgubić rekord przez użycie < zamiast <=.

Potem sprawdzam skrajne sytuacje. Przy braku pasujących zamówień wszyscy klienci powinni trafić do zera. Przy pustej tabeli klientów wszystkie kategorie powinny pozostać widoczne z liczebnością zero. Anulowane zamówienie nie może zmienić wyniku, a zakup z pierwszego lutego musi pozostać poza styczniem.

Dla udziałów procentowych wybieram mianownik świadomie. W całej populacji dwie osoby w grupie 2-5 oznaczają 2/6, czyli około 33,33%. Wśród samych kupujących byłoby to 2/5, czyli 40%. Obie wartości mają sens, jeśli odpowiednio je podpiszę. Bez podpisu czytelnik musi zgadywać, kogo naprawdę obejmuje analiza.

Zaokrąglenia mogą sprawić, że suma widocznych procentów będzie minimalnie różna od 100%. Nie poprawiam pojedynczej grupy ręcznie tylko po to, żeby tabela wyglądała idealnie. Zachowuję dokładne liczby do obliczeń, a prezentację opisuję zgodnie z przyjętą precyzją.

Duplikaty, braki i granice czasu

Jeżeli zamówienie występuje kilka razy, ustalam powód: pozycje, historia statusów, powtórzony import czy faktyczny błąd. Dopiero wtedy wybieram sposób sprowadzenia danych do poziomu zamówienia. Usunięcie identycznych wierszy bez zrozumienia ich znaczenia może wyrzucić prawidłową informację.

Przy identyfikatorze klienta równym NULL pytam, czy chodzi o zakup gościnny, błąd integracji czy niekompletną historię. W moim ćwiczeniu pole jest wymagane. W prawdziwym zbiorze nie traktowałbym wszystkich brakujących identyfikatorów jak jednej wyjątkowo aktywnej osoby. Taką grupę opisałbym osobno.

W danych ze znacznikiem czasu ustalam strefę biznesową i granice miesiąca przed filtrowaniem. Zamówienie tuż po północy może przypadać na inną datę w innym kraju. Na małej próbce to szczegół, a przy raportowaniu na przełomie okresów potrafi wywołać regularne rozbieżności między zespołami.

Jak czytam rozkład i rozwijam analizę

Duża grupa klientów z jednym zamówieniem podsuwa mi pytanie o powroty, ale nie dowodzi niskiej lojalności. Klient mógł zarejestrować się pod koniec miesiąca. Mógł kupić produkt potrzebny raz na kilka lat. Mógł też mieć wcześniejsze zakupy poza analizowanym okresem. Zanim opowiem historię o retencji, sprawdzam te możliwości.

Porównałbym klientów według stażu, kanału pozyskania i typu produktu, dbając o wystarczającą liczebność grup. Dobrym rozwinięciem jest równe okno obserwacji od pierwszego zakupu. Wtedy każda osoba ma podobną szansę na wykonanie kolejnego zamówienia, choć nadal trzeba pilnować sezonowości i zmian oferty.

Na potrzeby raportu zapisałbym obok wyniku populację, zakres dat, dozwolone statusy i definicje przedziałów. Dodałbym jedną obserwację i jeden proponowany następny krok. Jeśli nie da się sformułować sensownego działania, sprawdziłbym, czy wybrane grupowanie odpowiada rzeczywistej potrzebie.

Wydajność oceniam dopiero po ustaleniu poprawności. Przy dużym zbiorze sprawdzam plan wykonania, objętość danych i istniejące indeksy. Samo przepisanie podzapytania na CTE nie jest pomiarem. Nie obiecuję też, że jeden indeks będzie właściwy dla każdego układu tabel i sposobu filtrowania.

Jak przygotowałbym wersję do regularnego raportu

W jednorazowym ćwiczeniu daty wpisuję bezpośrednio do zapytania. W regularnym raporcie przekazywałbym początek i koniec okresu jako parametry, obsługiwane zgodnie z mechanizmem używanego narzędzia. Unikałbym ręcznego podmieniania tego samego miesiąca w kilku miejscach. Wystarczy przeoczyć jedno, żeby dwie części raportu zaczęły opisywać różne populacje.

Zapisałbym też datę odświeżenia i informację o kompletności danych. Raport uruchomiony pierwszego dnia miesiąca może nie zawierać wszystkich zdarzeń z końcówki poprzedniego. Jeżeli system uzupełnia je z opóźnieniem, odbiorca powinien wiedzieć, kiedy wynik jest jeszcze wstępny. Sama etykieta „styczeń” nie mówi, czy dane zdążyły już dojechać.

Przy eksporcie do narzędzia wizualizacji zachowałbym identyfikator przedziału obok etykiety i liczebności. Pozwoli to ustawić kolejność nawet wtedy, kiedy program domyślnie sortuje tekst. Kategorie z zerem również wysyłam dalej. Dzięki temu brak słupka oznacza zero klientów, a nie brak wiersza, którego znaczenie trzeba odgadywać.

Gdy raport obejmuje kilka miesięcy, przygotowałbym pełny zestaw par miesiąc i przedział. Dopiero do niego dołączyłbym policzone wartości. To rozwinięcie tej samej zasady, którą zastosowałem w bins: najpierw określam, co ma być widoczne, potem dokładam obserwacje. Pusty miesiąc staje się czytelnym wynikiem, zamiast znikać między sąsiednimi datami.

Liczba zamówień i wartość klienta to różne pytania

Ktoś z jedenastoma drobnymi zakupami może wydać mniej niż osoba z jednym dużym zamówieniem. Dlatego z samej częstotliwości nie robię rankingu najlepszych klientów. Jeśli potrzebuję ocenić wartość, do poziomu klienta dokładam osobno przychód, zwroty i odpowiednio zdefiniowaną marżę. Dopiero taki zestaw pokazuje kilka stron relacji.

Szczególnie uważałbym na interpretację grupy zerowej. Zero w styczniu oznacza brak pasujących zamówień w styczniu. Nie mówi, że klient nigdy niczego nie kupił, że odszedł na zawsze ani że konto jest bezwartościowe. Wniosek o odejściu wymaga definicji nieaktywności dobranej do produktu i wystarczająco długiej obserwacji.

Sprawdziłbym także zmianę identyfikatorów. Jedna osoba z dwoma kontami będzie w tym modelu dwoma klientami, jeśli tabela tak ją zapisuje. Nie poprawiam tego arbitralnie na podstawie podobnych nazw. Potrzebna jest uzgodniona reguła identyfikacji. W opisie raportu uczciwie wskazuję, czy mierzę konta, osoby, czy jednostki firmowe.

Małe ćwiczenie z przewidywania wyniku

Zacząłbym od dodania siódmego klienta bez zamówień. Oczekuję wtedy dwóch klientów w grupie zerowej i siedmiu w całej populacji. Liczba zakończonych zamówień pozostaje równa 25. Jeśli zmienia się coś jeszcze, mam konkretny sygnał do sprawdzenia zapytania.

Następnie przeniósłbym jedno zamówienie klienta szóstego poza analizowany miesiąc. Jego liczba zakupów spada z jedenastu do dziesięciu, więc przechodzi z 11+ do 6-10. Suma klientów nadal wynosi siedem, liczba pasujących zamówień spada do 24, a ostatni przedział pozostaje widoczny z zerem. To trzy niezależne oczekiwania dla jednej zmiany danych.

Na końcu usunąłbym wszystkie zamówienia z próbki, pozostawiając klientów. Cała siódemka powinna trafić do zera. Takie sprawdzenie trwa chwilę, a obejmuje logikę populacji, filtrów, agregacji i pustych kategorii. Jeżeli umiem przewidzieć wynik i wyjaśnić każdą różnicę, rozumiem mechanizm. Do interpretacji biznesowej dołożyłbym kilka dobrze zdefiniowanych KPI, żeby z tabeli częstości przejść do pytania, które ma znaczenie dla odbiorcy.