El desafío de la asignación exacta con unidades indivisibles se remonta al método de Hamilton de apportionment utilizado para los asientos del congreso de EE.UU., donde los representantes fraccionarios son imposibles y los restos deben distribuirse de manera justa. En SQL, esto se manifiesta al dividir cantidades monetarias entre entradas de libro contable o distribuir inventario donde las cuentas de SKU deben ser enteras. Las primeras soluciones dependían de cursores o bucles procedimentales, violando el paradigma basado en conjuntos. Las modernas funciones de ventana ANSI SQL:2003 permitieron algoritmos de acarreo puramente declarativos que mantienen la precisión matemática sin procesamiento fila por fila.
Al dividir una cantidad total $T$ entre $n$ filas con partes fraccionarias exactas $s_1, s_2, ..., s_n$ (donde $\sum s_i = T$), el simple redondeo de filas individuales da lugar a $\sum \text{round}(s_i) \neq T$ debido a errores fraccionarios acumulados. Se requiere producir enteros $a_1, a_2, ..., a_n$ de tal forma que $\sum a_i = T$ exactamente, mientras se minimiza la desviación $|a_i - s_i|$ para cada fila. El algoritmo debe respetar un orden de prioridad definido (por ejemplo, antigüedad, marca de tiempo de transacción) para decidir de manera determinista qué filas reciben el valor teto cuando existen restos fraccionarios.
Utilizar funciones de ventana acumulativa para calcular la asignación acumulativa objetivo en cada paso como $\text{floor}(\sum_{j=1}^{i} s_j)$. La asignación para la fila $i$ es la diferencia entre la acumulativa objetivo actual y la acumulativa objetivo anterior: $a_i = \text{floor}(\text{cum}i) - \text{floor}(\text{cum}{i-1})$. Esto lleva adelante cualquier déficit de redondeo de filas anteriores en el cálculo de la fila actual.
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;
Esto garantiza que el $\text{cum_target}$ final sea igual a $T$, y cada paso intermedio absorbe errores de redondeo previos.
Un sistema de nómina debe distribuir un fondo de bonificación anual de $10,000.00 entre 150 empleados según proporciones de rendimiento. La participación teórica de cada empleado se calcula en valores como $66.666...$ dólares, pero el sistema contable exige asignaciones en céntimos enteros y la suma debe coincidir exactamente con el presupuesto de $10,000.00 para cumplir con los controles de auditoría.
Un enfoque utiliza un cursor para iterar a través de los empleados, asignando el valor FLOOR y llevando el resto fraccionario a la siguiente fila. Esto asegura precisión pero requiere código procedimental y tiene un mal rendimiento con conjuntos de datos grandes debido al procesamiento fila por fila y el bloqueo. También complica la gestión de transacciones y los escenarios de reversión.
Otro enfoque calcula todos los valores FLOOR, luego intenta agregar 1 céntimo a los $N$ empleados con los mayores restos fraccionarios utilizando una subconsulta ROW_NUMBER(). Si bien está basado en conjuntos, esto requiere múltiples escaneos de tablas y uniones complejas para determinar cuántas filas necesitan el céntimo extra (el conteo de restos), y tiene dificultades con la ruptura de empates cuando muchos empleados comparten partes fraccionarias idénticas.
La solución elegida implementa el método de acarreo acumulativo ANSI SQL. Al calcular la suma acumulativa de participaciones exactas y tomar el FLOOR de ese valor acumulativo en cada paso, el sistema determina exactamente cuánto debería haberse distribuido hasta ese punto. La diferencia entre los objetivos acumulativos sucesivos da la asignación de la fila actual. Esto absorbe naturalmente las discrepancias de redondeo: si el primer empleado recibe 66 céntimos en lugar de 66.666, el déficit de 0.666 se lleva a cabo en el cálculo acumulativo del segundo empleado, empujando potencialmente su objetivo acumulativo de 133.333 a 133, dándole 67 céntimos. Este enfoque procesa toda la nómina en un solo paso basado en conjuntos, mantiene la estricta conformidad ACID y garantiza que el total distribuido sea exactamente $10,000.00.
El resultado fue una reducción del 95% en el tiempo de procesamiento comparado con el método del cursor y cero errores de conciliación durante la auditoría financiera trimestral.
¿Por qué sustraer el piso acumulativo anterior del piso acumulativo actual distribuye correctamente el resto?
Los candidatos a menudo intentan calcular los errores de redondeo de filas individuales y luego distribuirlos explícitamente. La idea es que FLOOR(cumulative_exact) representa la asignación total ideal hasta esa fila si solo pudiéramos asignar unidades enteras. Al diferenciar estos objetivos acumulativos, efectivamente preguntamos "¿cuántas nuevas unidades enteras introduce esta fila en el total acumulativo?" Si las filas anteriores dejaron un resto de 0.4, la parte 0.6 de la siguiente fila empuja la fracción acumulativa más allá del límite entero, permitiendo que la fila actual reclame la unidad extra que la fila anterior no pudo. Este acarreo implícito elimina la necesidad de rastrear restos explícitamente.
¿Cómo se comporta este algoritmo con valores exactos negativos y por qué podría ser problemático FLOOR allí?
La mayoría de los candidatos asumen valores positivos. Para participaciones negativas (por ejemplo, débitos o devoluciones), FLOOR redondea lejos de cero (por ejemplo, FLOOR(-1.2) = -2), lo que exaggera la magnitud negativamente. La lógica de acarreo aún se equilibra matemáticamente, pero la interpretación comercial de "prioridad" cambia: las asignaciones negativas pueden consumir el "resto" de manera inesperada. La solución requiere usar TRUNC (hacia cero) o CEIL para valores negativos dependiendo de si el negocio prefiere redondear hacia cero para créditos. Una implementación robusta usa expresiones CASE para aplicar FLOOR para valores positivos y CEIL para valores negativos, asegurando que la desviación absoluta se minimice consistentemente.
¿Qué asegura que la asignación de la última fila agote exactamente el recurso total sin una verificación explícita?
Los candidatos se preocupan por errores de uno. La garantía matemática proviene de la propiedad de la serie telescópica: $\sum_{i=1}^{n} (T_i - T_{i-1}) = T_n - T_0$, donde $T_i = \text{FLOOR}(\sum_{j=1}^{i} s_j)$ y $T_0 = 0$. Dado que $T_n = \text{FLOOR}(\sum_{j=1}^{n} s_j) = \text{FLOOR}(T) = T$ (suponiendo que $T$ es entero), la suma de todas las diferencias debe ser igual a $T$. El marco de ventana ANSI SQL asegura que la función LAG recupere correctamente $T_{i-1}$, haciendo que la asignación final absorba automáticamente cualquier resto residual de todas las filas anteriores.