3. SQL告警核心指标设计：延迟时长、延迟率、尖峰阈值、基线偏移量、告警等级划分

精算模型延迟尖峰告警的核心在于将业务语义转化为可量化、可计算的SQL指标。本章定义五个核心指标，构成告警引擎的度量基础。每个指标均需在SQL层面实现高效聚合与实时比对。

3.1 指标总览与定义

3.2 延迟时长与延迟率：SQL实现

延迟时长是原始度量，延迟率是归一化后的告警触发基础。以下为典型SQL实现片段：

-- 延迟时长计算（行级）
SELECT
    policy_id,
    event_timestamp,
    expected_timestamp,
    DATEDIFF('second', expected_timestamp, event_timestamp) AS delay_seconds
FROM raw_claim_events
WHERE event_timestamp > expected_timestamp;

-- 延迟率计算（1分钟滑动窗口）
WITH delay_stats AS (
    SELECT
        window_start,
        COUNT(*) AS total_count,
        SUM(CASE WHEN delay_seconds > 30 THEN 1 ELSE 0 END) AS delay_count
    FROM (
        SELECT
            date_trunc('minute', event_timestamp) AS window_start,
            DATEDIFF('second', expected_timestamp, event_timestamp) AS delay_seconds
        FROM raw_claim_events
        WHERE event_timestamp BETWEEN NOW() - INTERVAL '5 minutes' AND NOW()
    ) t
    GROUP BY window_start
)
SELECT
    window_start,
    total_count,
    delay_count,
    ROUND(delay_count * 100.0 / NULLIF(total_count, 0), 2) AS delay_rate_pct
FROM delay_stats
ORDER BY window_start;

3.3 尖峰阈值与基线偏移量

尖峰阈值决定“多高算异常”，基线偏移量决定“与正常相比偏离多少”。两者结合可有效区分突发尖峰与持续高水位。

• 静态阈值：由业务方直接配置，如“延迟率超过20%即告警”。适用于监管硬性要求。
• 动态阈值：基于历史数据计算P95/P99百分位数，自动适应业务波动。SQL实现需使用PERCENTILE_CONT或NTILE。
• 基线偏移量：当前窗口延迟率减去历史同期均值。例如：当前15:00延迟率15%，过去7天同一分钟平均延迟率5%，则偏移量为+10%。

-- 动态阈值计算（过去7天P95）
SELECT
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY delay_rate_pct) AS p95_threshold
FROM historical_delay_rates
WHERE window_ts BETWEEN NOW() - INTERVAL '7 days' AND NOW();

-- 基线偏移量计算
WITH current_window AS (
    SELECT AVG(delay_rate_pct) AS cur_rate
    FROM delay_stats
    WHERE window_start = date_trunc('minute', NOW())
),
historical_baseline AS (
    SELECT AVG(delay_rate_pct) AS base_rate
    FROM historical_delay_rates
    WHERE EXTRACT(hour FROM window_ts) = EXTRACT(hour FROM NOW())
      AND EXTRACT(minute FROM window_ts) = EXTRACT(minute FROM NOW())
      AND window_ts BETWEEN NOW() - INTERVAL '7 days' AND NOW()
)
SELECT
    cur_rate,
    base_rate,
    ROUND(cur_rate - base_rate, 2) AS delta_base
FROM current_window, historical_baseline;

3.4 告警等级划分规则

告警等级采用二维判定矩阵：横轴为延迟率绝对值，纵轴为基线偏移量。每个交叉点映射到P0~P3四个等级。

3.5 核心指标关系图

下图展示五个核心指标之间的数据流与依赖关系：