0

So I'm doing this in MySQL.

I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their columns.


students

student_id | name | email | GPA


classes

class_id | name | room


student_class_rel

student_id | class_id


I want to get a student_id of the 5 students for each class with the highest GPAs (along with the class info). How would I do this?

For example, this query gets 100 classes and then gets every student_id for all students in that class

SELECT *, 
    JSON_ARRAYAGG(student_id) 
FROM classes 
    INNER JOIN student_class_rel 
    USING(class_id) 
GROUP BY class_id; 
LIMIT 100

I want that query, expect the JSON_ARRAYAGG(student_id) only holds the ID to the top 5 students in terms of GPA for each class rather than every student.

Also, assume there could be upwards of millions of students and hundreds of thousands of classes and that there are around 50 to 1000 students per class. So performance is key for this query.

And I'm aware of hacky type solutions (like make multiple queries, once for each class) but so far those have been WAY too slow. I've spent too much time trying to figure this out, any help is greatly appreciated! (Also let me know if there's any additional info needed).

  • 1
    What MYSQL version? this might be the start of a solution but also see the greatest-n-per-group tag that was added. For performance SHOW CREATE TABLE student_class_rel for each table as the indexes and type contribute significantly to query performance. – danblack Aug 12 at 4:16
  • 1
    On 8+ verstion use ROW_NUMBER in CTE. For 5+ version use enumeration via user-defined variables in subquery. Then filter by this number. – Akina Aug 12 at 4:35
1
  • Student with GPAs is an aggregation
  • Class information is not an aggregation

Solution:

  • Use two queries
  • Query the GPAs (CTE and Window Function)
  • Query the class information

You will probably need two indices on student_id and class_id to boost the performance.

Example queries in MySQL 8.0.20:

WITH ordered_gpa AS(
    SELECT students.id,
        students.gpa, 
        student_class_rel.class_id,
        rank() over (partition by student_class_rel.class_id order by students.gpa DESC) gpa_rank
    FROM student_class_rel
    INNER JOIN students 
    ON student_class_rel.stu_id = students.id
)
SELECT * 
FROM ordered_gpa
WHERE gpa_rank <= 5;

Here, we use Windows rather than GROUP BY, which is the PARTITION BY to split the rows.

SELECT class_id, name, room
FROM classes
WHERE class_id IN (
  SELECT DISTINCT class_id
  FROM student_class_rel
) tmp;

You could combine this query with the first one.

| improve this answer | |
  • 1
    I want to get a student_id of the 5 students for each class with the highest GPAs may mean "5 or more if their GPAs are equal" or "strictly 5". I'd recommend either to ask OP about the clarification or to explain to OP what must differ in the query for this two cases. – Akina Aug 12 at 6:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.