连接两个表的平均数

Question

寻找其他方法来编写一个查询，为每个学生找到每个科目的平均平均值。

我的查询运行良好，我希望从其他方法中学习。

例如:me(又名Error404)的成绩如下：

• 代数:第一次考试: 99，第二次考试: 97，第三次考试: 96 ->平均: 97.3333
• 机器学习:第一次考试: 95，第二次考试: 94，第三次考试: 100 ->平均: 96.3333
• 资料科学:第一次考试: 91次，第二次考试: 91.5，第三次考试: 93 ->平均: 91.8333

我想要AVG(97.3333,96.3333,91.8333)=95.1666

学生桌：

  pk-INT           VARCHAR             VARCHAR
+-----------+------------------+-----------------+
| StudentID | StudentFirstName | StudentLastName |
+-----------+------------------+-----------------+
|         1 | agam             | rafaeli         |
|         2 | amir             | aizinger        |
|         3 | avi              | caspi           |
|         4 | avia             | wolf            |
+-----------+------------------+-----------------+

测试表：

  PK-VARCHR    PK-VARCHR     PK&FK-INT   INT
+------------+------------+-----------+-------+
| TestDate   | ClassName  | StudentID | Grade |
+------------+------------+-----------+-------+
| 2017-07-01 | Algebra    |         1 |    88 |
| 2017-08-02 | Algo       |         1 |    97 |
| 2017-09-01 | Algebra    |         1 |    80 |
| 2017-09-01 | Algebra    |         1 |    97 |
| 2017-09-01 | Set-theory |         1 |    85 |
| 2017-09-04 | Calcules   |         1 |    86 |
| 2016-05-03 | Set-theory |         2 |    84 |
| 2016-07-02 | Calcules   |         2 |    89 |
| 2016-07-04 | Algo       |         2 |    83 |
| 2016-07-05 | Algebra    |         2 |    79 |
| 2016-06-03 | Algebra    |         3 |    99 |
| 2016-07-02 | Algo       |         3 |    97 |
| 2016-07-03 | Calcules   |         3 |    96 |
| 2016-09-03 | Set-theory |         3 |    95 |
| 2016-06-03 | Algebra    |         4 |    78 |
+------------+------------+-----------+-------+

输出：

+------------------+-----------------+--------------+
| StudentFirstName | StudentLastName | AVG(average) |
+------------------+-----------------+--------------+
| agam             | rafaeli         |  87.20832500 |
| amir             | aizinger        |  83.75000000 |
| avi              | caspi           |  96.75000000 |
| avia             | wolf            |  79.75000000 |
| ben              | moskovich       |  80.50000000 |
| chen             | segalovich      |  90.50000000 |
| dana             | levy            |  72.00000000 |
| daniel           | marcus          |  88.00000000 |
| daphna           | chwarts         |  90.25000000 |
| david            | cohen           |  87.75000000 |
+------------------+-----------------+--------------+

My查询：

SELECT  StudentFirstName, StudentLastName, AVG(average)
FROM
(
SELECT   StudentFirstName, StudentLastName, AVG(Grade) AS average
FROM tests 
INNER JOIN students ON tests.StudentID=students.StudentID
GROUP BY StudentFirstName, StudentLastName,ClassName) AS t
GROUP BY StudentFirstName, StudentLastName;

样本数据

DROP DATABASE IF EXISTS error404;
CREATE DATABASE error404;
USE error404
CREATE TABLE students
(
    StudentID        INT NOT NULL AUTO_INCREMENT,
    StudentFirstName VARCHAR(25),
    StudentLastName  VARCHAR(25),
    PRIMARY KEY (StudentID)
);
INSERT INTO students (StudentFirstName,StudentLastName) VALUES
('agam'  , 'rafaeli'), ('amir'  , 'aizinger'), ('avi'   , 'caspi'),
('avia'  , 'wolf ');
CREATE TABLE tests
(
    testid    INT NOT NULL AUTO_INCREMENT,
    TestDate  DATE,
    ClassName VARCHAR(25),
    StudentID INT NOT NULL,
    Grade     INT NOT NULL,
    PRIMARY KEY (testid),
    KEY (StudentID)
);
INSERT INTO tests (TestDate,ClassName,StudentID,Grade) VALUES
('2017-07-01', 'Algebra'    , 1 , 88 ), ('2017-08-02', 'Algo'       , 1 , 97 ),
('2017-09-01', 'Algebra'    , 1 , 80 ), ('2017-09-01', 'Algebra'    , 1 , 97 ),
('2017-09-01', 'Set-theory' , 1 , 85 ), ('2017-09-04', 'Calcules'   , 1 , 86 ),
('2016-05-03', 'Set-theory' , 2 , 84 ), ('2016-07-02', 'Calcules'   , 2 , 89 ),
('2016-07-04', 'Algo'       , 2 , 83 ), ('2016-07-05', 'Algebra'    , 2 , 79 ),
('2016-06-03', 'Algebra'    , 3 , 99 ), ('2016-07-02', 'Algo'       , 3 , 97 ),
('2016-07-03', 'Calcules'   , 3 , 96 ), ('2016-09-03', 'Set-theory' , 3 , 95 ),
('2016-06-03', 'Algebra'    , 4 , 78 );

Answer 1

通过使用OVER子句和一个临时表，可以使它更加可读性。

SELECT DISTINCT StudentID,ClassName,AVG(grade) OVER (PARTITION BY classname,StudentID) AS AVGforClass
INTO #Average_for_each_class_by_student
FROM tests

SELECT StudentID,AVG(AVGforClass) AS AVGoverall 
INTO #Average_for_each_student_over_all_classes
FROM #Average_for_each_class 
GROUP BY StudentID

这将给你一个表格，每个班级的平均每一个学生和一个表，每个学生在所有的班级。然后，您可以将该表加入学生表，以获取每个学生的姓名。

SELECT s.StudentFirstName,s.StudentLastName,a.AVGoverall 
FROM #Average_for_each_student_over_all_classes a INNER JOIN students s ON a.StudentID = s.StudentID

这样，如果您必须回到这个SQL脚本，就可以轻松地读取和访问脚本的每个部分。(例如，你的客户或老师改变了他们的想法，想要一个学生的平均水平为一个特定的班级)

()子句-何时以及为什么有用？