【SQL 周周练】爬取短视频发现数据缺失，如何用 SQL 填充

苏锦广 發表於 2025-5-11 10:10:00

【SQL 周周练】爬取短视频发现数据缺失，如何用 SQL 填充

大家好，我是“蒋点数分”，多年以来一直从事数据分析工作。从今天开始，与大家持续分享关于数据分析的学习内容。
本文是第 5 篇，也是【SQL 周周练】系列的第 4 篇。该系列是挑选或自创具有一些难度的 SQL 题目，一周至少更新一篇。后续创作的内容，初步规划的方向包括：
<h2 id="后续内容规划">后续内容规划</h2>
1.利用 Streamlit 实现 <code>Hive 元数据展示</code>、<code>SQL 编辑器</code>、结合<code>Docker 沙箱实现数据分析 Agent</code> 
2.时间序列异常识别、异动归因算法 
3.留存率拟合、预测、建模 
4.学习 <code>AB 实验</code>、复杂实验设计等 
5.<code>自动化机器学习</code>、自动化特征工程 
6.<code>因果推断</code>学习 
7. ……
欢迎关注，一起学习。
<h2 id="第-4-期题目">第 4 期题目</h2>
题目来源：自创题目，曾经在工作中遇到过该问题
<h3 id="一题目介绍">一、题目介绍</h3>
公司市场部找到一些达人在抖音、快手等平台进行短视频营销，需要监测视频的点赞量。公司内有一位专职的爬虫工程师，他的项目也很多。因此很难对该项目爬虫数据提供高质量的维护，会出现一些字段缺失的情况。
我们将问题简化，有一张表记录了爬虫抓取的短视频点赞量数据，其中部分日期的点赞量是缺失的。请你利用 SQL 将这些数据补齐，即“插值”。
<table>
<thead>
<tr>
<th>列名</th>
<th>数据类型</th>
<th>注释</th>
</tr>
</thead>
<tbody>
<tr>
<td>video_id</td>
<td>string</td>
<td>短视频id</td>
</tr>
<tr>
<td>dt</td>
<td>string</td>
<td>日期</td>
</tr>
<tr>
<td>likes_num</td>
<td>int</td>
<td>点赞量（用来对比结果，不要直接用）</td>
</tr>
<tr>
<td>show_likes_num</td>
<td>int</td>
<td>展示点赞量（用来补全数据）</td>
</tr>
</tbody>
</table>
用 SQL 实现几种比较简单的插值方法，复杂的方法可以利用 <code>Hive</code> 中的 <code>transform</code> 函数调用 <code>Python</code> 脚本来实现（后面哪期会根据这个点水一篇文章）
本文实现的简单补全方法有：
1.前向填充，使用前面最近的一个非空值来填充 
2.后向填充，使用后面最近的一个非空值来填充 
3.相邻的平均数填充，使用前后最近的非空值，取两个数的平均数填充 
4.相邻的分位数填充，使用前后最近的非空值，缺失值根据分位数来填充
额外说明：这四种方法都依赖于缺失值邻近的前后非空值，需要存在这样的非空值。 
如果该非空值不存在，比如短视频第一天发布就没有爬取到点赞量 —— 这样没有办法，找到它之前的非空点赞量。我本文的处理方法是将它“视为”前一天发布，或者说增加一个前一条点赞量为零的数据（还有其他的处理方法，我这里只提出一种）。
这条增加的数据不需要显式存在，只不过是在数据处理时兜底的逻辑等效于它。而如果短视频缺少的是最后几天的数据，比如某一天开始后面一直缺失数据，这样就将最后一个有数据的点赞量“顺延”下去。这 4 种填充方法，都用这样的逻辑兜底。
<h3 id="二题目思路">二、题目思路</h3>
想要答题的同学，可以先思考答案🤔。 
.……
.……
.……
我来谈谈我的思路： 
1.前向填充，使用前面最近的非空值来填充。使用 <code>last_value</code> 窗口函数来实现，注意 <code>last_value</code> 支持两个参数，其中第二个参数设置为 <code>true</code> 则在寻找的时候跳过 <code>null</code>；注意 <code>rows</code> 的范围，另外如果前面实在找不到非 <code>null</code> 值，用 <code>0</code> 来兜底。
2.后向填充，使用后面最近的非空值来填充。使用 <code>first_value</code> 窗口函数来实现，同样 <code>first_value</code> 也是支持两个参数，其中第二个参数设置为 <code>true</code> 则在寻找的时候跳过 <code>null</code>；这个 <code>rows</code> 的范围更要注意。如果后面实在找不到非 <code>null</code> 值，用前一个非 <code>null</code> 值兜底。所以这里要同时往前往后查找。
3.相邻的平均数填充，融合了前两种方法，前向和后向数据都要寻找，找到后求平均值，这里要更加小心的处理找不到的情况。
4.相邻的分位数填充，是上一种方法的改进。比如 2 个有效的点赞量中间缺少了 3 天的数据，如果这 3 天的数据都用这 2 个有效值的平均值来填充，则相当于这几天的点赞数没有变化，这逻辑不太现实。
采用分位数的方法保持线性增长的关系去填充，比上一种方法更好。注意如果真的是这 2 个有效点赞量数据一致，也就是假设这几天点赞量数据停止变化。平均数和分位数填充，计算的结果是能“兼容”这种情况。
下面，我用 <code>NumPy</code> 和 <code>Scipy</code> 生成模拟的数据集：
<h3 id="三生成模拟数据">三、生成模拟数据</h3>
只关心 SQL 代码的同学，可以跳转到第四节（我在工作中使用 <code>Hive</code> 较多，因此采用 <code>Hive</code> 的语法）
模拟代码如下：
<ol>
<li>定义模拟逻辑需要的<code>常量</code>，定义随机数发生器：</li>
</ol>
<pre><code class="language-python">import numpy as np
from scipy import stats
import pandas as pd
import datetime

