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

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

本文是第 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-已售出）

说明：

为了简化问题，假设不存在“过道”（以后有机会再水一篇文章）
为了简化问题，“最优选座”的逻辑是——最优点在（总行数*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 的语法）

模拟代码如下：

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

import numpy as np
import pandas as pd

# 感觉随机数种子 2024 比 2025 最后展示的效果
np.random.seed(2024)

n_rows = 9  # 多少排座位
n_cols = 25  # 多少列座位，为了简化假设每排座位数相同
occupancy_rate = 0.3  # 电影院上座率

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

df = pd.DataFrame(
    {
        "seat_no": [
            f"{r}-{c}" for r in range(1, n_rows + 1) for c in range(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)

这里创建 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 来进行大数据集群的认证。

既然上面数据都写入 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

with open('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 (partition by seat_row order by seat_col asc
        rows between current row and 4 following) as tag
    , collect_set(seat_no) over (partition by seat_row order by seat_col asc 
        rows between current row and 4 following) as seat_plan_array
    , max(seat_row) over () as seat_max_row
    -- 这里队列之所以用 partition by seat_row，不像求最多行 over 后面没有内容，
    -- 其实还是兼容了每排座位数不同的情况，只是没有过于细致的处理
    , max(seat_col) over (partition by 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 (partition by seat_row order by seat_col asc
      rows between current row and 4 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
order by 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='module'>
    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
        }
      }
    });
    
    app.mount('#app');
    </script>
    </body>
    </html>
    """ % (seat_info_str)