Historische voorraad analyse, horizontaal of verticaal?

[Onoffon]

Geachte tweakers,

Vanuit onze leverancier (dropship) krijgen we elke uur een bestand van ong. 10.000 records met daarin de actuele voorraad. Graag zouden we op de voorraad een analyse willen doen zodat we weten wat (im) populaire artikelen zijn, hierop kunnen we dan onze webshop op aanpassen.

Huidige situatie:

1 tabel met artikelen:

- Id
- Omschrijving
- Voorraad

Elk uur word deze overschreven met de dump vanuit de leverancier.

Nu zit ik te twijfelen hoe dit het beste te doen, bij gebrek aan betere bewoording heb ik ze maar hortizontaal en verticaal genoemd:

Horizontaal:

Een nieuwe tabel met daarin:

-Id
-Voorraad{Dag}{Uur}

Elk uur maakt het import script een nieuwe (uniek genaamde) kolom met daarin de voorraad op dat moment. Dit wou ik een week verzamelen en dan hier een samenvatting van maken en dit in een nieuwe tabel plaatsen zodat deze makkelijk uit te lezen is d.m.v. bv. JPgraph. Maximaal aantal kolommen is dus (24*7) 168 en 10k records.

Verticaal:

Een nieuwe tabel met daarin:

-Id
-Timestamp
-Voorraad

Elk uur voegt het import script 10.000 records toe met daarin de actuele voorraad op dat moment. Ook hier na een week een samenvatting van en de oude tabel leeghalen. Maximaal aantal kolommen is 3 met (10000*7*24) 1680000 records

Eigen conclusie:

• Nu ik dit opschrijf bedenk ik me dat ik natuurlijk niet 24/per dag hoef te doen, buiten kantoortijden wordt de voorraad in iedergeval niet positief geupdate
• Ik heb zitten nadenken over updaten 1x per dag, maar als er dan is bijgevuld zie ik niet hoeveel er besteld is (Huidige voorraad is: 20, van 08:00 tot 12:00 zijn er 10 besteld en 3 uur later word de voorraad bijgevuld met 10, als ik 1x per dag zou checken zou deze daling niet opgevallen zijn. Nu heb je dit natuurlijk ook als ik 1x per uur de controle doe, maar dat is een stuk minder erg. Het gaat om de trend en niet zozeer om de aantallen

De 'verticale' manier lijkt me in iedergeval niet de beste manier, maar als ik zo snel even nadenk misschien wel de makkelijkste voor het php import script)

Dan nog de kwestie van de 'samenvatting':

Ik denk haast niet dat ik dit via het import script kan doen (zeker niet met de horizontale manier), maar ik moet dus een nieuwe data set krijgen met het verschil in voorraad tussen de verschillende waardes. Hier zit ik ook nog te puzzelen hoe ik dit het beste kan doen, vooralsnog neig ik er naar om dit op applicatie niveau via PHP te doen, maar voorkeur is natuurlijk via SQL.

Wie heeft hier soortgelijke ervaring mee en hoe heb je dit opgelost? Of wie ziet hier een andere geniale oplossing?

[ Voor 8% gewijzigd door Onoffon op 26-02-2018 08:21 ]

[Harrie_]

Ik zou zeker niet voor de 'horizontale' manier gaan; daar is een relationionele database niet voor bedoeld en lijkt me performance-wise ook bepaald niet handig.

De 'verticale' methode is wel de manier en ik denk dat je dan niet telkens 10k records hoeft in te voeren. Persoonlijk zou ik hiervoor een stored procedure maken die de voorraadtabel afzet tegen de (nieuwe) historische-voorraad tabel. Als er geen voorraad voor een artikel is gewijzigd dan doe je niets. Is er wel iets gewijzigd doe je een nieuwe instert met time-stamp. De stored procedure laat je afvuren op het moment dat de import van je leverancier is voltooid....

De 'samenvatting' is dan toch niets anders dan een view waar je 2 data aan meegeeft?

