[SQL] Laagte prijs in prijsgroepen na activatie datum

[xiD]

Ik ben bezig met een backoffice systeempje waarin verschillende relaties worden bijgehouden die hun eigen prijsafspraken hebben op verschillende producten. Deze prijsafspraken hebben een datum/tijd waarop ze actief worden.

Hiervoor heb ik de volgende tabel;

product	prijsgroep	prijs	activatie
1	a	9.5	2013-12-30 00:00:00
1	b	7.5	2013-12-29 00:00:00
1	a	6.5	2013-12-27 00:00:00
2	a	13.5	2013-12-30 00:00:00
2	a	6.5	2013-12-28 00:00:00

Nou ben ik op zoek naar de laagste prijs die op dit moment voor een bepaalde relatie geldt.

Stel een relatie zit in prijsgroep a en b en ik wil de prijs voor product 1 opvragen. Als ik de volgende query uitvoer

SELECT * FROM tabel WHERE product = '1' AND (prijsgroep = 'a' OR prijsgroep = 'b') AND activatie < 'NOW' ORDER BY prijs ASC LIMIT 1

Kom ik tot de prijs van 6.5. Maar dit klopt niet aangezien deze prijs niet meer actief is omdat er 2013-12-30 00:00:00 een nieuwe prijs in die groep actief is geworden. De prijs moet 7.5 zijn aangezien dit de laagste prijs is die op dit moment actief is (groep b)

Hoe kan ik ervoor zorgen dat ik alleen de prijzen krijg met de activatietijd het dichtst bij nu maar wel in het verleden?

[enigmafan]

Tja, maar 6.5 is de laagste prijs waarvan de activiatie voor NOW ligt, en je sorteert op laagste prijs dus het is zeer logisch dat 6.5 je resultaat is.
Als je sorteert op activatie DESC krijg je de meest recente prijs maar weer niet de laagste.

Je moet iets doen als select PRIJS IN (select PRIJS FROM tabel where prijsgroep='a' AND activatie < 'NOW' ORDER BY prijs ASC LIMIT 1) or (select PRIJS FROM tabel where prijsgroep='b' AND activatie < 'NOW' ORDER BY prijs ASC LIMIT 1) order by PRIJS asc limit 1

Dit is uit het hoofd, dus maak er wat moois van :-)

[ Voor 0% gewijzigd door enigmafan op 31-12-2013 11:43 . Reden: limit aan het einde toegevoegd ]

[Woy]

Je bent op zoek naar een Groupwise Maximum

[xiD]

@enigmafan: Ik snap precies wat ik doe met mn huidige query, dat is t niet. Ik probeer alleen te zoeken naar de volgende stap. Ik ga even spelen met je query

@woy: Thx daar ga ik ook even naar kijken!

[ Voor 103% gewijzigd door xiD op 31-12-2013 15:47 ]

[CaVeFiSh]

Persoonlijk zou ik gaan voor een oplossing in de brontabel en niet de query zodat je op lange termijn veel kunt winnen qua performance. Een makkelijke oplossing zou bijvoorbeeld het toevoegen van een bit waarmee je aangeeft of een gegeven prijs de actieve is. Daarnaast zorgen dat de code waarmee je een nieuwe record toevoegt hiermee rekening houd en een update op te tabel afvuurt waarmee je dus alle bits op "inactive" set behalve de meeste recente voor dat specifieke artikel.

[Gomez12]

Alhoewel ik ook neig naar de oplossing van CaVeFiSh, moet je wel in de gaten houden dat die erg afhankelijk is van of wat jij zegt wel alles omvat

Als er in de toekomst een wens komt dat mensen bijv een offerte met bijv een week oudere prijzen willen maken (of een week nieuwere prijzen) dan loop je hiermee klem. Want je zit dan echt klem in de huidige prijzen

[CaVeFiSh]

Gomez12 schreef op woensdag 01 januari 2014 @ 16:04:

Als er in de toekomst een wens komt dat mensen bijv een offerte met bijv een week oudere prijzen willen maken (of een week nieuwere prijzen) dan loop je hiermee klem. Want je zit dan echt klem in de huidige prijzen

Dat hoeft niet perse maar je zult wel een systeem moeten hanteren. Als je bijvoorbeeld een week wilt runnen op de oude prijzen dan zul je hoe dan ook een actie ingang moeten zetten zodat je systeem weet welke prijzen het moet tonen. Dus dan kan je net zo goed nieuw record toevoegen en die op actief zetten. Dat bitje zal niet zorgen dat je systeem omvalt. Ik ben het helemaal met je eens dat dit misschien niet alles omvattend is, maar voor het probleem waar de TS nu tegenop loopt lijkt het me een oplossing.

[Gomez12]

CaVeFiSh schreef op donderdag 02 januari 2014 @ 00:04:
[...]
Dat hoeft niet perse maar je zult wel een systeem moeten hanteren. Als je bijvoorbeeld een week wilt runnen op de oude prijzen dan zul je hoe dan ook een actie ingang moeten zetten zodat je systeem weet welke prijzen het moet tonen. Dus dan kan je net zo goed nieuw record toevoegen en die op actief zetten. Dat bitje zal niet zorgen dat je systeem omvalt.

