Oracle中跨午夜Feed到达时间平均值及运行平均值计算（用于SLO）

原理说明 #

跨午夜的时间属于环形数据（类似时钟），线性计算平均值会忽略其环形特性，导致结果偏离实际。正确做法是将时间转换为角度值，通过计算余弦和正弦的平均值反推平均时间，规避线性偏差。

Oracle 查询实现 #

假设表名为feed_arrivals，字段为feed_name（Feed名称）、arrival_datetime（含日期和时间的到达时间）。以下查询按Feed分组计算准确的平均到达时间：

WITH time_conversion AS (
    SELECT
        feed_name,
        -- 提取时间在一天内的总秒数（范围0-86399）
        TO_NUMBER(TO_CHAR(arrival_datetime, 'SSSSS')) AS seconds_in_day,
        -- 转换为0到2π的弧度值
        (TO_NUMBER(TO_CHAR(arrival_datetime, 'SSSSS')) / 86400) * 2 * PI() AS radians
    FROM feed_arrivals
),
avg_trig AS (
    SELECT
        feed_name,
        AVG(COS(radians)) AS avg_cos,
        AVG(SIN(radians)) AS avg_sin
    FROM time_conversion
    GROUP BY feed_name
)
SELECT
    feed_name,
    -- 将平均弧度转换回HH24:MI:SS格式的时间
    TO_CHAR(
        TRUNC(SYSDATE) + 
        (MOD(ATAN2(avg_sin, avg_cos) + 2 * PI(), 2 * PI()) / (2 * PI()))
        , 'HH24:MI:SS'
    ) AS average_arrival_time
FROM avg_trig;

关键逻辑解释 #

• TO_CHAR(arrival_datetime, 'SSSSS')：把时间转为一天内的总秒数，对应00:00:00到23:59:59。
• 弧度转换：将秒数映射到环形角度范围（0-2π），适配时间的环形特性。
• ATAN2(avg_sin, avg_cos)：通过三角函数平均值计算平均角度，再通过MOD调整到0-2π范围，最终转换回时间格式。

通过滑动窗口平均值（运行平均）可追踪到达时间的变化趋势，识别持续性延迟。以下查询按Feed分组，计算最近N条记录的到达时间滑动平均值，辅助判断延迟趋势。

Oracle 查询实现 #

假设以最近7条记录为滑动窗口，目标到达时间为23:30:00（可根据实际调整）：

WITH arrival_metrics AS (
    SELECT
        feed_name,
        arrival_datetime,
        -- 把到达时间转为一天内的分钟数，方便计算偏移
        (EXTRACT(HOUR FROM arrival_datetime)*60 + EXTRACT(MINUTE FROM arrival_datetime)) AS arrival_minutes,
        -- 目标时间转为分钟数
        (EXTRACT(HOUR FROM TO_DATE('23:30:00', 'HH24:MI'))*60 + EXTRACT(MINUTE FROM TO_DATE('23:30:00', 'HH24:MI'))) AS target_minutes,
        -- 计算到达时间与目标的偏移（正数表示延迟，处理跨午夜场景）
        CASE
            WHEN arrival_minutes < target_minutes AND arrival_datetime >= TRUNC(arrival_datetime) + TO_DATE('23:00:00', 'HH24:MI') THEN (arrival_minutes + 1440) - target_minutes
            ELSE arrival_minutes - target_minutes
        END AS delay_minutes
    FROM feed_arrivals
)
SELECT
    feed_name,
    arrival_datetime,
    delay_minutes,
    -- 计算最近7条记录的运行平均延迟
    AVG(delay_minutes) OVER (
        PARTITION BY feed_name
        ORDER BY arrival_datetime
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- 窗口包含当前行及前6行，共7条
    ) AS running_avg_delay
FROM arrival_metrics
ORDER BY feed_name, arrival_datetime;

关键逻辑解释 #

• 偏移计算：处理跨午夜的延迟判断，比如目标时间23:30，到达时间00:22，实际延迟为52分钟（而非负数）。
• 滑动窗口：OVER (PARTITION BY feed_name ORDER BY arrival_datetime ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 指定按Feed分组、按时间排序，取最近7条记录计算平均延迟。
• 趋势识别：若running_avg_delay持续上升，说明Feed到达时间存在持续性延迟。

针对SLI/SLO的扩展建议 #

结合你提到的基于区域的用户数据可用时间SLI，可做以下扩展：

• 给不同区域设置专属target_time，适配区域差异。
• 按日/小时聚合运行平均延迟，生成SLO合规报表（比如统计每周延迟超标的天数占比）。
• 加入告警逻辑：当连续N个窗口的运行平均延迟超过阈值时，触发告警。

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