题解|存档！时间的截取和差值 #牛客直播各科目出勤率#

### 题目讲的比较模糊，在线时长10分钟，需要考虑以下两种情况
### 1.是否包括直播为开始前已经在线或者直播结束后仍在线的时间
### 2.是否将多段短时的在线时长进行累加求和


select course_id, course_name,
       round(100 * attend_cnt / sign_cnt, 2) as 'attend_rate(%)'
from (
    # 每科在线时长10分钟及以上人数
    select distinct course_id, course_name
        ,sum(if(sum(
            case when in_datetime <= starttime and out_datetime <= endtime then timestampdiff(second, starttime, out_datetime)
                when in_datetime <= starttime and out_datetime > endtime then timestampdiff(second, starttime, endtime)
                when in_datetime > starttime and out_datetime <= endtime then timestampdiff(second, in_datetime, out_datetime)
                else timestampdiff(second, in_datetime, endtime)
            end
        ) / 60 >= 10, 1, 0))over(partition by course_id) as attend_cnt
    from (
        # 得到课程开始结束时间，方便后续比较
        select course_id, course_name, user_id, in_datetime, out_datetime
            ,left(course_datetime, 15) as starttime -- 直播开始时间
            ,concat(left(course_datetime, 10), right(course_datetime, 5)) as endtime -- 直播结束时间
        from course_tb left join attend_tb using(course_id)
    ) as t1
    where in_datetime <= endtime
        and out_datetime >= starttime
    group by course_id, course_name, user_id
) as t2
left join (
    # 每科报名人数
    select course_id, sum(if_sign) as sign_cnt
    from behavior_tb
    group by course_id
) as t3
    using(course_id)
order by course_id;