每篇文章同一时刻最大在看人数

select artical_id,max(cnt) max_uv from 
(select artical_id,
sum(num) over (partition by artical_id order by dt asc,num desc) as cnt
from
(select artical_id,in_time dt,1 num from tb_user_log
where artical_id != 0
union all 
select artical_id,out_time dt,-1 num from tb_user_log
where artical_id != 0 ) as a) as b
group by artical_id
order by max_uv desc

select artical_id,max(num) as max_uv from (
    select t1.artical_id,t1.id,
    sum(case when t1.out_time between t2.in_time and t2.out_time then 1 else 0 end) as num
    from tb_user_log t1 left join tb_user_log t2
            on t1.artical_id = t2.artical_id
    where t1.artical_id >0
    group by t1.artical_id,t1.id

    ) t 
group by artical_id
order by max_uv desc

1、这个题目首先想到的思路就是利用自链接，通过文章相连
2、这样同样文章内的内容，就会产生笛卡尔积，每行都可以和该文章的所有行进行对比
3、只要该行的结束时间在其他行开始和结束之间，那么就存在同一时刻，这样每行的记录都能算出他有多少同一时刻一起看的
4、然后利用id,分组，求出每行的同一时刻人数
5、最后使用最值函数，求出同一时刻的峰值人数。

看了评论区后，发现评论区是使用的另一个思路，更方便理解

select artical_id,max(num) as max_uv from (
select artical_id,SUM(tag) over(Partition by artical_id order by dt ,tag desc) as num from (
select artical_id,uid,in_time as dt ,1 as tag from tb_user_log where artical_id >0
union all
select artical_id,uid,out_time as dt ,-1 as tag from tb_user_log where artical_id >0)t)t1
group by artical_id
order by max_uv desc;

这个思路的关键是将用户的进入和离开都做了标识，然后利用窗口函数的累计计算逻辑，进行求和。

with t as (select t1.artical_id a1,t1.in_time a2,count(1) a3
from tb_user_log t1,tb_user_log t2
where t1.artical_id = t2.artical_id
and (t1.in_time >= t2.in_time
and t1.in_time <= t2.out_time)
and (t1.artical_id !=0
and t2.artical_id !=0)
group by a1, a2
)
select a1 artical_id, max(a3) max_uv
from t 
group by a1
order by max_uv desc

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

【分类】：窗口函数、tag标记、嵌套子查询

分析思路

难点：

1.同一时刻的在线人数 区别于 某个时刻之前的在线人数

（1）用tag标记增加还是减少

进入增加1，出去减少1

• [使用]：union；1 as tag；-1 as tag

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

如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，所以在窗口函数中使用sum()需要order by dt,tag desc

• [使用]：sum(tag) over (partition by artical_id order by dt,tag desc)

（3）求在看人数的最大值

• [使用]：group by exam_id；order by uv desc,avg_score

求解代码

方法一：

with 子句

with
    main as(
        #用tag标记增加还是减少
        (select
            artical_id,
            uid,
            in_time as dt,
            1 as tag
        from tb_user_log
        where artical_id != 0)
        union
        (select
            artical_id,
            uid,
            out_time as dt,
            -1 as tag
        from tb_user_log
        where artical_id != 0)
    ),
    main1 as(
        #如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，所以在窗口函数中使用sum()需要order by dt,tag desc
        select
            artical_id,
            sum(tag) over (partition by artical_id order by dt,tag desc) as uv
        from main
    )
#统计每篇文章同一时刻最大在看人数，结果按最大人数降序 
select
    artical_id,
    max(uv) as max_uv
from main1
group by artical_id
order by max_uv desc

方法二：

嵌套子查询 + from子查询

#统计每篇文章同一时刻最大在看人数，结果按最大人数降序 
select
    artical_id,
    max(uv) as max_uv
from(
    #如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，所以在窗口函数中使用sum()需要order by dt,tag desc
    select
        artical_id,
        sum(tag) over (partition by artical_id order by dt,tag desc) as uv
    from(
        #用tag标记增加还是减少
        (select
            artical_id,
            uid,
            in_time as dt,
            1 as tag
        from tb_user_log
        where artical_id != 0)
        union
        (select
            artical_id,
            uid,
            out_time as dt,
            -1 as tag
        from tb_user_log
        where artical_id != 0)
    ) main
) main1
group by artical_id
order by max_uv desc

select artical_id,max(cnt) max_uv from 
(select artical_id,
sum(num) over (partition by artical_id order by dt asc,num desc) as cnt
from
(select artical_id,in_time dt,1 num from tb_user_log
where artical_id != 0
union all 
select artical_id,out_time dt,-1 num from tb_user_log
where artical_id != 0 ) as a) as b
group by artical_id
order by max_uv desc

select b.artical_id, max(people) max_uv
from
(select a.artical_id,a.ct,sum(num) over 
(partition by artical_id order by ct, num desc) people
from
(select uid, artical_id, in_time ct, 1 num 
from tb_user_log
UNION
select uid, artical_id, out_time ct, -1 num 
from tb_user_log) a
where a.artical_id <> 0) b
group BY
b.artical_id
order BY
max_uv desc

select a,max(p)
from (select a,sum(n)over(partition by a order by t,n desc) p 
      from(select artical_id a,in_time t,1 n from tb_user_log
           union all
           select artical_id a,out_time t,-1 n from tb_user_log) k
      where a!=0) l
group by 1
order by 2 desc;

with temp as(
    select 
    artical_id,
    in_time dt,
    1 tag
    from tb_user_log
    where artical_id != 0
    union all
    select 
    artical_id,
    out_time dt,
    -1 tag
    from tb_user_log
    where artical_id != 0  
)
select
artical_id,max(cnt) max_ux
from(
select 
artical_id,
sum(tag) over(partition by artical_id order by dt,tag desc) cnt
from temp
) t
group by artical_id
order by max_ux desc