Teknologian jakaminen

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

Testitiedot

-- 创建 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

Ilmoitus tarpeista

Laske verkossa olevien ihmisten huippumäärä kullakin hetkellä tietyssä järjestelmässä.

Esimerkki tuloksista:

Tulos paina activity_time Nouseva.

sisään:

• activity_time Ilmaisee tilastollisen ajankohdan;
• max_users Ilmaisee suurimman ihmisten määrän tuolloin.

Vaatimusten toteutus

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

Tulos on seuraava:

Tämän kysymyksen ydin on alikysely t2 Logiikka:

	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

Ensin teemme alikyselyn t1 Muunna sarakkeet riveiksi, joten miksi sinun on tehtävä tämä? Tämä on tietysti tilastollisen mukavuuden vuoksi.

Ajatellaanpa sitä, kun käyttäjä kirjautuu sisään ja tulee järjestelmään, lisääntyykö järjestelmän ihmisten määrä? +1päinvastoin, lisääntyykö ihmisten määrä, kun käyttäjä poistuu? -1。

Kun laitamme kirjautumis- ja uloskirjautumisajat samaan sarakkeeseen ja lajittelemme ajan mukaan, voimmeko laskea tarkasti, kuinka monta ihmistä verkossa kulloinkin on. Tämä on alikysely? t2 Teemme kumulatiivisia laskelmia ikkunafunktioiden kautta,t2 Tulos näyttää tältä:

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

Lopuksi ryhmittele ja aggregoi ajankohtien mukaan max Toiminto löytää maksimihuippujen määrän jokaisella aikapisteellä ja suorittaa ~