我正在查询允许我按分数订购食谱。

表格结构

结构是一张传单包含一个或多个flyer_items，它可以包含一个或多个ingredients_to_flyer_item(此表将成分链接到传单项目)。另一个表 ingredient_to_recipe 将相同的成分链接到一个或多个食谱。 .sql 文件的链接包含在末尾。

示例查询

我想获得 recipe_id 和作为食谱一部分的每种成分的 MAX 价格重量的总和(由 ingredient_to_recipe 链接)，但是如果一个食谱有多种成分属于同一个 flyers_item，它应该被计算一次.

SELECT itr.recipe_id,
       SUM(itr.weight),
       SUM(max_price_weight),
       SUM(itr.weight + max_price_weight) AS score
FROM
  ( SELECT MAX(itf.max_price_weight) AS max_price_weight,
           itf.flyer_item_id,
           itf.ingredient_id
   FROM
     (SELECT ifi.ingredient_id,
             MAX(i.price_weight) AS max_price_weight,
             ifi.flyer_item_id
      FROM flyer_items i
      JOIN ingredient_to_flyer_item ifi ON i.id = ifi.flyer_item_id
      WHERE i.flyer_id IN (1,
                           2)
      GROUP BY ifi.ingredient_id ) itf
   GROUP BY itf.flyer_item_id) itf2
JOIN `ingredient_to_recipe` AS itr ON itf2.`ingredient_id` = itr.`ingredient_id`
WHERE recipe_id = 5730
GROUP BY itr.`recipe_id`
ORDER BY score DESC
LIMIT 0,10

查询几乎可以正常工作，因为大多数结果都不错，但对于某些行，某些成分被忽略并且没有按应有的分数计入。

测试用例

| recipe_id | 'score' with current query | what 'score' should be | explanation                                                                 |
|-----------|----------------------------|------------------------|-----------------------------------------------------------------------------|
| 8376      | 51                         | 51                     | Good result                                                                 |
| 3152      | 1                          | 18                     | Only 1 ingredient having a score of one is counted, should be 4 ingredients |
| 4771      | 41                         | 45                     | One ingredient worth score 4 is ignored                                     |
| 10230     | 40                         | 40                     | Good result                                                                 |
| 8958      | 39                         | 39                     | Good result                                                                 |
| 4656      | 28                         | 34                     | One ingredient worth 6 is ignored                                           |
| 11338     | 1                          | 10                     | 2 ingredients, worth 4 and 5 are ignored                                    |

我很难找到一种简单的方法来解释它。让我知道是否还有其他帮助。

这里是演示数据库的链接，用于运行查询、测试示例和测试用例:https://nofile.io/f/F4YSEu8DWmT/meta.zip

非常感谢。

更新(如 Rick James 所问):