SEED = 2025
rng = np.random.default_rng(SEED)

# 开始日期
START_DATETIME = datetime.datetime(2025, 5, 1)
# 短视频数量
VIDEO_NUM = 100
# 一般最高点赞量
GENERAL_HIGHEST_LIKES_NUM = 1_000_000
# 一般最低点赞量
GENERAL_LOWEST_LIKES_NUM = 1
</code></pre>
<ol start="2">
<li>使用 <code>Gompertz</code> 函数模拟短视频点赞量每日变化。大家一般都知道用 <code>S 型曲线</code>模拟这类增长但有上限的数据，最常见的就是 <code>Logistic</code> 函数。我这里用 <code>Gompertz</code> 函数纯粹是以前没用过，尝尝鲜。工作中肯定是用这两个函数的拟合效果来对比。网上能搜到大量文章从数学角度对两者进行对比。我这里偷懒就不研究了，大家可以自行搜索：</li>
</ol>
<pre><code class="language-python"># 参考正态分布 3-sigma，转换到对数正态分布的范围
sigma = np.log(GENERAL_HIGHEST_LIKES_NUM / GENERAL_LOWEST_LIKES_NUM) / 6

# 如果在 JupyterLab 中分为不同单元格执行，
# 必须重置随机数生成器，否则不能复现同样结果
# rng = np.random.default_rng(SEED)
K_arr = stats.lognorm.rvs(
s=sigma,
loc=0,
scale=np.sqrt(GENERAL_HIGHEST_LIKES_NUM * GENERAL_LOWEST_LIKES_NUM),
size=VIDEO_NUM,
random_state=rng,
)
K_arr = np.round(K_arr, 0)

# 参数 a 随机生成
a_arr = rng.uniform(low=0.85, high=0.95, size=VIDEO_NUM)
# 参数 b 随机生成
b_arr = rng.uniform(low=0.75, high=0.85, size=VIDEO_NUM)
# 参数 t0 随机生成
t0_arr = rng.choice(, size=VIDEO_NUM, replace=True, p=)

