不同时间范围时间序列均值计算及数据表时间匹配问题咨询

核心思路 #

不需要强行对表1的时间戳做秒数截断做等值关联，优先用时间范围重叠判断逻辑匹配，比截断后关联的准确率更高，也能覆盖抽水时段跨多分钟、或仅覆盖某分钟部分时段的场景。如果确实需要先做秒数截断对齐到分钟，也附对应的时间处理函数。

不同技术栈实现 #

1. SQL场景 #

不同数据库截断时间到分钟的常用函数：

• MySQL：DATE_FORMAT(字段名, '%Y-%m-%d %H:%i:00')，可再嵌套TIMESTAMP()转成时间类型
• PostgreSQL：DATE_TRUNC('minute', 字段名)
• SQL Server：DATEADD(minute, DATEDIFF(minute, 0, 字段名), 0)

优先推荐时间重叠匹配的计算逻辑，避免截断导致的边界数据错误：

SELECT 
  t1.*,
  AVG(t2.`Concentration X`) AS `Mean X`
FROM 表1 t1
LEFT JOIN 表2 t2
-- 满足两个时段有交集就算命中
ON t2.End > t1.Timestamp_Start 
AND t2.Start < t1.Timestamp_End
GROUP BY t1.Timestamp_Start, t1.Timestamp_End -- 补全表1的所有字段即可

如果需要先截断秒数再关联的写法参考：

SELECT 
  t1.*,
  AVG(t2.`Concentration X`) AS `Mean X`
FROM 表1 t1
LEFT JOIN 表2 t2
-- 此处示例用PostgreSQL语法，可替换为对应数据库的截断函数
ON t2.Start >= DATE_TRUNC('minute', t1.Timestamp_Start)
AND t2.End <= DATE_TRUNC('minute', t1.Timestamp_End) + INTERVAL '1 minute'
GROUP BY t1.Timestamp_Start, t1.Timestamp_End -- 补全表1的所有字段即可

2. Python Pandas场景 #

import pandas as pd
# 第一步先统一转成datetime类型
df1['Timestamp_Start'] = pd.to_datetime(df1['Timestamp_Start'])
df1['Timestamp_End'] = pd.to_datetime(df1['Timestamp_End'])
df2['Start'] = pd.to_datetime(df2['Start'])
df2['End'] = pd.to_datetime(df2['End'])

# 截断时间到分钟的写法
df1['start_minute'] = df1['Timestamp_Start'].dt.floor('min')
df1['end_minute'] = df1['Timestamp_End'].dt.floor('min') + pd.Timedelta(minutes=1)

# 关联计算平均值（适合数据量不大的场景）
cross_df = df1.merge(df2, how='cross')
mask = (cross_df['End'] > cross_df['Timestamp_Start']) & (cross_df['Start'] < cross_df['Timestamp_End'])
mean_df = cross_df[mask].groupby(['Timestamp_Start','Timestamp_End'])['Concentration X'].mean().reset_index(name='Mean X')

# 拼接回原表1得到最终结果
result = df1.merge(mean_df, on=['Timestamp_Start','Timestamp_End'], how='left')

注意：如果你的表2分钟级数据是左闭右开规则（比如13:00的行代表13:00:00到13:01:00的浓度），可以自行调整关联条件的大于/小于等于符号，避免边界数据漏匹配。

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