Maandomzet per regio — een rapportagefunctie lezen
Een SQL-functie met CTE's, een HAVING-filter en een window-functie. Begin hier als je stored procedures nog niet dagelijks leest.
Deze functie draait elke maandagochtend voor het management-dashboard. Hij is niet lang, maar er
zitten vier dingen in die je in vrijwel elke rapportagequery terugziet: een filter op een
datumbereik, een aggregatie met een HAVING, een berekend gemiddelde en een window-functie voor
een percentage van het totaal.
Lees hem eerst helemaal door zonder naar de vragen te kijken. Probeer daarna per vraag eerst zelf een antwoord te formuleren voordat je op Toon antwoord klikt — anders leer je vooral hoe overtuigend het antwoord klinkt.
CREATE OR REPLACE FUNCTION rapportage.maandomzet_per_regio(
p_jaar int,
p_min_omzet numeric DEFAULT 0
)
RETURNS TABLE (
regio text,
maand date,
omzet numeric,
orders bigint,
gem_orderwaarde numeric,
aandeel_pct numeric
)
LANGUAGE sql
STABLE
AS $$
WITH betaalde_orders AS (
SELECT o.id,
o.klant_id,
date_trunc('month', o.besteld_op)::date AS maand,
o.totaal_excl_btw
FROM verkoop.orders o
WHERE o.status = 'betaald'
AND o.besteld_op >= make_date(p_jaar, 1, 1)
AND o.besteld_op < make_date(p_jaar + 1, 1, 1)
),
per_regio AS (
SELECT k.regio,
b.maand,
SUM(b.totaal_excl_btw) AS omzet,
COUNT(*) AS orders
FROM betaalde_orders b
JOIN verkoop.klanten k ON k.id = b.klant_id
GROUP BY k.regio, b.maand
HAVING SUM(b.totaal_excl_btw) >= p_min_omzet
)
SELECT r.regio,
r.maand,
r.omzet,
r.orders,
ROUND(r.omzet / r.orders, 2) AS gem_orderwaarde,
ROUND(100 * r.omzet / SUM(r.omzet) OVER (PARTITION BY r.maand), 1) AS aandeel_pct
FROM per_regio r
ORDER BY r.maand, r.omzet DESC;
$$;
Wat staat er precies in de kolom maand, en waarom staat er ::date achter?
Het jaarfilter staat er als >= make_date(p_jaar, 1, 1) AND < make_date(p_jaar + 1, 1, 1).
Waarom niet gewoon WHERE EXTRACT(year FROM o.besteld_op) = p_jaar?
Wat berekent SUM(r.omzet) OVER (PARTITION BY r.maand), en tellen de waarden in aandeel_pct
altijd op tot 100?
Kan deze functie een division by zero opleveren? Er wordt op twee plekken gedeeld.
JOIN verkoop.klanten k ON k.id = b.klant_id — welke twee dingen gaan hier stil mis met de cijfers?
De functie is gemarkeerd als STABLE en geschreven in LANGUAGE sql in plaats van plpgsql.
Wat levert die combinatie op, en wat verandert er bij VOLATILE?