牛客直播各科目同时在线人数

with new_tab as (select user_id,course_id,in_datetime as time_,if(in_datetime is not null,1,0) as cnt
                from attend_tb
                union all
                select user_id,course_id,out_datetime as time_,if(out_datetime is not null,-1,0) as cnt
                from attend_tb)

select b.course_id,b.course_name,max(num)
from 
    (select *,sum(cnt)over(partition by course_id order by time_) as num
     from new_tab
    ) t 
 left join course_tb b on t.course_id=b.course_id
 group by b.course_id,b.course_name
 order by b.course_id

select b.course_id,c.course_name,max(cnt) max_num
from
(select course_id,
sum(num) over (partition by course_id order by dt asc,num desc) cnt
from
(select course_id,in_datetime dt,1 num from attend_tb
union all 
select course_id,out_datetime dt,-1 num from attend_tb) as a) as b
inner join course_tb c on c.course_id=b.course_id
group by b.course_id,c.course_name
order by b.course_id

【场景】：同时在线人数

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

分析思路

难点：
1.窗口函数和分组查询要分开求解

（1）统计进出记录表

注：进入是增加一个在线人数，出去是减少一个在线人数

• [条件]：timestampdiff(minute,in_datetime,out_datetime) >= 10

• [使用]：union；使用1和-1标记进入、出去

（2）统计每个时刻的同时在线人数

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

（3）统计每个科目最大同时在线人数（按course_id排序）

• [使用]：max() ; group by course_id,course_name; order by course_id

最终结果

select 查询结果 [课程号;课程名称;最大同时在线人数]
from 从哪张表中查询数据[多表]
group bu 分组条件 [课程号；课程名称]
order by 对查询结果排序 [按课程号升序排序];

求解代码

方法一

with子句

with
    main as(
        #进入是增加一个在线人数，出去是减少一个在线人数
        select
            user_id,
            course_id,
            in_datetime as datetime,
            1 as tag
        from attend_tb
        union
        select
            user_id,
            course_id,
            out_datetime as datetime,
            -1 as tag
        from attend_tb
    )
    ,main1 as(
        #统计每个时刻的同时在线人数
        select distinct
            course_id,
            course_name,
            sum(tag) over(partition by course_id order by datetime) as sum_num
        from course_tb
        left join main using(course_id)
    )
#统计每个科目最大同时在线人数（按course_id排序）
select
    course_id,
    course_name,
    max(sum_num) as max_num
from main1
group by course_id,course_name
order by course_id

方法二

多表连接

#统计每个科目最大同时在线人数（按course_id排序）
select
    course_id,
    course_name,
    max(sum_num) as max_num
from(
    select distinct
        course_id,
        course_name,
        sum(tag) over(partition by course_id order by datetime) as sum_num
    from course_tb
    left join(
        #进入是增加一个在线人数，出去是减少一个在线人数
        select
            user_id,
            course_id,
            in_datetime as datetime,
            1 as tag
        from attend_tb
        union
        select
            user_id,
            course_id,
            out_datetime as datetime,
            -1 as tag
        from attend_tb
    ) main using(course_id)
) main1
group by course_id,course_name
order by course_id

select c.course_id, c.course_name, count(*) max_num 
from course_tb c 
left join attend_tb a on c.course_id = a.course_id
where a.in_datetime <= (select min(out_datetime) from attend_tb where course_id = c.course_id)
group by c.course_id, c.course_name

select c,course_name,max(s)
from(select c,sum(r)over(partition by c order by t,r desc) s
    from(select course_id c,in_datetime t,1 r from attend_tb
        union all
        select course_id c,out_datetime t,-1 r from attend_tb)a)b
join course_tb b on c=b.course_id
group by 1,2
order by 1;

# 考点：同时在线人数的统计
#三步走

# 选择最大在线人数
SELECT
  C.course_id,D.course_name,max(total_num) as max_num
