从 A 中选择全部并连接 B 中的所有非重复组合,以便在 MySQL 中: ab、ac、ad、abc、abd...?

发布于 2024-12-09 23:47:23 字数 709 浏览 1 评论 0原文

我有一张 A 桌子,里面有食品。

我有一张带有食物插件的表B

B具有字段:

  • 价格
  • 添加_卡路里
  • 名称
  • 产品

我想要进行这样的查询,该查询将获得产品和插件的前10个(或更多)组合,从而产生最佳卡路里 / 价格比率。我需要保留要向用户显示的插件产品的名称。

我应该能够在 1 个产品上安装多个插件,这样我就应该尝试并运行所有组合。

如果我们有插件 abcd 那么我必须尝试组合:

  • Product + a
  • Product + b
  • Product + ab
  • Product + ac
  • Product + ad
  • Product + bc
  • Product + bd
  • ...

并非每个产品都可以选择每个插件。还有一个附加的“连接器”表,指示产品支持的插件。

I have a table A with food products.

I have a table B with food addons.

Table B has the fields:

  • price
  • added_calories
  • name
  • products

I want to make such query that will get the top 10 (or more) combinations of product and addons that will result the best calories / price ratio . I need to keep the names of the addons and product to display to the user.

I should be able to have several addons on 1 product so that I should try and run through all combinations.

If we have addons a, b, c, d then I have to try the combinations:

  • Product + a
  • Product + b
  • Product + ab
  • Product + ac
  • Product + ad
  • Product + bc
  • Product + bd
  • ...

Not every addon can be selected for each product. There's an additional 'connector' table which indicates supported addons for a product.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

北陌 2024-12-16 23:47:23

假设您想最大化卡路里/价格,我认为这是可行的。诀窍在于,对于给定的特定产品,您可以通过按卡路里/价格比降序堆积附加组件来最大化卡路里/价格,直到总比率开始下降。

以下查询可能效率低得可笑,需要认真优化,但至少它表明存在解决方案:

SELECT
  p.name AS product,
  GROUP_CONCAT(a.name) AS additives,
  p.price + COALESCE(SUM(a.price), 0) AS price,
  p.calories + COALESCE(SUM(a.added_calories), 0) AS calories,
  (p.calories + COALESCE(SUM(a.added_calories), 0)) /
    (p.price + COALESCE(SUM(a.price), 0)) AS calories_per_price
FROM
  products AS p
  LEFT JOIN connector AS c ON c.product = p.id
  LEFT JOIN additives AS a
    ON a.id = c.additive
    AND (a.added_calories / a.price) > (
      (p.calories + COALESCE((
        SELECT SUM(b.added_calories) FROM connector AS d, additives AS b
        WHERE d.product = p.id AND b.id = d.additive
          AND (b.added_calories / b.price) > (a.added_calories / a.price)
      ), 0)) /
      (p.price + COALESCE((
        SELECT SUM(b.price) FROM connector AS d, additives AS b
        WHERE d.product = p.id AND b.id = d.additive
          AND (b.added_calories / b.price) > (a.added_calories / a.price)
      ), 0))
    )
GROUP BY p.id
ORDER BY calories_per_price DESC
LIMIT 10;

编辑: 好的,我调试了它,现在它实际上可以工作了(!)。以下是一些测试数据:

INSERT INTO products (id, name, calories, price) VALUES
  (1, 'Cardboard', 0, 1),
  (2, 'Lard', 1000, 100),
  (3, 'Spaghetti', 10, 50);
INSERT INTO additives (id, name, added_calories, price) VALUES
  (1, 'Salt', 0, 2),
  (2, 'Butter', 500, 100),
  (3, 'Cheese', 300, 70),
  (4, 'Pepper', 0, 3),
  (5, 'Ketchup', 50, 10),
  (6, 'Milk', 20, 10);
