大家好，我是“蒋点数分”，多年以来一直从事数据分析工作。从今天开始，与大家持续分享关于数据分析的学习内容。
本文是第 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语句来增加备注。
更正：我查了 Hive 的文档：https://hive.apache.org/docs/latest/languagemanual-ddl_273620... 里面提到了：The CREATE part of the CTAS takes the resulting schema from the SELECT part and creates the target table with other table properties such as the SerDe and storage format. 意思大概是，从 SELECT 的结果拿走 schema （大概就是列的定义）但是 other table properties 可以自己定义，包括SerDe和Storage format，表级别的备注也是可以在CTAS中直接定义的
关于 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)😃😃😃
我现在正在求职数据类工作（主要是数据分析或数据科学）；如果您有合适的机会，恳请您与我联系，即时到岗，不限城市。您可以发送私信或通过公众号联系我（全网同名：蒋点数分）。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。