题解 | #牛客直播各科目出勤率#

牛客直播各科目出勤率

https://www.nowcoder.com/practice/0cab547df4f0430b93042128f445d899

题目：

请你统计每个科目的出勤率，结果保留两位小数；输出结果按course_id升序排序
指标说明：出勤率 = 在线时长10分钟及以上的人数 / 报名人数

个人笔记：

个人认为，这道题目的查询语句是否会冗杂/过长，取决于是否意识到可以对user_id进行去重：可知一个用户即使对同一门课注册了多次，或是在同一门课中在线多次、甚至在线时长超10分钟多次，这些记录都只识别为一行记录，因为一个用户是否注册、是否在线时长超10分钟，答案只需要“是”或者“不是”

因为一开始忽略了对user_id的去重，第一次写的查询语句会变得很长，并且还需要注意在多次GROUP BY中使用聚合函数，比较影响易读性：

SELECT t2.course_id, t2.course_name,
      ROUND((t2.attend_cnt / t3.sign_cnt) * 100, 2)
FROM

  (SELECT course_id, course_name, COUNT(course_id) AS attend_cnt
  FROM
      (SELECT user_id, attend_tb.course_id, course_name,
              MAX(TIMESTAMPDIFF(SECOND, in_datetime, out_datetime)/60) AS max_time
      FROM attend_tb LEFT OUTER JOIN course_tb
      ON attend_tb.course_id = course_tb.course_id
      GROUP BY user_id, attend_tb.course_id, course_name
      HAVING max_time >= 10) AS t1
  GROUP BY course_id, course_name) AS t2
INNER JOIN
  (SELECT course_id, COUNT(course_id) AS sign_cnt
  FROM behavior_tb
  WHERE if_sign = 1
  GROUP BY course_id) AS t3
ON t2.course_id = t3.course_id
ORDER BY t2.course_id ASC;

解题方法：参考他人的答案之后，有两个解法会更加简练，第一个方法是分块查询，第二个方法则是一步到位，只查询一次

方法1：

步骤 1：先查询每个课程的注册人数

SELECT course_id, 
       /* DISTINCT user_id为了避免一个用户会注册多次同一个课程，
          但也可以不加，本次题目没有这种特殊情况 */
       COUNT(DISTINCT user_id) AS sign_cnt 
FROM behavior_tb
WHERE if_sign = 1
GROUP BY course_id

步骤 2：查找每一门课出勤时间超过10分钟的人数

SELECT a.course_id, 
       c.course_name, 
       /* DISTINCT user_id，无论user_id有多少次出勤超过10分钟，都只记录为1人 */
       COUNT(DISTINCT user_id) AS attend_cnt
FROM attend_tb AS a LEFT OUTER JOIN course_tb AS c ON a.course_id = c.course_id
/* 这里TIMESTAMPDIFF的第一个参数可换成SECOND，但记得要除以60转换为分钟，
   但由于题目实例都没有秒单位，因此可直接使用MINUTE */
WHERE TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime) >= 10
GROUP BY a.course_id, c.course_name

步骤 3：联结两个子查询表，即可对应上每一门课的id、名称以及出勤率

SELECT attends.course_id, attends.course_name, 
        ROUND((attend_cnt / sign_cnt) * 100, 2) AS "attend_rate(%)"
FROM
    (SELECT course_id, COUNT(DISTINCT user_id) AS sign_cnt
    FROM behavior_tb
    WHERE if_sign = 1
    GROUP BY course_id) AS signs
INNER JOIN
    (SELECT a.course_id, c.course_name, COUNT(DISTINCT user_id) AS attend_cnt
    FROM attend_tb AS a LEFT OUTER JOIN course_tb AS c ON a.course_id = c.course_id
    WHERE TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime) >= 10
    GROUP BY a.course_id, c.course_name) AS attends
ON signs.course_id = attends.course_id
ORDER BY attends.course_id ASC;

方法 2：一次性联结三个表，结合CASE WHEN来计算每一门课的注册人数、出勤人数

一步步拆解，再组合为一个完整的查询语句
首先，是联结顺序和方式：
1）以behavior_tb为左主表，联结attend_tb，两者的联结条件必须为user_id、course_id相等，以找出每一个用户在每一个课程对应的在线记录，否则，单使用course_id作为联结条件，会出现一个user在一门课程中会匹配上多余的、来自注册了同一门课的其他users的在线记录
2）第二个联结，则是左主表behavior_tb联结course_tb，联结条件为course_id，这个就很好理解，不多赘述
```
SELECT * /* 先空着 */
FROM
behavior_tb AS b LEFT OUTER JOIN attend_tb AS a ON a.course_id = b.course_id AND a.user_id = b.user_id
                 LEFT OUTER JOIN course_tb AS c ON b.course_id = c.course_id
```

查询出出勤时间超过10分钟的人，则可以使用CASE WHEN来创造新的字段，若user出勤时间超过10分钟则将其user_id返回，否则返回NULL值；随后，再对user_id进行去重计数，则可得到每一门课对应的出勤人数

SELECT b.course_id, c.course_name,
       /* 这里使用了SECOND为TIMESTAMPDIFF的参数，可以换成MINUTE
          DISTINCT user_id去重，对其再COUNT即可得出人数 */
       COUNT(DISTINCT CASE WHEN (TIMESTAMPDIFF(SECOND, in_datetime, out_datetime) / 60) >= 10 THEN a.user_id ELSE NULL END)
FROM behavior_tb AS b LEFT OUTER JOIN attend_tb AS a ON a.course_id = b.course_id AND a.user_id = b.user_id
                      LEFT OUTER JOIN course_tb AS c ON b.course_id = c.course_id
GROUP BY b.course_id, c.course_name

同样的道理，使用CASE WHEN来创造新字段，若if_sign = 1则返还user_id，否则返还NULL值，去重并计数，则可得到每一门课程的注册人数

SELECT b.course_id, c.course_name,
       COUNT(DISTINCT CASE WHEN if_sign = 1 THEN b.user_id ELSE NULL END) * 100
FROM behavior_tb AS b LEFT OUTER JOIN attend_tb AS a ON a.course_id = b.course_id AND a.user_id = b.user_id
                      LEFT OUTER JOIN course_tb AS c ON b.course_id = c.course_id
GROUP BY b.course_id, c.course_name

最后，三者结合即可

SELECT b.course_id, c.course_name,
     ROUND(
         COUNT(DISTINCT CASE WHEN (TIMESTAMPDIFF(SECOND, in_datetime, out_datetime) / 60) >= 10 THEN a.user_id ELSE NULL END)
       / COUNT(DISTINCT CASE WHEN if_sign = 1 THEN b.user_id ELSE NULL END) * 100
          , 2) AS "attend_rate(%)"
FROM behavior_tb AS b LEFT OUTER JOIN attend_tb AS a ON a.course_id = b.course_id AND a.user_id = b.user_id
                    LEFT OUTER JOIN course_tb AS c ON b.course_id = c.course_id
GROUP BY b.course_id, c.course_name
ORDER BY "attend_rate(%)" ASC;