This is an apportionment function using the Largest Remainder Method (LRM) used to divide a given total amount between any number of buckets proportionally.

apportion_lrm.sql

DROP FUNCTION IF EXISTS apportion_lrm(NUMERIC, JSONB, INTEGER, NUMERIC, BOOLEAN);
CREATE FUNCTION apportion_lrm
  (
    IN apportion_amt NUMERIC,
    IN proportions JSONB,
    IN data_scale INTEGER DEFAULT 0,
    IN proportion_total NUMERIC DEFAULT NULL,
    IN allow_truncate_apportion BOOLEAN DEFAULT FALSE
  )
RETURNS JSONB AS $BODY$
DECLARE
  v_apportion_amt NUMERIC;
  v_proportions JSONB;
  v_data_scale INTEGER;
  v_proportion_total NUMERIC;
  v_allow_truncate_total BOOLEAN;
  v_apportioned_data JSONB; -- Return Var
  v_total_needs_trunc BOOLEAN;
  v_dist_increment NUMERIC;
  v_proportion_data RECORD;
  v_apportion_sum NUMERIC;
  v_quota NUMERIC;
  v_proportion_name TEXT;
  v_proportion_value NUMERIC;
  v_amt_per_quota NUMERIC;
  v_auto_amt NUMERIC;
  v_auto_remainder NUMERIC;
  v_dist_count_remaining INTEGER;
BEGIN
  v_apportion_amt := $1;
  v_proportions := $2;
  v_data_scale := $3;
  v_proportion_total := $4;
  v_allow_truncate_total := $5;

  IF (v_apportion_amt < 0) THEN
    RAISE EXCEPTION 'apportion_lrm: Apportion amount must be >= 0. Val: %', v_apportion_amt;
  END IF;

  IF (v_data_scale IS NULL OR v_data_scale NOT BETWEEN 0 AND 14) THEN
    RAISE EXCEPTION 'apportion_lrm: Data scale must be between 0 and 14 inclusive. Val: %', v_data_scale;
  END IF;

  IF (v_apportion_amt = 0) THEN
    -- Nothing to apportion, return empty object
    v_apportioned_data := '{}'::JSONB;
    RETURN v_apportioned_data;
  END IF;

  v_total_needs_trunc := (trunc(v_apportion_amt, v_data_scale) <> v_apportion_amt);

  IF (v_total_needs_trunc) THEN

    -- If the flag allow_truncate_total is set
    IF (v_allow_truncate_total) THEN

      -- Truncate the total to match the data scale
      v_apportion_amt := trunc(v_apportion_amt, v_data_scale);
      RAISE NOTICE 'apportion_lrm: Truncating total to be apportioned to: %', v_apportion_amt;
    ELSE

      -- Raise exception if given apportion total is more precise than the provided data scale
      RAISE EXCEPTION 'apportion_lrm: Total to be apportioned is more precise than given data scale. Scale: %, Total: %'
        , v_data_scale, v_apportion_amt;
    END IF;
  END IF;

  -- Calculate the size of one amount distribution (varies by scale)
  v_dist_increment := (10 ^ -v_data_scale);

  -- Proportion total not provided, sum it manually
  IF (v_proportion_total IS NULL) THEN

    -- Sum the input proportion to use as the total
    SELECT sum(json_data.value) INTO v_proportion_total
      FROM (
        SELECT (value)::NUMERIC
          FROM jsonb_each_text(v_proportions)
      ) AS json_data;

    RAISE DEBUG 'apportion_lrm: Proportion Total (calculated): %', v_proportion_total;
  ELSE
    RAISE DEBUG 'apportion_lrm: Proportion Total (provided): %', v_proportion_total;
  END IF;

  -- Use the Hare (simple) quota AKA Hamilton's method
  v_quota := v_proportion_total / v_apportion_amt;
  RAISE DEBUG 'apportion_lrm: Hare Quota: %', v_quota;

  CREATE TEMP TABLE IF NOT EXISTS "apportion_data" (
    proportion_name TEXT PRIMARY KEY,
    amount NUMERIC,
    remainder NUMERIC
  ) ON COMMIT DROP;
  TRUNCATE TABLE ONLY "apportion_data";

  FOR v_proportion_data IN
    SELECT key, value
    FROM jsonb_each_text(v_proportions)
  LOOP
    v_proportion_name := (v_proportion_data.key)::TEXT;
    v_proportion_value := (v_proportion_data.value)::NUMERIC;

    RAISE DEBUG 'apportion_lrm: Proportion Name: %', v_proportion_name;
    RAISE DEBUG 'apportion_lrm: Proportion Value: %', v_proportion_value;

    v_amt_per_quota := v_proportion_value / v_quota;

    -- Remove the remainder from the amount per quota to get the automatic amount
    v_auto_amt := trunc(v_amt_per_quota, v_data_scale);
    RAISE DEBUG 'apportion_lrm: Automatic Amount: %', v_auto_amt;

    v_auto_remainder := (v_amt_per_quota - v_auto_amt);
    RAISE DEBUG 'apportion_lrm: Automatic Remainder: %', v_auto_remainder;

    INSERT INTO "apportion_data" (proportion_name, amount, remainder)
      VALUES (v_proportion_name, v_auto_amt, v_auto_remainder);

  END LOOP;

  -- Calculate the total currently apportioned amount
  v_apportion_sum := (SELECT sum(amount) FROM "apportion_data");
  RAISE DEBUG 'apportion_lrm: Before remainder distribute. Apportioned Sum: %', v_apportion_sum;

  -- Check if we need to distribute any remaining amount
  WHILE (v_apportion_sum < v_apportion_amt) LOOP

    -- Calculate the count of distributions remaining
    v_dist_count_remaining := (
      (v_apportion_amt - v_apportion_sum) / v_dist_increment
    )::INTEGER;
    RAISE DEBUG 'apportion_lrm: Distribution Count Remaining: %', v_dist_count_remaining;

    -- Distribute remaining amount by adding an amount increment to the current apportion amount
    -- with the largest proportion remainders first
    UPDATE "apportion_data"
      SET amount = amount + v_dist_increment
      WHERE proportion_name IN (
        SELECT proportion_name
          FROM "apportion_data"
          WHERE (amount <> 0 OR remainder <> 0)
          ORDER BY remainder DESC
          LIMIT v_dist_count_remaining
      );

    -- Calculate the total currently apportioned amount
    v_apportion_sum := (SELECT sum(amount) FROM "apportion_data");
    RAISE DEBUG 'apportion_lrm: After distribute. Apportion Sum: %', v_apportion_sum;

  END LOOP;

  -- Do a final sanity check on the apportion sum since we aren't allowed to violate this rule
  IF (v_apportion_sum <> v_apportion_amt) THEN
    RAISE EXCEPTION 'Amount not correctly distributed! Sum: % Expected: %', v_apportion_sum, v_apportion_amt;
  END IF;

  -- Convert the table data to JSON in format {input_proportion_name: apportioned_amount}
  SELECT jsonb_object_agg(apportioned_data.proportion_name, apportioned_data.amount) INTO v_apportioned_data
    FROM "apportion_data" AS apportioned_data;

  RETURN v_apportioned_data;
