live_max_online

1
-- step0 建表
2
-- drop table cm.tb_live_logs;
3
create table cm.tb_live_logs
4
(
5
    live_id     int,
6
    user_id     int,
7
    ts  datetime,
8
    type varchar(10)
9
);
10

11
-- step1 插入数据
12
insert into cm.tb_live_logs
13
(live_id, user_id, ts, type) values
14
(901,1001,'2022-10-01 12:00:00','IN'),
15
(901,1002,'2022-10-01 12:01:00','IN'),
16
(901,1003,'2022-10-01 12:01:00','IN'),
17
(901,1004,'2022-10-01 12:02:00','IN'),
18
(901,1005,'2022-10-01 12:02:00','IN'),
19
(901,1006,'2022-10-01 12:03:00','IN'),
20
(901,1007,'2022-10-01 12:03:00','IN'),
21
(901,1008,'2022-10-01 12:05:00','IN'),
22
(901,1009,'2022-10-01 12:05:00','IN'),
23
(901,1010,'2022-10-01 12:06:03','IN'),
24
(902,1101,'2022-10-01 12:00:00','IN'),
25
(902,1102,'2022-10-01 12:01:00','IN'),
26
(902,1103,'2022-10-01 12:01:00','IN'),
27
(902,1104,'2022-10-01 12:02:00','IN'),
28
(902,1105,'2022-10-01 12:29:00','IN'),
29
(902,1106,'2022-10-01 12:30:00','IN'),
30
(902,1107,'2022-10-01 12:31:00','IN'),
31
(902,1108,'2022-10-01 12:32:00','IN'),
32
(902,1109,'2022-10-01 12:39:00','IN'),
33
(902,1110,'2022-10-01 12:06:03','IN'),
34
(901,1001,'2022-10-01 12:03:03','OUT'),
35
(901,1002,'2022-10-01 12:01:00','OUT'),
36
(901,1003,'2022-10-01 12:03:03','OUT'),
37
(901,1004,'2022-10-01 12:05:03','OUT'),
38
(901,1005,'2022-10-01 12:10:03','OUT'),
39
(901,1006,'2022-10-01 12:03:01','OUT'),
40
(901,1007,'2022-10-01 12:03:03','OUT'),
41
(901,1008,'2022-10-01 12:06:12','OUT'),
42
(901,1009,'2022-10-01 12:06:03','OUT'),
43
(901,1010,'2022-10-01 12:10:03','OUT'),
44
(902,1101,'2022-10-01 12:03:03','OUT'),
45
(902,1102,'2022-10-01 12:03:03','OUT'),
46
(902,1103,'2022-10-01 12:03:03','OUT'),
47
(902,1104,'2022-10-01 12:05:03','OUT'),
48
(902,1105,'2022-10-01 12:30:03','OUT'),
49
(902,1106,'2022-10-01 12:30:01','OUT'),
50
(902,1107,'2022-10-01 12:40:03','OUT'),
51
(902,1108,'2022-10-01 12:44:12','OUT'),
52
(902,1109,'2022-10-01 12:42:03','OUT'),
53
(902,1110,'2022-10-01 12:10:03','OUT');
54

55
-- step2 IN/OUT转数字
56
select
57
    live_id,
58
    user_id,
59
    ts ,
60
    IF(type = 'IN', 1, -1) as contribution
61
from cm.tb_live_logs;
62

63
-- step3 按照时间进行累加，求每个时间点的delta
64
select
65
    live_id,
66
    ts,
67
    sum(contribution) as new_number
68
from (select
69
        live_id,
70
        user_id,
71
        ts,
72
        IF(type = 'IN', 1, -1) as contribution
73
      from cm.tb_live_logs
74
      ) t1
75
group by live_id, ts;
76

77
-- step4 按照时间顺序累加
78
select
79
    *,
80
    sum(new_number) over (partition by live_id order by ts) as online_number
81
from (select
82
        live_id,
83
        ts,
84
        sum(contribution) as new_number
85
      from (select
86
                live_id,
87
                user_id,
88
                ts,
89
                IF(type = 'IN', 1, -1) as contribution
90
            from cm.tb_live_logs
91
            ) t1
92
      group by live_id, ts
93
      ) t2;
94

95
-- step5 求累加过程中，最大的值，就是同一时刻最大在线用户
96
select
97
    live_id,
98
    max(online_number) as max_online_number
99
from (select
100
        *,
101
        sum(new_number) over (partition by live_id order by ts) as online_number
102
        from (select
103
                live_id,
104
                ts,
105
                sum(contribution) as new_number
106
              from (select
107
                        live_id,
108
                        user_id,
109
                        ts,
110
                        IF(type = 'IN', 1, -1) as contribution
111
                    from cm.tb_live_logs
112
                    ) t1
113
              group by live_id, ts
114
              ) t2
115
        ) t3
116
group by live_id;