FROM
  (
    SELECT 
      # 窗口函数 sum() over() 计算在线人数
      user_id,course_id,pt,tag,
      sum(tag) over(partition by course_id order by pt,tag desc) as total_num
    FROM
      (
        #开始时间、离开时间打标签
          SELECT
          user_id,course_id,pt,tag
        FROM
          (
            SELECT
              user_id,course_id,in_datetime as pt,1 as tag
            FROM
              attend_tb
            union all
            SELECT
              user_id,course_id,out_datetime as pt,-1 as tag
            FROM
              attend_tb
          ) A
        order by pt asc
      ) B
  ) C
  left join course_tb D on C.course_id = D.course_id
group by
  C.course_id,D.course_name
order by
  C.course_id asc

with attend_detail as  (
    select 
      course_id, active_time
    -- , cnt  -- 此种算法是同时计算进出，如果先算进或者先算出，可以保留 cnt 来单独计算
    -- 另：如果不需要同时计算进出，则不需要单独对 course_id, active_time 汇总，直接窗口函数
    , sum(cnt) as cnt 
    from (
        select course_id, in_datetime as active_time, 1 as cnt from attend_tb 
        union all
        select course_id, out_datetime as active_time, -1 as cnt from attend_tb 
    ) t
    group by course_id, active_time -- , cnt 
)

select 
  t2.course_id as course_id
, c.course_name as course_name 
, t2.max_num as max_num
from (
    select 
    course_id, max(online_num) as max_num
    from (
        select 
          course_id, active_time
        -- 此处计算同时进出，先计算进，则 order by active_time, cnt
        -- 先计算出，则 order by active_time, cnt desc
        -- 另外测试时间效率，窗口不如自关联
        , sum(cnt) over(partition by course_id order by active_time) as online_num
        from attend_detail 
    ) t1
    group by course_id
) t2
inner join course_tb c 
    on t2.course_id = c.course_id 
order by course_id 
;

with t as (
    select user_id,course_id,in_datetime,1 as state
    from attend_tb
    union all 
    select user_id,course_id,out_datetime,-1 as state
    from attend_tb
)
select a.course_id,
       c.course_name,
       max(a.num) as max_num
from (
select course_id,
      sum(state) over (partition by course_id order by in_datetime,state desc) as num
      from t
    ) as a

join course_tb as c on a.course_id= c.course_id
group by 1,2

select
    course_id
    ,course_name
    ,max(num) as max_num
from
    (
        select
            a1.course_id
            ,course_name
            ,sum(cnt) over (order by dt) as num
        from
            (select user_id,course_id,in_datetime as dt,1 as cnt from attend_tb union all select user_id,course_id,out_datetime,-1 as cnt from attend_tb) a1
            inner join course_tb a2 using (course_id)
    ) t
group by
    course_id
    ,course_name
order by
    course_id
;

with temp as(
SELECT user_id, course_id, in_datetime as time, 1 as counter
FROM attend_tb
UNION ALL
SELECT user_id, course_id, out_datetime as time, -1 as counter
FROM attend_tb
ORDER BY time asc)
SELECT temp2.course_id, c.course_name, max(temp2.num_people) as max_num
FROM
(SELECT user_id, course_id, time,
SUM(counter) OVER(PARTITION BY course_id order by time asc) as num_people
FROM temp) as temp2
INNER JOIN course_tb c
ON temp2.course_id = c.course_id
GROUP BY temp2.course_id, c.course_name
ORDER BY temp2.course_id;

with a as
    (select course_id,max(so) max_num
    from  (select course_id,sum(num)over(partition by course_id
           order by cn ) so
           from (select course_id,in_datetime cn,1 num
                from attend_tb
                union all 
                select course_id,out_datetime cn,-1 num
                from attend_tb) t)t2
    group by course_id
    order by course_id )
select course_id,course_name,max_num
from a
left join course_tb using(course_id)

with tmp as(
    select
    user_id,
    course_id,
    in_datetime as dt,
    1 as cnt
    from attend_tb
    union
    select
    user_id,
    course_id,
    out_datetime as dt,
    -1 as cnt
    from attend_tb
)

select
course_id,
course_name,
max(num) as max_num
from (
    select
    tmp.course_id,
    course_name,
    sum(cnt)over(partition by tmp.course_id order by dt,cnt) as num
    from tmp
    left join course_tb
    on tmp.course_id = course_tb.course_id
) a
group by 1,2
order by course_id