Dit artikel is de on-line tegenhanger van de rubriek Puzzelen met SQL die verschijnt in de Optimize, het vakblad voor Oracle ontwikkelaars in Nederland.
Vooral op het einde van het jaar wordt het op de kleintjes letten. Een groot deel van je budget gaat op aan Sinterklaas- en Kerst inkopen. Niet alleen aan kadootjes, maar ook in de supermarkt wordt het nodige besteed.
Weinig omgevingen zijn vanuit informatie management oogpunt zo interessant als de supermarkt. Naast de logistieke stromen die moeten worden gecontroleerd, zijn er natuurlijk de kassasystemen, de loyalty-programma’s en de koopgedrag-analyses. Hoe wordt optimaal gebruik gemaakt van de winkelruimte? Welke producten moet je naast elkaar zetten voor optimale verkoopmarges? Mijn vader, intussen gepensioneerd, was een kruidenier die altijd zei “Dit is echt centenwerk. De marges zijn maar klein, dus hoe je winkel is ingedeeld is van wezenlijk belang”.
Maar ook: hoe moet je, met al die kortingsacties, de totaalprijs op de kassabon berekenen? Dat laatste gaan we doen in deze puzzel.
Het onderstaande model doet, op primitieve wijze, drie dingen:
- Registratie van het assortiment
- Vastleggen van voordeelacties
- Registratie van alle aankopen en berekeningen van het totaal op de kassabon
De acties zijn van uiteenlopende aard – deze vormen het meest complexe onderdeel van het datamodel. Een actie kan van toepassing zijn op een speciaal product (Calvé Pindakaas, 400g) of op een productsoort (alle potten pindakaas).
Een actie kan een product simpelweg afprijzen: “Van prijs X voor Prijs Y”, of een bepaalde korting geven: “Nu product A met X% korting”. Vaak is een kortingspas van toepassing bij aankoop van meerdere exemplaren: “koop nu X exemplaren van product A voor prijs Y” of: “Drie halen, twee betalen (hetgeen hetzelfde is als bij aankoop van 3 exemplaren krijg je 33% korting).
Een actie kan ook betrekking hebben op een productsoort: “Drie potten pindakaas naar keuze voor 5 euro”. Of: “25% korting bij aankoop van 6 flessen rode wijn”. Vaak zit er een addertje onder het gras bij kortingen op productsoort, en slaat de korting alleen op het goedkoopste product: “Bij aankoop van 6 flessen rode wijn naar keuze, de goedkoopste met 50% korting”.
In een tabelletje staan deze actievormen gerelateerd aan het datamodel:
| PDT_ID | PST_ID | K_VGK_JN | AANTAL | PRIJS | % | Omschrijving |
| X | nvt | nvt | X | X | <aantal> exemplaren van <product> voor <prijs> | |
| X | nvt | nvt | X | X | <aantal> exemplaren van <product> met <%> korting | |
| nvt | X | N | X | X | Bij <aantal> exemplaren van <productsoort> naar keuze, <%> korting (over de hele set) | |
| nvt | X | N | X | X | <aantal> exemplaren van <productsoort> naar keuze voor <prijs> | |
| nvt | X | J | X | X | Bij <aantal> exemplaren van <productsoort> naar keuze, <%> korting op het goedkoopste artikel | |
| nvt | X | J | X | X | Bij aankoop van <aantal> exemplaren van <productsoort> naar keuze, de goedkoopste voor <prijs> |
Uitdagingen
1) Wat is het (wat beperkte) assortiment van deze supermarkt? Toon de artikelen per (en op volgorde van) productgroep en productsoort.
Om deze vraag te kunnen beantwoorden hebben we in ieder geval drie tabellen nodig, te weten de Productgroepen, de productsoorten en de producten. Een query tegen deze drie tabellen is dan ook zo geschreven.
select pgp.naam
,pst.label
,pdt.label
,pdt.prijs
from productgroepen pgp
,productsoorten pst
,producten pdt
where pdt.pst_id = pst.id
and pgp.id = pst.pgp_id
order by pgp.naam
,pst.label
,pdt.label;
NAAM LABEL LABEL PRIJS
-------------------- ------------------------- ------------------------- ----------
Alcoholische Dranken Port Finest Ruby Port 7.99
Alcoholische Dranken Rode Wijn Bordeaux Mas de la 2.99
Maume,2005
Alcoholische Dranken Rode Wijn Huiswijn, Bodega de las 1.99
Torres
Alcoholische Dranken Rode Wijn Tempranillo Berberana 4.26
Alcoholische Dranken Rode Wijn Vino Tinto de Penedes, 6.78
2004
Broodbeleg Hagelslag De Ruyter Pure Hagelslag, 2.89
400g
Broodbeleg Jam Eigen Merk Jam, 1.69
Abrikozen, 600g
Broodbeleg Jam Hero, Extra Confiture, 2.56
Granaatappeljam, 375g
Broodbeleg Pindakaas Calve Pindakaas, 3.19
…
Zoals je kunt zien, en wat ook te verwachten is, is dat de Productgroep namen en de Productsoort labels meerdere keren in de restultaat set te zien zijn. Als je frequent gebruiker bent van SQL*Plus dan weet je dat misschien dat er een BREAK commando tot je beschikking staat. Met behulp van dit BREAK commando kun je repeterende namen laten onderdrukken.
SQL> break on naam
SQL> select pgp.naam
2 ,pst.label
3 from productgroepen pgp
4 ,productsoorten pst
5 ,producten pdt
6 where pdt.pst_id = pst.id
7 and pgp.id = pst.pgp_id
8 order by pgp.naam
9 ,pst.label
10 ,pdt.label
11 /
NAAM LABEL
-------------------- -----------
Alcoholische Dranken Port
Rode Wijn
Rode Wijn
Rode Wijn
Rode Wijn
Broodbeleg Hagelslag
Jam
Jam
Pindakaas
Pindakaas
Pindakaas
11 rows selected.
Hierboven zie je het resultaat van het BREAK commando. Nu heeft SQL*Plus deze mogelijkheid wel, en waarschijnlijke vele reporting tools ook, maar dit effect is ook mogelijk door handig gebruik te maken van Analytische functies.
Als we aan bovenstaande query de volgende Analytische Functie toevoegen dan weten we op welk niveau we een break moeten uitvoeren, we willen namelijk alleen de eerste tonen en de overige namen willen we onderdrukken.
row_number() over (partition by pgp.naam
order by pgp.naam
, pst.label
, pdt.label
) pst_rn
SQL> select pgp.naam 2 , pst.label 3 , row_number() over (partition by pgp.naam 4 order by pgp.naam 5 , pst.label 6 , pdt.label 7 ) pst_rn 8 from productgroepen pgp 9 ,productsoorten pst 10 ,producten pdt 11 where pdt.pst_id = pst.id 12 and pgp.id = pst.pgp_id 13 order by pgp.naam 14 ,pst.label 15 ,pdt.label 16 / NAAM LABEL PST_RN ----------------------------------- ------------------------- ---------- Alcoholische Dranken Port 1 Alcoholische Dranken Rode Wijn 2 Alcoholische Dranken Rode Wijn 3 Alcoholische Dranken Rode Wijn 4 Alcoholische Dranken Rode Wijn 5 Broodbeleg Hagelslag 1 Broodbeleg Jam 2 Broodbeleg Jam 3 …
Als we dan alleen de eerste uit de groep laten tonen, bijvoorbeeld via een CASE statement, dan hebben we hetzelfde effect bereikt als de SQL*Plus BREAK.
SQL> select case
2 when pst_rn = 1
3 then naam
4 end naam
5 , label
6 from (
7 select pgp.naam
8 , pst.label
9 , row_number() over (partition by pgp.naam
10 order by pgp.naam
11 , pst.label
12 , pdt.label
13 ) pst_rn
14 from productgroepen pgp
15 ,productsoorten pst
16 ,producten pdt
17 where pdt.pst_id = pst.id
18 and pgp.id = pst.pgp_id
19 order by pgp.naam
20 ,pst.label
21 ,pdt.label
22 )
23 /
NAAM LABEL
----------------------------------- -----------------
Alcoholische Dranken Port
Rode Wijn
Rode Wijn
Rode Wijn
Rode Wijn
Broodbeleg Hagelslag
Jam
Jam
Pindakaas
…
Uiteraard kunnen we dezelfde truuk voor de Productsoorten Label uithalen.
2) Welke artikelen zijn aangeschaft bij de transactie die kassabon 1 heeft opgeleverd? Geef de aantallen per product.
Aangezien alle transacties worden geregistreerd in de KASSABONNEN tabel wordt het terugzoeken van transacties heel eenvoudig. Met behulp van onderstaande query word een overzicht getoond van artikelen en de aantallen zoals deze vermeld staan op de kassabon.
with items as
( select pdt.label
, pdt.prijs
, kbn.datum
from productsoorten pst
, producten pdt
, aankopen akp
, kassabonnen kbn
where akp.pdt_id = pdt.id
and pdt.pst_id = pst.id
and akp.kbn_id = kbn.id
and kbn.id = 1
order by pst.label
, pdt.label
)
select label
, count(*) aantal
from items
group by label
/
LABEL AANTAL
--------------------------------------------- ----------
Euroshopper, Supervoordeel, 1 kg 2
Tempranillo Berberana 1
Huiswijn, Bodega de las Torres 2
Eigen Merk, Met nootjes, 450 g 1
Bordeaux Mas de la Maume,2005 1
Vino Tinto de Penedes, 2004 1
Calve Pindakaas, grootverpakking, 700 gr 2
De query begint met de WITH Clause, in de Oracle documentatie wordt dit Subquery Factoring genoemd. Deze techniek is het best te vergelijken met een inline view. Vooraf definieer je een naam voor de inline view en deze kun je gebruiken in de rest van het SQL statement. Uiteraard is deze query ook met een inline view te schrijven.
De reden dat er meer tabellen worden gebruikt dan strikt noodzakelijk is vooruitlopend op de derde uitdaging.
3) Toon nogmaals kassabon nummer 1: nu met alle verschillende producten op de kassabon, het aantal exemplaren van elk product en de totaalprijs.
Een kassabon zou geen kassabon zijn als er niet een totaalprijs op zou staan. Door verder te bouwen op de query zoals we die bij de tweede uitdaging hebben opgesteld, met alle benodigde informatie in het Subquery Factoring deel, is deze uitdaging vrij snel gerealiseerd.
with items as
( select pdt.label
, pdt.prijs
, kbn.datum
from productsoorten pst
, producten pdt
, aankopen akp
, kassabonnen kbn
where akp.pdt_id = pdt.id
and pdt.pst_id = pst.id
and akp.kbn_id = kbn.id
and kbn.id = 1
order by pst.label
, pdt.label
)
select case grouping(label)
when 1
then 'Totaal'
else label
end label
, count(*) aantal
, sum(prijs) prijs
from items
group by rollup(label)
/
LABEL AANTAL PRIJS
--------------------------------------------- ---------- ----------
Bordeaux Mas de la Maume,2005 1 2.99
Calve Pindakaas, grootverpakking, 700 gr 2 6.38
Eigen Merk, Met nootjes, 450 g 1 1.45
Euroshopper, Supervoordeel, 1 kg 2 1.58
Huiswijn, Bodega de las Torres 2 3.98
Tempranillo Berberana 1 4.26
Vino Tinto de Penedes, 2004 1 6.78
Totaal 10 27.42
Wat opvalt in deze query is het gebruik van GROUPING en ROLLUP. Met behulp van de ROLLUP functie kun je Subtotalen en Totalen bepalen. Om dit te tonen wordt er een extra rij toegevoegd in het resultaat waarin het subtotaal staat. Waar er in de tweede uitdaging slechts zeven rijen stonden, staan er nu acht.
Maar hoe herken je nu zo’n subtotaal regel? Hiervoor is de GROUPING functie. Deze functie geeft een 1 of een 0 terug afhankelijk of de huidige regel een subtotaal regel is of niet.
Gebruik makend van een CASE statement en de GROUPING functie kun je heel netjes het query resultaat formateren. Vandaar dat er in de laatste regel een label is met “Totaal” die het totale aantal en de totale prijs toont.
4) Toon de acties die op dit moment geldig zijn. We maken acties van verschillende types:
- een actie gekoppeld aan een product waarbij een aantal exemplaren voor een vaste prijs wordt aangeboden (“3 keer dit product voor 5 euro”)
- een actie gekoppeld aan een product waarbij bij aankoop van een aantal exemplaren een kortingspercentage geldt (“3 voor de prijs van 2 (33% korting bij aankoop van 3)
- een actie gekoppeld aan een productsoort, waarbij een aantal artikelen van een bepaalde soort samen voor een prijs worden aangeboden (“3 flessen rode wijn naar keuze voor 8 euro”)
- een actie gekoppeld aan een productsoort, waarbij bij aankoop een aantal artikelen van een bepaalde soort de goedkoopste met een bepaalde korting of voor een bepaalde prijs wordt aangeboden (“bij aankoop van 3 flessen rode wijn, de goedkoopste voor half geld”)
Om de lopende acties te kunnen tonen moeten we gebruik maken van de ACTIES tabel, de PRODUCTEN tabel en de PRODUCT_SOORTEN tabel. Acties kunnen namelijk voor individuele producten gelden of voor Productsoorten.
Om een outer join tussen deze drie tabellen te leggen maken we gebruik van de ANSI-standaard voor de OUTER JOIN. Op de traditionele manier van het schrijven van joins over meerdere tabellen worden join- en filtercriteria in de WHERE clause van het SQL statement geplaatst. Bij een ANSI-join wordt er wel degelijk onderscheid gemaakt tussen de join- en filtercriteria. De join criteria worden in de ON-clause gezet en de filtercriteria worden in de WHERE clause geplaatst.
select pdt.label actie_product
, pst.label actie_product_soort
, case nvl(pdt.id,-1)
when -1 -- kortingsactie voor productsoort
then
case ace.korting_voor_goedkoopste_jn
when 'N'
then case
when ace.prijs is not null
then nvl(aantal,1)
||' keer '
||pst.label
||' naar keuze voor '
||ace.prijs
when ace.aantal is not null
then nvl(aantal,1)
||' keer '
||pdt.label
||' voor de prijs van '
||round(ace.aantal*(1-ace.kortings_percentage/100))
end
else case
when ace.aantal is not null and ace.prijs is null
then 'bij aankoop van '
||nvl(aantal,1)
||' keer '
||pst.label
||' naar keuze, de goedkoopste met '
||ace.kortings_percentage
||'% korting'
when ace.aantal is not null and ace.prijs is not null
then 'bij aankoop van '
||nvl(aantal,1)
||' keer '
||pst.label
||' naar keuze, de goedkoopste voor '
||ace.prijs
end
end
else -- kortingsactie voor product
case
when ace.prijs is not null
then nvl(aantal,1)
||' keer '
||pdt.label
||' van '
||ace.aantal*pdt.prijs
||' voor '
||ace.prijs
when ace.aantal is not null
then nvl(aantal,1)
||' keer '
||pdt.label
||' voor de prijs van '
||round(ace.aantal*(1-ace.kortings_percentage/100))
end
end actie_omschrijving
from actie ace
left outer join
producten pdt
on ( pdt.id = ace.pdt_id)
left outer join
productsoorten pst
on ( pst.id = ace.pst_id)
Met, nogal uitgebreide, CASE statements wordt bepaald of er acties gelden voor producten of voor productsoorten. Je zou er uiteraard ook voor kunnen kiezen om dit met diverse DECODE’s te schrijven. Een CASE statement biedt echter het voordeel dat het een stuk eenvoudiger te begrijpen is dan een DECODE, vooral als je meerdere DECODE statements genest hebt.
Een ander voordeel wat het CASE statement boven de DECODE biedt, is dat je eenvoudig vergelijkingen kunt doen in het statement. Als we naar een klein stukje van bovenstaande query kijken, bijvoorbeeld:
... case when ace.aantal is not null and ace.prijs is null then ...
Dan is het nog niet zo eenvoudig om dit te vertalen naar een DECODE statement, terwijl het CASE statement zo geschreven is en goed te begrijpen is.
5) Bereken de kassabon met in achtname van alle kortingsacties die van toepassing zijn.
Nu alle verschillende componenten van de kassabon bepaald zijn, het aantal artikelen – de speciale acties die gelden op de artikelen, kan alles samenkomen en de kortingen kunnen verwerkt gaan worden. In het volgende, ruim 190 regels tellende SQL statement, wordt de nieuwe prijs berekend inclusief het bedrag wat door de klant voldaan dient te worden.
with items as
(select pdt.label product
, pst.label product_soort
, pdt.prijs product_prijs
, kbn.datum transactie_datum
, pst.id pst_id
, pdt.id pdt_id
, akp.id aankoop_id
, count(*) over (partition by pdt_id) aantal_pdt
, count(*) over (partition by pst_id) aantal_pst
, row_number() over (partition by pdt_id
order by akp.id desc) rang_binnen_product
, row_number() over (partition by pst_id
order by pdt.prijs desc) rang_binnen_productsoort
, rownum itm_rn
from productsoorten pst
, producten pdt
, aankopen akp
, kassabonnen kbn
where akp.pdt_id = pdt.id
and pdt.pst_id = pst.id
and akp.kbn_id = kbn.id
and kbn.id = 1
)
, voordeelacties as
( select pdt.label actie_product
, pdt.id actie_pdt_id
, pst.label actie_product_soort
, pst.id actie_pst_id
, ace.aantal
, ace.prijs
, ace.kortings_percentage
, ace.KORTING_VOOR_GOEDKOOPSTE_JN
, ace.begin_datum
, ace.eind_datum
, case nvl(ace.pdt_id,-1)
when -1 -- kortingsactie voor productsoort
then
case ace.KORTING_VOOR_GOEDKOOPSTE_JN
when 'N'
then
case
when ace.prijs is not null
then nvl(aantal,1)||' keer '||pst.label||' naar keuze voor '||ace.prijs
when ace.aantal is not null
then nvl(aantal,1)||' keer '||pdt.label||' voor de prijs van '||round(ace.aantal*(1-ace.kortings_percentage/100))
end
else
case
when ace.aantal is not null and ace.prijs is null
then 'bij aankoop van '||nvl(aantal,1)||' keer '||pst.label||' naar keuze, de goedkoopste met '||ace.kortings_percentage||'% korting'
when ace.aantal is not null and ace.prijs is not null
then 'bij aankoop van '||nvl(aantal,1)||' keer '||pst.label||' naar keuze, de goedkoopste voor '||ace.prijs
end
end
else -- kortingsactie voor product
case
when ace.prijs is not null
then nvl(aantal,1)||' keer '||pdt.label||' van '||ace.aantal*pdt.prijs||' voor '||ace.prijs
when ace.aantal is not null
then nvl(aantal,1)||' keer '||pdt.label||' voor de prijs van '||round(ace.aantal*(1-ace.kortings_percentage/100))
end
end actie_omschrijving
from actie ace
left outer join
producten pdt
on ( pdt.id = ace.pdt_id)
left outer join
productsoorten pst
on ( pst.id = ace.pst_id)
)
, geprijsde_aankopen as
( select ace.aantal actie_aantal_halen
, ace.kortings_percentage
, ace.prijs actie_prijs
, ace.actie_omschrijving
, ace.actie_pst_id
, ace.actie_pdt_id
, row_number() over (partition by itm.itm_rn
order by ace.actie_pdt_id nulls last
, ace.actie_pst_id nulls last
) itm_ace_rn
-- bepaal de correcte prijs
-- als kortingsactie op pst dan geldt
-- of er is een totaalprijs voor X producten in die categorie (en dus totaalprijs/x per product)
-- of er is een kortingsprijs voor het Xe product in die categorie (en dan geldt voor Xe en 2Xe etc. product de kortingsprijs : of ace.prijs of (pdt.prijs * 100-kortings_pertcentage)/100)
-- als kortingsactie op pdt dan geldt
-- of de ace.prijs (als ace.aantal = 1) (van 3.98 voor 2.00)
-- of de ace.prijs/x of (pdt.prijs * ace.kortings_percentage)/100 (als ace.aantal > 1) voor de eerste trunc(pdt.aantal/ace.aantal) (dus als 3 voor 5 euro dan 7 voor 10 euro + 1* pdt.prijs)
, case
when ace.actie_pdt_id is not null -- kortingsactie voor product
then case
when ace.prijs is not null
then
case -- 'prijs is '||ace.prijs||' per '||ace.aantal; oftewel de aantalste voor ace.prijs, de eerste gratis
when mod(rang_binnen_product, aantal) = 0 -- bij drie voor is dat de 3e, de 6e, de 9e etc.
then -ace.prijs
else -- nu hangt het er van af: de 1e en 2e binnen de eerste 3 zijn nu gratis, maar de 4e en 5e van 5 gaan voor het normale bedrag case
case
when rang_binnen_product > trunc(aantal_pdt/ ace.aantal)
then product_prijs
else 0
end
end
else --'wat is de korting ' -- ||ace.kortings_percentage||' per '||ace.aantal
case
when rang_binnen_product > trunc(aantal_pdt/ ace.aantal)
then product_prijs
else -product_prijs * (1-ace.kortings_percentage/100)
end
end
when ace.actie_pst_id is not null -- kortingsactie voor productsoort
then
case ace.KORTING_VOOR_GOEDKOOPSTE_JN
when 'N'
then
case
when ace.prijs is not null
then -- 'prijs is '||ace.prijs||' per '||ace.aantal||' van soort '||ace.actie_product_soort
case -- 'prijs is '||ace.prijs||' per '||ace.aantal; oftewel de aantalste voor ace.prijs, de eerste gratis
when mod(rang_binnen_productsoort, aantal) = 0 -- bij drie voor is dat de 3e, de 6e, de 9e etc.
then -ace.prijs
else -- nu hangt het er van af: de 1e en 2e binnen de eerste 3 zijn nu gratis, maar de 4e en 5e van 5 gaan voor het normale bedrag case
case
when rang_binnen_productsoort > trunc(aantal_pst/ace.aantal)
then product_prijs
else 0
end
end
else --'hier percent ' -- 'kortingspercentage is '||ace.kortings_percentage||' per '||ace.aantal
case
when rang_binnen_productsoort > trunc(aantal_pst/ ace.aantal)
then product_prijs
else -product_prijs * (1-ace.kortings_percentage/100)
end
end
else -- korting voor de goedkoopste
case mod(rang_binnen_productsoort, aantal)
when 0 -- de aantalste of de twee-keer-aantalste
then
case
when ace.prijs is not null
then -ace.prijs
else -product_prijs* (1-ace.kortings_percentage/100)
end
else product_prijs
end
end
else product_prijs
end gecorrigeerde_prijs
, itm.*
from items itm
left outer join
voordeelacties ace
on ((itm.pdt_id = ace.actie_pdt_id
and itm.transactie_datum between ace.begin_datum
and ace.eind_datum
)
or
(itm.pst_id = ace.actie_pst_id
and itm.transactie_datum between ace.begin_datum
and ace.eind_datum
)
)
)
, nog_te_ontdubbelen_prijzen as
(
select geprijsde_aankopen.*
, row_number() over (partition by itm_rn
order by abs(gecorrigeerde_prijs) asc) prijs_rn
from geprijsde_aankopen
)
select product
, sum(abs(gecorrigeerde_prijs))
, count(*)
, case sign(gecorrigeerde_prijs)
when 1
then null
else actie_omschrijving
end actie
from nog_te_ontdubbelen_prijzen
where prijs_rn=1
group by rollup ( ( product
, case sign(gecorrigeerde_prijs)
when 1
then null
else actie_omschrijving
end
)
)
order by product
;
Net als de vorige “Puzzelen met SQL” artikelen, kun je met behulp van deze demo scripts zelf de puzzel maken.
Anders nog iets?
![Using unique function based index as check constraint]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Using unique function based index as check constraint")
Bonuskaart en Airmiles? 😉