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

with 
    dataList as (
        select 
            distinct
            user_id,    
            course_id,
            course_name,
            if_sign,
            summinute
        from 
        course_tb join behavior_tb using (course_id)
        left join 
        (
            select 
                user_id,
                course_id,
                sum(timestampdiff(minute,
                if(
                    date_format(in_datetime,'%H%i%s') < '190000',concat(date(in_datetime),' 19:00:00'),  
                    in_datetime 
                ),#如果用户是在直播之前进入的，那么之前进入的时间理应不算做10分钟之内，所以不超过19点的应该被改为19点整
                if(
                    date_format(out_datetime,'%H%i%s') > '210000',concat(date(out_datetime),' 21:00:00'),
                    out_datetime
                )#如果用户是在直播之后进入的，那么之前观看的时间理应不算做10分钟之内，所以不超过21点的应该被改为21点整
                )) as summinute
            from 
                attend_tb 
                join course_tb using(course_id)
            where 
                date(course_datetime) = date(in_datetime) #这个条件判断用户是否是直播当天观看
            group by 
            user_id,
            course_id
            having 
                summinute >= 10
        ) t1
            using (user_id,course_id)
        #本体需要用left join 因为如果只用了join 会统计不完整报名人数, 因为需要统计每一个user_id的报名情况，一个人可以报名多次
    )
    select 
        course_id,
        course_name,
        round(count(summinute)*100/sum(if_sign),2)  `attend_rate(%)`
    from 
        dataList
    group by
        course_id,
        course_name

本题有一定的难度，因为同一个user_id可能会有多次报名的情况，所以在多表连接的情况，应该填充完用户行为表behavior_tb，左连接之后就知道全部的报名情况。代码如上