[digifun]

Horizontaal dan heb je een array die goed te verwerken is.

[P_Tingen]

Gevoelsmatig zou ik voor de verticale oplossing gaan. Zoals @Harrie_ zegt is een relationele db niet echt bedoeld voor dit soort acties; omdat het kán wil nog niet zeggen dat het moet. De verticale oplossing lijkt me de meest recht-toe recht-aan oplossing. En ongewijzigde voorraden zou ik wel opnemen. Dit maakt je script een stuk makkelijker wanneer je analyses er op los gaat laten. Als je ongewijzigde records niet opneemt in je db, dan moet je daar in je script rekening mee houden. Als je er blind vanuit kan gaan dat er ALTIJD twee records zijn van een artikel bij een analyse, is het script simpel. En simpel is altijd beter.

Je db zal wel groeien door zoiets als dit, dus als je dit gaat doen, denk dan ook al direct na hoe lang je de data in je db wil houden; 10.000 records x aantal kantooruren (10?) per dag tikt redelijk aan na verloop van tijd.
edit: lees net dat je daar al over had nagedacht, goed bezig!

[ Voor 4% gewijzigd door P_Tingen op 26-02-2018 09:48 ]

[spank_mojoo]

Ik kan me haast niet voorstellen dat dit de enige data is die je hebt wanneer je verantwoordelijke bent voor voorraad. Kun je niet aan een tabel met transacties komen? zulke dat is veel geschikter voor deze doeleinden.

[Janoz]

Horizontaal zou ik sowieso niet doen. Dat is echt complete onzin.

Als je bang bent dat het aantal records te veel gaat groeien zou je altijd nog kunnen overwegen om juist de delta's op te slaan. Het import script is misschien iets lastiger. Het voordeel is dat je ongewijzigde voorraad niet op hoeft te slaan. En als je de totale wijzigingen op een dag wilt opvragen doe je gewoon een SUM met een BETWEEN.

[T i M]

Waarom niet alleen de mutaties bijhouden? Voor de leverancier zou het ook efficiënter zijn om enkel incrementele updates door te sturen.

Stel om 10:00 is de voorraad voor Y 10 dan is dat je beginstand. Insert record mutatie voor Y +10. Om 11:00 is de voorraad nog steeds 10 dan doe je niks. Om 12:00 is de voorraad 3 dan insert je een mutatie record met -7. Bij elke mutatie sla je ook de timestamp op. Achteraf kun je dan dezelfde analyze uitvoeren op de data.

[RobIII]

digifun schreef op maandag 26 februari 2018 @ 09:39:
Horizontaal dan heb je een array die goed te verwerken is.

Alsjeblieft niet zeg

[Onoffon]

P_Tingen schreef op maandag 26 februari 2018 @ 09:46:
Gevoelsmatig zou ik voor de verticale oplossing gaan. Zoals @Harrie_ zegt is een relationele db niet echt bedoeld voor dit soort acties; omdat het kán wil nog niet zeggen dat het moet. De verticale oplossing lijkt me de meest recht-toe recht-aan oplossing. En ongewijzigde voorraden zou ik wel opnemen. Dit maakt je script een stuk makkelijker wanneer je analyses er op los gaat laten. Als je ongewijzigde records niet opneemt in je db, dan moet je daar in je script rekening mee houden. Als je er blind vanuit kan gaan dat er ALTIJD twee records zijn van een artikel bij een analyse, is het script simpel. En simpel is altijd beter.

Je db zal wel groeien door zoiets als dit, dus als je dit gaat doen, denk dan ook al direct na hoe lang je de data in je db wil houden; 10.000 records x aantal kantooruren (10?) per dag tikt redelijk aan na verloop van tijd.
edit: lees net dat je daar al over had nagedacht, goed bezig!

Eens, als ik uitga van 13 uur (08.00 tot 21.00) en een periode van 1 week dan kom ik uit op 910.000 records, hiermee kan ik dan nog wel een mooie view maken

