Sprawdzenie Pareto w SQL

0

Słuchajcie, ostatnio zastanawiam się jak sprawdzić Pareto w przypadku sprzedaży.

Załóżmy, że jest tabela sprzedaży.

Data_sprzedazy Kod_produktu Grupa_produktu Wartość_sprzedaży

Czy jest sposób, aby policzyć ile produktów (w podziale na dni) w danej grupie generuje 80% obrotu?

Posegregowanie po wielkości obrotu (desc), ale co dalej?

0

Jaka baza?

0

Access (niestety).

1

Skoro już napisałem, to pozostawię. Na Access nie działa (postgreSQL)

with x(wartosc) as (select unnest(array[1,1,4,3,2,2,10,1,1,12])),
p as (select wartosc, 1.*(sum(wartosc) oveR(order by wartosc desc))/(sum(wartosc) over()) wsk, sum(wartosc) oveR(order by wartosc desc) suma_biez, sum(wartosc) oveR() razem from x)
select * from p where wsk<.8
0

A jest jakaś szansa zrobić w Accessie? W sensie - czy się da w ogóle?

0

Tak mnie zastanawia... Nie możesz po prostu zebrać dochodów po produkcie po dniach i z tak przygotowanej relacji wyciągnąć to co ciebie interesuje?

0

Da się, ale to rzeźba, skoro to Access to zapodaj accdb z przykładowymi danymi i oczekiwanym wynikie, bo podział na dni to można róznie rozumieć (dzień tygodnia/miesiąca/roku)

0
wartek01 napisał(a):

Tak mnie zastanawia... Nie możesz po prostu zebrać dochodów po produkcie po dniach i z tak przygotowanej relacji wyciągnąć to co ciebie interesuje?

Mogę, mogę też dodać na kalkulatorze :)
Chodzi mi bardziej o ćwiczenie, które można odświeżać w przyszłości - wraz z momentem aktualizacji tabeli źródłowej.

Nie mam teraz gotowych danych, ale ćwiczenie jest takie, żeby dla danego dnia i grupy produktów podać ile produktów (unikalnych) robi 80% obrotu.

Gdybym miał zrobić makro w Excelu to pewnie zrobiłbym to tak, że w tabeli (posegregowanej wg obrotu malejąco) działałaby pętla, która zatrzymuje się w wierszu, gdzie obroty narastająco przekraczają 80%. Wynikiem byłby numer wiersza, gdzie się zatrzymało.

Po prostu zastanawiam się od paru dni, czy da się to zrobić w Accessowym SQL'u.

0

Da sie, jak nie masz danych to je przygotuj, własna incjatywa mile widziana

0

Jasne, przygotowałem na szybko. Mam nadzieję, że zachowałem spójność :)

2

Własciwie do policzenia potrzebujemy kilka rzeczy:

  1. Tabele sprzedaży w okresie
  2. Sumy sprzedaży narastajaco
  3. Sumy globalnej
  4. Troche matematyki

ad.1 Kwerenda suma_okres (w przykładzie okres I półrocza 2018)

SELECT 
     sales.group
    ,sales.productid
   , um(CCur([sales])) AS s
FROM 
     sales
