SQL面试题挑战02：同时最大在线人数问题

NULL PTR

已于 2024-01-30 15:19:56 修改

阅读量238

点赞数 2

分类专栏： SQL刷题文章标签： sql 数据库

于 2024-01-30 15:16:47 首次发布

本文链接：https://blog.csdn.net/qq_46582929/article/details/135933901

版权

SQL刷题专栏收录该内容

14 篇文章 0 订阅

订阅专栏

SQL面试题挑战02：同时最大在线人数问题

问题

如下为某直播平台各主播的开播及关播时间明细数据，现在需要计算出该平台最高峰期同时在线的主播人数。

user_id     start_date              end_date
1001    2021-06-14 12:12:12     2021-06-14 18:12:12
1003    2021-06-14 13:12:12     2021-06-14 16:12:12
1004    2021-06-14 13:15:12     2021-06-14 20:12:12
1002    2021-06-14 15:12:12     2021-06-14 16:12:12
1005    2021-06-14 15:18:12     2021-06-14 20:12:12
1001    2021-06-14 20:12:12     2021-06-14 23:12:12
1006    2021-06-14 21:12:12     2021-06-14 23:15:12
1007    2021-06-14 22:12:12     2021-06-14 23:10:12

建表语句

drop table if exists login;
create table login(
    user_id varchar(20),
    start_date datetime,
    end_date datetime
);
insert into login
select 1001 as user_id, '2021-06-14 12:12:12' as start_date , '2021-06-14 18:12:12' as end_date
union all
select 1003 as user_id, '2021-06-14 13:12:12' as start_date , '2021-06-14 16:12:12' as end_date
union all
select 1004 as user_id, '2021-06-14 13:15:12' as start_date , '2021-06-14 20:12:12' as end_date
union all
select 1002 as user_id, '2021-06-14 15:12:12' as start_date , '2021-06-14 16:12:12' as end_date
union all
select 1005 as user_id, '2021-06-14 15:18:12' as start_date , '2021-06-14 20:12:12' as end_date
union all
select 1001 as user_id, '2021-06-14 20:12:12' as start_date , '2021-06-14 23:12:12' as end_date
union all
select 1006 as user_id, '2021-06-14 21:12:12' as start_date , '2021-06-14 23:15:12' as end_date
union all
select 1007 as user_id, '2021-06-14 22:12:12' as start_date , '2021-06-14 23:10:12' as end_date

解答

思路：

分别提取出开播和关播时间，然后打标记，开播用1打标记，关播用-1打标记
然后直接对标记开窗求和就知道每个时间节点的人数
随后对人数求最大值即可

select
    max(total)
from
    (
        select
            ts,
            sum(flag)over(order by ts)total
        from
            (
                select
                    start_date ts,
                    1 flag
                from
                    login
                union all
                 select
                    end_date ts,
                    -1 flag
                from
                    login
            )t1
    )t2