Wat ik bedoel is 1 verkoper die 1 offerte wil maken met prijzen van een week geleden (bijv zo afgesproken met de klant) terwijl er 99 andere verkopers in hetzelfde systeem zitten te werken die uiteraard wel de huidige prijzen moeten hebben.

[xiD]

Aan de oplossing van CaveFish heb ik inderdaad gedacht. Alleen gaat dit in dit geval niet op omdat het gaat om reserveringen die op een bepaalt moment gemaakt worden.

Deze reserveringen worden eens per periode gefactureerd. Om de prijs van de producten te bepalen op het moment dat de reservering gemaakt is wordt de 'NOW' in de SQL query een variabele.

Ik heb een aantal verschillende query's en oplossingen geprobeerd wat verder zitten rommelen maar op de een of andere manier gaat het nog niet zoals ik het wil. Het kwartje wil nog niet vallen.

SELECT *
FROM product_price p1
JOIN (
  SELECT product, MAX(activate) AS activate
  FROM product_price
  WHERE activate < 'NOW'
  GROUP BY product) AS p2
  ON p1.product = p2.product AND p1.activate = p2.activate
ORDER BY price ASC 

Dit geeft me de laatste activatie datum maarja dan zit ik natuurlijk nog met de verschillend groepen. Iemand die mij nog een stukje de goede richting op kan duwen?

[ACM]

Voor Product 1 de 'nieuwste prijs' per prijsgroep en vervolgens van alle nieuwste prijzen de laagste zou met zoiets kunnen:

SQL:

SELECT *
FROM product_price p1
WHERE 
  product = 1
  AND prijsgroep IN ('a', 'b')
  AND activate = (SELECT MAX(activate) 
                       FROM product_price p2 
                       WHERE p2.product = p1.product AND p2.prijsgroep = p1.prijsgroep 
                          AND activate <= 'NOW')
ORDER BY price ASC
LIMIT 1

[ Voor 3% gewijzigd door ACM op 02-01-2014 20:24 ]

[Rath]

Volgens mij moet deze ook het gewenste resultaat geven

SQL:

;with tbl_activeprice as (
    select
        product,
        prijsgroep,
        max(activatie) activatie
    from
        product_price pp with (nolock)
    where
        pp.activate <= now
        and pp.product = 1
        and pp.prijsgroep in ('a', 'b')
    group by
        product,
        prijsgroep
) select top 1
    pp.product,
    pp.prijsgroep,
    pp.prijs
from
    tbl_activeprice ap
    join product_price pp on pp.product = ap.product
        and pp.prijsgroep = ap.prijsgroep
        and pp.activatie = ap.activatie
order by
    pp.prijs asc

[pedorus]

with gaat niet met mysql - http://bugs.mysql.com/bug.php?id=16244 (of die tag klopt niet)

Ideetje voor meerdere producten in een query en zonder dependent subqueries:

SQL:

select p1.* from product_price p1 
left join product_price p2 on 
    p1.product=p2.product and (
        (p2.price<p1.price or (p2.price=p1.price and p2.pricegroup<p1.pricegroup)) or
        (p2.pricegroup=p1.pricegroup and p2.activate>p1.activate)
    )
    and p2.activate <= '2014-01-04' and p2.prijsgroep IN ('a', 'b')
left join product_price p3 on 
    p3.product=p2.product and p3.pricegroup=p2.pricegroup and p3.activate>p2.activate 
    and p3.activate <= '2014-01-04'
where p1.product IN (1, 2) and p1.activate <= '2014-01-04' and p1.pricegroup IN ('a', 'b') 
    and p2.product is null and p3.product is null

left join p2 kijkt naar lagere prijzen in een andere categorie of latere updates in dezelfde prijscategorie. Deze mogen er niet zijn.
left join p3 is null zorgt ervoor dat we alleen naar de lagere prijzen bij laatste updates kijken

Je ziet dat het vrij ingewikkeld wordt..

[Rath]

pedorus schreef op zaterdag 04 januari 2014 @ 14:45:
with gaat niet met mysql - http://bugs.mysql.com/bug.php?id=16244 (of die tag klopt niet)

...

Dat zou kunnen, nog nooit gewerkt met mysql. Ik heb alleen nergens zien staan dat het over mysql ging

[n3ck]

OK - daar klopte niks van. Even aangenomen dat je geen analytische functies kunt gebruiken (met row_number() in oracle/postgres kun je dit vrij simpel doen). In ieder geval moet je in dit geval inderdaad een join doen moet de laatste prijzen en daar vervolgens de goedkoopste van pakken. Ongetwijfeld werkt wat hierboven staat ook, maar dit moet ook werken en is een stuk korter :

SQL:

select product, min(prijs) as goedkoopste from
prijzen a join
(select product, pijsgroep, max(activatie) as activatie from prijzen where [CONDITIES] 
group by product, prijsgroep) b
on a.product=b.product and a.prijsgroep=b.prijsgroep and a.activatie=b.activatie
group by product

(aangenomen dat je per dag dus maar 1 nieuwe prijsvermelding kunt krijgen per product en prijsgroep)

Inderdaad wat ACM zegt; dat komt op hetzelfde neer.

[ Voor 80% gewijzigd door n3ck op 06-01-2014 11:36 ]