[SQL] Prijsvergelijking *

[rmfloris]

Ik was bezig met een prijsvergelijking. Nu liep ik tegen het volgende probleem aan. Sommige aanbieders kunnen een staffel prijs hebben.

Ik had de volgende tabel structuur:

Leveranciers
ID
Naam

Producten
ID
leveranciers ID
prijs_product1
prijs_product2

Iedere leverancier heeft in principe dezelfde producten, waardoor alleen de prijs hoefte worden opgenomen. De goedkooptste leverancier was dan hoeveelheid x prijs. Nu met de mogelijkheid tot staffel loop ik hier tegen een probleem op.
Voorbeeld:
Bij de eerste 5 producten betaal je prijs p1 en
bij de volgende 5 producten betaal je p2.

Hoe verwerk ik dit in mijn database model?

Ik heb zitten denken om een tweede rij aan te maken voor iedere staffel en gelijk in de tabel producten een hoeveelheid neer te zetten. Dit is volgens mij niet de oplossing omdat je dan bijna oneindig aantal rijen krijgt.

Hopelijk kan iemand mij hierbij helpen.

[ Voor 1% gewijzigd door rmfloris op 27-01-2005 09:47 . Reden: Toevoegen leveranciers ID ]

[Boss]

Je kan:
- voor producten die geen staffel prijs hebben: de prijs in de producten tabel zetten onder 'prijs'
- producten die wel een staffel prijs hebben in een aparte tabel zetten.
Koppelen op ProductID. Verder een kolom voor het aantal en de dan geldende prijs PER STUK (dus niet voor het staffel). Dat lijkt me makkelijker rekenen.

[avon]

Hoe koppel je eigenlijk de producten en leveranciers?

Leverancier (leverancierid,n,a,w,gegevens)

Product(productid,leverancierid,prijs,staffelid)

Staffelfactor(staffelid,0.95,0.90,0.85)

bestelling([u]bestelid[u],leverancierid,productid,aantal)

Op die manier kan je handig een aantal standaard staffels bakken met een
bepaald prijsvoordeel.

[ Voor 81% gewijzigd door avon op 27-01-2005 09:43 ]

[Verwijderd]

Leverancier ID?

[schoene]

Een mogelijke oplossing:

<Leveranciers>
LID
Naam

<Producten>
PID
Naam

<Prijzen>
LID
PID
aantal
prijs

Per koppel <Leverancier, Product> is er minimaal 1 record in <Prijzen>
(als die leverancier dat product verkoopt natuurlijk),
waarbij aantal het minimum aantal is dat je moet aanschaffen (meestal 1).

Indien een leverancier met staffels werkt, dan heb je meerdere lijnen.
bvb
LeverancierX, ProductY, 1, 50
LeverancierX, ProductY, 5, 45

Vanaf 5 betaal je maar 45 euro

Om op te zoeken hoeveel je bij een bepaalde leverancier betaalt, zoek je
de record met <Prijzen.aantal> zo groot mogelijk, maar wel kleiner of gelijk
aan het aantal dat je wil bestellen.

[ Voor 19% gewijzigd door schoene op 27-01-2005 09:43 ]

[rmfloris]

Bedankt voor de reacties. Ik was inderdaad de link vergeten te noteren tussen leverancier en product. Er is een leveranciers ID veld bij de tabel producten.

Een extra puntje, dat ik nog niet had genoemd. De staffel is niet bij alle leveranciers hetzelfde.

leverancier 1 kan de volgende staffels hebben:
0-4 / 5 - 9 / 10 - 15 / > 15

Leverancier 2:
0-9 / 10-20 / >20

Levernacier 3:
0-4 / 5-9 / >10

Er moet dus een combinatie zijn tussen aantal en de prijs en de leverancier.

@schoene:

Bedankt voor de tip. Is een logische oplossing. Alleen de query wil niet helemaal lukken.

Ik had het volgende:
select * from prijzen where aantal <= X

Dit werkt natuurlijk niet als de limiet hoger ligt dan X. Ik kom niet uit de query om een maximale hoeveelheid te selecteren, maar minder of gelijk aan het aantal bestelde.

[ Voor 28% gewijzigd door rmfloris op 27-01-2005 10:08 . Reden: toevoeging ]

[MichelVH]

Als je een startwaarde en een eindwaarde van de range hebt opgeslagen, kan je doen:

SELECT * FROM prijzen WHERE (min_aantal >= X AND max_aantal <= X) OR (min_aantal IS NULL AND max_aantal IS NULL)

Maak dan de min_aantal en max_aantal waarde NULL als een leverancier geen staffelkorting hanteert.

[rmfloris]

Die laatste query lijkt niet helemaal te werken of ik doe iets verkeerd (wat het meestal is).

Een voorbeeld van een tabel (1 leverancier)

