寻找电影院连续3个空闲座位
一、题目
有电影院座位预定数据如下，现有3个朋友要一同去看电影，按照要求找到符合要求的座位。
- 问题一： 找到连续空闲超过3个的座位，并给出开始和结束座位号； 期望结果
+-------------+-----------+
| start_seat | end_seat |
+-------------+-----------+
| 3 | 5 |
| 7 | 12 |
| 15 | 19 |
+-------------+-----------+
- 问题二： 找到同排中连续空闲超过3个的座位，给出排号、开始和结束座位号； 期望结果
+---------+-------------+-----------+
| row_id | start_seat | end_seat |
+---------+-------------+-----------+
| 1 | 3 | 5 |
| 1 | 7 | 10 |
| 2 | 15 | 19 |
+---------+-------------+-----------+
样例数据
+---------+----------+-------+
| row_id | seat_id | free |
+---------+----------+-------+
| 1 | 1 | 1 |
| 1 | 2 | 0 |
| 1 | 3 | 1 |
| 1 | 4 | 1 |
| 1 | 5 | 1 |
| 1 | 6 | 0 |
| 1 | 7 | 1 |
| 1 | 8 | 1 |
| 1 | 9 | 1 |
| 1 | 10 | 1 |
| 2 | 11 | 1 |
| 2 | 12 | 1 |
| 2 | 13 | 0 |
| 2 | 14 | 0 |
| 2 | 15 | 1 |
| 2 | 16 | 1 |
| 2 | 17 | 1 |
| 2 | 18 | 1 |
| 2 | 19 | 1 |
| 2 | 20 | 0 |
| 3 | 21 | 0 |
| 3 | 22 | 1 |
| 3 | 23 | 1 |
| 3 | 24 | 0 |
| 3 | 25 | 1 |
| 3 | 26 | 0 |
| 3 | 27 | 1 |
| 3 | 28 | 1 |
| 3 | 29 | 0 |
| 3 | 30 | 1 |
+---------+----------+-------+
二、分析
本题两问都是连续问题的查询，差别在问题二添加了一层分组，如果能理清楚问题一，问题二不难，直接做问题难度略大一点，整体属于连续问题的入门款
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
第1问：找全局连续3个空位
不考虑排的限制，只要座位编号连续且空闲 ≥ 3 即可。
- 先获得连续数据的分组ID，使用差值法，由于座位号原本连续，直接使用seat_id减去空闲座位的排序即可
执行SQL
SELECT seat_id,
seat_id - ROW_NUMBER() OVER (ORDER BY seat_id) AS grp
FROM t7_cinema
WHERE free = 1
执行结果
+----------+------+
| seat_id | grp |
+----------+------+
| 1 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 7 | 2 |
| 8 | 2 |
| 9 | 2 |
| 10 | 2 |
| 11 | 2 |
| 12 | 2 |
| 15 | 4 |
| 16 | 4 |
| 17 | 4 |
| 18 | 4 |
| 19 | 4 |
| 22 | 6 |
| 23 | 6 |
| 25 | 7 |
| 27 | 8 |
| 28 | 8 |
| 30 | 9 |
+----------+------+
21 rows selected (0.301 seconds)
- 根据分组ID grp进行分组统计，行数>=3 即为符合条件的分组，然后取得组内的最小值（开始座位ID），最大值（结束座位ID）
执行SQL
SELECT MIN(seat_id) AS start_seat,
MAX(seat_id) AS end_seat
FROM (SELECT seat_id,
seat_id - ROW_NUMBER() OVER (ORDER BY seat_id) AS grp
FROM t7_cinema
WHERE free = 1) t
GROUP BY grp
HAVING COUNT(*) >= 3;
结果
+-------------+-----------+
| start_seat | end_seat |
+-------------+-----------+
| 3 | 5 |
| 7 | 12 |
| 15 | 19 |
+-------------+-----------+
3 rows selected (0.302 seconds)(数据仓库技术dwsql.com)
第2问：找同一排内连续3个空位
| 大家需要坐在一起，跨排座位ID连续，但是实际不连续，所以要求在同一排。
- 获得分组ID，这里row_number()需要按照row_id 分组
执行SQL
SELECT row_id,
seat_id,
seat_id - ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY seat_id) AS grp
FROM t7_cinema
WHERE free = 1
执行结果
+---------+----------+------+
| row_id | seat_id | grp |
+---------+----------+------+
| 1 | 1 | 0 |
| 1 | 3 | 1 |
| 1 | 4 | 1 |
| 1 | 5 | 1 |
| 1 | 7 | 2 |
| 1 | 8 | 2 |
| 1 | 9 | 2 |
| 1 | 10 | 2 |
| 2 | 11 | 10 |
| 2 | 12 | 10 |
| 2 | 15 | 12 |
| 2 | 16 | 12 |
| 2 | 17 | 12 |
| 2 | 18 | 12 |
| 2 | 19 | 12 |
| 3 | 22 | 21 |
| 3 | 23 | 21 |
| 3 | 25 | 22 |
| 3 | 27 | 23 |
| 3 | 28 | 23 |
| 3 | 30 | 24 |
+---------+----------+------+
21 rows selected (8.806 seconds)dwsql.com
- 根据row_id和grp进行分组（这里如果不是要展示座位所属排，可以仅根据grp进行分组，因为在上一步row_number中已经包含排的信息进行分组了），统计组内座位个数，限制3个以上，并取最大最小值）
执行SQL
SELECT row_id,
MIN(seat_id) AS start_seat,
MAX(seat_id) AS end_seat
FROM (SELECT row_id,
seat_id,
seat_id - ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY seat_id) AS grp
FROM t7_cinema
WHERE free = 1) t
GROUP BY row_id, grp
HAVING COUNT(*) >= 3;
执行结果
+---------+-------------+-----------+
| row_id | start_seat | end_seat |
+---------+-------------+-----------+
| 1 | 3 | 5 |
| 1 | 7 | 10 |
| 2 | 15 | 19 |
+---------+-------------+-----------+
3 rows selected (0.597 seconds)