一文详解在线人数统计

发表于2022-10-09更新于2022-10-09阅读次数

在线人数的统计

使用场景

在视频场景下，经常可以看到当前有多少人在看，或者某些场景下，需要我们计算直播间的瞬时人数，作为后端人员，sql编写是必须的，但是这种场景的sql在怎么编写呢？

要实现动态计算访客，可以给sql打一个数据标签，进入（访问）时记录为1，退出时记录为-1。

实现步骤

1、取用户进入直播间时为1，退出为-1。

这里贴上nowCode原题目

https://www.nowcoder.com/practice/d69677e41f9a4bf3b3ed7a42573e9490

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
  course_id,
  user_id,
  in_datetime dt,
  1 uv
from
  attend_tb
union all
select
  course_id,
  user_id,
  out_datetime dt,
  -1 uv
from
  attend_tb
)t1

这样可以计算到每个进入或出去的记录的值，方便后面进行计算

2、使用窗口函数进行计算

关于窗口函数，不熟悉的可以前去这个知乎er解析里面看，讲解的十分清晰。

通俗易懂的学会：SQL窗口函数 - 知乎 (zhihu.com)

1
sum(uv) over(partition by course_id order by dt,uv desc) uv_cnt

关键点：按照日期排序，就可以得到每个时间瞬时进入或出去的人数，使用sum(uv)则可以计算瞬时值，因为需要计算每个课程的瞬时值，所以需要按照课程的id进行分组，这个分组的好处是，不是聚合分组，不会减少行的数量。

3、 最后计算最大值即可

贴上完整sql代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
select 
course_id,
course_name,
max(uv_cnt) max_num
from 
(
select 
a.course_id,
course_name,
sum(uv) over(partition by course_id order by dt,uv desc) uv_cnt
from (
select
  course_id,
  user_id,
  in_datetime dt,
  1 uv
from
  attend_tb
union all
select
  course_id,
  user_id,
  out_datetime dt,
  -1 uv
from
  attend_tb
)t1 join course_tb a on t1.course_id=a.course_id
)t2
group by course_id,course_name order by course_id