lid	pid	min_aantal	max_aantal	prijs
1	2	0	24	0.23
1	2	25	49	0.21
1	2	50	74	0.18
1	2	75	99	0.14
1	2	100	99999	0.12

lid = leveranciers ID
pid = product ID

De query werkt hier dus niet op als er bijvoorbeeld 10 producten worden besteld.

[schoene]

rmfloris schreef op donderdag 27 januari 2005 @ 09:46:
@schoene:

Bedankt voor de tip. Is een logische oplossing. Alleen de query wil niet helemaal lukken.

Ik had het volgende:
select * from prijzen where aantal <= X

Dit werkt natuurlijk niet als de limiet hoger ligt dan X. Ik kom niet uit de query om een maximale hoeveelheid te selecteren, maar minder of gelijk aan het aantal bestelde.

Per leverancier de correcte lijn:

SQL:

SELECT LID, prijs FROM prijzen p1
WHERE PID = ProductY
AND aantal = (SELECT MAX (aantal) FROM prijzen p2 WHERE p2.LID = p1.LID AND p2.PID = p1.PID AND p2.aantal <= Z)

Als de leverancier er niet in voorkomt:
- verkoopt hij het product niet
- Wil je minder kopen dan het minimum aantal dat je moet kopen

(mogelijks bestaat er een betere sql-statement, mn sql-kennis is redelijk basic)

[schoene]

zo zou ie moeten werken:

SELECT * FROM prijzen WHERE (min_aantal [b]<=[/b] X AND max_aantal [b]>=[/b] X) OR (min_aantal IS NULL AND max_aantal IS NULL)

de kleiner dan en de groter dan moeten omgekeerd

[ Voor 50% gewijzigd door schoene op 27-01-2005 10:52 ]

[MichelVH]

Inderdaad Met deze query kan je trouwens in plaats van een willekeurig hoge boven/ondergrens gewoon NULL instellen als er geen onder- of bovengrens is (denk ik )

SELECT * FROM prijzen WHERE (min_aantal <= X AND max_aantal >= X) OR (min_aantal IS NULL AND max_aantal IS NULL) OR (min_aantal <= X AND max_aantal IS NULL) OR (min_aantal IS NULL AND max_aantal >= X)

Nog iets korter is het gedeelte zonder onbekende ondergrens, daar kan je toch wel 0 instellen:

SELECT * FROM prijzen WHERE (min_aantal <= X AND max_aantal >= X) OR (min_aantal IS NULL AND max_aantal IS NULL) OR (min_aantal <= X AND max_aantal IS NULL)

[rmfloris]

De query werkt nu perfect. Iedereen bedankt hiervoor. Gelijk na het invoeren, loop ik gelijk tegen een volgend probleem aan. Het selecteren van meerdere producten met de prijs uit de tabel.
Zie onderstaande tabel (tabel 1). Een voorbeeld is:

Ik wil de totaal prijs vergelijken tussen leverancier 1 & 2 voor de producten 2+4 en voor product 2 is de hoeveelheid 53. De uitkomst zou moeten zijn zoals in tabel 2, maar dit krijg ik niet uit een query. Moet ik voor ieder product een aparte query draaien, de uitkomst in een array gooien en aan het einde alle arrays bij elkaar vegen of is er een makkelijker manier?

tabel 1

lid	pid	min_aantal	max_aantal	prijs
1	2	0	24	0.23
1	2	25	49	0.21
1	2	50	74	0.18
1	2	75	99	0.14
1	2	100	99999	0.12
2	2	0	24	0.24
2	2	25	49	0.22
2	2	50	74	0.15
2	2	75	99	0.12
2	2	100	99999	0.10
1	3	0	99999	1.23
1	4	0	99999	2.00
2	3	0	99999	1.13
2	4	0	99999	1.99

lid = leveranciers ID
pid = product ID

tabel

lid	pid	aantal	prijs
1	2	53	0.18
2	2	53	0.15
1	4	10	2.00
2	4	10	1.99

[rmfloris]

een kleine update, ik ben tot de volgende query gekomen:

SELECT * FROM prijzen WHERE pid=2 or pid = 1 and (min_aantal <= X AND max_aantal >= X) OR (min_aantal IS NULL AND max_aantal IS NULL)

probleem is dat hij dan dus geen rekening meer houdt met de staffels.

Ik heb ook gekeken of het eventueel met arrays kan, dus voor iedere product een aparte query, in een array en deze aan het einde optellen. Dit brengt meer problemen dan oplossingen. Mijn idee is dat er een SQL oplossing voor moet zijn.

Hopelijk kan iemand mij hiermee verder helpen.

[TukkerTweaker]

Maak een join op de tabel staffels

[rmfloris]

