题解 | 统计每个学校各难度的用户平均刷题数
统计每个学校各难度的用户平均刷题数
https://www.nowcoder.com/practice/5400df085a034f88b2e17941ab338ee8
select u1.university,
q3.difficult_level,
round(count(q3.id)/count(DISTINCT u1.device_id),4) as avg_answer_cnt
from question_practice_detail q2
left join user_profile u1 on q2.device_id=u1.device_id
left join question_detail q3 on q2.question_id=q3.question_id
group by u1.university ,q3.difficult_level
order by u1.university ,q3.difficult_level;
COUNT(qpd.id)：统计这一组里总共有多少条答题记录（即总共答了多少题）。
COUNT(DISTINCT u.device_id)：统计这一组里有多少个不同的用户（因为一个用户可能答多题，要去重）。
两者相除：平均每个用户答了多少题（按大学+难度分组）。
ROUND(..., 4)：保留 4 位小数，让结果更整洁。
AS avg_answer_cnt：给这个计算结果起个名字，叫“平均答题次数”。
✅ 举个例子：
如果“清华大学”的学生一共答了 1000 道“困难题”，而这些题是由 200 个不同学生答的，
那么 avg_answer_cnt = 1000 / 200 = 5.0000 —— 平均每人答了 5 道困难题。
第六步：ORDER BY u.university, qd.difficult_level
作用：把结果按大学字母顺序排，同一所大学内再按难度排序（比如 easy → medium → hard）。
通俗理解：让结果看起来整齐，方便阅读。
✅ 总结一句话：
这条 SQL 的目的是：统计每所大学的学生，在不同难度题目下的平均答题数量（人均答了几道题）。
🧠 小贴士
COUNT(qpd.id) 可以换成 COUNT(*)，效果一样（只要 qpd.id 不为 NULL）。
如果某大学没人答某种难度的题，这组就不会出现在结果里（因为没数据可 JOIN）。
使用 COUNT(DISTINCT device_id) 是为了防止重复计算同一个用户。
希望这个解释清晰易懂！如果你有表结构或想改需求，也可以继续问我 😊
SELECT
u.university,
qd.difficult_level,
ROUND(COUNT(qpd.id) / COUNT(DISTINCT u.device_id), 4) AS avg_answer_cnt
FROM
user_profile u
JOIN
question_practice_detail qpd ON u.device_id = qpd.device_id
JOIN
question_detail qd ON qpd.question_id = qd.question_id
GROUP BY
u.university, qd.difficult_level
ORDER BY
u.university, qd.difficult_level;
结合图中表格，解释一下这个SQL语句的，每一行的用法，每执一个SQL语句并，展示图的变化，并标记执行顺序，用通俗易懂的话术来解答
Qwen3-Max
太好了！你提供了三张表的数据，我们结合具体数据来一步一步执行这个 SQL 语句，并用“图示变化”和通俗语言解释每一步发生了什么。
🧩 先回顾一下三张表
✅ 表1：user_profile（用户信息）
表格
id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 25 复旦大学 4.0 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 male 28 复旦大学 3.6 9 6 52
每个用户有一个 device_id，代表他的设备号。
✅ 表2：question_practice_detail（答题记录）
表格
id device_id question_id result
1 2138 111 wrong
2 3214 112 wrong
3 3214 113 wrong
4 6543 111 right
5 2315 115 right
6 2315 116 right
7 2315 117 wrong
8 5432 117 wrong
9 5432 112 wrong
10 2131 113 right
11 5432 113 wrong
12 2315 115 right
13 2315 116 right
14 2315 117 wrong
15 5432 117 wrong
16 5432 112 wrong
17 2131 113 right
18 5432 113 wrong
19 2315 117 wrong
20 5432 117 wrong
21 5432 112 wrong
22 2131 113 right
23 5432 113 wrong
记录了每个用户（通过 device_id）做了哪些题（question_id），以及对错情况。
✅ 表3：question_detail（题目详情）
表格
id question_id difficult_level
1 111 hard
2 112 medium
3 113 easy
4 115 easy
5 116 medium
6 117 easy
告诉我们每道题的难度。
🔁 执行顺序（逻辑顺序）
FROM + JOIN → 连接三张表
GROUP BY → 按大学+难度分组
SELECT → 计算平均答题数
ORDER BY → 排序输出
🚀 一步步执行并展示“图的变化”
📌 第一步：FROM user_profile u
只有用户信息表，暂时没变。
✅ 当前数据：7 条用户记录
表格
id device_id university ...
1 2138 北京大学 ...
2 3214 复旦大学 ...
... ... ... ...
📌 第二步：JOIN question_practice_detail qpd ON u.device_id = qpd.device_id
把用户的答题记录关联进来，匹配 device_id
📌 举个例子：
用户 device_id=2138 在 qpd 中有 1 条记录（id=1，题111）
用户 device_id=3214 有 2 条记录（id=2,3）
用户 device_id=6543 有 1 条记录（id=4）
用户 device_id=2315 有 6 条记录（id=5～14）
用户 device_id=5432 有 8 条记录（id=8～23）
用户 device_id=2131 有 3 条记录（id=10,17,22）
用户 device_id=4321 ❌ 没有答题记录 → 被过滤掉！
✅ 所以最终连接后得到：
表格
u.id u.device_id u.university qpd.id qpd.question_id result
1 2138 北京大学 1 111 wrong
2 3214 复旦大学 2 112 wrong
2 3214 复旦大学 3 113 wrong
3 6543 北京大学 4 111 right
4 2315 浙江大学 5 115 right
4 2315 浙江大学 6 116 right
4 2315 浙江大学 7 117 wrong
4 2315 浙江大学 12 115 right
4 2315 浙江大学 13 116 right
4 2315 浙江大学 14 117 wrong
4 2315 浙江大学 19 117 wrong
5 5432 山东大学 8 117 wrong
5 5432 山东大学 9 112 wrong
5 5432 山东大学 11 113 wrong
5 5432 山东大学 15 117 wrong
5 5432 山东大学 16 112 wrong
5 5432 山东大学 20 117 wrong
5 5432 山东大学 21 112 wrong
5 5432 山东大学 18 113 wrong
5 5432 山东大学 23 113 wrong
6 2131 山东大学 10 113 right
6 2131 山东大学 17 113 right
6 2131 山东大学 22 113 right
✅ 总共：1 + 2 + 1 + 6 + 8 + 3 = 21 条记录
❌ device_id=4321（复旦大学）没有答题记录 → 被丢弃
📌 第三步：JOIN question_detail qd ON qpd.question_id = qd.question_id
再把每道题的难度加进来
比如：
题111 → hard
题112 → medium
题113 → easy
题115 → easy
题116 → medium
题117 → easy
现在每一行都有：
用户大学
答题记录
题目难度
✅ 最终连接后的数据（只列出关键字段）：
表格
u.university qpd.question_id qd.difficult_level qpd.id
北京大学 111 hard 1
复旦大学 112 medium 2
复旦大学 113 easy 3
北京大学 111 hard 4
浙江大学 115 easy 5
浙江大学 116 medium 6
浙江大学 117 easy 7
浙江大学 115 easy 12
浙江大学 116 medium 13
浙江大学 117 easy 14
浙江大学 117 easy 19
山东大学 117 easy 8
山东大学 112 medium 9
山东大学 113 easy 11
山东大学 117 easy 15
山东大学 112 medium 16
山东大学 117 easy 20
山东大学 112 medium 21
山东大学 113 easy 18
山东大学 113 easy 23
山东大学 113 easy 10
山东大学 113 easy 17
山东大学 113 easy 22
✅ 总共还是 21 条记录，但每条都带了难度
📌 第四步：GROUP BY u.university, qd.difficult_level
按 大学 + 难度 分组
我们来看每一组有哪些数据：
🟦 组1：北京大学 + hard
有 2 条记录（题111，来自两个用户）
用户：2138（男）、6543（女）
🟦 组2：北京大学 + easy
没有记录 → 不出现
🟦 组3：复旦大学 + medium
有 1 条记录（题112）
用户：3214
🟦 组4：复旦大学 + easy
有 1 条记录（题113）
用户：3214
注意：虽然用户3214答了两道题，但分别属于不同难度，所以分成两组
🟦 组5：浙江大学 + easy
有 5 条记录（题115、117，多次）
用户：2315（只一个用户）
🟦 组6：浙江大学 + medium
有 2 条记录（题116）
用户：2315
🟦 组7：山东大学 + easy
有 10 条记录（题113、117）
用户：5432（7次）、2131（3次）→ 2 个不同用户
🟦 组8：山东大学 + medium
有 4 条记录（题112）
用户：5432（4次）
📌 第五步：SELECT ...
计算每组的：
sql
编辑
ROUND(COUNT(qpd.id) / COUNT(DISTINCT u.device_id), 4)
✅ 逐组计算：
✅ 北京大学 + hard
COUNT(qpd.id) = 2（两条记录）
COUNT(DISTINCT u.device_id) = 2（两个用户：2138 和 6543）
平均 = 2 / 2 = 1.0000
✅ 复旦大学 + medium
COUNT(qpd.id) = 1
COUNT(DISTINCT u.device_id) = 1（只有3214）
平均 = 1 / 1 = 1.0000
✅ 复旦大学 + easy
COUNT(qpd.id) = 1
COUNT(DISTINCT u.device_id) = 1
平均 = 1 / 1 = 1.0000
✅ 浙江大学 + easy
COUNT(qpd.id) = 5
COUNT(DISTINCT u.device_id) = 1（只有2315）
平均 = 5 / 1 = 5.0000
✅ 浙江大学 + medium
COUNT(qpd.id) = 2
COUNT(DISTINCT u.device_id) = 1
平均 = 2 / 1 = 2.0000
✅ 山东大学 + easy
COUNT(qpd.id) = 10
COUNT(DISTINCT u.device_id) = 2（5432 和 2131）
平均 = 10 / 2 = 5.0000
✅ 山东大学 + medium
COUNT(qpd.id) = 4
COUNT(DISTINCT u.device_id) = 1（只有5432）
平均 = 4 / 1 = 4.0000
📌 第六步：ORDER BY u.university, qd.difficult_level
按大学排序，同一大学内按难度排序（easy → medium → hard）
✅ 最终结果
表格
university difficult_level avg_answer_cnt
北京大学 hard 1.0000
复旦大学 easy 1.0000
复旦大学 medium 1.0000
浙江大学 easy 5.0000
浙江大学 medium 2.0000
山东大学 easy 5.0000
山东大学 medium 4.0000
🎯 总结：这条 SQL 是在做什么？
统计每所大学的学生，在不同难度题目上的“人均答题次数”
为什么是“人均”？因为用了 COUNT(DISTINCT u.device_id) 除以总答题数。
为什么有些大学没出现？比如“复旦大学”只出现在 easy 和 medium，因为没人答 hard 题。
为什么“浙江大学”的 easy 题人均答了 5 次？因为只有一个用户（2315）答了 5 次 easy 题。
💡 小贴士
如果你想看“每个用户平均答了多少题”，可以用 user_profile 的 answer_cnt 字段，但这里是看“不同难度下的答题行为”，所以要用明细表。
device_id 是关键连接字段，相当于“身份证号”。
difficult_level 是从 question_detail 表来的，不能直接查。
希望这个图文结合、一步步演示的方式让你彻底理解了这条 SQL 的执行过程！如果想看某个大学的详细数据，也可以继续问我 😊
，让我扣2办理