MariaDB 8.0.19中检测布尔值跳变的最佳方法是什么？

需求明确 #

• 要统计tinyint类型布尔值的**上升沿（0→1）或下降沿（1→0）**跳变次数，示例数据的上升沿计数应为2：

  1
  1
  1
  0
  0
  1
  1
  1
  0
  0
  

• 数据表phgt包含带索引的dt列（CURRENT_TIMESTAMP类型，数据粒度1秒），还有site、ffr、por等字段。

核心实现思路 #

利用MariaDB 8.0支持的窗口函数LAG()获取当前行的上一行数据，通过对比当前值与上一行值的差异识别跳变，同时借助dt列的索引优化性能。

1. 统计上升沿（0→1）的SQL #

SELECT 
    site,
    COUNT(*) AS 上升沿次数
FROM (
    SELECT 
        site,
        ffr,
        dt,
        -- 按站点分组、时间排序，获取上一行的ffr值
        LAG(ffr) OVER (PARTITION BY site ORDER BY dt) AS prev_ffr
    FROM phgt
    WHERE dt >= '2023-12-18 08:00:00' 
      AND dt < '2023-12-21 20:00:00'
) AS t
-- 当前值为1且上一行值为0，判定为上升沿
WHERE ffr = 1 AND prev_ffr = 0
GROUP BY site;

2. 统计下降沿（1→0）的SQL #

仅需调整WHERE条件：

SELECT 
    site,
    COUNT(*) AS 下降沿次数
FROM (
    SELECT 
        site,
        ffr,
        dt,
        LAG(ffr) OVER (PARTITION BY site ORDER BY dt) AS prev_ffr
    FROM phgt
    WHERE dt >= '2023-12-18 08:00:00' 
      AND dt < '2023-12-21 20:00:00'
) AS t
WHERE ffr = 0 AND prev_ffr = 1
GROUP BY site;

3. 按时间分组统计多字段跳变 #

如果需要按天、小时维度统计ffr（2m）和por（5m）的跳变次数，可使用以下语句：

SELECT 
    site,
    DAY(dt) AS 日期,
    HOUR(dt) AS 小时,
    SUM(CASE WHEN ffr = 1 AND prev_ffr = 0 THEN 1 ELSE 0 END) AS 2m上升沿次数,
    SUM(CASE WHEN por = 1 AND prev_por = 0 THEN 1 ELSE 0 END) AS 5m上升沿次数
FROM (
    SELECT 
        site,
        ffr,
        por,
        dt,
        LAG(ffr) OVER (PARTITION BY site ORDER BY dt) AS prev_ffr,
        LAG(por) OVER (PARTITION BY site ORDER BY dt) AS prev_por
    FROM phgt
    WHERE dt >= '2023-12-18 08:00:00' 
      AND dt < '2023-12-21 20:00:00'
) AS t
GROUP BY site, DAY(dt), HOUR(dt)
ORDER BY DAY(dt), HOUR(dt), site;

方案优势 #

• 窗口函数LAG()逻辑直观，直接关联当前行与上一行数据，无需复杂关联；
• 依托dt列的索引，排序操作会被优化，避免全表扫描，性能更优；
• 支持按站点、时间维度灵活分组，适配多维度统计需求。

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