[分享]SQL23 统计每个学校各难度的用户平均刷题数

2025-05-27 15:24:32

学生产生报错的写法

select university,difficult_level,round(count(qpd.question_id)/count(distinct qpd.device_id),4) as avg_answer_cntfrom question_practice_detail as qpd,user_profile as up,question_detail as qdwhere qd.question_id=qpd.question_id and up.device_id=qpd.question_idgroup by university,difficult_level;

以下为报错原因

一方面qpd.question_idgroup，这里的id和group连起来了，另一方面，不能这样去关联，应该是up.device_id = qpd.device_id

round(...) as avg_answer_cntfrom -- 错误写法

-- 正确应为 ↓

round(...) as avg_answer_cnt FROM -- 字段别名与 FROM 之间需换行或空格

隐式 JOIN 导致笛卡尔积

使用逗号 , 隐式连接多表时，若未在 WHERE 子句中明确 所有表间的关联关系，会导致产生笛卡尔积。

AND up.device_id = qpd.device_id -- 仅明确了两两关联，但未限制 user_profile 与 question_detail 间关系

这会导致 user_profile 与 question_detail 无关联条件下产生交叉连接，虽然业务逻辑中这两个表确实不需要直接关联，但在隐式 JOIN 语法中，所有表会先做笛卡尔积再用 WHERE 过滤，可能意外放大数据量。

SELECT
university,
difficult_level,
ROUND(COUNT(qpd.question_id) / COUNT(DISTINCT qpd.device_id), 4) AS avg_answer_cnt
FROM
question_practice_detail AS qpd
JOIN user_profile AS up ON qpd.device_id = up.device_id -- 注意 device_id 拼写
JOIN question_detail AS qd ON qpd.question_id = qd.question_id
GROUP BY
university,
difficult_level;

建议显式使用 JOIN 语法（避免笛卡尔积），可以看下官方给出的题解，也是用的JOIN