- 解题思路
“统计每个科目的转换率(sign_rate(%),转化率=报名人数/浏览人数,结果保留两位小数)。 注:按照course_id升序排序。” 题目的重点就在这句话,下面来拆解这句话这里的考察知识点。
-
“每个科目”,表示会用到GROUP BY 函数来分组,分组的字段是course_name。 即GROUP BY course_name;
-
“转换率(sign_rate(%),转化率=报名人数/浏览人数)”,这里包含了三个变量。
-
第一个:报名人数,这里定义变量为sign,那么怎么获取这个sign值?根据题目里给出的信息 “if_sign表示是否报名” 和behavior_tb表格里的数据, 可知当if_sign = 1的时候,这个人就是成功报名了,sign就+1。只需要先用CASE WHEN函数来判断这个条件,再用SUM函数对符合这个条件的人数进行统计即可。 转换为SQL语言就是:SUM(CASE WHEN if_sign = 1 THEN 1 ELSE 0 END)sign;
-
第二个:浏览人数,同理可用上面的思路求出浏览人数的总数,这里定义变量为vw,即SUM(CASE WHEN if_vw = 1 THEN 1 ELSE 0 END)vw;
-
第三个:转换率,根据已知公式,直接把上面两个变量相除即可,即 sign/vw。
-
-
然后 “结果保留两位小数”,说明要用ROUND函数 进行小数位数保留。不过这里转换率是百分数,所以要用100去乘以这个除后的值。 即 ROUND(100*(sign/vw), 2) AS
sign_rate(%)
。 -
最后这句 “注:按照course_id升序排序。”,表示会用ORDER BY函数来进行升序排序。
这道题思路基本就拆解的七七八八了,我们只需要合并上面的步骤。
- 解题步骤
- 首先可以先建立一张表c来获取每个科目的报名人数sign和浏览人数vw,具体代码如下:
SELECT course_name,
SUM(CASE WHEN if_sign = 1 THEN 1 ELSE 0 END)sign,
SUM(CASE WHEN if_vw = 1 THEN 1 ELSE 0 END)vw
FROM course_tb ct, behavior_tb bt
WHERE ct.course_id = bt.course_id
GROUP BY course_name;
会生成这样一张表格c:
- 然后用课程表course_tb和刚刚新表连接,连接点是course_name,接着就可以获取转换率sign_rate(%) 和最后结果的排序:
- 参考代码
SELECT course_id, c.course_name, ROUND(100*(sign/vw), 2) AS `sign_rate(%)`
FROM course_tb ct,
(SELECT course_name,
SUM(CASE WHEN if_sign = 1 THEN 1 ELSE 0 END)sign,
SUM(CASE WHEN if_vw = 1 THEN 1 ELSE 0 END)vw
FROM course_tb ct, behavior_tb bt WHERE ct.course_id = bt.course_id GROUP BY course_name)c
WHERE ct.course_name = c.course_name
ORDER BY course_id;
没想到通过率还挺高的🤣