【记录一下】获取报警时长计算

kzz

这个帖子是笔记，防止我自己忘记。
我的项目是6488246bd7c5cfb28a4405c1，如果你能看懂就可以操作，
如果看不懂，请在他人协助下完成以下操作。

获取报警恢复耗时计算。
报警恢复时间/时长统计。报警持续时间统计。

这是说明：

一个报警事件 = 从异常开始，到异常恢复的整个连续过程

无论中间触发多少次提醒，只要属于同一异常持续过程，就只算 一次报警。

查询通过以下规则进行“报警压缩”：

按 ruleid + desc + table + tableData 分组

如果时间间隔未超过 1.2 × interval，则认为仍属于同一次报警

如果前一条已恢复，则视为新报警

超过 1.2 × interval，则视为新报警

方法一：直接运行sql语句查询报警数据表。
直接查询报警数据表，查询出每次报警恢复耗时。

/* ============================================================
   报警压缩 + 自动计算恢复时间 + 计算恢复时长（单位：秒）

   逻辑说明：
   1. 按 ruleid + desc + table + tableData 分组
   2. 判断是否为新报警段：
        - 第一条数据
        - 前一条已经物理恢复
        - 时间间隔 > 1.2 × interval（interval 单位：秒）
   3. 通过窗口累计求和生成分段ID
   4. 每段聚合出：
        - 报警开始时间 start_time
        - 报警结束时间 end_time
        - 记录条数 alarm_count
   5. 计算最终恢复时间
   6. 计算恢复持续时长（秒）

   注意：
   interval 字段单位为 秒
   ============================================================ */

WITH base AS (

    /* ========================================================
       取同组内上一条报警时间和恢复时间
       ======================================================== */
    SELECT *,
        LAG("time") OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS prev_time,

        LAG("recoveryTime") OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS prev_recovery

    FROM "6488246bd7c5cfb28a4405c1".warning
),

/* ============================================================
   判断是否为新报警段
   ============================================================ */
mark AS (
    SELECT *,

        CASE
            WHEN prev_time IS NULL THEN 1

            WHEN prev_recovery IS NOT NULL
                 AND prev_recovery <= "time"
                 THEN 1

            WHEN EXTRACT(EPOCH FROM ("time" - prev_time))
                 > ("interval" * 1.2)
                 THEN 1

            ELSE 0
        END AS is_new_group

    FROM base
),

/* ============================================================
   生成报警分段ID
   ============================================================ */
grp AS (
    SELECT *,
        SUM(is_new_group) OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS grp_id
    FROM mark
),

/* ============================================================
   按分段ID进行聚合
   ============================================================ */
agg AS (
    SELECT
        ruleid,
        "desc",
        "table",
        "tableData",
        grp_id,

        MIN("time") AS start_time,
        MAX("time") AS end_time,

        /* 段内最大恢复时间 */
        MAX("recoveryTime") AS max_recovery,

        COUNT(*) AS alarm_count

    FROM grp
    GROUP BY
        ruleid, "desc", "table", "tableData", grp_id
),

/* ============================================================
   计算最终恢复时间
   ============================================================ */
final_result AS (
    SELECT
        ruleid,
        "desc",
        "table",
        "tableData",
        grp_id,
        start_time,
        end_time,
        alarm_count,

        /* 有恢复时间就用恢复时间，否则用 end_time */
        COALESCE(max_recovery, end_time) AS final_recovery_time

    FROM agg
)

/* ============================================================
   最终输出
   ============================================================ */
SELECT
    ruleid,
    "desc",
    "table",
    "tableData",
    grp_id,

    start_time,
    end_time,
    alarm_count,

    final_recovery_time,

    /* 恢复持续时长（单位：秒） */
    CAST(
        EXTRACT(EPOCH FROM (final_recovery_time - start_time))
    AS BIGINT) AS recovery_seconds

FROM final_result

ORDER BY
    ruleid,
    "desc",
    "table",
    "tableData",
    start_time;

方法二、
如果报警数据归档了，又想查询全部报警数据，需要创建两个视图来实现。
方法二第一步：

/* 创建全部报警数据视图 */
CREATE OR REPLACE VIEW "6488246bd7c5cfb28a4405c1".warning_all AS