spank_mojoo schreef op maandag 26 februari 2018 @ 09:46:
Ik kan me haast niet voorstellen dat dit de enige data is die je hebt wanneer je verantwoordelijke bent voor voorraad. Kun je niet aan een tabel met transacties komen? zulke dat is veel geschikter voor deze doeleinden.

Het is voor een hobby webshop, dus echt 'verantwoordelijk' voor de voorraad in dat opzicht ben ik niet.

Resume:

• Verticaal
• Bij performance problemen alleen de delta's registreren i.p.v. volledige data set

[PolarBear]

Ik snap nooit zo goed waarom mensen 910.000 records of zelfs 1680000 records een issue vinden. Een SQL Server is gemaakt voor dit soort dingen. Mocht het echt tot issues leiden kan je altijd nog partitioneren of met delta's werken. De 'horizontale' oplossing leidt alleen maar tot lastige queries als je echt iets van je data wil weten.

[RobIII]

Onoffon schreef op maandag 26 februari 2018 @ 10:51:
Resume:

• Horizontaal

Na alle reacties in dit topic? Echt?
Waarom denk je dat aggregates (sum, avg, dat soort zaken) alleen op rijen (aka records) werken en niet op kolommen?

[ Voor 22% gewijzigd door RobIII op 26-02-2018 11:12 ]

[Onoffon]

PolarBear schreef op maandag 26 februari 2018 @ 11:06:
Ik snap nooit zo goed waarom mensen 910.000 records of zelfs 1680000 records een issue vinden. Een SQL Server is gemaakt voor dit soort dingen. Mocht het echt tot issues leiden kan je altijd nog partitioneren of met delta's werken. De 'horizontale' oplossing leidt alleen maar tot lastige queries als je echt iets van je data wil weten.

Eens, maar daarna moet ik er op applicatielaag ook wat mee en dan wordt het al wat lastiger.

[Onoffon]

RobIII schreef op maandag 26 februari 2018 @ 11:11:
[...]

Na alle reacties in dit topic? Echt?

Ow, sorry, haha, blijkbaar kan ik niet 2 dingen te gelijk, dat moet verticaal zijn natuurlijk....

[RobIII]

Onoffon schreef op maandag 26 februari 2018 @ 11:12:
[...]


Eens, maar daarna moet ik er op applicatielaag ook wat mee en dan wordt het al wat lastiger.

Je gaat toch geen honderdduizenden records doorkauwen in je applicatie? Je select gewoon uit je DB wat je wil hebben, zorg dat je alleen de data terugkrijgt die relevant is en gaat daarmee aan de slag.

[Onoffon]

RobIII schreef op maandag 26 februari 2018 @ 11:13:
[...]

Je gaat toch geen honderdduizenden records doorkauwen in je applicatie? Je select gewoon uit je DB wat je wil hebben, zorg dat je alleen de data terugkrijgt die relevant is en gaat daarmee aan de slag.

Ik moet alles hebben in de applicatie, waarom zou ik het anders opslaan in de DB? Nee, ik hoef inderdaad niet alles in de applicatielaag te hebben, ik wil een top 10 hebben van artikelen waar de meeste delta's tussen zitten. Probleem is echter als de voorraad word bijgevuld

Tabel voorbeeld:

Artikelnummer	Voorraad	Timestamp
1	10	2018-02-26 11:00
2	30	2018-02-26 11:00
3	15	2018-02-26 11:00
1	8	2018-02-26 12:00
2	35	2018-02-26 12:00
3	15	2018-02-26 12:00

De functionaliteit is:
Per artikel kunnen zien op dagbasis welke het meest is aangevraagd

Hoe kan ik de delta's opvragen t.o.v. andere records met hetzelfde Artikelnummer? Dan zou ik iets moeten maken met subquery's vermoed ik?

