【SQL周周练】一句 SQL 如何帮助 5 个人买到电影院最好的座位？
<section><p>大家好，我是“蒋点数分”，多年以来一直从事数据分析工作。从今天开始，与大家持续分享关于数据分析的学习内容。</p>
<p>本文是第 3 篇，也是【SQL 周周练】系列的第 3 篇。该系列是挑选或自创具有一些难度的 SQL 题目，一周至少更新一篇。后续创作的内容，初步规划的方向包括：</p>
<h2 data-heading="true">后续内容规划</h2>
<p>1.利用 <strong>Streamlit</strong> 实现 <code>Hive 元数据展示</code>、<code>SQL 编辑器</code>、 结合<code>Docker 沙箱实现数据分析 Agent</code><br>2.时间序列异常识别、异动归因算法<br>3.留存率拟合、预测、建模<br>4.学习 <code>AB 实验</code>、复杂实验设计等<br>5.<code>自动化机器学习</code>、自动化特征工程<br>6.<code>因果推断</code>学习<br>7. ……</p>
<p><strong>欢迎关注</strong>，一起学习。</p>
<h2 data-heading="true">第 3 期题目</h2>
<p>题目来源：改进的题目，增加了电影院最优选座的逻辑</p>
<h3 data-heading="true">一、题目介绍</h3>
<p>看到这个题目，有同学可能会吐槽：你小子拉了，第 3 期就出现常见题目。这里我解释一下，【<strong>SQL 周周练</strong>】系列的确是想输出一系列我认为有挑战性有意思的题（所谓挑战性是对于大多数数分，SQL 资深者除外）我不想照搬 LeetCode 或牛客的题，更不想写 “学生表” 那类题。<br><br></p>
<p>奈何我想象力有限（未来可能增加一个 SQL 破案系列，目前手里有几篇草稿，比如通过行车轨迹计算罪案策划地点）对于市面上的题，如果增加一些创新点，我觉得也值得跟大家分享。比如这道题，我就增加了“最优选座”的逻辑。下面直接说题：</p>
<p>有一张表记录了电影院某个厅某个场次的座位售出情况，假设有 5 个人来买票，请您用 SQL 输出所有可以选择的 5 个连续座位，还要按照一定规则根据座位的位置进行优劣排序。列名如下（这里不显示日期、放映厅名和场次等冗余信息）：</p>
<section>
<table>
<thead>
<tr>
<td>
<section>列名</section>
</td>
<td>
<section>数据类型</section>
</td>
<td>
<section>注释</section>
</td>
</tr>
</thead>
<tbody>
<tr>
<td>
<section>seat_no</section>
</td>
<td>
<section>string</section>
</td>
<td>
<section>座位号（格式：行号码-列号码）</section>
</td>
</tr>
<tr>
<td>
<section>is_saled</section>
</td>
<td>
<section>int</section>
</td>
<td>
<section>是否售出（0-未销售，1-已售出）</section>
</td>
</tr>
</tbody>
</table>
</section>
<p>说明：</p>
<ol class="list-paddingleft-1">
<li>
<section>1. 为了简化问题，假设不存在“过道”（以后有机会再"水"一篇文章）</section>
</li>
<li>
<section>2. 为了简化问题，“最优选座”的逻辑是——最优点在（总行数*0.65，总列数*0.5）所选座位相对于它的“欧式距离”<span data-pm-slice="0 0 []">（行方向与列方向的权重比是 3:2）</span>之和最小者</section>
</li>
</ol>
<h3 data-heading="true">二、题目思路</h3>
<p>想要答题的同学，可以先思考答案🤔。<br>……</p>
<p>……</p>
<p>……</p>
<p>我来谈谈我的思路：这道题题目中的“连续”，可能会让部分数据分析师想起“连续登录”这个经典题型。我在第 1 期文章中提到，这类题型需要构造一个分组标识。但是今天的题目简单的多，“连续登录”类问题之所以要构造分组标识，是因为我们没办法确定窗口范围。如果这道题是求最多有多少个连续空座，那套路是一样的。</p>
<p>可是既然是求 5 个或者特定个连续空座，那么问题大大简化了。我们的窗口范围是固定的 5 行就行了，你这 5 行从哪个位置开始都可以写。我就从当前行开始算，也就是 <code>order by seat_col asc rows between current row and 4 following</code>。</p>
<p>下面，我在 <code>Python</code> 中生成模拟的数据集。相对于前两期，这期模拟数据简单得多：</p>
<h3 data-heading="true">三、生成模拟数据</h3>
<p>只关心 SQL 代码的同学，可以跳转到第四节（我在工作中使用 <code>Hive</code> 较多，因此采用 <code>Hive</code> 的语法）</p>
<p>模拟代码如下：</p>
<section>1. 定义模拟逻辑需要的<code>常量</code>，多少排多少列的座位。为了简化问题，这里就模拟一个长方形的普通厅，没有过道，每排座位数一致：</section>
<pre class="highlighter-prismjs prismjs-lines-highlighted language-python" tabindex="0" data-dark-theme="true"><code>import numpy as np
import pandas as pd
# 感觉随机数种子 2024 比 2025 最后展示的效果
np.random.seed(2024)
n_rows = 9# 多少排座位
n_cols = 25# 多少列座位，为了简化假设每排座位数相同
occupancy_rate = 0.3# 电影院上座率</code></pre>
<section></section>
<section>2. 生成 <code>pd.DataFrame</code>。这里强调一点，上座率不能当作 <code>0-1 分布</code>的概率，不能用 <code>0-1 分布</code>抽样来模拟座位售出情况；而是应该用随机抽指定数量的座位，即用<strong>频率</strong>的方式来处理：</section>
<pre class="highlighter-prismjs language-python prismjs-lines-highlighted" tabindex="0" data-dark-theme="true"><code>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), replace=False
)
df.loc = 1# 0 表示座位未售出，1表示已售出
# 在 Jupyer 环境中展示数据框
# 如果在其他环境执行，可能报错
display(df)</code></pre>
<section></section>
<section>3. 这里创建 <code>Hive</code>表，并将数据写入。与前两期不同，之前我都是将<code>pd.DataFrame</code>采用<code>to_csv</code>转为<code>csv</code>文件；然后用<code>pyHive</code>在<code>Hive</code>中建好表，再使用<code>load data local inpath</code>的方法导入数据。而这一次，我采用<code>CTAS</code>的方式来建表并写入数据，也就是<code>create table ... as select...</code>；但是这种方法有缺点，比如无法在建表时增加表和列的备注。因此我使用<code>alter table</code> 语句来增加备注。</section>
<p>关于 <code>alter table</code> 语句的使用格式和官方文档，我已经在代码注释中说明：</p>
<pre class="highlighter-prismjs prismjs-lines-highlighted language-python" tabindex="0" data-dark-theme="true"><code>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},
{','.join(}',{row}" 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 CHANGE col_old_name col_new_name column_type
# ;
# 没有打方括号的部分是必须写的，也就是哪怕你不更改列名，不更改数据类型，也要写上新旧列名和数据类型
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()</code></pre>
<section></section>
<section><img src="https://img2024.cnblogs.com/blog/3640949/202504/3640949-20250430075944488-333474640.png" alt="" height="580" width="900">
<p id="1745971185377"></p>
</section>
<section></section>
<p>在写入数据时，我在 <code>select</code> 语句中使用了 <code>stack</code> 函数，它是表生成函数。如果你之前没有在 <code>Hive</code> 中使用过这个函数，你可以搜搜它的用法。数据比较简单的时候，我就用它来配合 <code>CTAS</code> 写入数据。</p>
<blockquote>
<p>我通过使用 <code>PyHive</code> 包实现 Python 操作 <code>Hive</code>。我个人电脑部署了 <code>Hadoop</code> 及 <code>Hive</code>，但是没有开启认证，企业里一般常用 <code>Kerberos</code> 来进行大数据集群的认证。</p>
</blockquote>
<p> </p>
<p>4. 既然上面数据都写入 <code>Hive</code> 了，那么我这里又贴一段代码，是干什么呢？这段代码是用来做可视化的，将模拟生成的数据利用函数写入网页（以前在数分工作中简单的使用过 <code>Vue</code>）<code>getCinemaHtml</code> 函数其实就是一个格式化字符串，根据参数返回完整的字符串。完整字符串就是一个简单的网页，里面使用 CDN 方法引入了 <code>Vue3</code>，作为初学者，我这里没有使用前端构建等方法来做：</p>
<section></section>
<section><img src="https://img2024.cnblogs.com/blog/3640949/202504/3640949-20250430080033453-195967952.png" alt="" height="732" width="1112"></section>
<section></section>
<section></section>
<pre class="highlighter-prismjs prismjs-lines-highlighted language-python" tabindex="0" data-dark-theme="true"><code># 构造特定格式 dict 给网页画图提供数据
seats_info_list = []
for i in df.groupby(by=df["seat_no"].apply(lambda s: s.split("-"))):
d = {"seat_row_no": int(i)}
d["seat_col_arr"] = i["seat_no"].apply(lambda s: int(s.split("-"))).to_list()
d["is_saled_arr"] = i["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)</code></pre>
</section>
<section>
<figure></figure>
</section>
<h3 data-pm-slice="0 0 []">四、SQL 解答</h3>
<section>
<p>我先将 <code>seat_no</code> 切开，这样行号码和列号码后面写着方便。计算连续 5 个空座位，为什么要 <code>sum(if(is_saled=0, 1, 0)) = 5</code> 而不是 <code>sum(is_saled) = 0</code>，因为 <code>rows between ... 4 following</code>，在扫到该分组最后 4 行时，此时窗口的实际长度已经不是 5 个了，因为后面没有数据了。<code>用 sum(is_saled) = 0</code> 需要增加额外的逻辑。用 <code>3*abs(seat_row-0.65*seat_max_row)+2*abs(seat_col-0.5*seat_max_col_current_row</code> 来处理我自定义的“欧式距离”，这里行方向和列方向的权重是 3:2；最后筛选 5 个连续空座的标志，将“欧式距离”升序排列，并且将连续座位的显示格式调整一下即可。</p>
</section>
<p> </p>
<section><span data-pm-slice="0 0 []"><br></span>
<pre class="highlighter-prismjs language-sql prismjs-lines-highlighted" tabindex="0" data-dark-theme="true"><code>-- 求连续 5 个的空座位
with simple_processing_table as (
-- 表名根据“有道翻译”取的，就是简单处理一下
-- 将行号和列号单独拿出来，后面写着方便一点点；不处理也可以
select
seat_no
, int(split(seat_no, '-')) as seat_row
, int(split(seat_no, '-')) 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</code></pre>
<p>查询结果如下：</p>
<section><img src="https://img2024.cnblogs.com/blog/3640949/202504/3640949-20250430080125618-295329761.png" alt="" height="809" width="1186">
<p id="1745971286134"></p>
</section>
<p>咱们跟网页示意图核对核对（在 WPS 演示中加工一下展示），绿色区域是最好的选择，红色区域就是最差的选择：</p>
<section></section>
<section><img src="https://img2024.cnblogs.com/blog/3640949/202504/3640949-20250430080138585-1669680149.png" alt="" height="707" width="1412"></section>
<section></section>
<figure>以下是返回网页的 <code>Python</code> 函数，初学者采用 <code>CDN</code> 方法引入的 <code>Vue3</code>，未使用前端构建工具，也没使用 <code>Flask</code> 或 <code>Fastapi</code> 前后端分离。作为一名数学，接触 <code>Vue</code> 不久，还请懂前端的大佬放过😃：</figure>
<pre class="highlighter-prismjs prismjs-lines-highlighted language-python" tabindex="0" data-dark-theme="true"><code>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}"
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)</code></pre>
<hr>
<p>😃😃😃<br><strong>我现在正在求职数据类工作</strong>（主要是数据分析或数据科学）；如果您有合适的机会，恳请您与我联系，即时到岗，不限城市。</p>
</section><br><br>
来源：https://www.cnblogs.com/data-analytics/p/18854781
頁:
[1]