# 定义 Gompertz 函数，为什么不选常见的 Logistic 函数
# 纯粹为了多尝试尝试以前没玩过的
def gompertz_func(t, K=100000, a=0.9, b=0.8, t0=10):
return K * np.power(a, np.power(b, t-t0))
</code></pre>
<code>Gompertz</code> 函数其中的 \(K\) 代表极限值，在我模拟的场景就是各个短视频点赞的上限。因此我采用对数正态分布模拟这个 \(K\)，我在第 1 节的代码中定义了一般最高/低的点赞量，其实就是借鉴正态分布的 3-sigma 方法。只不过 \(\ln(x)\) 服从正态分布，取 \([\mu - 3\sigma, \mu + 3\sigma]\) ；那么 \(x\) 就应该取 \(\)，我们将其分别记为 \(a\) 和 \(b\)，则 \(\sigma = \frac{\ln{b} - \ln{a}}{6}\)，而 \(\mu = \frac{\ln{a} + \ln{b}}{2}\)。
在 <code>scipy.stats.lognorm</code> 中，<code>s</code> 是形状参数，对应正态分布的标准差 \(\sigma\)。<code>scale</code> 是尺度参数，对应正态分布的指数均值 \(e^{\mu}\)。根据上面的推导结果。\(\text{s} = \frac{\ln{b}-\ln{a}}{6}\)，\(\text{scale} = \sqrt{ab}\)。这就是我代码中对数正态分布参数制定的逻辑。
根据 <code>Gompertz</code> 函数的定义，参数 \(a\) 和 \(b\) 都是在 \((0, 1)\) 之间，我自己手动测试绘图，觉得 \(a\) 在 0.9 附近，\(b\) 在 0.8 附近，函数形状比较靠谱。实际工作中，是应该用数据来拟合去推算参数的取值
<ol start="3">
<li>定义随机缺失数据的标识，注意点赞量是整数，四舍五入后转为整数。将前面生成的数据转为 <code>pd.DataFrame</code>，并输出为 <code>csv</code> 文件：</li>
</ol>
<pre><code class="language-python">days = np.arange(1, 31)

# 定义随机缺失；1 - 表示缺失数据
random_missing_flag = rng.choice(, size=VIDEO_NUM * len(days), p=)

video_data = { 'video_id': [], 'dt': [], 'likes_num': [], 'random_miss': random_missing_flag}

for i, (k, a, b, t0) in enumerate(zip(K_arr, a_arr, b_arr, t0_arr)):
# 视频 id 数字部分不低于 3 位，补零
video_data['video_id'].extend(*len(days))
video_data['dt'].extend(days)
video_data['likes_num'].extend(gompertz_func(days, k, a, b, t0))

df = pd.DataFrame(video_data)

# 四舍五入并转为整型
df['likes_num'] = np.round(df['likes_num'], 0).astype(int)
df['dt'] = df['dt'].apply(lambda d: START_DATETIME + datetime.timedelta(days=(d-1)))
df['show_likes_num'] = df.apply(lambda r: np.nan if r['random_miss'] == 1 else r['likes_num'], axis = 1)
df.drop(['random_miss'], axis=1, inplace=True)

out_csv_path = "dwd_short_videos_likes_num_missing_data_from_crawler.csv"
df.to_csv(out_csv_path, header=False, index=False, encoding='utf-8-sig')
</code></pre>
<ol start="4">
<li>如果表存在则删除，创建新的 <code>Hive</code> 表，并将数据 <code>load</code> 到表中：</li>
</ol>
<pre><code class="language-python">from pyhive import hive

# 配置连接参数
host_ip = "127.0.0.1"
port = 10000
username = "蒋点数分"

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

hive_table_name = "data_exercise.dwd_short_videos_likes_num_missing_data_from_crawler"

drop_table_sql = f"""
drop table if exists {hive_table_name}
"""

print(drop_table_sql)
cursor.execute(drop_table_sql)

create_table_sql = f"""
create table if not exists {hive_table_name} (
 video_id string comment "短视频id",
 dt string comment "日期",
 likes_num int comment "点赞量（用来对比结果）",
 show_likes_num int comment "展示点赞量（用来补全数据）"
)
comment "短视频点赞量缺失数据，用来练习 SQL 补全数据 | author: 蒋点数分 | 文章编号：0a94d809"
row format delimited fields terminated by ","
stored as textfile
"""

print(create_table_sql)
cursor.execute(create_table_sql)

import os

load_data_sql = f"""
load data local inpath "{os.path.abspath(out_csv_path)}"
overwrite into table {hive_table_name}
"""

print(load_data_sql)
cursor.execute(load_data_sql)

