当前位置：首页 > article >正文

【力扣白嫖日记】601.体育馆的人流量

article 2025/1/4 14:19:49

前言

练习sql语句，所有题目来自于力扣（https://leetcode.cn/problemset/database/）的免费数据库练习题。

今日题目：

601.体育馆的人流量
表：Stadium

列名	类型
id	int
visit_date	date
people	int

编写解决方案找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列的结果表。

我那不值一提的想法：

我的思路：首先梳理表内容，题干一共给了一张表，记录了序号id，日期和人流量。
其次分析需求，需要找到每行的人数大于或等于100且id连续的三行或更多的记录。
针对于连续出现的次数的方法，最简单的方法就是自连接，连续出现三行就自连接三次，但是不够灵活，如果需要连续出现100行以上的数据就很难去计算了，这里我们可以使用row_number()去灵活的查询,以前也做过类似的题

https://blog.csdn.net/dkmaa/article/details/136302362?spm=1001.2014.3001.5506

针对于这道题，我的想法是需要先找到所有>100的数据，在这里我把所有大于100的数据设置为0，方便以后partition by

with onehund_people as (
    select id,people,
    case when people >=100 then 0
    else people 
    end as peo
    from Stadium
)

其次使用id - row_number() over(partition by peo order by id)，得到它们的差值，至于为何这样，引用里面的文章，我已经详细说明过了，这里就不多说了。

select o.id,s.visit_date,peo,s.people,o.id - row_number() over(partition by peo order by o.id) as num
from onehund_people o 
left join Stadium s 
on o.id = s.id 
order by o.id

在得到所有的差值后，我们对其分组求数量，数量>=3，便是我们需要的数据

select num 
    from (
        select o.id,s.visit_date,peo,s.people,o.id - row_number() over(partition by peo order by o.id) as num
        from onehund_people o 
        left join Stadium s 
        on o.id = s.id 
        order by o.id 
    ) as a 
group by peo,num 
having count(*) >=3

现在我们已经得到了连续出现3次的num，那么现在再嵌套一层查询，使num在连续出现三次的num里面，同时注意，我们并没有筛选>100 的数据，所以末尾加个条件peo = 0

select id,visit_date,people
from (
    select o.id,s.visit_date,peo,s.people,o.id - row_number() over(partition by peo order by o.id) as num
    from onehund_people o 
    left join Stadium s 
    on o.id = s.id 
    order by o.id 
) b 
where num in
    (select num 
    from (
        select o.id,s.visit_date,peo,s.people,o.id - row_number() over(partition by peo order by o.id) as num
        from onehund_people o 
        left join Stadium s 
        on o.id = s.id 
        order by o.id 
    ) as a 
    group by peo,num 
    having count(*) >=3
)
and peo = 0

完整代码

with onehund_people as (
    select id,people,
    case when people >=100 then 0
    else people 
    end as peo
    from Stadium
)
select id,visit_date,people
from (
    select o.id,s.visit_date,peo,s.people,o.id - row_number() over(partition by peo order by o.id) as num
    from onehund_people o 
    left join Stadium s 
    on o.id = s.id 
    order by o.id 
) b 
where num in
    (select num 
    from (
        select o.id,s.visit_date,peo,s.people,o.id - row_number() over(partition by peo order by o.id) as num
        from onehund_people o 
        left join Stadium s 
        on o.id = s.id 
        order by o.id 
    ) as a 
    group by peo,num 
    having count(*) >=3
)
and peo = 0