如何在SQL或Python中对时间序列进行插值/上采样？

DevOps 小满

2026-8-17

时间序列插值/上采样解决方案（针对两类时序数据）

需求说明

第一类数据：前半段为10分钟间隔采样，后半段变为1小时间隔，需将后半段上采样至10分钟频率
第二类数据：本该以30分钟间隔采集的IoT数据存在缺失，需转换为10分钟频率

数据示例

第一类数据（10分钟转1小时）

A_LOC_FK A_LOG_FK           M_WL_time M_WL_level_m
0          100      100 2021-11-09 00:20:00         0.18
1          100      100 2021-11-09 00:30:00         0.19
2          100      100 2021-11-09 00:40:00         0.19
3          100      100 2021-11-09 00:50:00          0.2
4          100      100 2021-11-09 01:00:00         0.21
       ...      ...                 ...          ...
27025      100      100 2022-09-13 04:00:00        -0.37
27026      100      100 2022-09-13 05:00:00        -0.41
27027      100      100 2022-09-13 06:00:00        -0.39
27028      100      100 2022-09-13 07:00:00        -0.27
27029      100      100 2022-09-13 08:00:00        -0.15

第二类数据（30分钟间隔IoT数据）

A_LOC_FK A_LOG_FK           M_WL_time M_WL_level_m
0           10       10 2021-11-09 00:19:54      -2.0624
1           10       10 2021-11-09 00:49:54      -2.0624
2           10       10 2021-11-09 01:19:55      -2.0616
3           10       10 2021-11-09 01:49:55      -2.0616
4           10       10 2021-11-09 02:19:55      -2.0616
       ...      ...                 ...          ...
14552       10       10 2022-09-09 05:00:10      -0.0045
14553       10       10 2022-09-09 05:30:09       -0.003
14554       10       10 2022-09-09 06:00:13      -0.0016
14555       10       10 2022-09-09 06:30:10      -0.0008
14556       10       10 2022-09-09 07:00:12       0.0006

[14557 rows x 4 columns]

已遇到的问题

Python端问题

使用resample时触发错误：Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex'。尝试pd.to_datetime转换时间列时，要么报错要么全部转为NaT；自定义lambda转换时间列的方案也未成功。

SQL端问题

尝试生成时间序列的方案时，触发语法错误：Incorrect syntax near the keyword 'table'。

解决方案

Python 实现步骤

时间列转换与索引设置
先确保时间列正确转为datetime类型，再设置为索引：

import pandas as pd

# 假设df是从SQL读取后的DataFrame
df['M_WL_time'] = pd.to_datetime(df['M_WL_time'], errors='coerce')
# 检查转换失败的行（如果有）
invalid_rows = df[df['M_WL_time'].isna()]
if not invalid_rows.empty:
    print("存在转换失败的时间行：", invalid_rows)
# 设置时间列为索引
df = df.set_index('M_WL_time')

分组处理多组数据
针对不同的A_LOC_FK和A_LOG_FK分组，分别执行上采样与插值：

# 按位置和日志ID分组
grouped = df.groupby(['A_LOC_FK', 'A_LOG_FK'])

def resample_group(group):
    # 上采样到10分钟频率，使用线性插值（可替换为ffill前向填充等）
    resampled = group.resample('10min').interpolate(method='linear')
    return resampled

# 应用分组处理并重置索引
result_df = grouped.apply(resample_group).reset_index()

第一类数据：前半段10分钟数据保留，后半段1小时数据自动插值为10分钟间隔；
第二类数据：30分钟间隔数据被填充为10分钟频率，缺失点自动补全。

自定义插值逻辑（可选）
若需要前向填充（保持上一个有效数据值），替换interpolate为ffill()：
```
resampled = group.resample('10min').ffill()
```

SQL 实现步骤

SQL Server 版本

生成10分钟时间序列CTE

WITH TimeSeries AS (
    SELECT MIN(M_WL_time) AS TimePoint
    FROM YourTable
    UNION ALL
    SELECT DATEADD(MINUTE, 10, TimePoint)
    FROM TimeSeries
    WHERE TimePoint < (SELECT MAX(M_WL_time) FROM YourTable)
)

关联原始数据并执行线性插值

SELECT 
    ts.TimePoint,
    yt.A_LOC_FK,
    yt.A_LOG_FK,
    -- 线性插值计算逻辑
    LAG(yt.M_WL_level_m) OVER (PARTITION BY yt.A_LOC_FK, yt.A_LOG_FK ORDER BY ts.TimePoint) 
    + ISNULL((yt.M_WL_level_m - LAG(yt.M_WL_level_m) OVER (PARTITION BY yt.A_LOC_FK, yt.A_LOG_FK ORDER BY ts.TimePoint))
    * DATEDIFF(MINUTE, LAG(ts.TimePoint) OVER (PARTITION BY yt.A_LOC_FK, yt.A_LOG_FK ORDER BY ts.TimePoint), ts.TimePoint)
    / NULLIF(DATEDIFF(MINUTE, LAG(ts.TimePoint) OVER (PARTITION BY yt.A_LOC_FK, yt.A_LOG_FK ORDER BY ts.TimePoint), yt.M_WL_time), 0), 0)
    AS M_WL_level_m
FROM TimeSeries ts
LEFT JOIN YourTable yt ON ts.TimePoint BETWEEN LAG(yt.M_WL_time) OVER (PARTITION BY yt.A_LOC_FK, yt.A_LOG_FK ORDER BY yt.M_WL_time) AND yt.M_WL_time
OPTION (MAXRECURSION 0); -- 处理大时间范围需设置递归次数

MySQL 版本（无递归CTE）

生成10分钟时间序列

SET @start_time = (SELECT MIN(M_WL_time) FROM YourTable);
SET @end_time = (SELECT MAX(M_WL_time) FROM YourTable);

SELECT 
    DATE_ADD(@start_time, INTERVAL (n*10) MINUTE) AS TimePoint
FROM 
    (SELECT @row := @row + 1 AS n FROM 
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
        (SELECT @row := -1) t0
    ) nums
WHERE DATE_ADD(@start_time, INTERVAL (n*10) MINUTE) <= @end_time;

之后关联原始数据，使用窗口函数实现插值逻辑即可。

内容的提问来源于stack exchange，提问作者RonjaCF

如何在SQL或Python中对时间序列进行插值/上采样？

需求说明 #

数据示例 #

第一类数据（10分钟转1小时） #

第二类数据（30分钟间隔IoT数据） #

已遇到的问题 #

Python端问题 #

SQL端问题 #

解决方案 #

Python 实现步骤 #

SQL 实现步骤 #