Een join op de tabel is bijna hetzelfde als met de where queries. Ik zit met het probleem dat niet alle leveranciers, alle producten leveren. Ik zie nu ook dat het voorbeeld niet helemaal duidelijk gekozen is.

De bedoeling is dat een bezoeker meerdere producten (kan) selecteren en hij voor de combinatie de goedkoopste prijs wil hebben. Dus als een leverancier 1 van de producten niet kan leveren moet hij niet in het rijtje voorkomen.

Dit is mij niet gelukt met een join.

[TukkerTweaker]

rmfloris schreef op donderdag 27 januari 2005 @ 15:14:
Een join op de tabel is bijna hetzelfde als met de where queries. Ik zit met het probleem dat niet alle leveranciers, alle producten leveren. Ik zie nu ook dat het voorbeeld niet helemaal duidelijk gekozen is.

Maak een inner join op de staffel tabel. Dan krijg je alleen de staffels die gekoppeld zijn aan jouw pid.

De bedoeling is dat een bezoeker meerdere producten (kan) selecteren en hij voor de combinatie de goedkoopste prijs wil hebben. Dus als een leverancier 1 van de producten niet kan leveren moet hij niet in het rijtje voorkomen.

Dit is mij niet gelukt met een join.

zie 1.

[rmfloris]

TukkerTweaker schreef op donderdag 27 januari 2005 @ 15:27:
[...]

Maak een inner join op de staffel tabel. Dan krijg je alleen de staffels die gekoppeld zijn aan jouw pid.
.

Alle pid's komen voor in de tabel staffel, niet alle leveranciers komen voor in de tabel staffel.

je kan dus een leverancier hebben die product 1-8 leverd (met of zonder staffel), daarnaast is leverancier 2 die product 1-4 en 7+8 leverd.

Als ik nu een overzicht wil van product 1 en 2 dan komen beide leveranciers aan bod,. Bij het overzicht van product 1 en 5 komt alleen leverancier 1 in beeld. Beide kunnen een staffelkorting hebben. Deze bij elkaar krijg ik niet goed in een join. Of alle leveranciers komen te voorschijn of er komen teweinig leveranciers tevoorschijn.

[TukkerTweaker]

rmfloris schreef op donderdag 27 januari 2005 @ 15:31:
[...]


Alle pid's komen voor in de tabel staffel, niet alle leveranciers komen voor in de tabel staffel.

je kan dus een leverancier hebben die product 1-8 leverd (met of zonder staffel), daarnaast is leverancier 2 die product 1-4 en 7+8 leverd.

Als ik nu een overzicht wil van product 1 en 2 dan komen beide leveranciers aan bod,. Bij het overzicht van product 1 en 5 komt alleen leverancier 1 in beeld. Beide kunnen een staffelkorting hebben. Deze bij elkaar krijg ik niet goed in een join. Of alle leveranciers komen te voorschijn of er komen teweinig leveranciers tevoorschijn.

Maak van de inner een outer join dan krijg en alle staffels en alle leveranciers ( ook als ze geen staffel hebben).

[rmfloris]

Als ze geen staffel hebben moeten ze ook niet in de lijst te voorschijn komen. Dus alleen leveranciers die in de staffel lijst voorkomen en de selectie is meerdere pid's tegelijkertijd.

[Verwijderd]

Even tussendoor: ik zie dat je in je staffel-tabel een min_aantal en max_aantal veld hebt staan. Die laatste is niet nodig: max_aantal is altijd min_aantal van de volgende staffel -1.

En dan kun je leuke (sub-)queries gebruiken als

SQL:

select TOP 1 prijs from staffel_tabel
where lid = <lid>
and pid = <pid>
and min_aantal <= <aantal>
order by min_aantal desc

(Beetje MSSQL-syntax, maar de meeste databases hebben wel zo'n mogelijkheid.)

[rmfloris]

Ik heb druk geprobeerd met een inner join te werken, maar dit lukt me niet helemaal. Het probleem lijkt hem te schuilen in het geval waar een klant 2 verschillende producten besteld (p1 & p2 bijvoorbeeld). Deze kunnen allebei verschillende staffels hebben en verschillende leveranciers.

Het uiteindelijke doel is dan om een overzicht te krijgen van alle leveranciers die beide producten leveren (p1 & p2) en een totaal prijs. Hopelijk heeft iemand een oplossing voor mij want ik kom er niet uit. Voorlopig gebruik ik verschillende queries die alles in een array gooien en dat wordt achteraf weer uitgelezen. Niet ideaal, want meer informatie dan naam en totaal prijs kan ik niet opnemen omdat de array weer wordt gesorteerd op de laagste prijs.

Dus ik hoop dat iemand mij een stuk in de juistte richting kan helpen.

[TukkerTweaker]

Maak een view die staffels producten combineerd.