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