Tijd afhankelijk gegevens ophalen

[RwD]

In een online winkel heb ik producten die vanuit meerdere leveranciers kunnen komen. Iedere leverancier heeft eigen levertijden en limieten tot wanneer de levertijd nog gehaald kan worden (bijv voor 22 uur besteld, morgen in huis). 22:01 bestellen is dus meteen overmorgen in huis.

Nu wil de klant dat ik een productlijst toon met als productprijzen de laagste prijs van de groep leveranciers die dat product het vroegst kunnen leveren. De leveranciers zijn allen "vandaag", "morgen" of "overmorgen" wat levering betreft, maar ze hebben allemaal andere limitieten (voor 13 uur, voor 17 uur en voor 22 uur). Dus belangrijkste is levertijd, daarna prijs.

Ik heb een database met 3 tabellen hiervoor (alleen relevante velden geschreven);

1. product[ean] (+/-10.000 records)
2. productstock[ean, supId, stock, price] (+/-25.000 records)
3. supplier[id, orderBefore, deliveryTime] (5 op dit moment)

Mijn probleem is dat ik niet goed weet hoe ik het aan zal pakken. Ik heb diverse queries geprobeerd om dit in 1 keer op te lossen, maar vraag me af of er 1 query is die het kan. Eerst heb ik er langzaam naartoe geprobeerd te werken, en het dichtsbij kwam de volgende query (verre van een oplossing):

code:

SELECT sp.id, sp.ean, sp.stock, sp.price, sup.id, IF(sup.orderBefore>40667, sup.deliveryTime, sup.deliveryTime+86400) as actualDLT
FROM `productstock` sp
JOIN `supplier` as sup ON sp.pid = sup.id
WHERE sp.stock > 0
ORDER BY ean, actualDLT, price

(40667 is eigenlijk huidige tijd vanaf middernacht in seconden) Wat ik hier lastig vind is om dit terug te brengen tot 1 resultaat per ean. GROUP BY levert me een willekeurig resultaat, terwijl iedere keer de bovenste optie per ean de gewenste waardes bevat (afgezien dat ik een SUM(stock) ook nodig heb)

Mijn opties lijken me:

1. Een query vinden die bovenstaande omschrijving kan, binnen 0,1 seconden. (Anders is de klant niet tevreden) Maar ik heb er nog niet eens een gevonden die het uberhaupt kan. (zou dit moeten kunnen??)
2. Regelmatig (via cron) de product tabel updaten, daar een veld price (en stock) in zetten. Dan mag de query iets langer duren en is de productlijst tonen veel sneller.

Optie 1 lukt me niet, enige aanwijzingen zijn welkom.
Optie 2 zitten wat haken en ogen aan, bijv; hoe vaak voer ik die cron uit bijvoorbeeld (als cron nodig). Bovendien vind ik het nog steeds lastig te visualiseren welke stappen noodzakelijk zijn en of ik het niet te ingwikkeld wil maken.

Hoe kan ik dit het beste aanpakken?

[ACM]

Dit zijn dingen die met sql vrij lastig worden. Eigenlijk is het niet zo geschikt voor "doe mij alle info van de goedkoopste"-queries. Wellicht werkt zoiets:

code:

SELECT ...
FROM product p 
 JOIN productstock ps USING (ean)
WHERE sp.id = 
  (SELECT psi.id 
  FROM productstock psi 
    JOIN supplier s ON psi.supId = s.id
  WHERE psi.ean = p.ean
  ORDER BY IF(sup.orderBefore>40667, sup.deliveryTime, sup.deliveryTime+86400), price
  LIMIT 1)

Maar het zou kunnen dat je versie van MySQL geen limit op die plek toelaat. En het is maar de vraag of de query wel in 0.1 seconde werkt.
Als je e.e.a. in een cron wilt gaan doen, dan moet je per product de op dat moment meest gunstige combinatie zien te vinden en dat vervolgens in een nieuwe tabel wegschrijven.

Als je meerdere leveranciers wilt bundelen per product, dan kan je ook zoiets doen:

code:

SELECT ..., MIN(ps.price), ..., SUM(ps.stock), ...
FROM product p 
 JOIN productstock ps USING (ean)
 JOIN supplier sup ON ps.supId = sup.id
WHERE 
IF(sup.orderBefore>40667, sup.deliveryTime, sup.deliveryTime+86400) = 
  (SELECT MIN(IF(sup.orderBefore>40667, sup.deliveryTime, sup.deliveryTime+86400))
  FROM productstock psi 
    JOIN supplier s ON psi.supId = s.id
  WHERE psi.ean = p.ean)
GROUP BY p.ean

Of een variant met een join:

code:

