大家好，我是“蒋点数分”，多年以来一直从事数据分析工作。从今天开始，与大家持续分享关于数据分析的学习内容。
本文是第 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 个连续座位，还要按照一定规则根据座位的位置进行优劣排序。列名如下（这里不显示日期、放映厅名和场次等冗余信息）：
|
| ||
说明：
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 的语法）
模拟代码如下：
常量，多少排多少列的座位。为了简化问题，这里就模拟一个长方形的普通厅，没有过道，每排座位数一致：import numpy as npimport 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 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)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 = 10000username = "蒋点数分"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 getCinamaHtmlwithopen('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_rowfrom calc_euclidean_distance_tablewhere tag =5-- 窗口函数卡了 5 行，不可能超过 5orderby 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)😃😃😃
我现在正在求职数据类工作（主要是数据分析或数据科学）；如果您有合适的机会，恳请您与我联系，即时到岗，不限城市。