cursor.close()
</code></pre>
<blockquote>
我通过使用 <code>PyHive</code> 包实现 Python 操作 <code>Hive</code>。我个人电脑部署了 <code>Hadoop</code> 及 <code>Hive</code>，但是没有开启认证，企业里一般常用 <code>Kerberos</code> 来进行大数据集群的认证。
</blockquote>
<h3 id="四sql-解答">四、SQL 解答</h3>
1.前向填充的 sql 语句，如果使用 <code>last_value</code> 则 <code>rows</code> 的范围是 <code>between unbounded preceding and 1 preceding</code>。如果省略这部分，只保留 <code>order by dt asc</code>，则默认为 <code>between unbounded preceding and current row</code> 从最终效果来说是一致的，但是前者写法表述更准确
<pre><code class="language-sql">with calc_exist_likes_num as (
select
 video_id, dt, likes_num, show_likes_num
-- 找前面的非空点赞量
-- 注意第二个参数为 true 表示跳过 null
, last_value(show_likes_num, true) over(partition by video_id order by dt asc
 rows between unbounded preceding and 1 preceding) as last_exist_likes_num
from data_exercise.dwd_short_videos_likes_num_missing_data_from_crawler
)

select
video_id, dt, likes_num, show_likes_num
-- nvl 只支持 2 个参数；使用 coalesce
-- 按照顺序，返回第一个非 Null 的值
-- 根据我写的兜底逻辑，如果向前寻找非空数值没有，则用 0 兜底
, coalesce(show_likes_num, last_exist_likes_num, 0) as forward_fill
from calc_exist_likes_num
</code></pre>
部分结果验证：
<img src="https://img2024.cnblogs.com/blog/3640949/202505/3640949-20250511100937657-1446556077.png" alt="" loading="lazy">
2.后向填充的 sql 语句，如果使用 <code>first_value</code> 则 <code>rows</code> 的范围是 <code>between 1 following and unbounded following</code>。注意 <code>first_value</code> 和 <code>last_value</code> 都是跟 <code>order by dt</code> 的顺序有关，完全可以使用 <code>desc</code> 降序来切换另一个函数。
<pre><code class="language-sql">with calc_exist_likes_num as (
select
 video_id, dt, likes_num, show_likes_num
-- 前后邻近的第一个非空点赞量
-- 注意第二个参数为 true，表示跳过 null
, last_value(show_likes_num, true) over(partition by video_id order by dt asc
 rows between unbounded preceding and 1 preceding) as last_exist_likes_num

, first_value(show_likes_num, true) over(partition by video_id order by dt asc
 rows between 1 following and unbounded following) as next_exist_likes_num
from data_exercise.dwd_short_videos_likes_num_missing_data_from_crawler
)

select
video_id, dt, likes_num, show_likes_num
-- nvl 只支持 2 个参数；使用 coalesce
-- 按照顺序，返回第一个非 Null 的值
-- 根据我写的兜底逻辑，如果向后寻找非空数值没有，则用前面的第一个非空兜底，最后用 0 兜底
, coalesce(show_likes_num, next_exist_likes_num, last_exist_likes_num, 0) as backward_fill
from calc_exist_likes_num
</code></pre>
部分结果验证：
<img src="https://img2024.cnblogs.com/blog/3640949/202505/3640949-20250511100928147-1124110576.png" alt="" loading="lazy">
3.相邻平均数填充的 sql 语句，寻找前后相邻的非空值逻辑，这里不再赘述。对前后相邻的非空值求平均，注意这里的兜底逻辑。首先上一个非空点赞量如果不存在，那就填充零，因此求平均的分母这部分的 “1” 必然存在；如果下一个非空点赞量不存在，可以将其当成零，那么分母求平均时，它就不起作用，它的分母部分是 “0”；最后结果注意四舍五入（如果写成显式的判断逻辑也可以，需要引入 <code>if</code> 或 <code>case when</code> 语句）。
注意结果要取整，我这里不取整，是为了跟大家展示结果时去反向验证：
<pre><code class="language-sql">with calc_exist_likes_num as (
select
 video_id, dt, likes_num, show_likes_num
-- 前后邻近的第一个非空点赞量
-- 注意第二个参数为 true，表示跳过 null
, last_value(show_likes_num, true) over(partition by video_id order by dt asc
 rows between unbounded preceding and 1 preceding) as last_exist_likes_num

, first_value(show_likes_num, true) over(partition by video_id order by dt asc
 rows between 1 following and unbounded following) as next_exist_likes_num
from data_exercise.dwd_short_videos_likes_num_missing_data_from_crawler
)

