Geek Answers Handbook

Database / Algorithm for Betting

I need to calculate the price based on the bid structure for these lines:

$303.00 fixed price up to 500 units
$0.023 additional per unit from 501-10,000 units
$0.022 additional per unit from 10,001-25,000 units
$0.021 additional per unit from 25,001-50,000 units

I lost a little when setting up the database structure and the algorithm (a larger anchor point) to calculate this. Has anyone done this? Is there a nice, elegant way to calculate these kinds of things?

edit: As an example, 25,100 units would cost $ 303.00 for the first 500 units, $ 218.50 for the next 9,500 units, $ 330 for the next 15,000 units, and $ 2.10 for the next 100 units totaling $ 853.60.

It will not be a simple calculation 25 100 * $ 0.021 - I know well how to choose and calculate this.

Similar to how income tax is assessed - on a marginal basis.

+5
source share
5 answers

What I've done:

size  units     fixed     per
1       500   303.000   0.000
1     10000     0.000   0.023
1     25000     0.000   0.022
1     50000     0.000   0.021



function calculate_price($size, $quantity) {
  global $db;

  $price = 0;
  $count = 0;

  // fetch rates from the database
  // note: $size is already sanitised by the calling function
  $query = "SELECT units, flat, per FROM rates WHERE size={$size} ORDER BY units ASC";
  $result = $db->query($query);

  // step through the rates
  while($rate = $result->fetch_object()) {
    // figure out how many of our units fall within this tier
    $tier_count = max(0, min($quantity - $count, $rate->units - $count));

    // calculate the price for this tier, including any flat rate
    $tier_price = $rate->flat + ($rate->per * $tier_count);

    // add tier price and count to the totals
    $price += $tier_price;
    $count += $tier_count;

    // store the last, largest number of units rate for any leftovers outside our tiers
    $last_rate = $rate;
  }

  // if some of our units fall outside our defined tiers, use the last tier values for them
  if($count < $quantity) {
    $tier_count = $quantity - $count;
    $tier_price = $last_rate->flat + ($last_rate->per * $tier_count);
    $price += $tier_price;
    $count += $tier_count;
  }

  return $price;
}
-1
source

Python

from collections import namedtuple

RateRule= namedtuple( 'RateRule', ['qty_band','fixed','per_unit'] )    

rate_table = [
    RateRule(500, 303, None),
    RateRule(9500, None, 0.023),
    RateRule(15000, None, 0.022),
    RateRule(25000, None, 0.021)
]

def total_price( units, rate_table ):
    # Base
    total = rate_table[0].fixed
    units_purchased_so_far = rate_table[0].qty_band
    # Whole Price Bands
    rule = 1
    while units > units_purchased_so_far + rate_table[rule].qty_band:
        total += rate_table[rule].qty_band * rate_table[rule].per_unit
        units_purchased_so_far += rate_table[rule].qty_band
        rule += 1
    # Units within the top Price Band
    if units > units_purchased_so_far:
        total += (units - units_purchased_so_far) * rate_table[rule].per_unit
    return total
+3
source

, - , .

:

ID MAX    FIX    UNIT
1  500    303    0
2  9500   0      .23
3  15000  0      .22
4  25000  0      .21

:

$items = ?;
$cost = 0;
$rows = get_rows("select max, fix, unit from pricing order by id asc");
foreach ($rows as $r)
{
    if ($items <= 0)
        break;
    $cost += $r['fix'] + min($r['max'], $items) * $r['unit'];
    $items -= $r['max'];
}

, PHP.

+3

- :

Product
-------
[PK] ProductID


Price
-----
[PK] PriceID
[FK] ProductID
Price
QtyMin
QtyMax

1- . , .

0
SELECT 
 CASE is_fixed_price
  WHEN 1
   THEN unit_price / ?  
  ELSE
   unit_price
  END
FROM rate_structure
WHERE ? BETWEEN min_qty AND max_qty

? , . , mysql 5.x. .

0

More articles:


All Articles