第一张是问题练习表，表示哪个设备答了哪道题，以及答得对不对
mysql> select * from question_practice_detail;
+-----------+-------------+--------+
| device_id | question_id | result |
+-----------+-------------+--------+
| 2138 | 111 | wrong |
| 3214 | 112 | wrong |
| 3214 | 113 | wrong |
| 6543 | 111 | right |
| 2315 | 115 | right |
| 2315 | 116 | right |
| 2315 | 117 | wrong |
| 5432 | 118 | wrong |
| 5432 | 112 | wrong |
| 2131 | 114 | right |
| 5432 | 113 | wrong |
+-----------+-------------+--------+第二章是用户表，表示每个设备的用户信息
mysql> select * from user_profile;
+-----------+--------+------+------------+------+-----------------------+
| device_id | gender | age | university | gpa | active_days_within_30 |
+-----------+--------+------+------------+------+-----------------------+
| 2138 | male | 21 | Beijing | 3.4 | 7 |
| 3214 | male | NULL | Fudan | 4 | 15 |
| 6543 | female | 20 | Beijing | 3.2 | 12 |
| 2315 | female | 23 | Zhejiang | 3.6 | 5 |
| 5432 | male | 25 | Shandong | 3.8 | 20 |
| 2131 | male | 28 | Shandong | 3.3 | 15 |
| 4321 | male | 28 | Fudan | 3.6 | 9 |
+-----------+--------+------+------------+------+-----------------------+根据device_id建立连接
mysql> SELECT *
-> FROM question_practice_detail AS Q
-> INNER JOIN user_profile AS U
-> ON Q.device_id = U.device_id;
+-----------+-------------+--------+-----------+--------+------+------------+------+-----------------------+
| device_id | question_id | result | device_id | gender | age | university | gpa | active_days_within_30 |
+-----------+-------------+--------+-----------+--------+------+------------+------+-----------------------+
| 2138 | 111 | wrong | 2138 | male | 21 | Beijing | 3.4 | 7 |
| 3214 | 112 | wrong | 3214 | male | NULL | Fudan | 4 | 15 |
| 3214 | 113 | wrong | 3214 | male | NULL | Fudan | 4 | 15 |
| 6543 | 111 | right | 6543 | female | 20 | Beijing | 3.2 | 12 |
| 2315 | 115 | right | 2315 | female | 23 | Zhejiang | 3.6 | 5 |
| 2315 | 116 | right | 2315 | female | 23 | Zhejiang | 3.6 | 5 |
| 2315 | 117 | wrong | 2315 | female | 23 | Zhejiang | 3.6 | 5 |
| 5432 | 118 | wrong | 5432 | male | 25 | Shandong | 3.8 | 20 |
| 5432 | 112 | wrong | 5432 | male | 25 | Shandong | 3.8 | 20 |
| 2131 | 114 | right | 2131 | male | 28 | Shandong | 3.3 | 15 |
| 5432 | 113 | wrong | 5432 | male | 25 | Shandong | 3.8 | 20 |
+-----------+-------------+--------+-----------+--------+------+------------+------+-----------------------+然后按university进行分组，并统计不同的device_id，以及回答了多少道题
mysql> SELECT COUNT(question_id), COUNT(DISTINCT Q.device_id), university
-> FROM question_practice_detail AS Q
-> INNER JOIN user_profile AS U
-> ON Q.device_id = U.device_id
-> GROUP BY university;
+--------------------+-----------------------------+------------+
| COUNT(question_id) | COUNT(DISTINCT Q.device_id) | university |
+--------------------+-----------------------------+------------+
| 2 | 2 | Beijing |
| 2 | 1 | Fudan |
| 4 | 2 | Shandong |
| 3 | 1 | Zhejiang |
+--------------------+-----------------------------+------------+可以看到Zhejiang回答了3道题（115、116、117），来自同一个设备（2315）
然后就可以计算平均值
mysql> SELECT university,
-> COUNT(question_id) / COUNT(DISTINCT Q.device_id) AS avg_answer_cnt
-> FROM question_practice_detail AS Q
-> INNER JOIN user_profile AS U
-> ON Q.device_id = U.device_id
-> GROUP BY university;
+------------+----------------+
| university | avg_answer_cnt |
+------------+----------------+
| Beijing | 1.0000 |
| Fudan | 2.0000 |
| Shandong | 2.0000 |
| Zhejiang | 3.0000 |
+------------+----------------+
