【SQL周周练】一句 SQL 如何帮助 5 个人买到电影院最好的座位？

【SQL周周练】一句 SQL 如何帮助 5 个人买到电影院最好的座位？

    Posted on 2025-05-04 08:52 蒋点数分 阅读(145) 评论(0) 收藏 举报

大家好，我是“蒋点数分”，多年以来一直从事数据分析工作。从今天开始，与大家持续分享关于数据分析的学习内容。

本文是第 3 篇，也是【SQL 周周练】系列的第 3 篇。该系列是挑选或自创具有一些难度的 SQL 题目，一周至少更新一篇。后续创作的内容，初步规划的方向包括：

后续内容规划

1.利用 Streamlit 实现 Hive 元数据展示、SQL 编辑器、 结合Docker 沙箱实现数据分析 Agent

2.时间序列异常识别、异动归因算法

3.留存率拟合、预测、建模

4.学习 AB 实验、复杂实验设计等

5.自动化机器学习、自动化特征工程

6.因果推断学习

7. ……

欢迎关注，一起学习。

第 3 期题目

题目来源：改进的题目，增加了电影院最优选座的逻辑

一、题目介绍

看到这个题目，有同学可能会吐槽：你小子拉了，第 3 期就出现常见题目。这里我解释一下，【SQL 周周练】系列的确是想输出一系列我认为有挑战性有意思的题（所谓挑战性是对于大多数数分，SQL 资深者除外）我不想照搬 LeetCode 或牛客的题，更不想写 “学生表” 那类题。

奈何我想象力有限（未来可能增加一个 SQL 破案系列，目前手里有几篇草稿，比如通过行车轨迹计算罪案策划地点）对于市面上的题，如果增加一些创新点，我觉得也值得跟大家分享。比如这道题，我就增加了“最优选座”的逻辑。下面直接说题：

有一张表记录了电影院某个厅某个场次的座位售出情况，假设有 5 个人来买票，请您用 SQL 输出所有可以选择的 5 个连续座位，还要按照一定规则根据座位的位置进行优劣排序。列名如下（这里不显示日期、放映厅名和场次等冗余信息）：

列名

 数据类型

 注释

seat_no

 string

 座位号（格式：行号码-列号码）

is_saled

 int

 是否售出（0-未销售，1-已售出）

说明：

1. 为了简化问题，假设不存在“过道”（以后有机会再"水"一篇文章）

2. 为了简化问题，“最优选座”的逻辑是——最优点在（总行数*0.65，总列数*0.5）所选座位相对于它的“欧式距离”（行方向与列方向的权重比是 3:2）之和最小者

二、题目思路

想要答题的同学，可以先思考答案🤔。

……

……

……

我来谈谈我的思路：这道题题目中的“连续”，可能会让部分数据分析师想起“连续登录”这个经典题型。我在第 1 期文章中提到，这类题型需要构造一个分组标识。但是今天的题目简单的多，“连续登录”类问题之所以要构造分组标识，是因为我们没办法确定窗口范围。如果这道题是求最多有多少个连续空座，那套路是一样的。

可是既然是求 5 个或者特定个连续空座，那么问题大大简化了。我们的窗口范围是固定的 5 行就行了，你这 5 行从哪个位置开始都可以写。我就从当前行开始算，也就是 order by seat_col asc rows between current row and 4 following。

下面，我在 Python 中生成模拟的数据集。相对于前两期，这期模拟数据简单得多：

三、生成模拟数据

只关心 SQL 代码的同学，可以跳转到第四节（我在工作中使用 Hive 较多，因此采用 Hive 的语法）

模拟代码如下：

1. 定义模拟逻辑需要的常量，多少排多少列的座位。为了简化问题，这里就模拟一个长方形的普通厅，没有过道，每排座位数一致：

import numpy as np

import pandas as pd

# 感觉随机数种子 2024 比 2025 最后展示的效果

np.random.seed(2024)

n_rows = 9 # 多少排座位