SELECT ..., MIN(ps.price), ..., SUM(ps.stock), ...
FROM product p 
 JOIN productstock ps USING (ean)
 JOIN supplier sup ON ps.supId = sup.id
 JOIN (SELECT psi.ean, MIN(IF(sup.orderBefore>40667, s.deliveryTime, s.deliveryTime+86400)) as deliveryTime
  FROM productstock psi 
    JOIN supplier s ON psi.supId = s.id
  GROUP BY psi.ean
) as delivery ON delivery.ean = p.ean AND psi.deliveryTime = IF(...)
GROUP BY p.ean

Als bovenstaande traag blijkt te zijn, probeer dan eens of de subquery in de join als losse query wel snel is. Als die niet snel blijkt te zijn, dan kan je die wel als basis voor je cron gebruiken, dat zou dan zoiets worden:

code:

START TRANSACTION;
DELETE FROM bestStockOptions;
INSERT INTO bestStockOptions
SELECT psi.ean, MIN(IF(sup.orderBefore>40667, s.deliveryTime, s.deliveryTime+86400)) as deliveryTime
  FROM productstock psi 
    JOIN supplier s ON psi.supId = s.id
GROUP BY psi.ean

Als die subquery als los statement wel snel is, dan kan je 'm ook in een temporary table gooien (create temporary table ... KEY(ean) as select ...). Niet vergeten een index op ean te maken, zoals met die KEY in tussen de haakjes.

't Belangrijkste punt van aandacht is dat als je zoiets hebt:
SELECT ean, MIN(price), MIN(deliveryTime)
...
GROUP BY ean

dat er geen relatie meer tussen de price en deliveryTime zit. Dat probeer ik met bovenstaande subqueries te voorkomen.

Alle queries en onderdelen zijn uit mijn hoofd en ongetest hier getyped. Dus e.e.a. zal waarschijnlijk niet direct werken

[MrBucket]

RwD schreef op zaterdag 06 oktober 2012 @ 14:46:
Wat ik hier lastig vind is om dit terug te brengen tot 1 resultaat per ean. GROUP BY levert me een willekeurig resultaat, terwijl iedere keer de bovenste optie per ean de gewenste waardes bevat.

Ik weet niet of MySQL dit ondersteunt, maar in MSSQL kun je met een subquery werken waarop je dan top 1 (in MySQL ws. "limit 1") toepast:

MSSQL:

select
    ord.orderId,
    (select top 1 orderLineId
     from tblOrderLine as ol
     where ol.orderId = ord.orderId
     order by ol.Amount desc) as biggestOrderLineId
from tblOrder as ord

[.oisyn]

Soms moet je gewoon niet zo moeilijk doen met SQL en het in plaats daarvan gewoon in code oplossen. Ik snap niet waar de krampachtigheid toch vandaan komt dat alles maar op DB-niveau al opgelost moet worden.

[RwD]

ACM schreef op zaterdag 06 oktober 2012 @ 18:20:
Dit zijn dingen die met sql vrij lastig worden. Eigenlijk is het niet zo geschikt voor "doe mij alle info van de goedkoopste"-queries. Wellicht werkt zoiets:

code:

SELECT ...
FROM product p 
 JOIN productstock ps USING (ean)
WHERE sp.id = 
  (SELECT psi.id 
  FROM productstock psi 
    JOIN supplier s ON psi.supId = s.id
  WHERE psi.ean = p.ean
  ORDER BY IF(sup.orderBefore>40667, sup.deliveryTime, sup.deliveryTime+86400), price
  LIMIT 1)

...

Deze werkt, is verbazingwekkend simpel EN is bijna 5 keer sneller dan de oude query die deze vervangt. Het enige dat ik heb veranderd is dat ik de laagste prijs MET stock>0 moest hebben. Dit heb ik met een IFNULL gedaan, is dit de juiste manier? (LEFT JOIN kon door WHERE niet):

code:

SELECT ...
FROM product p 
 JOIN productstock sp USING (ean)
WHERE sp.id = 
  IFNULL((SELECT psi.id 
  FROM productstock psi 
    JOIN supplier s ON psi.supId = s.id
  WHERE psi.ean = p.ean
  AND stock>0
  ORDER BY IF(sup.orderBefore>40667, sup.deliveryTime, sup.deliveryTime+86400), price
  LIMIT 1), (SELECT psi.id 
  FROM productstock psi 
    JOIN supplier s ON psi.supId = s.id
  WHERE psi.ean = p.ean
  ORDER BY IF(sup.orderBefore>40667, sup.deliveryTime, sup.deliveryTime+86400), price
  LIMIT 1))

@.oisyn; Ik heb ook niet gezegd dat het met alleen SQL moest. Als iemand had geadviseerd het met code te doen omdat om een reden efficienter was had ik het zo opgelost. Nu blijkt dat de eerste query uit het eerste antwoord eenvoudig EN werkend is. Ideaal!