Kawałek Kodu

Skoro jesteśmy już po obejrzeniu znanego teleturnieju, to możemy na fali emocji wykorzystać następne pół godziny i również pobawić się w sumowanie liczb. Dziś o sumie skumulowanej oraz diagramie pana Pareto.

Po zsumowaniu punktów.

Suma skumulowana, to wartość dla danej pozycji zawierająca jej sumę oraz sumę poprzednich pozycji. W MySQL nie mamy funkcji umożliwiającej obliczanie sumy skumulowanej (running total, cumulative sum). Trzeba to jakość obejść, aby wygrać rundę i dojść do finału. Możemy to zrobić na dwa sposoby: z wykorzystaniem zmiennej pomocniczej poza zapytaniem lub z wykorzystaniem zmiennej w środku zapytania.

Zacznijmy najpierw od tabeli towar:

Mamy tu ewidencję sprzedaży towarów per dzień, choć nie ułożoną chronologicznie. Chcemy obliczyć sumę skumulowaną przychodu dla każdego dnia.

Z wykorzystaniem zmiennej poza zapytaniem:

SET @suma:=0;
SELECT nazwa, data, przychod, @suma:=@suma+przychod AS suma
FROM towar
ORDER BY data

Z wykorzystaniem zmiennej w zapytaniu:

SELECT nazwa, data, przychod, @suma:=@suma+przychod AS suma
FROM towar, (SELECT @suma:=0) AS s
ORDER BY data

W pozostałych przykładach będziemy posługiwać się drugim wariantem, ale najpierw wyjaśnienie. W obydwu przypadkach chcemy otrzymać sumę skumulowaną chronologicznie, więc stosujemy klauzulę ORDER BY na kolumnie data. Zmienną suma inicjujemy wartością 0. Podczas wybierania każdego rekordu dodajemy do niej wartość z kolumny przychod z bieżącego rekordu.

Wynik jest następujący:

Ostatnią wartość (360) otrzymalibyśmy zapytaniem:

SELECT SUM(przychod) FROM towar

Ile punktów zdobyła przeciwna drużyna.

Wiemy już jak otrzymywać sumę dla wszytkich rekordów niezależnie od towaru. Jeśli jednak chcemy obliczać sumę skumulowaną per towar, to musimy wprowadzić dodatkową zmienną.

SELECT nazwa, data, przychod,
       IF(@nazwa<>nazwa,@suma:=0, TRUE),
       IF(@nazwa<>nazwa,@nazwa:=nazwa, TRUE),
       @suma:=@suma+przychod AS suma
FROM towar, (SELECT @suma:=0) AS s, (SELECT @nazwa:='') AS n
ORDER BY nazwa, data

Zapytanie trochę się skomplikowało, ale już wyjaśniam. Przede wszystkim sortujemy tym razem po nazwie towaru i potem po dacie, bo będziemy sumować właśnie w grupach produktowych. Mamy więc dodatkową zmienną nazwa. Początkowo ma wartość pustego stringu. W pierwszym rekordzie sprawdzamy czy nazwa towaru jest inna od poprzedniej (od wartości zmiennej nazwa), jeśli jest to ustawiamy wartość suma na 0. Drugi warunek jest podobny, ale jeśli jest spełniony, to zmieniamy wartość nazwa na aktualną nazwę. Obydwa warunki dotyczą po prostu startu nowej grupy produktu (czyli pierwszego rekordu i np. rekordu gdzie towar zmienia nazwę z towar 1 na towar 2). No i standardowo obliczamy sumę skumulowaną.

Dlaczego te dwa warunki są w takiej kolejności? Dlaczego nie zmieniamy najpierw nazwy? Jeśli byłby odwrotnie, to warunek resetujący sumę nigdy nie byłby spełniony, bo nazwa aktualnego rekordu byłaby tożsama z wartością zmiennej nazwa (która została zmieniona wcześniej przez ten drugi warunek).

Jeśli chciałbyś wprowadzić kolejny poziom grupowania, to trzeba dodać jeszcze jedną zmienną, a zmienną suma resetować przy spełnieniu podwójnego warunku (np. na nazwę i model).

Wynik z tego zapytania jest następujący:

Pytanie finałowe.

Co to jest diagram Pareta? Przede wszystkim jego nazwa pochodzi od nazwiska Vilfreda Pareta. Diagram ma dwie osie Y. Na jednej prezentuje wartości poszczególnych pozycji (które na diagramie są słupkami), na drugiej sumaryczną wartość lub wartości procentowe (które na diagramie są wykresem liniowym). Słupki prezentowane są w kolejności malejącej, czyli zaczynamy od największej wartości. Tak to wygląda:

Co możemy wyczytać z diagramu Pareta? Zerkając na prawą oś możemy "odciąć" słupki, których udział w całości stanowi pewną wartość lub procent. Jeśli np. prezentujemy ilość uszkodzeń w każdym produkowanym towarze na przestrzeni czasu, to prowadząc poziomą oś od 50% (z prawej osi) widzimy, które słupki znajdują się pod tą osią, czyli wadliwość których towarów stanowi 50% z całości produkcji. Jeśli na prawej osi będą wartości bezwzględne, np. przychody ze sprzedaży, to możemy stwierdzić, że np. zysk 3 mln zł stanowią określone towary. Na powyższym szkicu, zakładając, że przedstawia on ilości wadliwych towarów, to ok. 80% całości stanowią trzy pierwszej produkty (słupek 1 i 2, oraz część trzeciego).

Samym zapytaniem oczywiście nie wygenerujemy wykresu, ale możemy dostarczyć dla niego dane. Najpierw musimy przygotować zapytanie dla słupków:

SELECT nazwa, SUM(przychod) AS przychod FROM towar
GROUP BY nazwa
ORDER BY przychod DESC

 i wynik:

Teraz trzeba dobudować do tego sumę skumulowaną, którą przedstawimy jako wartości procentowe, czyli potrzebujemy również sumy przychodu dla wszystkich towarów razem. Najpierw suma całkowita:

SELECT nazwa, przychod, total FROM
 (
  SELECT nazwa, SUM(przychod) AS przychod FROM towar
  GROUP BY nazwa
  ORDER BY przychod DESC
 ) AS st,
 (
  SELECT SUM(przychod) AS total FROM towar
 ) AS total

I ostateczne zapytanie:

SELECT nazwa, przychod, total, (@suma:=@suma+przychod)/total AS udzial FROM
 (
  SELECT nazwa, SUM(przychod) AS przychod FROM towar
  GROUP BY nazwa
  ORDER BY przychod DESC
 ) AS st,
 (
  SELECT SUM(przychod) AS total FROM towar
 ) AS total,
 (
  SELECT @suma:=0
 ) AS suma

Daje to nam wynik:

Zakładając, że chcemy wiedzieć jaki produkt stanowi 80% przychodów, możemy łatwo odczytać, że są to towar 2 oraz towar 1.

Gratuluję finału i zapraszam Cię na następny odcinek!

Przydatne linki:
Diagram Pareta