相同ID事件平均耗时计算及SQL分组查询问题求解

原SQL问题说明 #

你当前使用的elapsed(id)写法不符合计算逻辑：elapsed函数默认计算时序数据中相邻两行的时间差，分组后直接调用无法得到单个ID对应事件从开始到结束的总时长，自然无法算出正确平均值。

正确计算逻辑 #

单个ID对应一个独立事件，事件耗时为该ID下记录的最大时间（结束时间）减去最小时间（开始时间），再对所有事件的耗时求平均即可得到结果，和示例中(6+4)/2=5秒的预期完全匹配。

可直接运行的SQL代码 #

仅查询全局平均耗时 #

SELECT AVG(single_event_duration) AS average_elapsed_seconds
FROM (
    -- 子查询按ID分组，计算每个事件的单独耗时
    SELECT MAX(time) - MIN(time) AS single_event_duration
    FROM measurement1
    GROUP BY id
) AS event_duration_tmp;

同时查询单ID耗时与全局平均 #

如果需要同时看到每个ID对应的事件耗时、以及所有事件的平均耗时，可以使用窗口函数实现：

SELECT
    id,
    MAX(time) - MIN(time) AS event_elapsed,
    AVG(MAX(time) - MIN(time)) OVER () AS global_average_elapsed
FROM measurement1
GROUP BY id;

结果验证 #

针对你给出的示例数据：

• ID=1的事件max(time)=129、min(time)=123，耗时6秒
• ID=2的事件max(time)=138、min(time)=134，耗时4秒
• 平均耗时计算为(6+4)/2=5秒，和预期结果完全一致。

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