n_cols = 25 # 多少列座位，为了简化假设每排座位数相同

occupancy_rate = 0.3 # 电影院上座率

2. 生成 pd.DataFrame。这里强调一点，上座率不能当作 0-1 分布的概率，不能用 0-1 分布抽样来模拟座位售出情况；而是应该用随机抽指定数量的座位，即用频率的方式来处理：

df = pd.DataFrame(

    {

        "seat_no": [

            f"{r}-{c}"for r inrange(1, n_rows + 1) for c inrange(1, n_cols + 1)

        ],

        "is_saled": np.zeros(n_rows * n_cols, dtype=int),

    }

)

# 根据上座率随机抽样指定个数座位改为售出状态

# 注意：我不是把上座率当成 0-1 分布的概率

# 而是当成“频率"，抽取实际频率对应的已售出座位数量

saled_index = np.random.choice(

    df.index, size=int(occupancy_rate * df.shape[0]), replace=False

)

df.loc[saled_index, "is_saled"] = 1# 0 表示座位未售出，1表示已售出

# 在 Jupyer 环境中展示数据框

# 如果在其他环境执行，可能报错

display(df)

3. 这里创建 Hive表，并将数据写入。与前两期不同，之前我都是将pd.DataFrame采用to_csv转为csv文件；然后用pyHive在Hive中建好表，再使用load data local inpath的方法导入数据。而这一次，我采用CTAS的方式来建表并写入数据，也就是create table ... as select...；但是这种方法有缺点，比如无法在建表时增加表和列的备注。因此我使用alter table 语句来增加备注。

关于 alter table 语句的使用格式和官方文档，我已经在代码注释中说明：

from pyhive import hive

# 配置连接参数

host_ip = "127.0.0.1"

port = 10000

username = "蒋点数分"

hive_table_name = 'data_exercise.dwd_cinema_seat_sales_status'

# '1-1' 必须用引号括起来，否则在 sql 中被当成 1-1 的数学表达式

create_table_and_write_data_sql = f'''

create table {hive_table_name} as 

select stack({df.shape[0]},

{','.join([f"'{row[0]}',{row[1]}" for row in df.values])}

) as (seat_no, is_saled)

'''

drop_table_sql = f'''

drop table if exists {hive_table_name}

'''

with hive.Connection(host=host_ip, port=port) as conn:

    cursor = conn.cursor()

    print(f'\n执行删除表语句：\n{drop_table_sql}')

    # 如果该表已存在，则 drop

    cursor.execute(drop_table_sql)

    # 创建表并写入数据

    print(f'\n采用 `CTAS` 建表并写入数据：\n{create_table_and_write_data_sql}')

    cursor.execute(create_table_and_write_data_sql)

    # `CTAS` 不能在创建时添加备注，使用 `alter` 语句增加备注

    # 官方文档

    # https://hive.apache.org/docs/latest/languagemanual-ddl_27362034/#alter-table-comment

    # ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);

    cursor.execute(f'''

    alter table {hive_table_name} set tblproperties ('comment' = 

        '电影院连续选座 | author：蒋点数分 | 文章编号：7b68c66c')

    ''')

    # 增加列备注，根据官方文档

    # https://hive.apache.org/docs/latest/languagemanual-ddl_27362034/#alter-column

    # ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type

      # [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];

    # 没有打方括号的部分是必须写的，也就是哪怕你不更改列名，不更改数据类型，也要写上新旧列名和数据类型

    cursor.execute(f'''

    alter table {hive_table_name} change seat_no seat_no string comment '座位编号'

    ''')

    cursor.execute(f'''

    -- 如果尝试将 `is_saled` 改为 `tinyint` 会报错，只能往更大的整型修改

    alter table {hive_table_name} change is_saled is_saled int comment '是否已售出'

    ''')

    cursor.execute(f'''

    desc formatted {hive_table_name}

    ''')

    records = cursor.fetchall()

    for r in records:

        print(r)

        

    cursor.close()