Ik ga zelf even puzzelen en als ik een concrete vraag heb, meld ik mij weer....

[ Voor 9% gewijzigd door Onoffon op 26-02-2018 11:30 ]

[Janoz]

Sla nou gewoon de wijzigingen op. Dat werkt veel makkelijker. Je vraag is dan simpel te beantwoorden met

SQL:

SELECT TOP 10 artikelnummer, SUM(delta) 
FROM tabel 
WHERE timestamp>=gisteren AND timestamp<vandaag 
GROUP BY artikelnummer 
ORDER BY SUM(delta)

[Onoffon]

Janoz schreef op maandag 26 februari 2018 @ 11:52:
Sla nou gewoon de wijzigingen op. Dat werkt veel makkelijker. Je vraag is dan simpel te beantwoorden met

SQL:

SELECT TOP 10 artikelnummer, SUM(delta) 
FROM tabel 
WHERE timestamp>=gisteren AND timestamp<vandaag 
GROUP BY artikelnummer 
ORDER BY SUM(delta)

Ok, volgende voorbeeld:

11:00 Delta -1
12:00 Delta -3
13:00 Delta -5
14:00 Delta +8

11:00 Delta -1
12:00 Delta -3
13:00 Delta: -1
14:00 Delta -1

Met jouw query zou voorbeeld 2 boven voorbeeld 1 komen in de return data set, wat niet de bedoeling is, voorbeeld1 is interessanter.

Maar ik denk dat ik mijn eigen vraag al heb beantwoord, ik moet de positieve delta's (dus voorraad bijvullen) helemaal niet toevoegen in de database, maar alleen negatieve delta's.
Je mist dan nog steeds niks, want '+8' kan ook inhouden '16 bijgevuld, en 8 besteld', maar dat geldt voor elk artikel en het gaat om de trend (er vanuitgaande dat er niet elk uur wordt bijgevuld).

Thanks @Janoz , ik denk weer veel te moeilijk!

[Janoz]

Onoffon schreef op maandag 26 februari 2018 @ 13:19:
[...]


Ok, volgende voorbeeld:

11:00 Delta -1
12:00 Delta -3
13:00 Delta -5
14:00 Delta +8

11:00 Delta -1
12:00 Delta -3
13:00 Delta: -1
14:00 Delta -1

Met jouw query zou voorbeeld 2 boven voorbeeld 1 komen in de return data set, wat niet de bedoeling is, voorbeeld1 is interessanter.

Maar ik denk dat ik mijn eigen vraag al heb beantwoord, ik moet de positieve delta's (dus voorraad bijvullen) helemaal niet toevoegen in de database, maar alleen negatieve delta's.
Je mist dan nog steeds niks, want '+8' kan ook inhouden '16 bijgevuld, en 8 besteld', maar dat geldt voor elk artikel en het gaat om de trend (er vanuitgaande dat er niet elk uur wordt bijgevuld).

Thanks @Janoz , ik denk weer veel te moeilijk!

Die moet je zeker wij bijvoegen aangezien je die gegevens misschien ook nog wel nodig gaat hebben. De oplossing is nog simpeler. Zet gewoon een AND delta > 0 bij de WHERE.

[Morrar]

Nadeel van alleen delta's opslaan is dat je ergens de beginstatus vandaan moet halen; het kan dan zijn dat je de hele reeks moet teruggraven om bijvoorbeeld de huidige voorraad te berekenen. Als die shop een tijd loopt kan dat tijdrovend worden.

Daarom wordt vaak de delta en de huidige status opgeslagen bij de transactie. Bij banktransacties zie je dat bijvoorbeeld ook; transactiebedrag + huidige saldo. Als je het huidige saldo wilt weten pak je dan gewoon het meest recente record. Als je bijvoorbeeld afgeschreven bedrag in de laatste maand wilt hebben, tel je gewoon de delta's van de laatste maand op. Best of both worlds, terwijl je maar 1 kolom erbij krijgt.

