关于mysql：在多个表上选择最大值，不计算两次

codeneng 2023-03-28 原文

Select max value on multiple tables, without counting them twice

我正在做一个查询，允许我按分数订购食谱。

表结构

结构是Leaflet包含一个或多个flyer_items，其中可以包含一个或多个ingredients_to_flyer_item(此表将成分链接到Leaflet项目)。另一个表 ingredient_to_recipe 将相同的成分链接到一个或多个食谱。最后包含指向 .sql 文件的链接。

示例查询

我想获取 recipe_id 和作为配方一部分的每种成分的 MAX 价格权重的总和(由成分 to_recipe 链接)，但如果一个配方有多个成分属于同一个 flyers_item，它应该计算一次.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

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

测试用例

1
2
3
4
5
6
7
8
9

| 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)，(我最初尝试通过在第一个 group_by 成分 ID 上按 \\'flyer_item_id\\' 进行分组。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

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

1
2
3
4
5
6
7
8
9
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 | | | 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 | | | 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 毫秒才能运行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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

这里是解释：

1
2
3
4
5
6
7
8

| 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 | |

您能否仅提供 MCVE 作为文本而不是 zip 文件。
@Strawberry 即使是一个简单的例子，也很难在线上这么多。 :\\\\
删除 ORDER BY price_weight DESC。在子查询中排序没有意义。
在子查询中，您有列 flyer_id。您没有按此列进行分组，因此您应该使用聚合函数，例如 max(flyer_id)。 ingredient_id 列也存在相同的错误。
请为列使用前缀。我们无法猜测列来自哪些表。
@TheImpaler 查询已根据您的建议进行了更新。
您是否验证了子查询生成了正确的值？
@RickJames我已经更新了一个不太完整的版本，但我确信数据是正确的，直到那个缺失的部分。
@杰夫B。 - 子查询之一是 SELECT id,...，但从未使用过 id。去掉它。并检查其他小错误。
@杰夫B。 - 为了更好地关注 500ms 问题，请对可以从大查询中干净提取的每个子查询进行计时。这可能有助于缩小我们需要关注的范围。

我一直想看看这个，但不幸的是直到现在还没有时间。我认为这个查询会给你你正在寻找的结果。

1
2
3
4
5
6
7
8
9
10
11

SELECT recipe_id, SUM(weight) AS weight, SUM(max_price_weight) AS price_weight, SUM(weight + max_price_weight) AS score
FROM (SELECT recipe_id, ingredient_id, MAX(weight) AS weight, MAX(price_weight) AS max_price_weight
FROM (SELECT itr.recipe_id, MIN(itr.ingredient_id) AS ingredient_id, MAX(itr.weight) AS weight, fi.id, MAX(fi.price_weight) AS price_weight
FROM ingredient_to_recipe itr
JOIN ingredient_to_flyer_item itfi ON itfi.ingredient_id = itr.ingredient_id
JOIN flyer_items fi ON fi.id = itfi.flyer_item_id
GROUP BY itr.recipe_id, fi.id) ri
GROUP BY recipe_id, ingredient_id) r
GROUP BY recipe_id
ORDER BY score DESC
LIMIT 10

它首先按 flyer_item_id 分组，然后按 MIN(ingredient_id) 分组，以考虑配方中具有相同 flyer_item_id 的成分。然后将结果相加得到你想要的分数。如果我使用带有

的查询

1	HAVING recipe_id IN (8376, 3152, 4771, 10230, 8958, 4656, 11338)

子句给出以下结果，与您上面的"应该是什么分数"列相匹配：

1
2
3
4
5
6
7
8

recipe_id weight price_weight score
8376 10 41 51
4771 5 40 45
10230 10 30 40
8958 15 24 39
4656 15 19 34
3152 0 18 18
11338 0 10 10

我不确定此查询在您的系统上执行的速度有多快，它与您在我的笔记本电脑上的查询相当(我预计会慢一些)。我很确定有一些优化是可能的，但同样，还没有时间彻底研究它们。

我希望这能为您找到可行的解决方案提供更多帮助。

听起来像"爆炸-内爆"。这是查询具有 JOIN 和 GROUP BY.

的地方

JOIN 从连接表中收集适当的行组合；然后

GROUP BY COUNTs、SUMs 等，为您提供聚合的膨胀值。

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

分开

案例1：

1
2
3
4

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

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

案例 2：

1
2
3
4
5
6

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，所以我建议你从内到外调试(并重写)你的查询。

1
2
3
4
5
6
7

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 会得到"错误"的值。)

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

