PostgreSQL与MySQL中计算timestamp列相邻行平均数据加载间隔的方法

报错原因说明 #

此前执行select avg(starttime)时出现function avg(timestamp without time zone) does not exist报错，核心原因是PostgreSQL和MySQL的avg()聚合函数仅支持数值类型入参，无法直接对原生时间类型做聚合计算，因此需要将时间差转换为可聚合的数值类型后再完成间隔计算。

核心实现逻辑 #

整体计算流程拆解为3步，两个数据库的逻辑完全通用，仅时间差计算的语法存在细微差异：

• 第一步：过滤出状态为Loaded的所有导入时间记录，按导入时间升序排序
• 第二步：用窗口函数LAG()获取每条记录对应的上一条相邻导入时间，排除第一条无前置记录的空值
• 第三步：将相邻两条记录的时间差转换为秒级数值，对所有间隔值求平均，得到真实的平均加载间隔

兼容双库的SQL实现 #

其中窗口函数LAG()的用法在PostgreSQL 9.4+、MySQL 8.0+版本完全一致，仅需要根据数据库类型替换时间差计算的语句即可：

WITH sorted_load_records AS (
    SELECT 
        load_time,
        -- 获取按时间排序后的上一条导入时间
        LAG(load_time) OVER (ORDER BY load_time ASC) AS prev_load_time
    FROM business_table
    WHERE status = 'Loaded'
),
interval_cal AS (
    SELECT
        -- 替换为对应数据库的时间差计算逻辑
        -- 【PostgreSQL 写法】提取时间差的秒数
        -- EXTRACT(EPOCH FROM (load_time - prev_load_time)) AS interval_seconds
        -- 【MySQL 写法】按秒计算两个时间的差值
        -- TIMESTAMPDIFF(SECOND, prev_load_time, load_time) AS interval_seconds
        interval_seconds
    FROM sorted_load_records
    -- 过滤第一条无前置记录的空值，不参与间隔计算
    WHERE prev_load_time IS NOT NULL
)
SELECT 
    AVG(interval_seconds) AS avg_load_interval_seconds,
    ROUND(AVG(interval_seconds)/60, 2) AS avg_load_interval_minutes
FROM interval_cal;

计算提示：如果结果受长时段的调度中断（比如示例中2021-04-18 01:40到06:10的270分钟间隔）影响过大，可以在interval_cal的筛选条件中增加间隔值过滤规则，排除超过常规调度周期3倍的异常值，得到更贴近实际调度频率的近似结果。

示例数据匹配说明 #

针对提供的12条Loaded状态导入记录，按上述逻辑计算后，整体平均间隔会因中断时段拉高，去掉异常长间隔后，结果与观测到的约30分钟的常规调度周期一致。

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