WHERE 
    (((sales.date) Between #1/1/2018# And #6/30/2018#))
GROUP BY 
    sales.group
   , sales.productid;

ad.2 sumy narastajaco kwerenda sum_narastajaco

SELECT 
	o1.group
	, o1.productid
	, sum(o2.s) AS sn
FROM 
	suma_okres AS o1 
	INNER JOIN suma_okres AS o2 ON o1.s >= o2.s
GROUP BY 
	o1.group
	,o1.productid;

ad. 3 to załatwiamy cross joinem w głównym zapytaniu

ad. 4 Kwerenda raport

SELECT 
	s.group
	, s.productid
	, s.s as [Sprzedaż]
	, sum_narastajaco.sn [Sprzedaż narastająco]
	, s.sa [Sprzedaż globalnie]
	, round(s/sa,5)*100 as [% w sprzedaży]
	, round(sn/sa,5)*100 as [% narastająco]
FROM (select 
		* from 
	(select sum(s) as sa from suma_okres) as sa,suma_okres)  AS s 
	INNER JOIN sum_narastajaco ON (s.productid = sum_narastajaco.productid) AND (s.group = sum_narastajaco.group);

Wynik:
screenshot-20191011103412.png

W załączniku plik accdb

0

Ok, dzięki wielkie za opracowanie fajnej koncepcji! Zaraz będę próbował zrozumieć :)

Jedno co mi się rzuca w oczy - ja potrzebuję w tym ćwiczeniu odpowiedzieć na pytanie:
Ile produktów (distinct ProductId) dnia 2018-03-15 odpowiada za 80% obrotu w grupie G002?
Tak dla każdego dnia i każdej grupy.

1

Not to musisz troche zmodyfikować liczenie sum narastajaco i zakres datowy który bierzesz:

suma_okres

SELECT 
     sales.group
    ,sales.productid
   , um(CCur([sales])) AS s
FROM 
     sales
WHERE 
     (((sales.date)<=#3/15/2018#))
GROUP BY 
    sales.group
   , sales.productid;

sum_narastajaco

SELECT 
	o1.group
	, o1.productid
	, sum(o2.s) AS sn
FROM 
	suma_okres AS o1 
	INNER JOIN suma_okres AS o2 ON (o1.group=o2.group) AND (o1.s >= o2.s)
GROUP BY 
	o1.group
	,o1.productid;

raport

SELECT 
	s.group
	, s.productid
	, s.s as [Sprzedaż]
	, sum_narastajaco.sn [Sprzedaż narastająco]
	, s.sa [Sprzedaż globalnie]
	, round(s/sa,5)*100 as [% w sprzedaży]
	, round(sn/sa,5)*100 as [% narastająco]
FROM 
       (SELECT * FROM (SELECT group as g , sum(s) AS sa FROM suma_okres group by group)  AS sa inner join suma_okres on suma_okres.group=sa.g)  AS s 
       INNER JOIN sum_narastajaco ON (s.productid = sum_narastajaco.productid) AND (s.group = sum_narastajaco.group);

Na podstawie ostatniej kwerendy łatwo policzyśz co chcesz

0

Odpowiadaj w postach...

Jeszcze chyba trzeba dodać daty, tak? Bo nie chodzi o "zakres" dat, ale o każdy poszczególny dzień.

To ostatnie co pokazałem liczy konkretnie na 15 marca 2018, czy to jest to co Ci potrzeba wiesz tylko Ty.

I zastanawiam się, czy "% narastająco" nie powinien być odwrotnie liczony.

Jak odwrotnie?

0

Próbuję to ogarnąć, ale nie do końca mi to idzie. Może uprościmy na początek (jeżeli mogę prosić), czyli bez podziału na grupy.
Pytanie:
Ile produktów, danego dnia (ale nie danego w WHERE, tylko każdego rozumianego jako wszystkie w bazie) wygenerowało 80% wartości sprzedaży?

Tabela wynikowa wyglądałaby następująco (przykłady "z palca"):

Data Liczba_prod_gen_80%_obr
2018-06-25 20
2018-06-26 25
... ...

I rozumiem, że trzeba zrobić w pierwszym kroku tabelę w źródłową: Data, ProductId, Sum(Sales). Później obliczyć wartości narastająco (tego mechanizmu nie mogę do końca zrozumieć), odnieść to do sumy sprzedaży dla danego dnia i sprawdzać gdzie przekroczyło 80%.

Jeszcze dwie rzeczy:

  1. Boję się, że przy joinie a=b and (c>=d) i większej ilości danych (set tys. rekordów) Access może klęknąć :( Czy nie?
  2. Czy nie powinniśmy wychodzić od 0% udziału i dodawać do 80%, a nie na odwrót? W sensie, że w pierwszej kolejności bralibyśmy produkty z największym obrotem.
1

Jasne, że mozesz prosić, ale daj coś od siebie, ja podalem ci dwa gotowe rozwiazania, więc posługujmy sie SQL-em, bo na tym etapie nic wiecej nie pomogę.

aby liczyć to w ramach dnia:

  1. w kwerendzie suma_okres dodaj grupowanie po date
  2. w kwerendzie sum_narastajaco dodaj do joina w warunku złączenia pola date i grupowanie po o1.date, to pole też dodaj w select
  3. w kwerendzie raport dodaj do podzapytania sa grupowanie po date, zmień warunek złaczenia sa z suma_okres dodajac do niego pole date, umieść date w wyniku
  1. Boję się, że przy joinie a=b and (c>=d) i większej ilości danych (set tys. rekordów) Access może klęknąć :( Czy nie?

Obawy zostaw na boku po prostu wykonaj test, na podanej próbce danych suma narastajaco wykonuje sie 148 sekund i przy wiekszej access może kleknąć. rozwiazaniem jest stworzenie tabeli która będzie zapisywać wyliczone wartości i aktualizować ją o nowe dni wraz z zasilaniem tabeli sales

  1. Czy nie powinniśmy wychodzić od 0% udziału i dodawać do 80%, a nie na odwrót? W sensie, że w pierwszej kolejności bralibyśmy produkty z największym obrotem.

Jeżeli chcesz zmiany to odwróc nierówność w joinie w sum_narastająco

0

Jasne, dzięki!

Ogólnie przy większej bazie Access "klęczy". Wykończa go na 90% warunek w inner joinie >=.

Przy danych na poziomie 300 tys. rekordów - sumę narastająco po samych dniach liczy jakieś ~60 minut. Przy przygotowanej wcześniej, zrzuconej tabeli "suma_okres". Czy jest jakiś sposób na obejście tego, czy to wszystko na co stać accessa?

0

suma_okres nie jest problemem, sum_narastajaco "przytyka" Access'a jak zrzucisz do tabeli policzone sumy częściowe i wykorzystasz to zapytanie wykona się w klika sekund.

Nie ma sposobu na obejście tego, access będzie dostawał czkawki, jedyne co pozostaje to sukcesywne zapisywanie sum bieżących w ramach przyrostu danych. Lub zmiana silnika bazy...

0

No tak, tak. Suma_okres przygotowana w tabeli (nie zapytanie).

Przeliczenie sum_narastająco to ~50-60 minut. Dla ok. 300-350 tys. w gotowej tabeli "suma_okres".

Pytanie, czy jest sposób na jakieś zoptymalizowania (chociaż w sumie co tu optymalizować)...to Access...

Myślałem może o EXIST? Ale to raczej nie przyspieszy.

1 użytkowników online, w tym zalogowanych: 0, gości: 1