Mysql – Grouping data based on conditions

database-designMySQL

How would I go about grouping data according to values given in a separate file/table?

For example. I have one column in the table I want to be grouped, called ID, and a separate CSV file with IDs to group the data by. I have not created the CSV file yet, so if You think there is a better way to store these values, feel free to tell. The CSV file could for example look like:

ID1,ID2,ID3;ID4,ID5;ID6,ID7,ID9; … ;

In this case I would want to group the data by ID1 + ID2 + ID3 and ID4 + ID5 and ID6+ID7+ID9 and so on. The point is, that the groups are consistent, but the data is frequently changed and the tables updated. We are talking of hundreds of groups, so it would be neat to organize the groups into a separate file.

Best Answer

SQL is designed to work with rows and columns, it does not do CSVs very well, especially ones that use multiple delimiter symbols with different meaning.

You should figure out how to present your CSV in the form:

ID,GroupID

where IDs that need to be grouped have the same GroupID. Once that CSV is imported, it will be very easy to group your rows using a join to the imported data:

SELECT
  d.GroupID,
  AGG(...),
  ...
FROM
  YourTable AS t
  INNER JOIN ImportedData AS d ON t.ID = d.ID
GROUP BY
  d.GroupID
;

Related Solutions

Mysql – How to create Combination of records (Order does not matter, no repetition allowed) in theSQL tables

Like Mike says, this is not something that you should be doing with the database. This is more of an algorithm question.

Your problem looks very similar to the knapsack problem, which is NP-Complete. Fortunately, you are not looking for the set that adds up to exactly some amount of calories, but a range, so it's not NP-Complete. Luckily for you, a few hundred recipes will easily fit in memory, so you can easily write an algorithm for it.

Here's a simple algorithm. I'm sure there's a better way to do it:

orderedRecipes = recipes sorted by calorie asc

def combinations(recipes, lowerBound, upperBound, recipeCount):
    first = recipes[0]
    if first.calorie < upperBound:
        for c in combinations(recipes[1:], lowerBound - first.calorie, upperBound - first.calorie, recipeCount - 1):
            if sum(r.calorie for r in [first] + c) > lowerBound:
                yield [first] + c
        for c in combinations(recipes[1:], lowerBound, upperBound, recipeCount):
            if sum(r.calorie for r in c) > lowerBound:
                yield c

combinationsOf3Between440and460 = list(combinations(orderedRecipes, 440, 460, 3))

The first loop takes the first item in the rest of the list, and the second loop does not. You don't want it to always take the first item, you want both take and no-take.

Database Design – How to Store List of Unknown Size

You should create another table with three column

Start City [PK]
End City [PK]
Midway City [PK]

you have triple primary key now if you have city_start and city_finish and for example 4 cities between city_start and city_finish then you should add 4 rows to you table like this

(city_start,city_finish,midway_city_one)
(city_start,city_finish,midway_city_two)
(city_start,city_finish,midway_city_three)
(city_start,city_finish,midway_city_four)

now you know that from city_start to city_finish you will visit midway_city_one

Best Answer

Related Solutions

Mysql – How to create Combination of records (Order does not matter, no repetition allowed) in theSQL tables

Database Design – How to Store List of Unknown Size

Related Question