SQL进阶技巧：影院相邻的座位如何预定？

会飞的一十六 2024-10-14

点击上方【蓝字】关注我们

场景描述

影院座位预定表 T_SEATS 记录了当前座位的预定情况。如有2个人去影院看演唱会，需满足位置紧邻且至少其中一人靠过道(同一排最左或最右的座位靠过道)的座位组合,结果集按开始座位号从小到大排序。座位示意图如下：



CREATE TABLE `t_seats` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `row_no` int DEFAULT NULL COMMENT '第几排',
  `seat` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '座位',
  `status` int NOT NULL COMMENT '预定状态 0-未预定 1-已预定',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
复制

查询结果集，字段顺序及命名如下:开始座位号、结束座位号

数据准备



create table t_seat as (select stack(
                                     20,
                                     1, 1, 16, 0,
                                     2, 1, 17, 0,
                                     3, 1, 18, 1,
                                     4, 1, 19, 0,
                                     5, 1, 20, 1,
                                     6, 2, 11, 1,
                                     7, 2, 12, 0,
                                     8, 2, 13, 0,
                                     9, 2, 14, 1,
                                     10, 2, 15, 0,
                                     11, 3, 8, 0,
                                     12, 3, 9, 1,
                                     13, 3, 10, 0,
                                     14, 4, 5, 0,
                                     15, 4, 6, 0,
                                     16, 4, 7, 1,
                                     17, 5, 3, 0,
                                     18, 5, 4, 0,
                                     19, 6, 1, 1,
                                     20, 6, 2, 0
                             ) as (id, row_num, seat, status)
               )
;
复制

问题分析

方法1：利用lag()及lead()分析函数求解

第一步：利用lag()及lead()函数求出左边座位、右边座位，及左边座位状态、右边座位状态，进行辅助判断。



select id
           , row_num
           , seat
           , status
           , lag(seat) over (partition by row_num order by seat)               lag_seat
           , lead(seat) over (partition by row_num order by seat)              lead_seat
           , lag(status, 1, status) over (partition by row_num order by seat)  lag_status
           , lead(status, 1, status) over (partition by row_num order by seat) lead_status
from t_seat
复制

第二步：根据题设条件进行判断

过道的判断：同一排最左或最右的座位靠过道。即取lag()或lead()时值为null的，因为最左或最右的时候，座位取值为NULL，那么判断条件即为其中lag_seat ,lead_seat 任意一个为null即满足条件。即 (lag_seat + lead_seat) is null

紧邻判断：即当前seat值 +1 = lead_seat即可
状态判断：无论获取的lag_seat还是lead_seat都必须是未被预定的，即(lag_status + lead_status) = 0

整体判断条件如下：

case
    when (lag_seat + lead_seat) is null and (lag_status + lead_status) = 0 and seat + 1 = lead_seat
    then 1 end flg
复制

完整的SQL如下：



select row_num
     , seat
     , lead_seat
from (select id
           , row_num
           , seat
           , lag_seat
           , lead_seat
           , case
                 when (lag_seat + lead_seat) is null and (lag_status + lead_status) = 0 and seat + 1 = lead_seat
                     then 1 end flg
      from (select id
                 , row_num
                 , seat
                 , status
                 , lag(seat) over (partition by row_num order by seat)               lag_seat
                 , lead(seat) over (partition by row_num order by seat)              lead_seat
                 , lag(status, 1, status) over (partition by row_num order by seat)  lag_status
                 , lead(status, 1, status) over (partition by row_num order by seat) lead_status
            from t_seat) t
      where status = 0) t
where flg = 1;
复制

SQL可简化为：



select row_num
     , seat
     , lead_seat
from (select
            row_num
           , seat
           , status
           , lag(seat) over (partition by row_num order by seat)               lag_seat
           , lead(seat) over (partition by row_num order by seat)              lead_seat
           , lag(status, 1, status) over (partition by row_num order by seat)  lag_status
           , lead(status, 1, status) over (partition by row_num order by seat) lead_status
      from t_seat) t
where
    case when (lag_seat + lead_seat) is null and (lag_status + lead_status) = 0 and seat + 1 = lead_seat then 1 end = 1
复制

方法2：转换成字符串序列进行分析

第一步：利用collect_list()分析函数，将紧邻的空座位合并成数组

注意由于要求紧邻，此处需要用range子句进行逻辑计算

range between current row and 1 following
复制

select row_num
           , seat
           , collect_list(seat)
                          over (partition by row_num order by seat range between current row and 1 following) seat_list
      from t_seat
      where status = 0
复制

第二步：计算同一排座位中的最大最小值，依此来判断是否靠近过道



select row_num
                         , min(seat) min_seat
                         , max(seat) max_seat
                    from t_seat
                    group by row_num
复制

第三步：按照row_num 关联步骤2 的结果，并进行条件判断

（1）两人紧邻：步骤1中结果size(seat_list) = 2

(2) 判断座位号的最大最小值其中任意一个是否在数组seat_list中，存在则满足条件。即



array_contains(seat_list, min_seat) or array_contains(seat_list, max_seat) 为true
复制

完整的SQL如下：



select t.row_num
     , seat_list[0] start_num
     , seat_list[1] end_num
from (select row_num
           , seat
           , collect_list(seat)
                          over (partition by row_num order by seat range between current row and 1 following) seat_list
      from t_seat
      where status = 0) t
         left join (select row_num
                         , min(seat) min_seat
                         , max(seat) max_seat
                    from t_seat
                    group by row_num) t2
                   on t.row_num = t2.row_num
where size(seat_list) = 2
  and (array_contains(seat_list, min_seat) or array_contains(seat_list, max_seat))
复制

方法3：自关联求解

具体SQL如下：

select a.row_num
     , start_num
     , end_num
from (SELECT a.row_num row_num
           , a.seat    start_num
           , b.seat    end_num
      FROM t_seat a,
           t_seat b
      where a.row_num = b.row_num
        and a.seat + 1 = b.seat
        and a.status = 0
        and b.status = 0) a
         left join
     (select row_num
           , min(seat) min_seat
           , max(seat) max_seat
      from t_seat
      group by row_num) b
     on a.row_num = b.row_num
where greatest(start_num, end_num) = max_seat
   or least(start_num, end_num) = min_seat
复制