SELECT
    id::text                     AS id,
    level,
    uid,
    type,
    status,
    processed,
    "desc",
    audio,
    interval,
    ruleid,
    remark,
    "table",
    "tableData",
    fields,
    "recoveryFields",
    "time",
    "recoveryTime",
    "confirmTime",
    "handleTime",
    other,
    "warnTag",
    "timesOfPlay",
    handle,
    alert,
    "handleUser",
    "confirmUser",
    "warnMode",
    "broadcastContent",
    "broadcastVoice",
    "audioAlert",
    "i18nProp"
FROM "6488246bd7c5cfb28a4405c1".warning

UNION ALL

SELECT
    id::text                     AS id,
    level,
    uid,
    type,
    status,
    processed,
    "desc",
    audio,
    interval,
    ruleid,
    remark,
    "table",
    "tableData",
    fields,
    "recoveryFields",
    "time",
    "recoveryTime",
    "confirmTime",
    "handleTime",
    other,
    "warnTag",
    "timesOfPlay",
    handle,
    alert,
    "handleUser",
    "confirmUser",
    "warnMode",
    "broadcastContent",
    "broadcastVoice",
    "audioAlert",
    "i18nProp"
FROM "6488246bd7c5cfb28a4405c1".warning_archive;

方法二第二步：

/* 创建报警恢复时间统计视图 */
CREATE OR REPLACE VIEW "6488246bd7c5cfb28a4405c1".warning_recovery_view AS

WITH base AS (
    /* ========================================================
       取同组内上一条报警时间和恢复时间
       LAG() 返回当前行的前一条数据
       ======================================================== */
    SELECT *,
        LAG("time") OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS prev_time,

        LAG("recoveryTime") OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS prev_recovery

    FROM "6488246bd7c5cfb28a4405c1".warning_all
),

mark AS (
    /* ========================================================
       判断是否为新报警段
       is_new_group = 1 表示当前记录是新报警段起始
       ======================================================== */
    SELECT *,
        CASE
            /* 第一条记录必定是新段 */
            WHEN prev_time IS NULL THEN 1

            /* 前一条已恢复，并且恢复时间早于当前报警时间 */
            WHEN prev_recovery IS NOT NULL
                 AND prev_recovery <= "time"
                 THEN 1

            /* 与上一条时间间隔超过 1.2 × interval */
            WHEN EXTRACT(EPOCH FROM ("time" - prev_time))
                 > ("interval" * 1.2)
                 THEN 1

            /* 否则属于同一报警段 */
            ELSE 0
        END AS is_new_group
    FROM base
),

grp AS (
    /* ========================================================
       生成报警分段ID
       SUM(is_new_group) 累加，得到 grp_id，表示连续报警段
       ======================================================== */
    SELECT *,
        SUM(is_new_group) OVER (
            PARTITION BY ruleid, "desc", "table", "tableData"
            ORDER BY "time"
        ) AS grp_id
    FROM mark
),

agg AS (
    /* ========================================================
       按分段ID聚合报警信息
       ======================================================== */
    SELECT
        ruleid,
        "desc",
        "table",
        "tableData",
        grp_id,

        /* 本段报警起始时间 */
        MIN("time") AS start_time,

        /* 本段报警最后触发时间 */
        MAX("time") AS end_time,

        /* 段内最大恢复时间，可能为批量确认 */
        MAX("recoveryTime") AS max_recovery,

        /* 本段记录条数 */
        COUNT(*) AS alarm_count

    FROM grp
    GROUP BY
        ruleid, "desc", "table", "tableData", grp_id
)

SELECT
    ruleid,
    "desc",
    "table",
    "tableData",
    grp_id,

    start_time,        -- 报警开始时间
    end_time,          -- 报警最后触发时间
    alarm_count,       -- 本段记录条数

    /* ========================================================
       最终恢复时间：
       - 有恢复时间使用 max_recovery
       - 无恢复时间（单条或多条）使用 end_time
       ======================================================== */
    COALESCE(max_recovery, end_time) AS final_recovery_time,

    /* ========================================================
       恢复持续时长（秒）
       final_recovery_time - start_time
       ======================================================== */
    CAST(
        EXTRACT(EPOCH FROM (COALESCE(max_recovery, end_time) - start_time))
    AS BIGINT) AS recovery_seconds

FROM agg

ORDER BY
    ruleid,
    "desc",
    "table",
    "tableData",
    start_time;

AIRIOT智慧系统搭建平台经验交流

【记录一下】获取报警时长计算