php - 具有多个随机值但总和始终在一个范围内的 MySQL 查询

Question

我有一张商店商品及其价格表。我正在尝试编写一个 mysql 查询，以随机抽取多个项目(3 到 6 之间)，所有项目的总值在用户选择的值的 20 美元以内。

关于如何实现这个的任何想法？提前致谢!

编辑*** 到目前为止，这就是我所拥有的。最大的问题是 sum(price) 取所有项目的总和。第二个问题是让“LIMIT”是随机的，但我最终可以让 php 在运行查询之前选择 3 到 6 之间的随机数。

SELECT item,price,sum(price) 
FROM items 
WHERE sum(price) BETWEEN ($value-10) AND ($value+10) 
ORDER BY rand() LIMIT 6

Answer 1

我想不出在 SQL 查询中执行此操作的好方法，而无需多次连接您的项目表本身，随着表中项目数量的增加，这将导致组合爆炸。

我已经用 PHP 制定了一个解决方案，可以将您的商品分成价格组。考虑下表:

+----+--------------------+-------+
| id |        item        | price |
+----+--------------------+-------+
|  1 | apple              | 10.5  |
|  2 | banana             | 1.85  |
|  3 | carrot             | 16.22 |
|  4 | donut              | 13.33 |
|  5 | eclair             | 18.85 |
|  6 | froyo              | 26.99 |
|  7 | gingerbread        | 12.15 |
|  8 | honecomb           | 50.68 |
|  9 | ice-cream-sandwich | 2.44  |
| 10 | jellybean          | 2.45  |
| 11 | kitkat             | 2.46  |
| 12 | lollipop           | 42.42 |
+----+--------------------+-------+

http://sqlfiddle.com/#!9/0d815

首先，根据商品的随机数量(在您的情况下为 3 到 6 之间)将商品分成价格组。价格组增量将由价格差异 ($20.00) 除以所选项目数来确定。这确保你不会超出你的差异。以下是一组 4 个项目的示例:

PRICE_GROUP_INCREMENT = VARIANCE/NUMBER_ITEMS

PRICE_GROUP_INCREMENT = 20/4 = 5

SELECT Count(`id`)        AS `item_count`, 
       Round(`price` / 5) `price_group` 
FROM   `items` 
WHERE  `price` <= 35 
GROUP  BY `price_group` 
ORDER  BY `price_group` ASC; 

结果集:

+------------+-------------+
| item_count | price_group |
+------------+-------------+
|          4 |           0 |
|          2 |           2 |
|          2 |           3 |
|          1 |           4 |
+------------+-------------+

接下来，我们可以搜索结果集以找到与目标价格组相等的价格组组合。目标价格组由您的目标价格除以价格组增量确定。使用我们上面的示例，让我们尝试找到 4 件商品，总计 35.00 美元，差异为 20.00 美元。

TARGET_PRICE_GROUP = round(TARGET_PRICE/PRICE_GROUP_INCREMENT)

TARGET_PRICE_GROUP = round($35.00/$5.00) = 7

通过搜索结果集，我们可以得到 7 个目标价格组，其中包含 4 个项目:

SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 0 ORDER BY rand() ASC LIMIT 2;
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 4 ORDER BY rand() ASC LIMIT 1;
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 3 ORDER BY rand() ASC LIMIT 1;
or
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 0 ORDER BY rand() ASC LIMIT 1;
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 3 ORDER BY rand() ASC LIMIT 1;
SELECT `items`.* FROM `items` WHERE ROUND(`price`/5) = 2 ORDER BY rand() ASC LIMIT 2;

为了加快找到随机、合适的查询组合的速度，我编写了一个递归函数，它根据每个价格组中的商品数量随机加权，然后对其进行排序。这加快了速度，因为函数在找到第一个解决方案后立即返回。这是完整的 PHP 脚本:

<?php

function rand_weighted($weight, $total){
    return (float)mt_rand()*(float)$weight/((float)mt_getrandmax()*(float)$total);
};

//you can change these
$targetPrice = 35.00;
$numProducts = rand(3,6);
$maxVariance = 20.00;

$priceGroupIncrement = $maxVariance / $numProducts;
$targetPriceGroupSum = (int)round($targetPrice/$priceGroupIncrement, 0);

