Dubbele klanten opsporen — window-functies als ontdubbelaar
De klassieke opschoonquery — welke rij houd je, welke is de dubbele? Met ROW_NUMBER, FIRST_VALUE en een tiebreaker die belangrijker is dan hij lijkt.
Vroeg of laat staan er dubbele klanten in je database: dezelfde persoon die twee keer een account aanmaakte, één keer met een hoofdletter in het e-mailadres en één keer met een spatie erachter. Deze functie zoekt ze op — hij verwijdert niets, hij wijst alleen aan wat je zou verwijderen.
Dat onderscheid is belangrijk, want deze query wordt in de praktijk vrijwel altijd als basis voor een
DELETE gebruikt. Twee van de vragen hieronder gaan precies over waarom dat gevaarlijk is.
CREATE OR REPLACE FUNCTION beheer.vind_dubbele_klanten(
p_max_resultaten int DEFAULT 500
)
RETURNS TABLE (
behouden_id bigint,
dubbele_id bigint,
email text,
naam_verschil boolean,
orders_dubbel bigint
)
LANGUAGE sql
STABLE
AS $$
WITH genormaliseerd AS (
SELECT k.id,
k.naam,
lower(btrim(k.email)) AS email,
k.aangemaakt_op
FROM crm.klanten k
WHERE k.email IS NOT NULL
AND k.verwijderd_op IS NULL
),
gerangschikt AS (
SELECT g.*,
ROW_NUMBER() OVER (PARTITION BY g.email
ORDER BY g.aangemaakt_op, g.id) AS rn,
FIRST_VALUE(g.id) OVER (PARTITION BY g.email
ORDER BY g.aangemaakt_op, g.id) AS eerste_id,
FIRST_VALUE(g.naam) OVER (PARTITION BY g.email
ORDER BY g.aangemaakt_op, g.id) AS eerste_naam
FROM genormaliseerd g
)
SELECT r.eerste_id,
r.id,
r.email,
r.naam IS DISTINCT FROM r.eerste_naam,
(SELECT COUNT(*) FROM verkoop.orders o WHERE o.klant_id = r.id)
FROM gerangschikt r
WHERE r.rn > 1
ORDER BY r.email, r.rn
LIMIT p_max_resultaten;
$$;
Waarom staat er lower(btrim(k.email)) in plaats van gewoon k.email?
Er staan drie window-functies met exact dezelfde PARTITION BY en ORDER BY. Wat doet elk van de
drie, en waarom moet die ORDER BY identiek zijn?
Waarom r.naam IS DISTINCT FROM r.eerste_naam en niet gewoon r.naam <> r.eerste_naam?
De sortering is ORDER BY g.aangemaakt_op, g.id. Wat gebeurt er als je die g.id weglaat?
(SELECT COUNT(*) FROM verkoop.orders o WHERE o.klant_id = r.id) staat in de SELECT-lijst. Wat is
daar het probleem mee?
Deze functie verwijdert niets. Toch is hij gevaarlijk als je hem gebruikt om een DELETE op te
baseren. Waarom — twee redenen.