Als je echt bang bent voor teveel data, kun je ook zelf aggregaten gaan bouwen. Bijvoorbeeld na iedere maand sla je op het aantal transacties, gemiddelde transactie grootte, spreiding in transactie bedrag, gemiddelde voorraad, spreiding in voorraad, etc. Er zijn ook time-series databases die dit proces voor je kunnen autmatiseren zoals InfluxDB.

Welke aggregaten je wilt opslaan hangt af van welke analyses je wilt gaan doen. Daar ligt ook meteen het probleem; als je alleen nog maar de aggregaten hebt, kun je later niet ineens meer een nieuwe aggregatie berekenen voor een nieuw soort analyse. Daarom is ruwe data altijd te prefereren en eigenlijk is storage nu zo goedkoop geworden dat er geen reden meer is om ruwe data weg te gooien. Wel kan het handig zijn om een deel te archiveren (cold storage) en een kleiner deel live te houden (hot storage) voor de webshop. In een RDBMS is dit makkelijk te implementeren met 2 tabellen; sla de gegevens op in zowel hot als cold storage en verwijder ze bijvoorbeeld na 6 maanden alleen uit hot storage.

[ Voor 54% gewijzigd door Morrar op 26-02-2018 13:53 ]

[Janoz]

De huisige situatie^* is al aanwezig in de huidige situatie^** . Delta's worden nu alleen toegevoegd voor analyse.


* Huidige stand van de voorraard
** Huidige implementatie van het datamodel

[P_Tingen]

En hier zie je ook het gevaar: we slepen er meer en meer bij en de oplossing wordt steeds ingewikkelder. Als de TS niet meer en niet minder wil hebben dan een lijstje van de best lopende artikelen heb je genoeg aan de de negatieve delta's. Positieve delta's zijn bevoorradingen en niet interessant. Huidige voorraad is leuk, maar ook niet interessant want je wil alleen de best verkopende artikelen weten.

Let wel: voor ANDERE vragen aan het systeem zijn positieve delta's en werkelijke voorraadstanden misschien wel interessant, maar puur voor de vraag welke artikelen het beste verkopen heb je dat niet nodig.

[Onoffon]

Correct, en de huidige voorraad weet ik, want die word in een andere tabel opgeslagen. De laatste voorraad stand wil ik natuurlijk gewoon kunnen communiceren naar de klant.

Wel kunnen de bevoorradingen ook interessant zijn, bijvoorbeeld hoe de leverancier om gaat met goed (lees: snel) lopende artikelen, snelheid opnieuw bevoorraden, etc, etc.

Maar @Janoz heeft wederom gelijk, extra filter op delta < 0 en ik heb de de bevoorrading in iedergeval in de data set zitten voor fase 2, .

[Morrar]

Janoz schreef op maandag 26 februari 2018 @ 13:52:
De huisige situatie^* is al aanwezig in de huidige situatie^** . Delta's worden nu alleen toegevoegd voor analyse.


* Huidige stand van de voorraard
** Huidige implementatie van het datamodel

Point taken, maar als je dan de situatie van vorige maand wilt weten moet je nog steeds een reeks doorrekenen (huidige stand - SUM(deltas deze maand)). Dat was wat ik eignelijk wilde zeggen

Persoonlijk zou ik die stand dus liever toevoegen aan het record en dus wat storage opofferen om wat rekentijd te winnen als je de actuale stand op een ander moment wilt hebben.

[ACM]

Onoffon schreef op maandag 26 februari 2018 @ 08:18:
Graag zouden we op de voorraad een analyse willen doen zodat we weten wat (im) populaire artikelen zijn, hierop kunnen we dan onze webshop op aanpassen.

Kan je niet ook verkoopcijfers van ze krijgen? Of gaat het om voorraad van producten die jullie nu niet voeren en je - a.d.h.v. hun voorraadmutaties - mogelijk ook wilt gaan aanbieden?