在写入数据时，我在 select 语句中使用了 stack 函数，它是表生成函数。如果你之前没有在 Hive 中使用过这个函数，你可以搜搜它的用法。数据比较简单的时候，我就用它来配合 CTAS 写入数据。

我通过使用 PyHive 包实现 Python 操作 Hive。我个人电脑部署了 Hadoop 及 Hive，但是没有开启认证，企业里一般常用 Kerberos 来进行大数据集群的认证。

 

4. 既然上面数据都写入 Hive 了，那么我这里又贴一段代码，是干什么呢？这段代码是用来做可视化的，将模拟生成的数据利用函数写入网页（以前在数分工作中简单的使用过 Vue）getCinemaHtml 函数其实就是一个格式化字符串，根据参数返回完整的字符串。完整字符串就是一个简单的网页，里面使用 CDN 方法引入了 Vue3，作为初学者，我这里没有使用前端构建等方法来做：

# 构造特定格式 dict 给网页画图提供数据

seats_info_list = []

for i in df.groupby(by=df["seat_no"].apply(lambda s: s.split("-")[0])):

    d = {"seat_row_no": int(i[0])}

    d["seat_col_arr"] = i[1]["seat_no"].apply(lambda s: int(s.split("-")[1])).to_list()

    d["is_saled_arr"] = i[1]["is_saled"].to_list()

    seats_info_list.append(d)

print(seats_info_list)

# 外部的自定义函数

from cinema_seats_html import getCinamaHtml

withopen('cinema_seats.html', 'w') as f:

    '''

    将 DataFrame 的数据处理为特定格式，在作为字符串写入

    html 页面的 script 标签中；让 Javascript 将其作为

    一个对象

    '''

    html_str = getCinemaHtml(seats_info_list)

    f.write(html_str)

 

、

四、SQL 解答

