选择30个随机行，其中总和= x [英] select 30 random rows where sum amount = x

问题描述

我有一张桌子

items
id int unsigned auto_increment primary key,
name varchar(255)
price DECIMAL(6,2)

我想从这张表中至少获得30个随机物品，其中总价格等于500，什么是最好的方法?

I want to get at least 30 random items from this table where the total sum of price equals 500, what's the best approach to accomplish this?

我已经看到了这个解决方案，它看起来也有类似的问题

I have seen this solution which looks have a similar issue MySQL Select 3 random rows where sum of three rows is less than value

我想知道是否还有其他更易于实现和/或更有效的解决方案

And I am wondering if there are any other solutions that are easier to implement and/or more efficient

推荐答案

如果您的产品列表满足以下假设，则有解决方案:

There is a solution if your product list satisfies the following assumption:

您拥有所有价格在0.00到500.00之间的产品.例如. 0.01、0.02等至499.99.或0.05、0.10等至499.95.

You have products for all prices between 0.00 and 500.00. eg. 0.01, 0.02 etc to 499.99. or maybe 0.05, 0.10 etc to 499.95.

该算法基于以下内容:

在总计为S的n个正数的集合中，其中至少一个小于S除以n(S/n)

在这种情况下，步骤是:

In this case, the steps are:

1. 随机选择价格< 500/30.得到它的价格，比方说X.
2. 随机选择价格< (500-X)/29.得到它的价格，假设为Y.
3. 随机选择价格< (500-X-Y)/28.

重复29次，可获得29个产品.对于最后一种产品，请选择其中价格=剩余价格的一种. (或价格< =剩余价格和按价格降序排列的订单，希望您能获得足够的价格).

Repeat this 29 times and get 29 products. For the last product, select one where price = remaining price. (or price <= remaining price and order by price desc and hopefully you could get close enough).

对于表格项目:

获取随机产品的最高价格:

Get random product max price:

CREATE PROCEDURE getRandomProduct (IN maxPrice INT, OUT productId INT, productPrice DECIMAL(8,2))
BEGIN
   DECLARE productId INT;
   SET productId = 0;
       SELECT id, price INTO productId, productPrice
       FROM items
       WHERE price < maxPrice
       ORDER BY RAND()
       LIMIT 1;
END

获得29种随机产品:

CREATE PROCEDURE get29products(OUT str, OUT remainingPrice DECIMAL(8,2))
BEGIN
  DECLARE x INT;
  DECLARE id INT;
  DECLARE price DECIMAL(8,2);
  SET x = 30;
  SET str = '';
  SET remainingPrice = 500.00;

  REPEAT
    CALL getRandomProduct(remainingPrice/x, @id, @price);
    SET str = CONCAT(str,',', @id);
    SET x = x - 1;
    SET remainingPrice = remainingPrice - @price;
    UNTIL x <= 1
  END REPEAT;
END

调用过程

CALL `get29products`(@p0, @p1); SELECT @p0 AS `str`, @p1 AS `remainingPrice`;

最后尝试找到最后一个达到500的产品.

and in the end try to find the last product to get to 500.

或者，您可以选择28，然后在提供的链接问题上使用解决方案，以得到一对总和为剩余价格的产品.

Alternatively, you could select 28 and use the solution on the linked question you provided to get a couple of products that sum to the remaining price.

请注意，允许重复个产品.为了避免重复，您可以使用已找到产品的附加IN参数扩展getRandomProduct，并添加条件 NOT IN 来排除它们.

Note that duplicate products are allowed. To avoid duplicates, you could extend getRandomProduct with an additional IN parameter of the products already found and add a condition NOT IN to exclude them.

更新:您可以克服以上限制，以便您始终使用描述的cron流程找到总计为500的集合在下面的第二部分.

Update: You could overcome the above limitation, so that you always find collections that sum to 500 by using a cron process as described at the 2nd section below.

以@Michael Zukowski的建议为基础，您可以

Building on @Michael Zukowski `s suggestion, you could

• 创建一个表来保存找到的集合
• 定义运行上述算法多次(例如10次)的cron进程.每5分钟
• 如果找到与总和匹配的集合，请将其添加到新表中

这样，您可以找到总和总计为500 的收藏集.当用户发出请求时，您可以从新表中选择一个随机集合.

This way you can find collections that always sum exactly to 500. When a user makes a request, you could select a random collection from the new table.

即使匹配率达到20％，cron流程也会在24小时内每5分钟运行一次算法10次，因此您可以收集500多个集合.

Even with a match rate of 20%, a cron process that runs the algorithm 10 times every 5 minutes in 24h you could more than 500 collections.

我认为使用cron流程有以下优点和缺点:

Using a cron process has the following advantages and disadvantages in my opinion:

优势

• 查找完全匹配
• 未根据客户要求进行处理
• 即使匹配率较低，您也可以找到多个收藏集

缺点

• 如果价格数据经常更新，您可能会得出不一致的结果，也许使用cron流程将行不通.
• 必须丢弃或过滤旧收藏
• 对于每个客户端来说，它可能不是随机的，因为不同的客户端可能会看到相同的集合.

这篇关于选择30个随机行，其中总和= x的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！