INSERT INTO connector (product, additive) VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5), (1,6),
  (2,1), (2,3), (2,4), (2,5),
  (3,1), (3,2), (3,3), (3,4), (3,5);

和结果:

+-----------+-----------------------+-------+----------+--------------------+
| product   | additives             | price | calories | calories_per_price |
+-----------+-----------------------+-------+----------+--------------------+
| Lard      | NULL                  |   100 |     1000 |                 10 | 
| Cardboard | Butter,Ketchup        |   111 |      550 |   4.95495495495495 | 
| Spaghetti | Butter,Cheese,Ketchup |   230 |      860 |   3.73913043478261 | 
+-----------+-----------------------+-------+----------+--------------------+

编辑 2: 哎呀,我让它最大化每卡路里的价格,而不是每卡路里的价格。固定的。

编辑3:子查询忽略了连接器表。我(希望)已经修复了该错误,但我还无法对其进行测试。

Assuming that you want to maximize calories / price, I think this is doable. The trick is that, given a specific product, you'll maximize calories / price by piling on add-ons, in descending order by calories / price ratio, until the total ratio starts to decrease.

The following query is probably ridiculously inefficient and in need of serious optimization, but at least it shows that a solution exists:

SELECT
  p.name AS product,
  GROUP_CONCAT(a.name) AS additives,
  p.price + COALESCE(SUM(a.price), 0) AS price,
  p.calories + COALESCE(SUM(a.added_calories), 0) AS calories,
  (p.calories + COALESCE(SUM(a.added_calories), 0)) /
    (p.price + COALESCE(SUM(a.price), 0)) AS calories_per_price
FROM
  products AS p
  LEFT JOIN connector AS c ON c.product = p.id
  LEFT JOIN additives AS a
    ON a.id = c.additive
    AND (a.added_calories / a.price) > (
      (p.calories + COALESCE((
        SELECT SUM(b.added_calories) FROM connector AS d, additives AS b
        WHERE d.product = p.id AND b.id = d.additive
          AND (b.added_calories / b.price) > (a.added_calories / a.price)
      ), 0)) /
      (p.price + COALESCE((
        SELECT SUM(b.price) FROM connector AS d, additives AS b
        WHERE d.product = p.id AND b.id = d.additive
          AND (b.added_calories / b.price) > (a.added_calories / a.price)
      ), 0))
    )
GROUP BY p.id
ORDER BY calories_per_price DESC
LIMIT 10;

Edit: OK, I debugged it, now it actually works(!). Here's some test data:

INSERT INTO products (id, name, calories, price) VALUES
  (1, 'Cardboard', 0, 1),
  (2, 'Lard', 1000, 100),
  (3, 'Spaghetti', 10, 50);
INSERT INTO additives (id, name, added_calories, price) VALUES
  (1, 'Salt', 0, 2),
  (2, 'Butter', 500, 100),
  (3, 'Cheese', 300, 70),
  (4, 'Pepper', 0, 3),
  (5, 'Ketchup', 50, 10),
  (6, 'Milk', 20, 10);
INSERT INTO connector (product, additive) VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5), (1,6),
  (2,1), (2,3), (2,4), (2,5),
  (3,1), (3,2), (3,3), (3,4), (3,5);

And results:

+-----------+-----------------------+-------+----------+--------------------+
| product   | additives             | price | calories | calories_per_price |
+-----------+-----------------------+-------+----------+--------------------+
| Lard      | NULL                  |   100 |     1000 |                 10 | 
| Cardboard | Butter,Ketchup        |   111 |      550 |   4.95495495495495 | 
| Spaghetti | Butter,Cheese,Ketchup |   230 |      860 |   3.73913043478261 | 
+-----------+-----------------------+-------+----------+--------------------+

Edit 2: Oops, I had it maximizing price per calories instead of calories per price. Fixed.

Edit 3: The subqueries were ignoring the connector table. I've (hopefully) fixed that bug, but I haven't been able to test it yet.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文