In dat laatste geval moet je wellicht ook accepteren dat het niet perfect hoeft te zijn. Je kan zowel naar de som van voorraadafnames als naar de som van voorraadtoenames kijken om een goed beeld te hebben van wat veel verkocht wordt. Of wellicht beter nog, naar beide. Als de voorraad alleen maar toeneemt en nooit afneemt, dan is dat ook niet echt een handig product om te verkopen

Je kan echter ook zonder deltas werken. Er zijn allerlei mathematische/statistische indicatoren die de bewegelijkheid van een dataset aangeven, de simpelste is waarschijnlijk de standaarddeviatie of variantie, die zitten beide vast wel in je database (iig in mysql en postgresql). Een hoge std/var geeft een hint dat er waarschijnlijk veel mutaties zijn tov het gemiddelde.
Je zou dus eerst eens kunnen kijken wat er gebeurt als je simpelweg producten sorteert op hun standaarddeviatie van de voorraadcijfers; hoger is "beter".

Een andere simpele indicator kan zijn het verschil tussen de min en de max voorraad, alleen dan weet je niet of dat eenmalig was of heel vaak voorkwam (anderzijds kijk je maar naar een week).

[Onoffon]

ACM schreef op dinsdag 27 februari 2018 @ 08:23:
[...]

Kan je niet ook verkoopcijfers van ze krijgen? Of gaat het om voorraad van producten die jullie nu niet voeren en je - a.d.h.v. hun voorraadmutaties - mogelijk ook wilt gaan aanbieden?

Precies, het gaat om producten die we nu niet voeren, maar mogelijk wel interessant zijn om te gaan opvoeren. Het hoeft inderdaad niet perfect te zijn, het gaat om een indicatie van de topX meest verkochte artikelen.

Verschil tussen min en max voorraad zou inderdaad ook een goede hint kunnen zijn, ga ik ook even naar kijken.

[DirkZzZ]

Onoffon schreef op dinsdag 27 februari 2018 @ 08:28:
[...]


Precies, het gaat om producten die we nu niet voeren, maar mogelijk wel interessant zijn om te gaan opvoeren. Het hoeft inderdaad niet perfect te zijn, het gaat om een indicatie van de topX meest verkochte artikelen.

Verschil tussen min en max voorraad zou inderdaad ook een goede hint kunnen zijn, ga ik ook even naar kijken.

Los van het feit dat het natuurlijk leuk is om lekker te knutselen, maar als een klant bij ons de vraag neerlegt dat hij graag meer hardlopende producten op wil nemen dan krijgen ze bij ons gewoon een productlijst met hardlopende producten. En mochten ze dat met een bepaalde interval op de mail, via ftp of uit een api willen trekken dan valt daar ook nog wel iets voor te regelen.

Dus gewoon praktisch gezien; Hebben jullie de leverancier al gewoon om een dergelijke lijst gevraagd?

[Onoffon]

DirkZzZ schreef op dinsdag 27 februari 2018 @ 20:09:
[...]


Los van het feit dat het natuurlijk leuk is om lekker te knutselen, maar als een klant bij ons de vraag neerlegt dat hij graag meer hardlopende producten op wil nemen dan krijgen ze bij ons gewoon een productlijst met hardlopende producten. En mochten ze dat met een bepaalde interval op de mail, via ftp of uit een api willen trekken dan valt daar ook nog wel iets voor te regelen.

Dus gewoon praktisch gezien; Hebben jullie de leverancier al gewoon om een dergelijke lijst gevraagd?

Ha, nee. Maar ik vind het inderdaad ook leuk om zoiets zelf te bouwen...
Mochten we ooit meer dan 1 product per week verkopen, dan is dit inderdaad een overweging waard...

p.s. Site gaat trouwens bijna live, dan zal ik wel even een linkje posten in een daarvoor geschikt draadje

[ Voor 6% gewijzigd door Onoffon op 27-02-2018 20:12 ]