[SQL] Laagste prijs met voorraad laten zien

[Yagermeister]

Voor een project op het werk zijn wij bezig om een mysql database met een berg data te koppelen aan een frontend access gui. Dit is allemaal perfect en ook alle query's werken prima.

Alleen de query wat eigenlijk het belangrijkste voor ons is krijg ik niet goed werkend. In 1 van de tabellen hebben wij alle leveranciers informatie staan met daarbij de prijzen en voorraad. Nu willen wij van deze tabel de laagste prijs met voorraad weten gegroepeerd per sku.

Ik heb een query die mij de laagste prijs laat zien gegroepeerd per sku echter pakt hij altijd de eerste waarde die hij tegenkomst in de tabel in plaats van de daadwerkelijk correcte waarde.

code:

pps_reference   stock   price   sku         mpn         manufacturer    supplier_code   code (key)
77ssf07         8       74,61   S00025535   f7c029ea    belkin          supplier1       sup1-77ssf07
30fgh87         0       72,88   S00025535   f7c029ea    belkin          supplier2       sup2-30fgh87
lyuib3          5       53,61   S00025535   f7c029ea    belkin          supplier3       sup3-lyuib3

bovenstaand is 1 van de sku's die wij in de database hebben. Hier hoort supplier 3 als winnaar uit de bus te komen omdat deze de laagste prijs heeft en voorraad.

code:

SELECT
  l.sku,
  Min(s.price) AS MinVanPrice,
  s.stock,
  s.supplier_code,
  s.pps_reference,
  l.`code`
FROM
  leverancier AS l
  INNER JOIN leverancier AS s ON l.`code` = s.`code`
WHERE
  l.stock >= 1
GROUP BY
  l.sku
;

Met deze query krijg ik echter wel de laagste prijs echter laat hij de data zien van supplier 1 omdat deze eerder in de tabel staat.

Weet iemand hoe ik dit het beste kan doen of oplossen? Ik heb al een hele berg geprobeerd maar ik kom hier niet mee verder.

[wwwFan]

Je kan een join maken met een subquery die de laagste prijs per sku geeft:

SQL:

SELECT
  l.sku,
  l.price,
  l.stock,
  l.supplier_code,
  l.pps_reference,
  l.`code`
FROM leverancier as l
INNER JOIN (SELECT sku, MIN(price) as min_price FROM leverancier WHERE stock > 0 GROUP BY sku ) as mv
ON mv.sku = l.sku and mv.min_price = l.price

[Yagermeister]

wwwFan schreef op zondag 7 januari 2018 @ 14:27:
Je kan een join maken met een subquery die de laagste prijs per sku geeft:

SQL:

SELECT
  l.sku,
  l.price,
  l.stock,
  l.supplier_code,
  l.pps_reference,
  l.`code`
FROM leverancier as l
INNER JOIN (SELECT sku, MIN(price) as min_price FROM leverancier WHERE stock > 0 GROUP BY sku ) as mv
ON mv.sku = l.sku and mv.min_price = l.price

Bedankt voor deze query. Ik had deze eerst ook en doordat dit maar bleef lopen heb ik hem maar afgebroken omdat ik dacht dat die fout was. Echter doet die van jou precies hetzelfde. Ik heb hem nu 2 minuten laten lopen en de query is nog steeds niet klaar met het resultaat. Er zitten ongeveer 100k regels in de betreffende database. Dat mag toch geen probleem zijn lijkt me.

[alex3305]

Waarom niet een simpele ORDER BY gebruiken, eventueel in combinatie met een LIMIT 1 zodat je maar één resultaat terugkrijgt?

[KabouterSuper]

Allereerst: welk dialect gebruik je? Ik vermoed mysql.
Ten tweede: je query is malformed; je selecteert kolommen die waar je niet op groepeert. Normaliter mag dit niet. Mysql laat het in bepaalde gevallen toe (zoekterm: ONLY_FULL_GROUP_BY, of zoeken in https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html), maar dat levert soms onverwachte output op. Ik vermoed dat dat de reden is dat je de data van supplier 1 terugkrijgt.

Dit gezegd hebbende, wat je stiekem wil is een FIRST_VALUE-groepsfunctie. Dit wordt niet ondersteund door mysql, maar wel door bijvoorbeeld oracle. Het is wel te simuleren, maar je loopt het risico dat je queries erg traag worden (en ik denk dat je dat zelf inmiddels ondervonden hebt). Wellicht kan je een index toevoegen om de query iets trager te krijgen, of kan je op internet gaan zoeken naar gelijkgestemden die een first_value hebben geimplementeerd via een query in mysql.