select
video_id, dt, likes_num, show_likes_num
-- 如果一开始的数据没有，就按照零计算；因此“上一个点赞量必然存在”
-- 如果下一个点赞量不存在，那么就按照零计算，实际就等于不算
-- 注意四舍五入取整，我这里不取整，是为了给大家更方便的验证结果
, nvl(show_likes_num,(nvl(last_exist_likes_num, 0) + nvl(next_exist_likes_num,0))
/ (1 + if(next_exist_likes_num is not null, 1, 0))
) as half_fill_likes_num
from calc_exist_likes_num
</code></pre>
部分结果验证：
<img src="https://img2024.cnblogs.com/blog/3640949/202505/3640949-20250511100917445-38223862.png" alt="" loading="lazy">
4.相邻分位数填充的 sql 语句，基本逻辑跟平均数一样；但是不是简单求平均，而是需要计算每个缺失值所在的分位数位置，来“线性插值”。这里稍微推导一下再写 sql：我将缺失值的上一个邻近非空值记为 <code>s</code>，下一个邻近非空值记为 <code>e</code>；因为是分位数，还要考虑位置，将上一个邻近非空值的序号记为 <code>m</code>，下一个邻近非空值记为 <code>n</code>，这个缺失值的位置记录为 <code>i</code>。则根据推导它的位置分位数应该是 <code>(i-m)/(m-n)</code>，我们再推导它的值应该是 <code>s + (e-s)*(i-m)/(m-n)</code> 化简后为 <code>s*(n-i)+e*(i-m)</code>。在 sql 中，我利用日期充当序号，序号之间的减法结果，我用 <code>datediff</code> 函数来处理，代码如下：
<pre><code class="language-sql">with calc_exist_likes_num as (
select
 video_id, dt, likes_num, show_likes_num
-- 注意将 first_value 和 last_value 的第二个参数设置为 true 表示跳过 null
, last_value(show_likes_num, true) over(partition by video_id
 order by dt rows between unbounded preceding and 1 preceding) as last_exist_likes_num
, first_value(show_likes_num, true) over(partition by video_id
 order by dt rows between 1 following and unbounded following) as next_exist_likes_num
-- 取出前后存在的点赞量对应的日期端点
-- 注意要根据点赞量同时将日期设为 null
, last_value(if(show_likes_num is null, null, dt), true) over(partition by video_id
 order by dt rows between unbounded preceding and 1 preceding) as last_exist_likes_dt
, first_value(if(show_likes_num is null, null, dt), true) over(partition by video_id
 order by dt rows between 1 following and unbounded following) as next_exist_likes_dt
, min(dt) over(partition by video_id) as base_dt
from data_exercise.dwd_short_videos_likes_num_missing_data_from_crawler
)

select
video_id, dt, likes_num, show_likes_num
-- 推导见说明，此处不四舍五入也是为了验证结果
, nvl(show_likes_num, if( e is null, s, (s * n_i + e * i_m) / (n_i + i_m))) as percentile_fill_likes_num
from (
select
 video_id, dt, likes_num, show_likes_num
, nvl(last_exist_likes_num,0) as s
, next_exist_likes_num as e
, if(last_exist_likes_num is not null, datediff(dt, last_exist_likes_dt), datediff(dt, base_dt)+1) as i_m
, datediff(next_exist_likes_dt, dt) as n_i
from calc_exist_likes_num
) t
-- 经过一通处理，发现原来的顺序被破坏，重新排序
order by video_id asc, dt asc
</code></pre>
注意里面的兜底逻辑，比如取一个 <code>min(dt)</code> 作为如果找到前面的非空值，则将其设置为更早日期的前一天，求 <code>i_m</code> 即 i-m 时 <code>datediff(dt, base_dt)+1</code> 的 <code>+1</code> 就是这么来的。如果 <code>e</code> 不存在，也就是下一个非空值找不到，直接用上一个非空值“顺延”下去。
部分结果验证：
<img src="https://img2024.cnblogs.com/blog/3640949/202505/3640949-20250511100903698-627648885.png" alt="" loading="lazy">
<hr>
😁😁😁 
我现在正在求职数据类工作（主要是数据分析或数据科学）；如果您有合适的机会，恳请您与我联系，即时到岗，不限城市。 
来源：https://www.cnblogs.com/data-analytics/p/18870557

頁: [1]

琼殿技术论坛's Archiver

【SQL 周周练】爬取短视频发现数据缺失，如何用 SQL 填充