De uitdaging van exacte toewijzing met ondeelbare eenheden dateert uit de Hamilton-methode van verdeling die gebruikt wordt voor de zetels in het Amerikaanse congres, waar fractionele vertegenwoordigers onmogelijk zijn en restwaarden eerlijk verdeeld moeten worden. In SQL komt dit tot uiting wanneer financiële bedragen over boekhoudkundige entries verdeeld worden of wanneer inventaris verdeeld moet worden waarbij SKU-tellingen gehele getallen moeten zijn. Vroegere oplossingen maakten gebruik van cursors of procedurele lussen, wat in strijd was met het set-gebaseerde paradigma. Moderne ANSI SQL:2003 vensterfuncties maakten puur declaratieve carry-over-algoritmes mogelijk die wiskundige precisie handhaven zonder rij-voor-rij verwerking.
Bij het verdelen van een totale hoeveelheid $T$ over $n$ rijen met exacte fracties $s_1, s_2, ..., s_n$ (waarbij $\sum s_i = T$), zorgt eenvoudige afronding van individuele rijen ervoor dat $\sum \text{round}(s_i) eq T$ door geaccumuleerde fractiefouten. De vereiste is om gehele getallen $a_1, a_2, ..., a_n$ te produceren zodat $\sum a_i = T$ exact, terwijl de afwijking $|a_i - s_i|$ voor elke rij geminimaliseerd wordt. Het algoritme moet een gedefinieerde prioriteitsvolgorde respecteren (bijv. senioriteit, transactietijdstempel) om deterministisch te beslissen welke rijen de plafondwaarde ontvangen wanneer er fractionele restwaarden zijn.
Gebruik cumulatieve vensterfuncties om de doelcumulatieve toewijzing op elke stap te berekenen als $\text{floor}(\sum_{j=1}^{i} s_j)$. De toewijzing voor rij $i$ is het verschil tussen de huidige doelcumulatieve en de vorige doelcumulatieve: $a_i = \text{floor}(\text{cum}i) - \text{floor}(\text{cum}{i-1})$. Dit draagt effectief eventuele afrondingsdeficiënties van vorige rijen over in de berekening van de huidige rij.
WITH allocations AS ( SELECT id, priority, exact_share, SUM(exact_share) OVER (ORDER BY priority) AS cum_exact, FLOOR(SUM(exact_share) OVER (ORDER BY priority)) AS cum_target FROM distribution_queue ) SELECT id, priority, exact_share, (cum_target - COALESCE(LAG(cum_target) OVER (ORDER BY priority), 0)) AS allocated_units FROM allocations;
Dit garandeert dat de uiteindelijke $\text{cum_target}$ gelijk is aan $T$, en elke tussenstap absorbeert eerdere afrondingsfouten.
Een salarisadministratiesysteem moet een jaarlijkse bonuspool van $10,000.00 verdelen over 150 werknemers op basis van prestatieverhoudingen. Het theoretische aandeel van elke werknemer berekent tot waarden zoals $66.666...$ dollar, maar het boekhoudingssysteem vereist gehele centtoewijzingen (gehele centen) en de som moet exact overeenkomen met het budget van $10,000.00 om aan auditcontroles te voldoen.
Een benadering gebruikt een cursor om door de werknemers te itereren, de FLOOR-waarde toe te wijzen en de fractionele rest naar de volgende rij over te dragen. Dit garandeert nauwkeurigheid, maar vereist procedurele code en presteert slecht met grote datasets door rij-voor-rij verwerking en vergrendeling. Het bemoeilijkt ook het transactiebeheer en scenarios voor terugdraaien.
Een andere benadering berekent alle FLOOR-waarden, probeert vervolgens 1 cent toe te voegen aan de bovenste $N$ werknemers met de grootste fractionele restwaarden met behulp van een ROW_NUMBER() subquery. Hoewel set-gebaseerd, vereist dit meerdere tabelscans en complexe joins om te bepalen hoeveel rijen de extra cent nodig hebben (de restwaarde-telling), en het worstelt met het doorbreken van gelijke gevallen wanneer veel werknemers identieke fractionele delen delen.
De gekozen oplossing implementeert de ANSI SQL cumulatieve carry-over-methode. Door de lopende som van exacte aandelen te berekenen en de FLOOR van die cumulatieve waarde op elke stap te nemen, bepaalt het systeem precies hoeveel tot dat punt gedistribueerd had moeten worden. Het verschil tussen opeenvolgende cumulatieve doelen geeft de toewijzing van de huidige rij. Dit absorbeert van nature afrondingsdiscrepanties: als de eerste werknemer 66 cent ontvangt in plaats van 66.666, draagt de 0.666 tekort naar de cumulatieve berekening van de tweede werknemer, waardoor hun cumulatieve doel van 133.333 naar 133 kan verschuiven, waardoor ze 67 cent krijgen. Deze aanpak verwerkt de gehele salarisadministratie in één set-gebaseerde doorgang, behoudt strikte ACID-naleving, en garandeert dat de totaal gedistribueerde bedragen precies $10,000.00 gelijk zijn.
Het resultaat was een vermindering van 95% in verwerkingstijd vergeleken met de cursormethode en geen reconciliatiefouten tijdens de kwartaal financiële audit.
Waarom distribueert het aftrekken van de vorige cumulatieve vloer van de huidige cumulatieve vloer de restcorrect?
Kandidaten proberen vaak individuele rijafrondingsfouten te berekenen en deze vervolgens expliciet te verdelen. De inzicht is dat FLOOR(cumulatieve_exact) het ideale totale toewijzing tot die rij vertegenwoordigt als we alleen gehele eenheden konden toewijzen. Door deze cumulatieve doelen te differentiëren, vragen we effectief: "hoeveel nieuwe gehele eenheden introduceert deze rij in de cumulatieve totalen?" Als vorige rijen een 0.4 restwaarde achterlieten, duwt het 0.6 aandeel van de volgende rij de cumulatieve fractie over de gehele grens, waardoor de huidige rij de extra eenheid kan claimen die de vorige rij niet kon. Dit impliciete carry-forward elimineert de noodzaak om restwaarden expliciet bij te houden.
Hoe gedraagt dit algoritme zich met negatieve exacte_aandelenwaarden en waarom kan FLOOR daar problematisch zijn?
De meeste kandidaten gaan uit van positieve waarden. Voor negatieve aandelen (bijv. debet of retouren), rondt FLOOR weg van nul (bijv. FLOOR(-1.2) = -2), wat de magnitude negatief overdrijft. De carry-over-logica balanceert nog steeds wiskundig, maar de zakelijke interpretatie van "prioriteit" verandert—negatieve toewijzingen kunnen de "restwaarde" onverwacht verbruiken. De oplossing vereist het gebruik van TRUNC (richting nul) of CEIL voor negatieve waarden, afhankelijk van of het bedrijf de voorkeur geeft aan afronding richting nul voor tegoeden. Een robuuste implementatie gebruikt CASE-expressies om FLOOR voor positieve en CEIL voor negatieve waarden toe te passen, zodat de absolute afwijking consistent geminimaliseerd wordt.
Wat zorgt ervoor dat de toewijzing van de laatste rij exact de totale hulpbron uitput zonder expliciete controle?
Kandidaten maken zich zorgen over off-by-one fouten. De wiskundige garantie komt van de telescopische reeks eigenschap: $\sum_{i=1}^{n} (T_i - T_{i-1}) = T_n - T_0$, waarbij $T_i = \text{FLOOR}(\sum_{j=1}^{i} s_j)$ en $T_0 = 0$. Aangezien $T_n = \text{FLOOR}(\sum_{j=1}^{n} s_j) = \text{FLOOR}(T) = T$ (ervan uitgaande dat $T$ een geheel getal is), moet de som van alle verschillen gelijk zijn aan $T$. Het ANSI SQL vensterframe zorgt ervoor dat de LAG-functie correct $T_{i-1}$ ophaalt, waardoor de uiteindelijke toewijzing automatisch elke residuele rest van alle voorgaande rijen absorbeert.