[SQL] Voorraadverschillen

[fatbenny]

Mijn tabel heeft de volgende structuur:

ProductID Datum Voorraad
10001 11-3-2006 65
10001 12-3-2006 60
10001 13-3-2006 50
10002 11-3-2006 100
10002 12-3-2006 100
10002 13-3-2006 100
10003 11-3-2006 5
10003 12-3-2006 3
10003 13-3-2006 1

Per dag worden de voorraadgegevens opgehaald, het veld datum is de dag van ophalen. Ik moet diverse queries ontwikkelen:

- Overzicht van top 10 best verkopende producten (dus grootste afname in voorraad tussen de eerste dag en laatste dag van een periode)
- Overzicht van top 10 best verkopende producten per dag (dus grootste afname in voorraad tov de vorige dag)
- Overzicht van producten waarvan de voorraad niet is veranderd.

Ik ben SQL boeken en sites aan het doorpluizen, maar ik kom er niet uit. Heeft iemand een suggestie?

[P_de_B]

Heb je ook nog te maken met opboekingen in een periode?

Welke database gebruik je?

[André]

Je zult dus per dag de mutaties bij moeten houden in een aparte tabel. Daarna is het simpel om met een query een antwoord te geven op alledrie vragen. Kon je zoiets niet zelf bedenken? En wat waren de oplossingen die jij al bedacht had maar nog niet helemaal voldeden?

[cytherea]

Ik zou gebruik maken van alle rekenfuncties die SQL ondersteund:

• COUNT
• MIN
• MAX
• SUM
• enz...

Hiermee kun je berekenen hoeveel van dezelfde producten zijn verkocht bijvoorbeeld, en met BETWEEN kun je een bepaalde periode selecteren.
Of je tabelstructuur het meest voor de handliggend is om gemakkelijk de best verkopende producten te achterhalen is natuurlijk te bezien maar met bovenstaande functies moet je een heel eind kunnen komen.

Succes

[fatbenny]

André schreef op dinsdag 14 maart 2006 @ 12:43:
Je zult dus per dag de mutaties bij moeten houden in een aparte tabel. Daarna is het simpel om met een query een antwoord te geven op alledrie vragen. Kon je zoiets niet zelf bedenken? En wat waren de oplossingen die jij al bedacht had maar nog niet helemaal voldeden?

Het is een vastliggende tabelstructuur die ik niet zelf heb ontwikkeld. Het enige wat ik zelf kon bedenken is dat het lastig, misschien wel onmogelijk, om deze queries uit te voeren op deze tabel en ik geen idee had waar te beginnen.

[fatbenny]

P_de_B schreef op dinsdag 14 maart 2006 @ 12:42:
Heb je ook nog te maken met opboekingen in een periode?

Welke database gebruik je?

Opboekingen mogen buiten beschouwing gelaten worden. Het is een Oracle database.

[kenneth]

Alstu

[ Voor 4% gewijzigd door kenneth op 14-03-2006 12:50 ]

[fatbenny]

Ik heb nu dit:

SELECT * FROM
(SELECT A.productid as productidA, A.voorraad as voorraadA, B.productid as productidB, B.voorraad as voorraadB, SUM(B.voorraad - A.voorraad) AS voorraadverschil FROM productdb A, hemaproductdb B
WHERE A.datum = '2006-03-07 00:00:00.0'
AND B.datum = '2006-03-08 00:00:00.0'
AND A.productid = B.productid
GROUP BY A.voorraad, B.voorraad, A.productid, B.productid
ORDER BY voorraadverschil)
WHERE ROWNUM < 11

[ Voor 19% gewijzigd door fatbenny op 14-03-2006 13:24 ]

[Verwijderd]

En waarom heb je dat zo? Voldoet deze query?

Imo kan je beter eerst goed nadenken over wat je nou precies uit je tabel wil halen: je querie maken is dan het relatief eenvoudige deel.

Pak het stapsgewijs aan: ik wil de best-verkopende producten tussen dag x en dag y. Wanneer is iets het best verkochte product? Als het verschil tussen voorraad op x en voorrraad op y het grootst is. Hoe kom ik achter voorraad x, en achter voorraad y? Hoe bereken ik het verschil? Etc, etc.

Uiteindelijk komt er een querie uit, die je vast nog wel kan optimaliseren, maar dat is van latere zorg.

[P_de_B]

Hieronder van de eerste 2 een zo goed als werkend voorbeeld:

SQL:

--10 best verkopende
SELECT ProductId, MAX(Voorraad) - MIN(Voorraad) as Verschil
FROM Tabel
WHERE Datum .... AND Rownum < 10
GROUP BY ProductId
ORDER BY HoogsteVerschil DESC


--10 best verkopende per dag
SELECT ProductId, MAX(A.Voorraad - B.Voorraad) as Verschil
FROM Tabel A
LEFT OUTER JOIN Tabel B ON A.ProductId = B.ProductId AND A.Datum = B.Datum + 1
WHERE Datum .... AND Rownum < 10
GROUP BY ProductId

Kijk er eens naar, en probeer of je de code begrijpt.

[Verwijderd]

P_de_B schreef op dinsdag 14 maart 2006 @ 19:17:
Hieronder van de eerste 2 een zo goed als werkend voorbeeld:

SQL:

--10 best verkopende
SELECT ProductId, MAX(Voorraad) - MIN(Voorraad) as Verschil
FROM Tabel
WHERE Datum .... AND Rownum < 10
GROUP BY ProductId
ORDER BY HoogsteVerschil DESC


--10 best verkopende per dag
SELECT ProductId, MAX(A.Voorraad - B.Voorraad) as Verschil
FROM Tabel A
LEFT OUTER JOIN Tabel B ON A.ProductId = B.ProductId AND A.Datum = B.Datum + 1
WHERE Datum .... AND Rownum < 10
GROUP BY ProductId

Kijk er eens naar, en probeer of je de code begrijpt.

"HoogsteVerschil" moet "Verschil" zijn dacht ik zo

[Robbemans]

@P_de_B:

Met deze queries mag je geen dagen missen, ander loop je vast. Mijns inziens kun je dit het beste oplossen door altijd 2 opeenvolgende regels te nemen.

Deze query is echter iets lastiger, omdat je ipv de + 1 een subquery of extra join moet gebruiken.

Het grote voordeel is dat je dagen mag overslaan - bijvoorbeeld weekenden, of feestdagen.

[P_de_B]

@BeeYeF: klopt natuurlijk.

@Robbemans, klopt. Het was ook een beetje als aanzet bedoeld om verder mee te komen.