索引

1
2
3
4
5
6

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 是多：多映射表，请按照此处的提示进行操作。 ingredient_to_recipe 同上？

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

重新制定

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

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

到

1
2
3
4

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

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

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

我刚刚更新了查询以使所有列(包括 price_weight)都符合表的要求。我试图解构它，甚至从另一种方式重建它，但没有成功。我了解正在发生的事情，但无法找到可行的解决方案。
我在 MySQL 5.7.14 上。我正在尝试您的指示，我会尽快提供更新。
我已经更新了新的查询，不完整，但我可以用好的数据去最远。我还添加了解释。
请参阅更新 2，我找到了一个有效的查询，但希望加快速度。
@杰夫B。 - 对于第一次更新，在看到 EXPLAIN 之后，我在 itr 上添加了另一个索引：(recipe_id, ingredient_id, weight)。重要的部分是 recipe_id 是第一个。
@杰夫B。 - ingredient_to_flyer_item 需要 INDEX(ingredient_id, flyer_item_id)，也许在两个顺序中。 (请提供 SHOW CREATE TABLE)

我不确定我是否完全理解了这个问题。在我看来，您按错误的列 flyer_items.id 进行分组。您应该改为按列 ingredient_id 进行分组。如果你这样做，它更有意义(对我来说)。这是我的看法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

select
itr.recipe_id,
sum(itr.weight),
sum(max_price_weight),
sum(itr.weight + max_price_weight) as score
from (
select
ifi.ingredient_id,
max(price_weight) as max_price_weight
from flyer_items i
join ingredients_to_flyer_item ifi on i.id = ifi.flyer_item_id
where flyer_id in (1, 2)
group by ifi.ingredient_id
) itf
join `ingredient_to_recipe` as itr on itf.`ingredient_id` = itr.`ingredient_id`
group by itr.`recipe_id`
order by score desc
limit 0,10;

希望对你有帮助。

我以前试过。唯一的问题是，如果我开始按 ifi.ingredient_id 分组。如果相同的成分存在于 2 个不同的Leaflet中，它将在分数中计算两次(来自 flyers_item 的 price_weight)。它是同样的问题，但另一种方式。我试图在 flyer_item_id 和成分_id 上找到一个组合。如果我找不到让它按我希望工作的方法，我可能会使用这个查询。
"计数了两次"——那是我的 "explode-implode"。

上选不计 span nbsp class mysql sql

有关关于mysql：在多个表上选择最大值，不计算两次的更多相关文章

