1. 解题思路

“统计每个科目的转换率(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函数来进行升序排序。

这道题思路基本就拆解的七七八八了,我们只需要合并上面的步骤。

  1. 解题步骤
  • 首先可以先建立一张表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: alt

  • 然后用课程表course_tb和刚刚新表连接,连接点是course_name,接着就可以获取转换率sign_rate(%) 和最后结果的排序:

alt

  1. 参考代码
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;

没想到通过率还挺高的🤣 alt