CODEWITHSUNDEEP
sqlalgorithmmath
Pelle
Pelle

Reputation: 6588

Query that counts different combinations

First of all, my apologies for the title of this question, I don't have a better idea for the moment. Make a good suggestion and I will fix the title. (If I have permission to do that, I actually don't know.)

The situation:

I am having a tough time getting the correct SQL query done. I have a setup where people can place orders, with products etc, and they get discounts in certain circumstances.

Consider the following schema:

Product:
  [...]

Price:
  product_id: integer
  description: string
  [...]

Order:
  [...]

OrderItem:
  order_id: integer
  price_id: integer
  amount: integer

And consider the following rules:

The problem:

For every set of 5 different products with the same price level (i.e. PriceA vs. PriceB), the total price of the order is lowered with a certain amount. I am trying to write a query that tells me how many times that happens.

Examples:

Example 1:
A user places an order:

All at PriceA, the customer receives 3 times the discount, since there are 3 complete sets of 5

Example 2: A user places an order:

All the PriceA price. Now, the customer receives 5 times the discount, since there are 4 sets of 5, two involving product5, two involving product6 and one involving product7.

The struggle:

I tried this SQL:

SELECT min(amount) as amount from
    (SELECT oi.amount from `order` o
        inner join orderitem oi on oi.order_id = o.id
        inner join price p on oi.price_id = p.id AND p.description = "PriceA"
        inner join product pr on p.product_id = pr.id
        order by oi.amount desc
        limit 5) as z
    having count(amount) = 5;

This beautifully works for Example 1, but in example 2, it will give the wrong result, as it will select the first set of 5 items, and then disregard the

The question is: Is this solvable in SQL? Or would I be better of broadening my selection and doing the math by scripting? (My web application is written in PHP, so I do have room for some server-side mathematics.)

The solution:

I implemented Neil's solution; it now looks like this:

/** @var $oid integer The order ID. */

/* Select all amounts ordered per item, only for a given price title. */
$sql = <<<SQL
SELECT oi.amount as amount FROM orderitems oi
    INNER JOIN orders   o  ON oi.order_id  = o.id AND o.id = $oid
    INNER JOIN prices   p  ON oi.price_id  = p.id AND p.title = '%s'
    INNER JOIN products pr ON p.product_id = pr.id
    ORDER BY oi.amount DESC
SQL;
$discountInfo = array(
    array(
        'price'     => 'PriceA',
        'discounts' => array(
            9 => 49.50, /* Key is 'size of set', value is 'discount'. */
            5 => 23.50
        ),
    ),
    array(
        'price' => 'PriceB',
        'discounts'  => array(
            9 => 22,
            5 => 10,
        ),
    ),
);

foreach($discountInfo as $info)
{
    /* Store all ordered amounts per item in Array. */
    $arr = array();
    $result = $this->dbQuery(sprintf($sql,$info['price']));
    while ($row = mysql_fetch_assoc($result)) $arr[] = $row['amount'];

    foreach ($info['discounts'] as $am => $di)
    {
        /* For each highest set of $am items, fetch the smallest member. */
        while (count($arr) >= $am)
        {
            /* Add discount for each complete set */
            $discount += $di * $arr[$am - 1];

            /* Substract the amounts from all members of the set */
            for ($i=0; $i<=$am - 1; $i++) $arr[$i] -= $arr[$am - 1];

            /* Remove all elements that are 0 */
            foreach ($arr as $k=>$v) if ($v == 0) unset ($arr[$k]);

            /* Array is messed up now, re-sort and re-index it. */
            rsort($arr);
        } 
    } 
}

Upvotes: 6

Views: 456

Answers (2)

Neil
Neil

Reputation: 55402

This is how I would do it in code: Split the items into two arrays, one for each price level. For each array of products, while there are at least five products in the array:

  1. Sort the array in descending order by the number of items of product
  2. Add the number of items of the fifth product in the array to the total number of discounts
  3. Subtract the number of items of the fifth product in the array from the first five products in the array
  4. Delete any zero elements from the array

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29649

If I interpret this correctly, you want to divide the count of records, divided by 5, and find the lowest integer (http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_floor)....

Upvotes: 0

Related Questions