ruby-on-rails - Rails 3 中的多个路由文件 - 2
Rails2.3可以选择随时使用RouteSet#add_configuration_file添加更多路由。是否可以在Rails3项目中做同样的事情？最佳答案在config/application.rb中:config.paths.config.routes在Rails3.2(也可能是Rails3.1)中，使用:config.paths["config/routes"] 关于ruby-on-rails-Rails3中的多个路由文件，我们在StackOverflow上找到一个类似的问题
ruby-on-rails - 在 Ruby 中循环遍历多个数组 - 2
我有多个ActiveRecord子类Item的实例数组，我需要根据最早的事件循环打印。在这种情况下，我需要打印付款和维护日期，如下所示:ItemAmaintenancerequiredin5daysItemBpaymentrequiredin6daysItemApaymentrequiredin7daysItemBmaintenancerequiredin8days我目前有两个查询，用于查找maintenance和payment项目(非排他性查询)，并输出如下内容:paymentrequiredin...maintenancerequiredin...有什么方法可以改善上述(丑陋的)代
ruby-on-rails - Rails - 一个 View 中的多个模型 - 2
我需要从一个View访问多个模型。以前，我的links_controller仅用于提供以不同方式排序的链接资源。现在我想包括一个部分(我假设)显示按分数排序的顶级用户(@users=User.all.sort_by(&:score))我知道我可以将此代码插入每个链接操作并从View访问它，但这似乎不是“ruby方式”，我将需要在不久的将来访问更多模型。这可能会变得很脏，是否有针对这种情况的任何技术？注意事项:我认为我的应用程序正朝着单一格式和动态页面内容的方向发展，本质上是一个典型的网络应用程序。我知道before_filter但考虑到我希望应用程序进入的方向，这似乎很麻烦。最终从任何
ruby - 多个属性的 update_column 方法 - 2
我有一个具有一些属性的模型:attr1、attr2和attr3。我需要在不执行回调和验证的情况下更新此属性。我找到了update_column方法，但我想同时更新三个属性。我需要这样的东西:update_columns({attr1:val1,attr2:val2,attr3:val3})代替update_column(attr1,val1)update_column(attr2,val2)update_column(attr3,val3) 最佳答案您可以使用update_columns(attr1:val1,attr2:val2
ruby-on-rails - 在 ruby .gemspec 文件中，如何指定依赖项的多个版本？ - 2
我正在尝试修改当前依赖于定义为activeresource的gem:s.add_dependency"activeresource","~>3.0"为了让gem与Rails4一起工作，我需要扩展依赖关系以与activeresource的版本3或4一起工作。我不想简单地添加以下内容，因为它可能会在以后引起问题:s.add_dependency"activeresource",">=3.0"有没有办法指定可接受版本的列表？~>3.0还是~>4.0？最佳答案根据thedocumentation,如果你想要3到4之间的所有版本，你可以这
ruby - 使用多个数组创建计数 - 2
我正在尝试按0-9和a-z的顺序创建数字和字母列表。我有一组值value_array=['0','1','2','3','4','5','6','7','8','9','a','b'，'光盘'，'e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','','u','v','w','x','y','z']和一个组合列表的数组，按顺序，这些数字可以产生x个字符，比方说三个list_array=[]和一个当前字母和数字组合的数组(在将它插入列表数组之前我会把它变成一个字符串，]current_combo['0','0','0']
ruby-on-rails - before_filter 运行多个方法 - 2
是否有可能:before_filter:authenticate_user!||:authenticate_admin! 最佳答案 before_filter:do_authenticationdefdo_authenticationauthenticate_user!||authenticate_admin!end 关于ruby-on-rails-before_filter运行多个方法，我们在StackOverflow上找到一个类似的问题： https://
ruby-on-rails - Rails 3.1 中具有相同形式的多个模型？ - 2
我正在使用Rails3.1并在一个论坛上工作。我有一个名为Topic的模型，每个模型都有许多Post。当用户创建新主题时，他们也应该创建第一个Post。但是，我不确定如何以相同的形式执行此操作。这是我的代码:classTopic:destroyaccepts_nested_attributes_for:postsvalidates_presence_of:titleendclassPost...但这似乎不起作用。有什么想法吗？谢谢! 最佳答案 @Pablo的回答似乎有你需要的一切。但更具体地说...首先改变你View中的这一行对此#
ruby-on-rails - 使用 ruby 将多个实例变量转换为散列的更好方法？ - 2
我收到格式为的回复#我需要将其转换为哈希值(针对活跃商家)。目前我正在遍历变量并执行此操作:response.instance_variables.eachdo|r|my_hash.merge!(r.to_s.delete("@").intern=>response.instance_eval(r.to_s.delete("@")))end这有效，它将生成{:first="charlie",:last=>"kelly"},但它似乎有点hacky和不稳定。有更好的方法吗？编辑:我刚刚意识到我可以使用instance_variable_get作为该等式的第二部分，但这仍然是主要问题。
ruby - Rails 关联 - 同一个类的多个 has_one 关系 - 2
我的问题的一个例子是体育游戏。一场体育比赛有两支球队，一支主队和一支客队。我的事件记录模型如下:classTeam"Team"has_one:away_team,:class_name=>"Team"end我希望能够通过游戏访问一个团队，例如:Game.find(1).home_team但我收到一个单元化常量错误:Game::team。谁能告诉我我做错了什么？谢谢，最佳答案如果Gamehas_one:team那么Rails假设您的teams表有一个game_id列。不过，您想要的是games表有一个team_id列，在这种情况下

关于mysql：在多个表上选择最大值，不计算两次

Select max value on multiple tables, without counting them twice

有关关于mysql：在多个表上选择最大值，不计算两次的更多相关文章

随机推荐