기술나눔

한어Русский язык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 각 시점별 최대 인원수를 찾아 완료하는 기능입니다~