- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
SQL
目录
一,原题力扣链接
二,题干
表:
Stadium
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | visit_date | date | | people | int | +---------------+---------+ visit_date 是该表中具有唯一值的列。 每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people) 每天只有一行记录,日期随着 id 的增加而增加编写解决方案找出每行的人数大于或等于
100
且id
连续的三行或更多行记录。返回按
visit_date
升序排列 的结果表。查询结果格式如下所示。
示例 1:
输入: Stadium
表: +------+------------+-----------+ | id | visit_date | people | +------+------------+-----------+ | 1 | 2017-01-01 | 10 | | 2 | 2017-01-02 | 109 | | 3 | 2017-01-03 | 150 | | 4 | 2017-01-04 | 99 | | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-09 | 188 | +------+------------+-----------+ 输出: +------+------------+-----------+ | id | visit_date | people | +------+------------+-----------+ | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-09 | 188 | +------+------------+-----------+ 解释: id 为 5、6、7、8 的四行 id 连续,并且每行都有 >= 100 的人数记录。 请注意,即使第 7 行和第 8 行的 visit_date 不是连续的,输出也应当包含第 8 行,因为我们只需要考虑 id 连续的记录。 不输出 id 为 2 和 3 的行,因为至少需要三条 id 连续的记录。
三,建表语句
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');
四,分析
题解:
表:体育馆表
字段:编号id,日期,人流量
求人流量大于等于100 且id是连续的人流量 最后结果按照日期排序
第一步:过滤掉人流量小于100的行
with t1 as (
select * from stadium where people>=100
)
select * from t1;
第二步,以id排序,生成一个新序号
第三步,计算差值
with t1 as (
select * from stadium where people>=100
)
# select * from t1;
,t2 as (
select
id, visit_date, people,row_number() over (order by id) rn
from t1
)
# select * from t2;
,t3 as (
select id, visit_date, people, rn,
(id-rn) diff
from t2
)
select * from t3;
第四步,以差值分组 统计连续的行数
最后 取cnt大于等于3的行数 并且映射指定的列 然后以日期排序即可
五,SQL解答
with t1 as (
select * from stadium where people>=100
)
# select * from t1;
,t2 as (
select
id, visit_date, people,row_number() over (order by id) rn
from t1
)
# select * from t2;
,t3 as (
select id, visit_date, people, rn,
(id-rn) diff
from t2
)
# select * from t3;
,t4 as (
select
id, visit_date, people, rn, diff,count(1)over(partition by diff) cnt
from t3
)
,t5 as (
select id, visit_date, people from t4 where cnt >=3 order by visit_date
)
select * from t5;
六,验证
七,知识点总结
- 差值系列 用求差值的方法 屡试不爽
- 窗口行数排序的运用
- count排序的运用
- 条件过滤的运用
- 排序的运用
- 求体育馆人流量大于等于100 且id是连续的大于3行的记录 最后以日期排序
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用