在mySQL表中创建记录组合(顺序无所谓，不允许重复)的最佳方法 [英] Best way to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables

问题描述

我希望我不要对我的问题进行解释:

I hope I don't butcher the explanation of my question:

我有一个具有数百行的表格，每一行都是一个包含营养信息的食谱，例如:

I've got a table that has hundreds of rows, each row is a recipe with nutritional information, for example:

recipe_table:

recipe_table:

id  | calories | protein| carbs | fat

recipe1, 100,    20g,     10g,     2g
recipe2, 110,    10g,     12g,     12g
recipe3, 240,    20g,     1g,      23g
....

我需要创建一个新表(recipe_index)，该表以三个一组的形式显示配方表中每个配方的所有可能组合，因此它看起来像:

I needed to create a new table (recipe_index) that would show every possible combination of every recipe in recipe_table as a set of 3, so it would look something like:

recipe_index:

recipe_index:

id1     | id2    | id3    |calories| protein | carbs | fat
recipe1, recipe2, recipe3,   450,     50g,      23g,   37g
....

基本上，它使我可以查询recipe_index并说"3种配方组合的总价值介于440卡路里和460卡路里之间"

Basically it allows me to query recipe_index and say "what 3 recipe combinations come to a total value that's between 440 calories and 460 calories"

我目前执行此操作的代码在3餐时有效，但是最终我在recipe_index中记录了约450,000条记录，我也需要在4,5餐和6餐时执行相同的操作，因此我要计算数百万记录的结尾.有更有效的方法吗?也许我需要研究为每个范围划分一个表?

My current code for doing this works at 3 meals, however I end up with about 450,000 records in recipe_index, I need to do this same thing for 4,5 and 6 meals as well, so I'm calculating millions and millions of records at the end of this. Is there a more efficient way of doing this? Perhaps I need to look into partitioning a table for each range?

我当前的SQL代码:

INSERT INTO recipe_index
SELECT distinct '3' as nummeals, t1.id as id1, t2.id as id2, t3.id as id3, 0 as id4,   
t1.calories_ps+t2.calories_ps+t3.calories_ps as calories, t1.protein_ps+t2.protein_ps+t3.protein_ps as  
protein, t1.carbohydrate_ps+t2.carbohydrate_ps+t3.carbohydrate_ps as carbohydrate, 
t1.fat_ps+t2.fat_ps+t3.fat_ps as fat from recipes t1 inner join  recipes t2  on t1.Id < t2.Id inner join  recipes t3  on t2.Id < t3.Id WHERE t1.image <> '' AND t2.image <> '' AND t3.image <> ''

如果我错过任何明显的事情，请告诉我

If I missed anything obvious please let me know

推荐答案

您可以通过联接来实现.为了防止重复，您需要一种条件，其中配方ID顺序正确(这也可以防止一个配方出现3次):

You would do this with a join. In order to prevent duplicates, you want a condition where the recipe ids are in order (this also prevents one recipe from appearing three times):

select r1.id, r2.id, r3.id,
       (r1.calories + r2.calories + r3.calories) as calories,
       (r1.protein + r2.protein + r3.protein) as protein,
       (r1.carbs + r2.carbs + r3.carbs) as carbs,
       (r1.fat + r2.fat + r3.fat) as calories
from recipe_table r1 join
     recipe_table r2
     where r1.id < r2.id join
     recipe_table r3
     where r2.id < r3.id;

与查询唯一的不同是，distinct不是必需的，因为顺序可以防止重复.

The only difference from your query is that the distinct is not necessary, because the ordering prevents duplicates.

您面临的问题是有很多组合.因此，有4种配方的数百万种组合.我猜您是从77种左右的食谱开始的.其中4个的组合数量为77 * 76 * 75 * 74-对于5个和6个连击，此序列将快速增长.

The problem you are facing is that there are a lot of combinations. So there are millions of combinations of 4 recipes. I'm guessing you are starting with 77 or so recipes. The number of combinations of 4 of them is 77*76*75*74 -- and this sequence will grow quickly for 5 and 6 combos.

这篇关于在mySQL表中创建记录组合(顺序无所谓，不允许重复)的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！