END;
$BODY$ LANGUAGE plpgsql;

README.md

PostgreSQL: apportion_lrm()

Package Info

Author: Andy Kellerstrass (@zero5100)

License: MIT

Compatibility: Supports PostgreSQL v9.5+

Description

This is an apportionment function using the Largest Remainder Method (LRM) and the Hare Quota. It is used to divide a given total amount between any number of buckets based on a provided proportion. The apportioned output values are always guaranteed to sum to the input total provided.

Proportions are provided to the second argument in the form of a JSONB object where the keys are the names of the outputs and the values are the proportions. The output object keys will match the input object keys, except that the input proportion value will be replaced with the apportioned amount. Output data keys are not sorted by input order or otherwise due to the nature of key ordering with the JSONB data type. (The nature meaning that JSONB keys don't have an order.)

The default data_scale is 0, which will cause the output values to behave like integers. By changing the data_scale to 2 you can use this function to safely divide money proportionally.

Options

TODO: Add Options Documentation

Usage

Example: Apportion "integer" data. Example election data from Wikipedia. Uses the default data_scale of (0).

SELECT apportion_lrm(election_data.total_seats,
    jsonb_build_object(
      'yellow_seats', election_data.yellow_votes,
      'white_seats',  election_data.white_votes,
      'red_seats',    election_data.red_votes,
      'green_seats',  election_data.green_votes,
      'blue_seats',   election_data.blue_votes,
      'pink_seats',   election_data.pink_votes
    )
  )
  FROM (
    SELECT 10 AS total_seats,
      47000 AS yellow_votes,
      16000 AS white_votes,
      15800 AS red_votes,
      12000 AS green_votes,
      6100  AS blue_votes,
      3100  AS pink_votes
  ) AS election_data;

Result:

{
  "red_seats": 1,
  "blue_seats": 1,
  "pink_seats": 0,
  "green_seats": 1,
  "white_seats": 2,
  "yellow_seats": 5
}

Example: Working with money. Divide profit proportionally between offices based on the employee count at each office. Uses data_scale of (2).

SELECT
  (
    (apportion_result.per_office_profit->>'east_office_profit')::NUMERIC
  )::MONEY AS east_office_profit,
  (
    (apportion_result.per_office_profit->>'west_office_profit')::NUMERIC
  )::MONEY AS west_office_profit,
  (
    (apportion_result.per_office_profit->>'central_office_profit')::NUMERIC
  )::MONEY AS central_office_profit,
  (
    (apportion_result.per_office_profit->>'east_office_profit')::NUMERIC +
    (apportion_result.per_office_profit->>'west_office_profit')::NUMERIC +
    (apportion_result.per_office_profit->>'central_office_profit')::NUMERIC
  )::MONEY AS office_profit_sum
FROM (
  SELECT apportion_lrm(ledger.total_profit::NUMERIC,
    jsonb_build_object(
      'east_office_profit', employee_count.east_office,
      'west_office_profit', employee_count.west_office,
      'central_office_profit', employee_count.central_office
    ), data_scale := 2
  ) AS per_office_profit
  FROM (
    SELECT '$14,347.77'::MONEY AS total_profit
  ) AS ledger,
  (
    SELECT
      81  AS east_office,
      26  AS west_office,
      147 AS central_office
  ) AS employee_count
) AS apportion_result

Result:

east_office_profit	west_office_profit	central_office_profit	office_profit_sum
$4,575.47	$1,468.67	$8,303.63	$14,347.77

LICENSE.txt

Copyright (c) 2017 Andy Kellerstrass (@zero5100)

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

====

The latest version and documentation can be found on the original GitHub gist.
https://gist.github.com/zero5100/2f1ca10df4b415b2fac727cb2c3b2e8f

====