[CurlyMo]

Zoiets?

SQL:

select
    *
from
    (select
        l.sku,
        l.price,
        l.stock,
        l.supplier_code,
        l.pps_reference,
        l.`code`
    from
        leverancier 
    where
        stock > 0
    order by
        price
    )
group by
    sku

Maar wat @KabouterSuper al zegt. Je doet iets illegaals dat alleen MySQL toestaat. Idealiter wil je toegang hebben tot windows functions die wel in Oracle en PostgreSQL zitten, en in MySQL 8 eraan komen. In PostgreSQL heb je nog de distinct on:

Oracle:

SQL:

select
   foo
from
   (select
      row_number() over (partition by bar order by foo) as nr,
      foo
   from
      test
   )
where
   nr = 1

PostgreSQL:

SQL:

select
   distinct on(bar)
   foo
from
   test
order by
   foo

[ Voor 23% gewijzigd door CurlyMo op 07-01-2018 16:29 ]

[Yagermeister]

alex3305 schreef op zondag 7 januari 2018 @ 16:07:
Waarom niet een simpele ORDER BY gebruiken, eventueel in combinatie met een LIMIT 1 zodat je maar één resultaat terugkrijgt?

Hier heb ik eerlijk gezegd niet eens aan gedacht. Ik heb nu onderstaande query en het ziet er naar uit dat dit voldoende is. Ik heb geen limit gebruikt maar ik zal die toevoegen mocht het nodig zijn.

code:

SELECT
l.sku,
l.price,
l.stock,
l.supplier_code,
l.pps_reference,
l.`code`
FROM
leverancier AS l
WHERE
l.stock >= 1
ORDER BY
l.sku ASC,
l.price ASC

Super bedankt hiervoor.

KabouterSuper schreef op zondag 7 januari 2018 @ 16:23:
Allereerst: welk dialect gebruik je? Ik vermoed mysql.
Ten tweede: je query is malformed; je selecteert kolommen die waar je niet op groepeert. Normaliter mag dit niet. Mysql laat het in bepaalde gevallen toe (zoekterm: ONLY_FULL_GROUP_BY, of zoeken in https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html), maar dat levert soms onverwachte output op. Ik vermoed dat dat de reden is dat je de data van supplier 1 terugkrijgt.

Dit gezegd hebbende, wat je stiekem wil is een FIRST_VALUE-groepsfunctie. Dit wordt niet ondersteund door mysql, maar wel door bijvoorbeeld oracle. Het is wel te simuleren, maar je loopt het risico dat je queries erg traag worden (en ik denk dat je dat zelf inmiddels ondervonden hebt). Wellicht kan je een index toevoegen om de query iets trager te krijgen, of kan je op internet gaan zoeken naar gelijkgestemden die een first_value hebben geimplementeerd via een query in mysql.

CurlyMo schreef op zondag 7 januari 2018 @ 16:26:
Zoiets?

SQL:

select
    *
from
    (select
        l.sku,
        l.price,
        l.stock,
        l.supplier_code,
        l.pps_reference,
        l.`code`
    from
        leverancier 
    where
        stock > 0
    order by
        price
    )
group by
    sku

Maar wat @KabouterSuper al zegt. Je doet iets illegaals dat alleen MySQL toestaat. Idealiter wil je toegang hebben tot windows functions die wel in Oracle en PostgreSQL zitten, en in MySQL 8 eraan komen. In PostgreSQL heb je nog de distinct on:

Oracle:

SQL:

select
   foo
from
   (select
      row_number() over (partition by bar order by foo) as nr,
      foo
   from
      test
   )
where
   nr = 1

PostgreSQL:

SQL:

select
   distinct on(bar)
   foo
from
   test
order by
   foo

Ik gebruik inderdaad mysql. Met de bovenstaande aanpassing is het me ook gelukt waardoor ik dit niet meer nodig heb. Wat ik wel begrijp nu is dat dit eigenlijk niet mogelijk is in de huidige versie van mysql maar als het goed is wel in de toekomst zou moeten komen.

Een beetje raar is wel dat alles wat ik ervan gevonden heb ook dit bovenstaande gebruikt. In totaal heeft de query ruimschoots 10min gelopen wat dus te lang is maar dankzij de simpele oplossing die alex3305 gaf is het ook gelukt. Ik sorteer gewoon de gebruikte kolommen en filter op de stock. Dat is voldoende om altijd de winnaar te bepalen.

Het klinkt misschien raar maar dat komt omdat ik via sku koppel in access en dus alleen de eerste result te zien krijg.