Κοινή χρήση τεχνολογίας

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

Δεδομένα δοκιμής

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

Δήλωση αναγκών

Υπολογίστε τον αριθμό αιχμής των ατόμων στο διαδίκτυο σε κάθε χρονική στιγμή για ένα συγκεκριμένο σύστημα.

Παράδειγμα αποτελεσμάτων:

Πατήστε αποτέλεσμα activity_time Αύξουσα.

σε:

• activity_time Υποδεικνύει το στατιστικό χρονικό σημείο.
• max_users Υποδεικνύει τον υψηλότερο αριθμό ατόμων που είναι αιχμή εκείνη τη στιγμή.

Πραγματοποίηση απαιτήσεων

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

Η έξοδος είναι η εξής:

Ο πυρήνας αυτής της ερώτησης είναι το υποερώτημα t2 Η λογική στο:

	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

Πρώτον, υποβάλλουμε ερωτήματα t1 Μετατρέψτε τις στήλες σε γραμμές, οπότε γιατί χρειάζεται να το κάνετε αυτό; Φυσικά αυτό είναι για στατιστική διευκόλυνση.

Ας το σκεφτούμε όταν ένας χρήστης συνδεθεί και εισέλθει στο σύστημα, θα αυξηθεί ο αριθμός των ατόμων στο σύστημα; +1, αντιθέτως, όταν βγει ο χρήστης, θα αυξηθεί ο αριθμός των ατόμων; -1。

Όταν βάζουμε τους χρόνους σύνδεσης και αποσύνδεσης στην ίδια στήλη και ταξινομούμε ανά ώρα, μπορούμε να υπολογίσουμε με ακρίβεια τον αριθμό των ατόμων που βρίσκονται στο διαδίκτυο κάθε στιγμή; Αυτό είναι ένα δευτερεύον ερώτημα; t2 Αυτό που κάνουμε είναι να εκτελούμε αθροιστικούς υπολογισμούς μέσω συναρτήσεων παραθύρου,t2 Το αποτέλεσμα μοιάζει με αυτό:

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

Τέλος, ομαδοποιήστε και συγκεντρώστε κατά χρονικά σημεία, έως max Η συνάρτηση βρίσκει τον μέγιστο αριθμό αιχμής ατόμων σε κάθε χρονικό σημείο και ολοκληρώνει~