【场景】:同一时刻在线人数
【分类】:窗口函数、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
扩展
前往查看: MySQL 在线人数 场景分析
求解代码
方法一:
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