1. 背景

今天业务提出了一个小需求，希望查看舆情事件中涉及的预警事件，近几日内作品量和触达量的变化曲线

2. 基础数据

每行数据代表一个预警编号对应的预警时间和最新的触达量

3. 最终期望结果

计算出每天（统计日期）的触达量变化值，累计触达量，预警事件数量

4. STEP1：统计每天每个预警编号对应的变化值

INSERT INTO firewall_alert_event_statistic(alert_no, statistic_time, reach_num_inc)
SELECT
    ev.alert_no AS alert_no,
    -- 使用当前时间作为 statistic_time，表示插入记录的时间
    now() AS statistic_time,
    
    -- 计算 reach_num_inc：即 "starter_reach_num" 减去该 alert_no 在 firewall_alert_event_statistic 表中的历史 reach_num_inc 累积值
    ev.starter_reach_num - IFNULL(la.reachNumInc, 0) AS reachNumInc

FROM
    (
        SELECT
            alert_no, 
            starter_reach_num, 
            create_time
        FROM
            firewall_alert_event
    ) ev
    
    -- 通过 (LEFT JOIN) 将 firewall_alert_event_statistic 表中的数据与 ev 子查询结果进行连接
    LEFT JOIN (
        -- 计算每个 alert_no 累计的 reach_num_inc 值
        SELECT
            alert_no,
            SUM(reach_num_inc) AS reachNumInc
        FROM
            firewall_alert_event_statistic
        GROUP BY
            alert_no
    ) la ON ev.alert_no = la.alert_no;

5. firewall_alert_event_statistic数据

• 表中记录了每天预警事件的触达量变化值，例如id = 1 的一行数据代表A1在2024-12-15日一天内的触达量增加100

• 每个事件在每天只会记录一条数据

6. STEP2：最终统计

-- 1. "base"，用于获取基础数据
WITH base AS (
    SELECT
        alert_no,  -- 预警号 (alert_no)
        DATE(statistic_time) AS statistic_time,  -- 统计时间，提取日期部分
        DATE(alert_time) AS alert_time,  -- 预警触发时间，提取日期部分
        reach_num_inc  -- 每个预警的触达增量 (reach_num_inc)
    FROM
        firewall_alert_event_statistic  -- 数据源：预警事件统计表
),

-- 2.  "inc"，对每天的触达量进行汇总
inc AS (
    SELECT
        statistic_time AS st, 
        SUM(reach_num_inc) AS reach_num_inc,  -- 每天的触达增量总和
        SUM(reach_num_inc) OVER (ORDER BY statistic_time) AS aaaa,  -- 累计触达增量 (按统计时间排序的累计和)
        -- 计算每天的去重预警数量：计算在当天内所有 `alert_time` 等于该统计时间的预警总数
        (SELECT COUNT(DISTINCT alert_no) 
         FROM base 
         WHERE base.alert_time = st) AS total_inc  
    FROM
        base  -- 数据来源：前面的基础数据表
    GROUP BY
        statistic_time  -- 按统计时间分组
),

-- 3. 最终查询结果
SELECT
    st AS statistic_time,  -- 统计时间
    reach_num_inc,  -- 当天的触达增量
    SUM(reach_num_inc) OVER (ORDER BY st) AS reach_num,  -- 开窗取累计触达增量 (按统计时间排序的累计和)
    total_inc  -- 当天去重预警数量
FROM
    inc; 

7. 最终结果