DEMO
1.SQL163 每篇文章同一时刻最大在看人数
思路：记录变化，即登入跟登出时才会有人员变动
with
t as ( -- 记录变化表
select
uid,
artical_id,
in_time as t_time,
1 as diff
from
tb_user_log
where
artical_id != 0
union all
select
uid,
artical_id,
out_time as t_time,
-1 as diff
from
tb_user_log
where
artical_id != 0
)
select
artical_id,
max(uv) as max_uv
from
(
select
artical_id,
(
sum(diff) over (
partition by
artical_id
order by
t_time, -- 按照时间排序
diff desc -- 如果同一时刻有进入也有离开时，先记录用户数增加再记录减少
)
) as uv
from
t
) a
group by
artical_id
order by
max_uv desc
2.SQL189 牛客直播各科目同时在线人数
-- 请你统计每个科目最大同时在线人数（按course_id排序），以上数据的输出结果如下：
with
tb_dt as (
(
select
c.course_id,
c.course_name,
a.in_datetime as dt,
1 as diff
from
attend_tb a
join course_tb c on a.course_id = c.course_id
union all
select
c.course_id,
c.course_name,
a.out_datetime as dt,
-1 as diff
from
attend_tb a
join course_tb c on a.course_id = c.course_id
)
order by
dt ASC
),
tb_num as (
select
t.course_id,
t.course_name,
t.dt,
sum(t.diff) over (
partition by
t.course_name
order by
t.dt
) as num
from
tb_dt t
)
select
t.course_id,
t.course_name,
max(num) as max_num
from
tb_num t
group by t.course_id,
t.course_name order by t.course_id
