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

# 求每个科目的出勤率
# 出勤率=出勤（在线时长10分钟及以上）人数 / 报名人数
# 报名人数：SUM(if_sign)
# 在线时长10分钟及以上 的人数：in_datetime	out_datetime 差值在十分钟以上

# 联结三张表，分组求报名人数，求出勤人数 # 不行
# 还是先联结前两张表，求每科报名人数
# 再根据表3，求出勤人数
# 最后联结这两个查询

# 临时表0，查询课程id，课程名，报名人数
WITH temp_0 AS(
    SELECT course_id, course_name, SUM(if_sign) sign_cnt
    FROM course_tb
    JOIN behavior_tb USING(course_id)
    GROUP BY course_id, course_name
    ),
	# 临时表1，查询用户id，课程id，计算每个用户看每个课的总时长（因为用户可能看某一个课多次片段化）
    temp_1 AS(
    SELECT user_id, course_id, SUM(TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime)) dur_time
    FROM attend_tb
    GROUP BY user_id, course_id
    )

# 主查询，查询需要的内容
SELECT course_id, course_name, ROUND(attend_cnt / sign_cnt * 100, 2) 'attend_rate(%)'
FROM(# 子查询，根据临时表1计算的时长，求每科出勤的人数
    SELECT course_id, 
        SUM(CASE WHEN dur_time >= 10 THEN 1 ELSE 0 END) attend_cnt
    FROM temp_1
    GROUP BY course_id
    ) a
JOIN temp_0 USING(course_id)
ORDER BY course_id

踩的坑：直接联结三个表会因为表3存在一个用户在一个课里的多次观看记录增加冗余数据。

需要在表3先对用户聚合，再对课聚合。