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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
| -- step0 建表
-- drop table cm.tb_live_logs;
create table cm.tb_live_logs
(
live_id int,
user_id int,
ts datetime,
type varchar(10)
);
-- step1 插入数据
insert into cm.tb_live_logs
(live_id, user_id, ts, type) values
(901,1001,'2022-10-01 12:00:00','IN'),
(901,1002,'2022-10-01 12:01:00','IN'),
(901,1003,'2022-10-01 12:01:00','IN'),
(901,1004,'2022-10-01 12:02:00','IN'),
(901,1005,'2022-10-01 12:02:00','IN'),
(901,1006,'2022-10-01 12:03:00','IN'),
(901,1007,'2022-10-01 12:03:00','IN'),
(901,1008,'2022-10-01 12:05:00','IN'),
(901,1009,'2022-10-01 12:05:00','IN'),
(901,1010,'2022-10-01 12:06:03','IN'),
(902,1101,'2022-10-01 12:00:00','IN'),
(902,1102,'2022-10-01 12:01:00','IN'),
(902,1103,'2022-10-01 12:01:00','IN'),
(902,1104,'2022-10-01 12:02:00','IN'),
(902,1105,'2022-10-01 12:29:00','IN'),
(902,1106,'2022-10-01 12:30:00','IN'),
(902,1107,'2022-10-01 12:31:00','IN'),
(902,1108,'2022-10-01 12:32:00','IN'),
(902,1109,'2022-10-01 12:39:00','IN'),
(902,1110,'2022-10-01 12:06:03','IN'),
(901,1001,'2022-10-01 12:03:03','OUT'),
(901,1002,'2022-10-01 12:01:00','OUT'),
(901,1003,'2022-10-01 12:03:03','OUT'),
(901,1004,'2022-10-01 12:05:03','OUT'),
(901,1005,'2022-10-01 12:10:03','OUT'),
(901,1006,'2022-10-01 12:03:01','OUT'),
(901,1007,'2022-10-01 12:03:03','OUT'),
(901,1008,'2022-10-01 12:06:12','OUT'),
(901,1009,'2022-10-01 12:06:03','OUT'),
(901,1010,'2022-10-01 12:10:03','OUT'),
(902,1101,'2022-10-01 12:03:03','OUT'),
(902,1102,'2022-10-01 12:03:03','OUT'),
(902,1103,'2022-10-01 12:03:03','OUT'),
(902,1104,'2022-10-01 12:05:03','OUT'),
(902,1105,'2022-10-01 12:30:03','OUT'),
(902,1106,'2022-10-01 12:30:01','OUT'),
(902,1107,'2022-10-01 12:40:03','OUT'),
(902,1108,'2022-10-01 12:44:12','OUT'),
(902,1109,'2022-10-01 12:42:03','OUT'),
(902,1110,'2022-10-01 12:10:03','OUT');
-- step2 IN/OUT转数字
select
live_id,
user_id,
ts ,
IF(type = 'IN', 1, -1) as contribution
from cm.tb_live_logs;
-- step3 按照时间进行累加，求每个时间点的delta
select
live_id,
ts,
sum(contribution) as new_number
from (select
live_id,
user_id,
ts,
IF(type = 'IN', 1, -1) as contribution
from cm.tb_live_logs
) t1
group by live_id, ts;
-- step4 按照时间顺序累加
select
*,
sum(new_number) over (partition by live_id order by ts) as online_number
from (select
live_id,
ts,
sum(contribution) as new_number
from (select
live_id,
user_id,
ts,
IF(type = 'IN', 1, -1) as contribution
from cm.tb_live_logs
) t1
group by live_id, ts
) t2;
-- step5 求累加过程中，最大的值，就是同一时刻最大在线用户
select
live_id,
max(online_number) as max_online_number
from (select
*,
sum(new_number) over (partition by live_id order by ts) as online_number
from (select
live_id,
ts,
sum(contribution) as new_number
from (select
live_id,
user_id,
ts,
IF(type = 'IN', 1, -1) as contribution
from cm.tb_live_logs
) t1
group by live_id, ts
) t2
) t3
group by live_id;
|
相关文章：SQL 12