这是我能做的最远的。结果总是很好，在子查询中也是如此，但是，我已经完全按照'flyer_item_id'排除了组。所以通过这个查询，我得到了很好的分数，但是如果食谱中的许多成分都是相同的 flyer_item_item，它们将被计算多次(recipe_id = 10557 的分数是 59 而不是好的 56，因为 2 种成分值 3在同一个 flyers_item 中)。我唯一需要更多的是为每个食谱的每个 flyer_item_id 计算一个 MAX(price_weight)，(我最初尝试通过按 'flyer_item_id' 在第一个 group_by ingredient_id 上分组。

SELECT itr.recipe_id,
       SUM(itr.weight) as total_ingredient_weight,
       SUM(itf.price_weight) as total_price_weight,
       SUM(itr.weight+itf.price_weight) as score
FROM
  (SELECT fi1.id, MAX(fi1.price_weight) as price_weight, ingredient_to_flyer_item.ingredient_id as ingredient_id, recipe_id
FROM flyer_items fi1
INNER JOIN (
    SELECT flyer_items.id as id, MAX(price_weight) as price_weight, ingredient_to_flyer_item.ingredient_id as ingredient_id
    FROM flyer_items
    JOIN ingredient_to_flyer_item ON flyer_items.id = ingredient_to_flyer_item.flyer_item_id
    GROUP BY id
) fi2 ON fi1.id = fi2.id AND fi1.price_weight = fi2.price_weight
JOIN ingredient_to_flyer_item ON fi1.id = ingredient_to_flyer_item.flyer_item_id
JOIN ingredient_to_recipe ON ingredient_to_flyer_item.ingredient_id = ingredient_to_recipe.ingredient_id
GROUP BY ingredient_to_flyer_item.ingredient_id) AS itf
INNER JOIN `ingredient_to_recipe` AS `itr` ON `itf`.`ingredient_id` = `itr`.`ingredient_id`
GROUP BY `itr`.`recipe_id`
ORDER BY `score` DESC
LIMIT 10

这是解释，但我不确定它是否有用，因为最后的工作部分仍然缺失:

| id | select_type | table                    | partitions | type   | possible_keys                 | key           | key_len | ref                                                   | rows   | filtered | Extra                           |   |
|----|-------------|--------------------------|------------|--------|-------------------------------|---------------|---------|-------------------------------------------------------|--------|----------|---------------------------------|---|
| 1  | PRIMARY     | itr                      | NULL       | ALL    | recipe_id,ingredient_id       | NULL          | NULL    | NULL                                                  | 151800 | 100.00   | Using temporary; Using filesort |   |
| 1  | PRIMARY     | <derived2>               | NULL       | ref    | <auto_key0>                   | <auto_key0>   | 4       | metadata3.itr.ingredient_id                           | 10     | 100.00   | NULL                            |   |
| 2  | DERIVED     | ingredient_to_flyer_item | NULL       | ALL    | NULL                          | NULL          | NULL    | NULL                                                  | 249    | 100.00   | Using temporary; Using filesort |   |
| 2  | DERIVED     | fi1                      | NULL       | eq_ref | id_2,id,price_weight          | id_2          | 4       | metadata3.ingredient_to_flyer_item.flyer_item_id      | 1      | 100.00   | NULL                            |   |
| 2  | DERIVED     | <derived3>               | NULL       | ref    | <auto_key0>                   | <auto_key0>   | 9       | metadata3.ingredient_to_flyer_item.flyer_item_id,m... | 10     | 100.00   | NULL                            |   |
| 2  | DERIVED     | ingredient_to_recipe     | NULL       | ref    | ingredient_id                 | ingredient_id | 4       | metadata3.ingredient_to_flyer_item.ingredient_id      | 40     | 100.00   | NULL                            |   |
| 3  | DERIVED     | ingredient_to_flyer_item | NULL       | ALL    | NULL                          | NULL          | NULL    | NULL                                                  | 249    | 100.00   | Using temporary; Using filesort |   |
| 3  | DERIVED     | flyer_items              | NULL       | eq_ref | id_2,id,flyer_id,price_weight | id_2          | 4       | metadata3.ingredient_to_flyer_item.flyer_item_id      | 1      | 100.00   | NULL                            |   |

更新2

我设法找到了一个有效的查询，但现在我必须让它更快，它需要超过 500 毫秒才能运行。

SELECT sum(ff.price_weight) as price_weight, sum(ff.weight) as weight, sum(ff.price_weight+ff.weight) as score, ff.recipe_id FROM
(
SELECT DISTINCT
       itf.flyer_item_id as flyer_item_id,
       itf.recipe_id,
       itf.weight,
       aprice_weight AS price_weight
FROM
  (SELECT itfin.flyer_item_id AS flyer_item_id,
          itfin.price_weight AS aprice_weight,
          itfin.ingredient_id,
          itr.recipe_id,
          itr.weight
   FROM
     (SELECT ifi2.flyer_item_id, ifi2.ingredient_id as ingredient_id, MAX(ifi2.price_weight) as price_weight
        FROM
          ingredient_to_flyer_item ifi1
        INNER JOIN (
                SELECT id, MAX(price_weight) as price_weight, ingredient_to_flyer_item.ingredient_id as ingredient_id, ingredient_to_flyer_item.flyer_item_id
                FROM ingredient_to_flyer_item
                GROUP BY ingredient_id
            ) ifi2 ON ifi1.price_weight = ifi2.price_weight AND ifi1.ingredient_id = ifi2.ingredient_id
        WHERE flyer_id IN (1,2)
        GROUP BY ifi1.ingredient_id) AS itfin
      INNER JOIN `ingredient_to_recipe` AS `itr` ON `itfin`.`ingredient_id` = `itr`.`ingredient_id`

     ) AS itf
) ff
GROUP BY recipe_id
ORDER BY `score` DESC
LIMIT 20

这里是解释:

| id | select_type | table                    | partitions | type  | possible_keys                                | key           | key_len | ref                 | rows | filtered | Extra                           |   |
|----|-------------|--------------------------|------------|-------|----------------------------------------------|---------------|---------|---------------------|------|----------|---------------------------------|---|
| 1  | PRIMARY     | <derived2>               | NULL       | ALL   | NULL                                         | NULL          | NULL    | NULL                | 1318 | 100.00   | Using temporary; Using filesort |   |
| 2  | DERIVED     | <derived4>               | NULL       | ALL   | NULL                                         | NULL          | NULL    | NULL                | 37   | 100.00   | Using temporary                 |   |
| 2  | DERIVED     | itr                      | NULL       | ref   | ingredient_id                                | ingredient_id | 4       | itfin.ingredient_id | 35   | 100.00   | NULL                            |   |
| 4  | DERIVED     | <derived5>               | NULL       | ALL   | NULL                                         | NULL          | NULL    | NULL                | 249  | 100.00   | Using temporary; Using filesort |   |
| 4  | DERIVED     | ifi1                     | NULL       | ref   | ingredient_id,itx_full,price_weight,flyer_id | ingredient_id | 4       | ifi2.ingredient_id  | 1    | 12.50    | Using where                     |   |
| 5  | DERIVED     | ingredient_to_flyer_item | NULL       | index | ingredient_id,itx_full                       | ingredient_id | 4       | NULL                | 249  | 100.00   | NULL                            |   |

最佳答案

听起来像“explode-implode”。这是查询具有JOIN 和GROUP BY 的地方。

1. JOIN 从连接表中收集适当的行组合； 然后
2. GROUP BY COUNTs、SUM 等，为您提供夸大的聚合值。

有两个常见的修复方法，都涉及将聚合与 JOIN 分开。

案例一:

SELECT  ...
        ( SELECT SUM(x) FROM t2 WHERE id = ... ) AS sum_x,
        ...
    FROM t1 ...

如果您需要来自 t2 的多个聚合，这种情况会变得笨拙，因为它一次只允许一个。

案例二:

SELECT ...
    FROM ( SELECT grp,
                  SUM(x) AS sum_x,
                  COUNT(*) AS ct
           FROM t2 ) AS s
    JOIN t1 ON t1.grp = s.grp

你有 2 个 JOINs 和 3 个 GROUP BYs，所以我建议你从内到外调试(和重写)你的查询。

        SELECT  ifi.ingredient_id,
                MAX(price_weight) as max_price_weight,
                flyer_item_id
            from  flyer_items i
            join  ingredient_to_flyer_item ifi  ON i.id = ifi.flyer_item_id
            where  flyer_id in (1, 2)
            group by  ifi.ingredient_id 

但我帮不了你，因为你没有通过它所在的表(或别名)限定 price_weight。(其他一些列也是如此。)

(实际上，MAX 和 MIN 不会得到夸大的值；AVG 会得到稍微错误的值；COUNT 和 SUM 得到“错误”值。)

因此，我将把剩下的作为“练习”留给读者。

索引

itr:  (ingredient_id, recipe_id)  -- for the JOIN and WHERE and GROUP BY
itr:  (recipe_id, ingredient_id, weight)  -- for 1st Update
(There is no optimization available for the ORDER BY and LIMIT)
flyer_items:  (flyer_id, price_weight) -- unless flyer_id is the PRIMARY KEY
ifi:  (flyer_item_id, ingredient_id)
ifi:  (ingredient_id, flyer_item_id)  -- for 1st Update

请为相关表提供`SHOW CREATE TABLE。

请提供EXPLAIN SELECT ...。

如果ingredient_to_flyer_item是多对多映射表，请按照提示here . ingredient_to_recipe 也是如此吗？

GROUP BY itf.flyer_item_id 可能无效，因为它不包括非聚合的 ifi.ingredient_id。参见“only_full_group_by”。

重新制定

完成 INDEXes 的评估后，请尝试以下操作。 注意:我不知道它是否能正常工作。

JOIN  `ingredient_to_recipe` AS itr  ON itf2.`ingredient_id` = itr.`ingredient_id`

到

JOIN ( SELECT recipe_id,
              ingredient_id,
              SUM(weight) AS sum_weight
           FROM ingredient_to_recipe ) AS itr

并更改初始 SELECT 以用这些计算的和替换 SUMs。 (我怀疑我没有正确处理 ingredient_id。)

您运行的是什么版本的 MySQL/MariaDB？

关于mysql - 在多个表上选择最大值，而不计算它们两次，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/50609217/