Condivisione della tecnologia

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Dati di test

-- 创建 user_activity 表
DROP TABLE IF EXISTS user_activity ;
CREATE TABLE user_activity (
    user_id STRING,
    activity_start TIMESTAMP,
    activity_end TIMESTAMP
);

-- 插入数据
INSERT INTO user_activity VALUES
('user1', '2024-07-11 08:00:00', '2024-07-11 09:00:00'),
('user2', '2024-07-11 08:30:00', '2024-07-11 09:30:00'),
('user3', '2024-07-11 09:00:00', '2024-07-11 10:00:00'),
('user4', '2024-07-11 09:15:00', '2024-07-11 09:45:00'),
('user5', '2024-07-11 09:30:00', '2024-07-11 10:30:00'),
('user6', '2024-07-11 10:00:00', '2024-07-11 11:00:00'),
('user7', '2024-07-11 08:05:00', '2024-07-11 08:55:00'),
('user8', '2024-07-11 08:45:00', '2024-07-11 09:15:00'),
('user9', '2024-07-11 09:05:00', '2024-07-11 10:05:00'),
('user10', '2024-07-11 09:25:00', '2024-07-11 10:25:00'),
('user11', '2024-07-11 08:10:00', '2024-07-11 09:10:00'),
('user12', '2024-07-11 08:20:00', '2024-07-11 09:20:00'),
('user13', '2024-07-11 08:35:00', '2024-07-11 09:35:00'),
('user14', '2024-07-11 08:50:00', '2024-07-11 09:50:00'),
('user15', '2024-07-11 09:10:00', '2024-07-11 10:10:00'),
('user16', '2024-07-11 09:20:00', '2024-07-11 10:20:00'),
('user17', '2024-07-11 09:40:00', '2024-07-11 10:40:00'),
('user18', '2024-07-11 10:05:00', '2024-07-11 11:05:00'),
('user19', '2024-07-11 10:15:00', '2024-07-11 11:15:00'),
('user20', '2024-07-11 10:25:00', '2024-07-11 11:25:00');
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

Dichiarazione dei bisogni

Calcola il numero massimo di persone online in ogni momento per un determinato sistema.

Esempio di risultati:

Premere il risultato activity_time Ascendente.

In:

• activity_time Indica il punto temporale statistico;
• max_users Indica il numero massimo di persone in quel momento.

Realizzazione dei requisiti

select
    date_format(activity_time,'yyyy-MM-dd HH') activity_time,
    max(total_users) max_users
from
    (select
        activity_time,
        sum(flag) over(order by activity_time) total_users
    from
        (select
            activity_start activity_time,
            1 flag
        from
            user_activity
        union all
        select
            activity_end activity_time,
            -1 flag
        from
            user_activity)t1
    )t2
group by
    date_format(activity_time,'yyyy-MM-dd HH');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

L'output è il seguente:

Il nocciolo di questa domanda è la sottoquery t2 La logica in:

	select
        activity_time,
        sum(flag) over(order by activity_time) total_users
    from
        (select
            activity_start activity_time,
            1 flag
        from
            user_activity
        union all
        select
            activity_end activity_time,
            -1 flag
        from
            user_activity)t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Per prima cosa eseguiamo una sottoquery t1 Converti colonne in righe, quindi perché è necessario farlo? Naturalmente questo è per comodità statistica.

Pensiamoci. Quando un utente accede ed entra nel sistema, il numero di persone nel sistema aumenterà? +1, al contrario, quando l'utente esce, il numero delle persone aumenterà? -1。

Quando inseriamo gli orari di accesso e disconnessione nella stessa colonna e li ordiniamo per ora, possiamo calcolare con precisione il numero di persone online in ogni momento. Questa è una sottoquery. t2 Ciò che facciamo è eseguire calcoli cumulativi tramite funzioni finestra,t2 Il risultato è simile al seguente:

2024-07-11 08:00:00     1
2024-07-11 08:05:00     2
2024-07-11 08:10:00     3
2024-07-11 08:20:00     4
2024-07-11 08:30:00     5
2024-07-11 08:35:00     6
2024-07-11 08:45:00     7
2024-07-11 08:50:00     8
2024-07-11 08:55:00     7
2024-07-11 09:00:00     7
2024-07-11 09:00:00     7
2024-07-11 09:05:00     8
2024-07-11 09:10:00     8
2024-07-11 09:10:00     8
2024-07-11 09:15:00     8
2024-07-11 09:15:00     8
2024-07-11 09:20:00     8
2024-07-11 09:20:00     8
2024-07-11 09:25:00     9
2024-07-11 09:30:00     9
2024-07-11 09:30:00     9
2024-07-11 09:35:00     8
2024-07-11 09:40:00     9
2024-07-11 09:45:00     8
2024-07-11 09:50:00     7
2024-07-11 10:00:00     7
2024-07-11 10:00:00     7
2024-07-11 10:05:00     7
2024-07-11 10:05:00     7
2024-07-11 10:10:00     6
2024-07-11 10:15:00     7
2024-07-11 10:20:00     6
2024-07-11 10:25:00     6
2024-07-11 10:25:00     6
2024-07-11 10:30:00     5
2024-07-11 10:40:00     4
2024-07-11 11:00:00     3
2024-07-11 11:05:00     2
2024-07-11 11:15:00     1
2024-07-11 11:25:00     0
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

Infine, raggruppa e aggrega per punti temporali, attraverso max La funzione trova il numero di punta massimo di persone in ogni momento e completa~