$select = "SELECT COUNT(`id`) AS `item_count`, ROUND(`price`/{$priceGroupIncrement}) `price_group`";
$from = "FROM `items`";
$where = "WHERE `price` <= {$targetPrice}";
$groupBy = "GROUP BY `price_group`";
$orderBy = "ORDER BY `price_group` ASC"; //for readability of result set, not necessary
$sql = "{$select} {$from} {$where} {$groupBy} {$orderBy}";

echo "SQL for price groups:\n{$sql};\n\n";

//run your query here and get the result set
//here is a sample result set
//this assumes $targetPrice = 35.00, $numProducts=4, and $maxVariance=20.00
$numProducts = 4;
$priceGroupIncrement = 5;
$targetPriceGroupSum = 7;
$resultSet = array(
    array('item_count'=>4, 'price_group'=>0),
    array('item_count'=>2, 'price_group'=>2),
    array('item_count'=>2, 'price_group'=>3),
    array('item_count'=>1, 'price_group'=>4),
);
//end sample result set

$priceGroupItemCount = array();
$priceGroupWeight = array();
$total = 0;

//randomly weight price group based on how many items are in the group
foreach ($resultSet as $result){
    $priceGroupItemCount[$result['price_group']] = $result['item_count'];
    $total += $result['item_count'];
}
foreach ($resultSet as $result){
    $priceGroupWeight[$result['price_group']] = rand_weighted($result['item_count'], $total);
}

//recursive anonymous function to find a match
$recurse = function($priceGroupWeight, $selection=array(), $priceGroupSum=0) use ($priceGroupItemCount, $total, $numProducts, $targetPriceGroupSum, &$recurse){
    //sort by random weighted value
    arsort($priceGroupWeight);
    //iterate through each item in the $priceGroupWeight associative array
    foreach ($priceGroupWeight as $priceGroup => $weight){
        //copy variables so we can try a price group
        $priceGroupWeightCopy = $priceGroupWeight;
        $selectionCopy = $selection;
        $priceGroupSumCopy = $priceGroupSum + $priceGroup;
        //try to find a combination that adds up to the target price group
        if (isset($selectionCopy[$priceGroup])){
            $selectionCopy[$priceGroup]++;
        } else {
            $selectionCopy[$priceGroup] = 1;
        }
        $selectionCount = array_sum($selectionCopy);
        if ($priceGroupSumCopy == $targetPriceGroupSum && $selectionCount == $numProducts) {
            //we found a working solution!
            return $selectionCopy;
        } else if ($priceGroupSumCopy < $targetPriceGroupSum && $selectionCount < $numProducts) {
            //remove the item from the price group
            unset($priceGroupWeightCopy[$priceGroup]);
            //if there is still remaining items in the group, add the adjusted weight back into the price group
            $remainingInPriceGroup = $priceGroupItemCount[$priceGroup] - $selectionCopy[$priceGroup];
            if ($remainingInPriceGroup > 0){
                $remainingTotal = $total - count($selection);
                $priceGroupWeightCopy[$priceGroup] = rand_weighted($remainingInPriceGroup, $remainingTotal);
            }
            //try to find the solution by recursing
            $tryRecursion = $recurse($priceGroupWeightCopy, $selectionCopy, $priceGroupSumCopy);
            if ($tryRecursion !== null){
                return $tryRecursion;
            }
        }
    }
    return null;
};

$selection = $recurse($priceGroupWeight);

if ($selection===null){
    echo "there are no possible solutions\n";
} else {
    echo "SQL for items:\n";
    foreach ($selection as $priceGroup => $numberFromPriceGroup){
        $select = "SELECT `items`.*";
        $from = "FROM `items`";
        $where = "WHERE ROUND(`price`/{$priceGroupIncrement}) = {$priceGroup}";
        $orderBy = "ORDER BY rand() ASC";
        $limit = "LIMIT {$numberFromPriceGroup}";
        $sql = "{$select} {$from} {$where} {$orderBy} {$limit}";
        echo "$sql;\n";
    }
}

这种算法方法应该比基于纯 SQL 查询的解决方案执行得更好，尤其是当您的项目表增长时。