我先将 seat_no 切开，这样行号码和列号码后面写着方便。计算连续 5 个空座位，为什么要 sum(if(is_saled=0, 1, 0)) = 5 而不是 sum(is_saled) = 0，因为 rows between ... 4 following，在扫到该分组最后 4 行时，此时窗口的实际长度已经不是 5 个了，因为后面没有数据了。用 sum(is_saled) = 0 需要增加额外的逻辑。用 3*abs(seat_row-0.65*seat_max_row)+2*abs(seat_col-0.5*seat_max_col_current_row 来处理我自定义的“欧式距离”，这里行方向和列方向的权重是 3:2；最后筛选 5 个连续空座的标志，将“欧式距离”升序排列，并且将连续座位的显示格式调整一下即可。

 

-- 求连续 5 个的空座位

with simple_processing_table as (

    -- 表名根据“有道翻译”取的，就是简单处理一下

    -- 将行号和列号单独拿出来，后面写着方便一点点；不处理也可以

    select

      seat_no

    , int(split(seat_no, '-')[0]) as seat_row

    , int(split(seat_no, '-')[1]) as seat_col

    , is_saled

    from data_exercise.dwd_cinema_seat_sales_status

)

, calc_5_continuous_seats_table as (

   -- 计算连续 5 个空座位，为什么要 sum(if(is_saled=0, 1, 0)) = 5 而不是

   -- sum(is_saled) = 0，因为 rows between ... 4 following，在扫到该分组最后 4 行时

   -- 此时窗口的实际长度已经不是 5 个了，因为后面没有数据了。用 sum(is_saled) = 0

   -- 需要增加额外的逻辑

    select

      seat_no, seat_row, seat_col

    , sum(if(is_saled=0, 1, 0)) over (partitionby seat_row orderby seat_col asc

        rowsbetweencurrentrowand4 following) as tag

    , collect_set(seat_no) over (partitionby seat_row orderby seat_col asc

        rowsbetweencurrentrowand4 following) as seat_plan_array

    , max(seat_row) over () as seat_max_row

    -- 这里队列之所以用 partition by seat_row，不像求最多行 over 后面没有内容，

    -- 其实还是兼容了每排座位数不同的情况，只是没有过于细致的处理

    , max(seat_col) over (partitionby seat_row) as seat_max_col_current_row

    from simple_processing_table

) 

, calc_euclidean_distance_table as (

    -- 计算欧式距离和将座位汇总，依旧是有道翻译，取名太难了

    select

      seat_no as start_seat_no

      -- 注意加 4

    , concat(seat_row, '-', seat_col, '~', seat_row, '-',seat_col+4) as seat_plan

    , seat_max_row

    , seat_max_col_current_row

    , seat_plan_array

      -- 注意到每排最后 4 个的时候，实际可不是 5 个距离之和了；只不过后面会被条件 tag=5 筛掉

    , sum(

        3*abs(seat_row -0.65* seat_max_row) +2*abs(seat_col -0.5* seat_max_col_current_row)

      ) over (partitionby seat_row orderby seat_col asc

      rowsbetweencurrentrowand4 following) as a_distance

    , tag 

    from calc_5_continuous_seats_table

)

select

  start_seat_no

, seat_plan

, seat_plan_array

, a_distance

, seat_max_row

, seat_max_col_current_row

from calc_euclidean_distance_table

where tag =5-- 窗口函数卡了 5 行，不可能超过 5

orderby a_distance asc

查询结果如下：

咱们跟网页示意图核对核对（在 WPS 演示中加工一下展示），绿色区域是最好的选择，红色区域就是最差的选择：

以下是返回网页的 Python 函数，初学者采用 CDN 方法引入的 Vue3，未使用前端构建工具，也没使用 Flask 或 Fastapi 前后端分离。作为一名数学，接触 Vue 不久，还请懂前端的大佬放过😃：

def getCinemaHtml(seat_info_str):

    return """

    <!DOCTYPE html>

    <html lang="zh-CN">

    <head>

        <meta charset='UTF-8'>

        <meta name="viewport" content="width=device-width, initial-scale=1.0" />

        <title>电影院选座 demo</title>

        <link rel="stylesheet" href="https://lf6-cdn-tos.bytecdntp.com/cdn/expire-10-y/font-awesome/5.15.4/css/all.min.css" />

        <style>

          #app{

           overflow: visible;

          }

          .row {

            display: flex;

            align-items: center;

          }

          .seat {

            margin: 0 2px 2px 0;

            color: #DFDFDF;

            width: 30px;

            height: 50px;

            justify-content: center;

          }

          .seat.isSaled {

            color: #07c160;

          }

          .seat.isSaled:after {

            content: '✓';

            font-size: 14px;

            color: #000000;

            position: relative;

            font-weight: 800;

            left: 7px;

            bottom: 42px;

          }

          .series {

            margin-left: 10px;

            white-space: nowrap;

          }

          .seat_no {

            font-size: 12px;

            font-weight: 600;

            white-space: nowrap;

          }

          .title {

            position: relative;

            left: calc(10 * 30px);

            margin-bottom: 16px;

            font-size: 16px;

          }

        </style>

    </head>

    <body>

        <div id='app'>

            <div class="title">电影院连续选座 示意图 demo</div>

            <div class="row" v-for="(item, index) in seats_info" :key="item.seat_row_no">

                <div :class="{seat:true, isSaled:item.is_saled_arr[i]}" 

                    v-for="(s, i) in item.seat_col_arr">

                    <i class="fas fa-chair" style="font-size:30px;"></i>

                    <div class="seat_no">{{ `${item.seat_row_no}-${s}` }}</div>

                </div>

                <div class="series">{{ `第 ${item.seat_row_no} 排`}}</div>

            </div>

        </div>

    <script type='mo

dule'>

    import { createApp, reactive } from 'https://unpkg.zhimg.com/vue@3.5.13/dist/vue.esm-browser.js';

    

    const app = createApp({

      setup(){

        // 定义一个 message

        const seats_info = reactive(

            %s

        );

        return {

          seats_info

        }