MySQL日志表按日统计各列上升沿次数的高效实现方案问询

核心逻辑：精准识别0→1的上升沿 #

要统计每个故障字段的上升沿，核心判断条件是当前记录的故障值为1，且前一条记录的对应故障值为0。利用MySQL的窗口函数LAG()可以高效获取前序记录的故障值，结合条件判断完成计数。

具体SQL实现 #

假设日志表名为machine_fault_logs，包含time（时间戳）、def1至def30（故障状态字段，0=正常，1=故障）：

WITH log_with_prev AS (
    SELECT
        DATE(time) AS log_date,
        -- 逐个获取每个故障字段的前序值
        def1, LAG(def1) OVER (ORDER BY time) AS prev_def1,
        def2, LAG(def2) OVER (ORDER BY time) AS prev_def2,
        def3, LAG(def3) OVER (ORDER BY time) AS prev_def3,
        def4, LAG(def4) OVER (ORDER BY time) AS prev_def4,
        def5, LAG(def5) OVER (ORDER BY time) AS prev_def5,
        -- 继续添加def6至def30的LAG语句，格式与上述一致
        def6, LAG(def6) OVER (ORDER BY time) AS prev_def6,
        ...
        def30, LAG(def30) OVER (ORDER BY time) AS prev_def30
    FROM machine_fault_logs
    -- 可选：添加时间范围过滤，减少扫描数据量（推荐）
    -- WHERE time >= '2022-09-01' AND time < '2022-10-01'
)
SELECT
    log_date AS date,
    -- 统计每个故障字段的上升沿次数
    SUM(CASE WHEN def1 = 1 AND prev_def1 = 0 THEN 1 ELSE 0 END) AS def1,
    SUM(CASE WHEN def2 = 1 AND prev_def2 = 0 THEN 1 ELSE 0 END) AS def2,
    SUM(CASE WHEN def3 = 1 AND prev_def3 = 0 THEN 1 ELSE 0 END) AS def3,
    SUM(CASE WHEN def4 = 1 AND prev_def4 = 0 THEN 1 ELSE 0 END) AS def4,
    SUM(CASE WHEN def5 = 1 AND prev_def5 = 0 THEN 1 ELSE 0 END) AS def5,
    -- 继续添加def6至def30的统计语句，格式与上述一致
    SUM(CASE WHEN def6 = 1 AND prev_def6 = 0 THEN 1 ELSE 0 END) AS def6,
    ...
    SUM(CASE WHEN def30 = 1 AND prev_def30 = 0 THEN 1 ELSE 0 END) AS def30
FROM log_with_prev
GROUP BY log_date
ORDER BY log_date;

逻辑说明 #

1. LAG()函数按time排序，获取当前记录的前一条记录对应故障字段的值；第一条记录的prev_def会是NULL，此时不会被计入上升沿（因为没有前置的0状态）。
2. CASE语句仅在“当前故障为1且前序故障为0”时计数1，SUM()聚合后得到当日该故障的上升沿总次数。

性能优化建议 #

针对380万条历史数据+2年存储需求，必须优化查询效率：

• 添加时间索引：给time字段创建索引，加速窗口函数的排序操作：

  CREATE INDEX idx_machine_fault_time ON machine_fault_logs(time);
  

• 分区表存储：按日期（月份/季度）分区，查询时仅扫描目标分区，大幅降低数据扫描量：

  ALTER TABLE machine_fault_logs PARTITION BY RANGE (TO_DAYS(time)) (
      PARTITION p202209 VALUES LESS THAN (TO_DAYS('2022-10-01')),
      PARTITION p202210 VALUES LESS THAN (TO_DAYS('2022-11-01')),
      -- 依次添加后续月份的分区，覆盖2年存储周期
      PARTITION p202409 VALUES LESS THAN (TO_DAYS('2024-10-01'))
  );
  

• 限制查询范围：每次查询务必添加WHERE time的时间范围过滤，避免全表扫描。
• 动态SQL简化代码：30个故障字段的重复代码可通过应用层或存储过程生成动态SQL，减少手动编写工作量。

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