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:
- There are 9 different products.
- All Products have 2 Prices, one with description PriceA and one described PriceB.
- All these prices are the same per type for every product. (That is, all PriceA prices are the same, and all PriceB prices are the same.)
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:- 5 times product1,
- 5 times product2,
- 5 times product3,
- 3 times product4,
- 3 times product5.
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:
- 5 times product1,
- 5 times product2,
- 5 times product3,
- 5 times product4,
- 2 times product5,
- 2 times product6,
- 2 times product7
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 cou开发者_运维问答nt(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);
}
}
}
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:
- Sort the array in descending order by the number of items of product
- Add the number of items of the fifth product in the array to the total number of discounts
- Subtract the number of items of the fifth product in the array from the first five products in the array
- Delete any zero elements from the array
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)....
精彩评论