焦作网站建设服务,个人网站需求分析,c++怎么做网站,wordpress 引用图片一般在直播或者游戏中经常会统计用户在线人数#xff0c;主要分为求每个时刻的在线人数和求某个时刻的在线人数两种。
【场景】#xff1a;某个时刻的在线人数、每个时刻的在线人数
【知识点】#xff1a;窗口函数、时间函数、sum(tag) over (order by dt,tag desc rows b…一般在直播或者游戏中经常会统计用户在线人数主要分为求每个时刻的在线人数和求某个时刻的在线人数两种。
【场景】某个时刻的在线人数、每个时刻的在线人数
【知识点】窗口函数、时间函数、sum(tag) over (order by dt,tag desc rows between unbounded preceding and current row)、窗口函数与分组函数的区别
一、分析思路 1、在线人数如何定义 在线人数是指在某个时间段内某一时刻在线的用户数。 可以求每个时刻的在线人数也可以求某个时刻的在线人数。常用的是求每个时刻的同时在线人数所以我们以其为例进行讲解 2、求解思路 要计算每个时刻的同时在线人数我们可以按照以下步骤进行 将所有登录和退出记录按照时间排序得到一个按照时间递增的登录和退出记录。如果该记录表示用户登录将其标记为1如果表示用户退出标记为-1利用窗口函数按照上述顺序对标记列进行累计求和登录时人数1退出时人数-1。 3、MySQL执行步骤如下 1获取标记好登录和退出的记录表 #将所有用户的进入和离开记录合并一起统一为dt,并用tag为1和-1来标记进入还是离开
(selecta.登录日期 as dt,1 as tag
from 表名1 a)
union
(selecta.退出日期 as dt,-1 as tag
from 表名1 a)这样就得到标记好登录和退出的记录。 2对标记记录累计求和 #如果同一时刻有进入也有离开时先记录用户数增加再记录减少所以在窗口函数中使用sum()需要order by dt,tag desc
selectdt,sum(tag) over (order by dt,tag desc rows between unbounded preceding and current row) as 同时在线人数
from 标记好登录和退出的记录表二、实例
下面就以两个实例讲清楚某个时刻的在线人数和每个时刻的在线人数。 两种问题的区别
问题描述特点使用方法每个时刻的用户在线人数求用户状态变动时刻的用户在线人数先获取标记好登录和退出的记录然后按照时间和标记排序的顺序对标记列累计求和某个时刻的用户在线人数限制条件截止到某个时刻的在线人数先获取标记好登录和退出的记录然后对某个时刻前的标记列求和 1每个时刻的在线人数
案例来自SQL163 每篇文章同一时刻最大在看人数 问题统计每篇文章同一时刻最大在看人数如果同一时刻有进入也有离开时先记录用户数增加再记录减少结果按最大人数降序。 示例用户行为日志表tb_user_loguid:用户ID, artical_id:文章ID, in_time:进入时间, out_time:离开时间, sign_in:是否签到
iduidartical_idin_timeout_timesign_cin110190012021-11-01 10:00:002021-11-01 10:00:110210290012021-11-01 10:00:092021-11-01 10:00:380310390012021-11-01 10:00:282021-11-01 10:00:580410490022021-11-01 11:00:452021-11-01 11:01:110510590012021-11-01 10:00:512021-11-01 10:00:590610690022021-11-01 11:00:552021-11-01 11:01:240710790012021-11-01 10:00:012021-11-01 10:01:500
根据示例你的查询应返回以下结果
artical_idmax_uv9001390022
解释10点0分10秒时有3个用户正在浏览文章900111点01分0秒时有2个用户正在浏览文章9002。
求解代码
withmain as(#用tag标记增加还是减少(selectartical_id,uid,in_time as dt,1 as tagfrom tb_user_logwhere artical_id ! 0)union(selectartical_id,uid,out_time as dt,-1 as tagfrom tb_user_logwhere artical_id ! 0)),main1 as(#如果同一时刻有进入也有离开时先记录用户数增加再记录减少所以在窗口函数中使用sum()需要order by dt,tag descselectartical_id,sum(tag) over (partition by artical_id order by dt,tag desc rows between unbounded preceding and current row) as uvfrom main)
#统计每篇文章同一时刻最大在看人数结果按最大人数降序
selectartical_id,max(uv) as max_uv
from main1
group by artical_id
order by max_uv desc标记好登录和退出的记录 artical_id uid dt tag
1 9001 101 2021-11-01 10:00:00 1
2 9001 102 2021-11-01 10:00:09 1
3 9001 103 2021-11-01 10:00:28 1
4 9002 104 2021-11-01 11:00:45 1
5 9001 105 2021-11-01 10:00:51 1
6 9002 106 2021-11-01 11:00:55 1
7 9001 107 2021-11-01 10:00:01 1
8 9001 101 2021-11-01 10:00:11 -1
9 9001 102 2021-11-01 10:00:38 -1
10 9001 103 2021-11-01 10:00:58 -1
11 9002 104 2021-11-01 11:01:11 -1
12 9001 105 2021-11-01 10:00:59 -1
13 9002 106 2021-11-01 11:01:24 -1
14 9001 107 2021-11-01 10:01:50 -1对标记记录累计求和 artical_id dt tag
1 9001 2021-11-01 10:00:00 1
2 9001 2021-11-01 10:00:01 2
3 9001 2021-11-01 10:00:09 3
4 9001 2021-11-01 10:00:11 2
5 9001 2021-11-01 10:00:28 3
6 9001 2021-11-01 10:00:38 2
7 9001 2021-11-01 10:00:51 3
8 9001 2021-11-01 10:00:58 2
9 9001 2021-11-01 10:00:59 1
10 9001 2021-11-01 10:01:50 0
11 9002 2021-11-01 11:00:45 1
12 9002 2021-11-01 11:00:55 2
13 9002 2021-11-01 11:01:11 1
14 9002 2021-11-01 11:01:24 0统计每篇文章同一时刻最大在看人数
artical_id max_uv
1 9001 3
2 9002 22某个时刻的在线人数
案例来自SQL186 牛客直播开始时各直播间在线人数 问题请你统计直播开始时1900各科目的在线人数以上例子的输出结果为按照course_name升序排序
已知上课情况表attend_tb如下其中user_id用户编号、course_name课程名称course_datetime上课时间、in_datetime进入直播间的时间、out_datetime离开直播间的时间
user_idcourse_namecourse_datetimein_datetimeout_datetime100Python2021-12-1 19:00-21:002021-12-01 19:00:002021-12-01 19:28:00100Python2021-12-1 19:00-21:002021-12-01 19:30:002021-12-01 19:53:00101Python2021-12-1 19:00-21:002021-12-01 19:00:002021-12-01 20:55:00102Python2021-12-1 19:00-21:002021-12-01 19:00:002021-12-01 19:05:00104Python2021-12-1 19:00-21:002021-12-01 19:00:002021-12-01 20:59:00101SQL2021-12-2 19:00-21:002021-12-02 19:05:002021-12-02 20:58:00102SQL2021-12-2 19:00-21:002021-12-02 18:55:002021-12-02 21:00:00104SQL2021-12-2 19:00-21:002021-12-02 18:57:002021-12-02 20:56:00107SQL2021-12-2 19:00-21:002021-12-02 19:10:002021-12-02 19:18:00100R2021-12-3 19:00-21:002021-12-03 19:01:002021-12-03 21:00:00102R2021-12-3 19:00-21:002021-12-03 18:58:002021-12-03 19:05:00108R2021-12-3 19:00-21:002021-12-03 19:01:002021-12-03 19:56:00
示例数据的输出结果如下
course_nameonline_numPython4R1SQL2
求解代码
withmain as(#进入为增加人数出去为减少人数selectuser_id,course_name,date_format(in_datetime,%H:%i) as dt,1 as tagfrom attend_tbunionselectuser_id,course_name,date_format(out_datetime,%H:%i) as dt,-1 as tagfrom attend_tb)
#统计直播开始时1900各科目的在线人数
selectcourse_name,sum(tag) as online_num
from course_tb
left join main using(course_name)
where dt 19:00
group by course_name
order by course_name;标记好登录和退出的记录
device_id course_name dt tag
100 Python 19:00 1
100 Python 19:30 1
101 Python 19:00 1
102 Python 19:00 1
104 Python 19:00 1
101 SQL 19:05 1
102 SQL 18:55 1
104 SQL 18:57 1
107 SQL 19:10 1
100 R 19:01 1
102 R 18:58 1
108 R 19:01 1
100 Python 19:28 -1
100 Python 19:53 -1
101 Python 20:55 -1
102 Python 19:05 -1
104 Python 20:59 -1
101 SQL 20:58 -1
102 SQL 21:00 -1
104 SQL 20:56 -1
107 SQL 19:18 -1
100 R 21:00 -1
102 R 19:05 -1
108 R 19:56 -1 对截止时间点的标记记录求和
course_name dt online_num
Python 19:00 4
R 18:58 1
SQL 18:55 2 扩展
前往查看MySQL 窗口函数与分组函数的区别
