Блог

Главная - Вопросы по программированию - Вычисление среднего балла в запросе

Вычисление среднего балла в запросе

#mysql #calculated-columns

#mysql #вычисляемые столбцы

Вопрос:

У меня возникли некоторые проблемы с подключением таблиц в этом, чтобы получить единый результат. Вот как вы рассчитываете средний балл:

  1. Получите среднюю оценку за каждый курс.
  2. Умножьте каждое среднее значение на количество кредитных часов в указанном курсе.
  3. Добавьте результаты из 2.
  4. Разделите результат от 3 на общее количество кредитных часов.

У меня есть три соответствующие таблицы:

 CREATE TABLE Student
(
SSN        INT(11),
SName    VARCHAR(25),
Major    VARCHAR(25),
PRIMARY KEY (SSN)
);

CREATE TABLE Course
(
CNO        INT(11),
CName    VARCHAR(3),
CreditHour    TINYINT(4),
PRIMARY KEY (CNO)
);

CREATE TABLE Grade
(
SSN        INT(11),
CNO        INT(11),
Grade        TINYINT(4)
);

К сожалению, SQL fiddle, похоже, не работает, поэтому я не могу создать fiddle для этого. Как бы мне выполнить оператор select, чтобы перечислить информацию об ученике в столбце вычисленный средний балл? Каждый раз, когда я пытаюсь это сделать, я получаю раздутый набор вложенных подзапросов, подобных этому:

 SELECT student.*, (total/hours) as gpa WHERE hours = 
   (SELECT SUM(course.creditHours) AS hours 
   FROM course, student, grade 
   WHERE course.cno = grade.cno 
   AND grade.ssn = student.ssn 
   GROUP BY course.cno) 
   AND total = (SELECT SUM(AVG(grade.grade)*course.creditHours) as total 
   FROM grade, course, student 
   WHERE grade.ssn = student.ssn 
   AND grade.cno = course.cno)

На самом деле, похоже, что это тоже не в правильном направлении. Что я должен делать, чтобы получить этот столбец?

Комментарии:

1. Пожалуйста, отредактируйте свой вопрос с примерами данных и желаемыми результатами. Я не знаю, что, в частности, означает «Добавить результаты из 2».

2. «Добавить результат из 2» означает «добавить результаты с предыдущего шага» — но мне тоже нравятся правильные DDLS

3. Просто чтобы уточнить, какое отношение имеет таблица student к этой проблеме?

Ответ №1:

Если вам нужен средний балл по ученикам, я бы рассчитал следующим образом:

1 Умножьте количество зачетных часов для каждого курса на оценку и сложите результат на одного студента

2 Разделите результат от 1 на общее количество зачетных часов на одного студента.

 SELECT
    s.SSN,
    SUM(c.CreditHour * g.Grade) / SUM(c.CreditHour) Average_GPA
FROM Student s
INNER JOIN Grade g on s.SSN = g.SSN
INNER JOIN Course c on g.CNO = c.CNO
GROUP BY s.SSN;

Если вам просто нужен средневзвешенный средний балл для каждого курса, то можно использовать следующий запрос, используя шаги из вашего вопроса:

1. Получите среднюю оценку за каждый курс.

2. Умножьте каждое среднее значение на количество кредитных часов в указанном курсе.

3.Добавьте результаты из 2.

4. Разделите результат от 3 на общее количество кредитных часов.

 SELECT
    c.CNO,
    SUM(c.CreditHour * average_gpa.GPA) / SUM(c.CreditHour) Weighted_Average_GPA
FROM Course c
INNER JOIN
(
SELECT
    CNO,
    AVG(g.Grade) GPA
FROM Grade
GROUP BY CNO
) average_gpa
ON c.CNO = average_gpa.CNO
GROUP BY c.CNO;