leetcode 601.体育馆的人流量

最新推荐文章于 2024-07-26 14:20:58 发布

奋斗哼哼

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量36

点赞数

文章标签：数据库 sql mysql leetcode

本文链接：https://blog.csdn.net/weixin_51696882/article/details/130379909

版权

数据准备

Create table If Not Exists Stadium (id int, visit_date DATE NULL, people int);
Truncate table Stadium;
insert into Stadium (id, visit_date, people) values ('1', '2017-01-01', '10');
insert into Stadium (id, visit_date, people) values ('2', '2017-01-02', '109');
insert into Stadium (id, visit_date, people) values ('3', '2017-01-03', '150');
insert into Stadium (id, visit_date, people) values ('4', '2017-01-04', '99');
insert into Stadium (id, visit_date, people) values ('5', '2017-01-05', '145');
insert into Stadium (id, visit_date, people) values ('6', '2017-01-06', '1455');
insert into Stadium (id, visit_date, people) values ('7', '2017-01-07', '199');
insert into Stadium (id, visit_date, people) values ('8', '2017-01-09', '188');

输入

在这里插入图片描述

分析

在这里插入图片描述

输出

with t1 as (
    select *,
       row_number() over () rn1
    from stadium
    where people>=100
),t2 as (
    select *,
       (id - rn1) as rn2,
           count((id - rn1)) over (partition by (id - rn1)) cnt
    from t1
)
select id,
       visit_date,
       people
from t2
where cnt>=3;