Technologieaustausch

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

Testdaten

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

Bedarfserklärung

Berechnen Sie die maximale Anzahl an Personen, die zu jedem Zeitpunkt für ein bestimmtes System online sind.

Beispiel für Ergebnisse:

Ergebnispresse activity_time Aufsteigend.

In:

• activity_time Gibt den statistischen Zeitpunkt an;
• max_users Gibt die höchste Spitzenzahl an Personen zu diesem Zeitpunkt an.

Anforderungsrealisierung

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

Die Ausgabe ist wie folgt:

Der Kern dieser Frage ist die Unterabfrage t2 Die Logik 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

Zuerst führen wir eine Unterabfrage durch t1 Konvertieren Sie Spalten in Zeilen. Warum müssen Sie das tun? Dies dient natürlich der statistischen Vereinfachung.

Lassen Sie uns darüber nachdenken, ob sich die Anzahl der Personen im System erhöht, wenn sich ein Benutzer anmeldet und das System betritt. +1Wird im Gegenteil die Anzahl der Personen zunehmen, wenn der Benutzer das Programm verlässt? -1。

Wenn wir die Anmelde- und Abmeldezeiten in dieselbe Spalte eingeben und nach Zeit sortieren, können wir dann genau berechnen, wie viele Personen zu jedem Zeitpunkt online sind? Dies ist eine Unterabfrage. t2 Wir führen kumulative Berechnungen über Fensterfunktionen durch.t2 Das Ergebnis sieht so aus:

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

Zum Schluss gruppieren und aggregieren Sie nach Zeitpunkten max Die Funktion ermittelt zu jedem Zeitpunkt die maximale Spitzenanzahl an Personen und führt ~ aus