一、业务场景
求球队的比分；连续的空余座位；连续点击三次的用户数，中间不能有别人的点击等
二、解题思路
连续座位/得分与连续登陆的区别是，连续登陆是以同一用户为线索查看连续日期，连续得分/座位是以时间/座位号为线索，查看得分/座位空余的连续性。
首先使用窗口函数对球队得分、球员得分/座位状态/总点击数、用户点击数进行排序，然后构建辅助列求两者之间的差，最后按照相同的差值分组，筛选符合条件的座位号/得分等。
三、例题
例题链接：603. 连续空余座位 - 力扣（LeetCode）
1.题目描述
表: Cinema
+-------------+------+ | Column Name | Type | +-------------+------+ | seat_id | int | | free | bool | +-------------+------+ Seat_id 是该表的自动递增主键列。 在 PostgreSQL 中，free存储为整数。请使用 ::boolean将其转换为布尔格式。 该表的每一行表示第 i 个座位是否空闲。1 表示空闲，0 表示被占用。
查找电影院所有连续可用的座位。返回按 seat_id 升序排序 的结果表。测试用例的生成使得两个以上的座位连续可用。结果表格式如下所示。
输入: Cinema 表: +---------+------+ | seat_id | free | +---------+------+ | 1 | 1 | | 2 | 0 | | 3 | 1 | | 4 | 1 | | 5 | 1 | +---------+------+ 输出: +---------+ | seat_id | +---------+ | 3 | | 4 | | 5 | +---------+
2.解题思路
1️⃣排除占用座位使用row_number()进行排序
select seat_id,free,row_number()over() rn
from cinema
where free=12️⃣构建辅助列seat_id-rn
select seat_id,free,seat_id-rn as k
from (
select seat_id,free,row_number()over() rn
from cinema
where free=1
) t此时输出结果如下：
3️⃣题目要求返回2个以上的连续座位，也就是要筛选以k为分组数量大于1的k对应的seat_id
with a as(
select seat_id,free,seat_id-rn as k
from (
select seat_id,free,row_number()over() rn
from cinema
where free=1
) t)
select seat_id
from a
where k in (
select k
from a
group by k
having count(*)>1)3.完整代码
可以把构造差值辅助列和row_number合成一步看起来简洁一点。
with a as(
select seat_id,seat_id-row_number()over() as k
from cinema
where free=1
)
select seat_id
from a
where k in(
select k
from a
group by k
having count(*)>1)四、拓展
例题链接3140. 连续空余座位 II - 力扣（LeetCode）
1.题目描述
表：Cinema
+-------------+------+ | Column Name | Type | +-------------+------+ | seat_id | int | | free | bool | +-------------+------+ seat_id 是这张表中的自增列。 这张表的每一行表示第 i 个作为是否空余。1 表示空余，而 0 表示被占用。
编写一个解决方案来找到电影院中 最长的空余座位 的 长度。
注意：
- 保证 最多有一个 最长连续序列。
- 如果有 多个 相同长度 的连续序列，将它们全部输出。
返回结果表以 first_seat_id 升序排序。结果表的格式如下所示。
输入：
+---------+------+ | seat_id | free | +---------+------+ | 1 | 1 | | 2 | 0 | | 3 | 1 | | 4 | 1 | | 5 | 1 | +---------+------+ 输出： +-----------------+----------------+-----------------------+ | first_seat_id | last_seat_id | consecutive_seats_len | +-----------------+----------------+-----------------------+ | 3 | 5 | 3 | +-----------------+----------------+-----------------------+
2.解题思路
1️⃣本题目是在上一题的基础上作出更具体的要求，所以第一步还是先构建辅助列标记空闲座位。但是这一步并不需要求出连续座位数了，只要给空闲座位做好标记即可。
with a as(
select seat_id,seat_id-row_number()over() as k
from cinema
where free=1
)2️⃣题目要求返回起止座位号和连续长度，并且要找到最长的长度。首先计算起止座位号和长度，然后给长度降序排序，因为要求相同长度的全部输出，使用dense_rank()，最长的序号为1。
select
min(seat_id) first_seat_id,
max(seat_id) last_seat_id,
count(*) as consecutive_seats_len，
dense_rank()over(order by count(*) desc) rk
from b
group by k
3️⃣筛选连续长度最长的起止座位号和长度
select
first_seat_id,
last_seat_id,
consecutive_seats_len
from(
select
min(seat_id) first_seat_id,
max(seat_id) last_seat_id,
count(*) as consecutive_seats_len，
dense_rank()over(order by count(*) desc) rk
from b
group by k) t
wwhere rk=1
order by first_seat_id3.完整代码
#构建差值辅助列标记空闲座位
with a as(
select seat_id,(seat_id-row_number()over()) k
from cinema
where free=1
),
#求连续座位长度并排序
b as (
select
min(seat_id) first_seat_id,
max(seat_id) last_seat_id,
count(*) consecutive_seats_len,
dense_rank()over(order by count(*) desc) rk
from a
group by k
)
#筛选结果
select first_seat_id,last_seat_id,consecutive_seats_len
from b
where rk=1
order by first_seat_id