牛客直播开始时各直播间在线人数

select c.course_id,
c.course_name,
count(distinct a.user_id) online_num
from course_tb c
inner join attend_tb a on a.course_id=c.course_id
where time(a.in_datetime) <= '19:00:00'
and time(a.out_datetime) >= '19:00:00'
group by c.course_id,c.course_name
order by c.course_id

date_format(a.in_datetime,'%H:%i:%s') <= '19:00:00'
date_format(a.out_datetime,'%H:%i:%s') >= '19:00:00'

right(a.in_datetime,8) <= '19:00:00'
right(a.out_datetime,8) >= '19:00:00'

time(a.in_datetime) <= '19:00:00'
time(a.out_datetime) >= '19:00:00'

select
course_id,
course_name,
# substr(course_datetime,1,15)
sum(if(in_datetime<=substr(course_datetime,1,15),1,0)) 
as online_num
from attend_tb a
join course_tb b using(course_id)
group by course_id,course_name
order by course_id

select c.course_id,c.course_name,count(distinct a.user_id) as online_num
from course_tb c
inner join attend_tb a
on c.course_id = a.course_id
where time(a.in_datetime) <= '19:00:00'
and time(a.out_datetime) >= '19:00:00'
group by c.course_id,c.course_name
order by c.course_id;

【场景】：某时刻同时在线人数

【分类】：窗口函数、多表连接

分析思路

难点：

1.题目中说统计直播开始时的在线人数，使用小于等于。

注意窗口函数和分组函数的区别！见题解

（1）统计进入为增加人数，出去为减少人数

用tag来标记，进去增加人数1人，出去减少人数1人；然后把表连接起来

• [使用]：union

（2）统计直播开始时（19：00），各科目的在线人数

统计直播开始时，所以要用小于等于

• [条件]：datetime <= '19:00'

• [使用]：窗口函数sum()

最终结果

select 查询结果 [课程号;课程名称;在线人数]
from 从哪张表中查询数据[多表]
where 查询条件 [时间小于等于19点]
order by 对查询结果排序 [课程号];

求解代码

方法一：

with子句 + 分组函数

with
    main as(
        #进入为增加人数，出去为减少人数
        select
            user_id,
            course_id,
            date_format(in_datetime,'%H:%i') as datetime,
            1 as tag
        from attend_tb
        union
        select
            user_id,
            course_id,
            date_format(out_datetime,'%H:%i') as datetime,
            -1 as tag
        from attend_tb
    )
#统计直播开始时（19：00），各科目的在线人数
select
    course_id,
    course_name,
    sum(tag) as online_num
from course_tb
left join main using(course_id)
where datetime <= '19:00'
group by course_id,course_name
order by course_id

方法二：

with子句 + 窗口函数

注意：因为有where条件不大于19：00的限制，所以窗口函数里面不需要再用order by tag desc;使用窗口函数必须去重，但是使用分组函数是course_id是唯一的，不需要去重。

with
    main as(
        #进入为增加人数，出去为减少人数
        select
            user_id,
            course_id,
            date_format(in_datetime,'%H:%i') as datetime,
            1 as tag
        from attend_tb
        union
        select
            user_id,
            course_id,
            date_format(out_datetime,'%H:%i') as datetime,
            -1 as tag
        from attend_tb
    )
#统计直播开始时（19：00），各科目的在线人数
select distinct
    course_id,
    course_name,
    sum(tag) over(partition by course_id) as online_num
from course_tb
left join main using(course_id)
where datetime <= '19:00'
order by course_id

方法三：

表连接

#统计直播开始时（19：00），各科目的在线人数
select
    course_id,
    course_name,
    sum(tag) as online_num
from course_tb
left join(
    #进入为增加人数，出去为减少人数
    select
        user_id,
        course_id,
        date_format(in_datetime,'%H:%i') as datetime,
        1 as tag
    from attend_tb
    union
    select
        user_id,
        course_id,
        date_format(out_datetime,'%H:%i') as datetime,
        -1 as tag
    from attend_tb
) main using(course_id)
where datetime <= '19:00'
group by course_id,course_name
order by course_id

# 考点：日期函数的使用:date_format(日期,'%Y-%m-%d %H-%i-%s');timestampdiff(维度，小日期，大日期)

with t1 as (SELECT course_id,course_name,date_format(substring_index(course_datetime,'-',3),'%Y-%m-%d %H:%i:%s') as begin_dt FROM course_tb)

SELECT A.course_id,course_name,count(distinct user_id) as online_num
FROM attend_tb A left join t1 on A.course_id = t1.course_id
where timestampdiff(SECOND,in_datetime,begin_dt) >= 0
group by A.course_id,course_name
order by A.course_id asc

不知道为什么是对的。。。

select a.course_id,course_name,
sum(if(course_datetime between in_datetime and out_datetime,1,0)) online_num
from attend_tb a
join course_tb c using(course_id)
group by a.course_id,course_name
order by a.course_id

select 
	c.course_id,
	c.course_name,
	count(*) online_num
from attend_tb a
left join
course_tb c
on a.course_id=c.course_id
where date_format(in_datetime,'%H%i')<='1900' #注意判断条件，要19:00以前就在直播间的
group by 1,2
order by 1

select q2.course_id,q2.course_name,count(q1.user_id) online_num
from (
    select *
    from attend_tb
    where time(in_datetime)<='19:00:00' and time(out_datetime)>'19:00:00'
) q1 left join course_tb q2 on q1.course_id=q2.course_id
group by q2.course_id,q2.course_name
order by q2.course_id

with t1 as (
select 
x.course_id
,x.course_name
,in_datetime 
,out_datetime
from course_tb x 
join attend_tb y
using(course_id)
),t2 as (
select course_id,course_name,in_datetime as dt,1 as tag
from t1 
union all
select course_id,course_name,out_datetime as dt,-1 as tag
from t1 
),t3 as (
select course_id,course_name,dt,sum(tag) over(partition by course_id,course_name order by dt) as cnt
from t2
where time(dt)<="19:00:00"
)
select distinct course_id,course_name,last_value(cnt) over(partition by course_id,course_name order by dt rows between unbounded preceding and unbounded following) as online_num
from t3
order by 1

select
    at.course_id 
    , course_name
    , count(
        case 
            when in_datetime <= left(course_datetime, 15) and out_datetime >= left(course_datetime, 15) then user_id
            end
        ) as online_num
from attend_tb at
    left join course_tb co on at.course_id = co.course_id
group by at.course_id 
    , course_name
order by course_id
;