SQL问题：计算两张表中时间戳差值的平均值

嘿，这个问题我之前也碰到过，其实核心就是先把两张表正确关联，再计算时间差最后求平均值。我给你分不同数据库方言写了示例，你可以根据自己用的数据库来选：

首先我们需要通过study.id和study_monitoring.id_study将两张表关联起来，确保每一条监测记录都对应到正确的研究记录。然后计算每一对记录的时间戳差值，最后对所有差值取平均值即可。

MySQL/MariaDB #

使用TIMESTAMPDIFF函数来计算时间差，你可以指定想要的时间单位（秒、分钟、小时等）：

-- 计算平均秒级时间差
SELECT AVG(TIMESTAMPDIFF(SECOND, s.timestamp, sm.timestamp_2)) AS avg_time_diff_seconds
FROM study s
INNER JOIN study_monitoring sm ON s.id = sm.id_study;

如果需要其他单位，把SECOND换成MINUTE、HOUR、DAY等即可。

PostgreSQL #

PostgreSQL中两个时间戳相减会得到interval类型，我们可以用EXTRACT(EPOCH FROM ...)把它转成秒数再求平均：

-- 计算平均秒级时间差
SELECT AVG(EXTRACT(EPOCH FROM (sm.timestamp_2 - s.timestamp))) AS avg_time_diff_seconds
FROM study s
INNER JOIN study_monitoring sm ON s.id = sm.id_study;

-- 或者用AGE函数更直观地表示时间间隔
SELECT AVG(EXTRACT(EPOCH FROM AGE(sm.timestamp_2, s.timestamp))) AS avg_time_diff_seconds
FROM study s
INNER JOIN study_monitoring sm ON s.id = sm.id_study;

SQL Server #

使用DATEDIFF函数来计算时间差，同样可以指定时间单位：

-- 计算平均秒级时间差
SELECT AVG(DATEDIFF(SECOND, s.timestamp, sm.timestamp_2)) AS avg_time_diff_seconds
FROM study s
INNER JOIN study_monitoring sm ON s.id = sm.id_study;

• 确保两张表的时间戳字段都是合法的日期时间类型（比如timestamp、datetime等），如果是字符串格式，需要先用CAST或CONVERT转成日期类型再计算。
• 上面用的是INNER JOIN，只会计算两张表都有对应记录的时间差。如果需要包含所有研究记录（哪怕没有监测记录），可以换成LEFT JOIN，不过此时没有对应监测记录的时间差会是NULL，AVG函数会自动忽略这些NULL值。
• 时间单位可以根据你的需求灵活调整，比如要计算天数差就把SECOND换成DAY。

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