统计超阈值数值时合并连续记录的SQL查询方案

这问题我太熟了，用SQL窗口函数就能完美解决——比游标高效N倍，还能轻松适配不同指标和条件，完全不用逐行折腾！

核心思路 #

我们要统计的是连续超过阈值的“事件段”数量，本质就是找出每一段超标记录的起始点，最后数这些起始点的个数就行。比如示例里，13:30是第一段超标的起点（前一条没超标），16:00是第二段的起点（前一条也没超标），所以总共有2次，正好匹配你的预期。

通用SQL实现（兼容大部分主流数据库） #

WITH flagged_records AS (
  -- 第一步：给每条记录标记是否满足积压条件（这里是VALUE>40）
  SELECT
    ENTRYDATETIME,
    METRIC,
    VALUE,
    CASE WHEN VALUE > 40 THEN 1 ELSE 0 END AS is_over_threshold
  FROM log_table
),
event_starts AS (
  -- 第二步：用LAG窗口函数识别新事件的起点
  SELECT
    METRIC,
    DATE(ENTRYDATETIME) AS log_date,
    -- 规则：当前记录超标，且上一条记录不超标（或当前是第一条超标记录），就算新事件开始
    CASE 
      WHEN is_over_threshold = 1 
        AND COALESCE(LAG(is_over_threshold) OVER (
          PARTITION BY METRIC, DATE(ENTRYDATETIME) 
          ORDER BY ENTRYDATETIME
        ), 0) = 0 
      THEN 1 
      ELSE 0 
    END AS is_event_start
  FROM flagged_records
)
-- 第三步：按指标和日期统计事件次数
SELECT
  METRIC,
  log_date,
  SUM(is_event_start) AS backlog_event_count
FROM event_starts
GROUP BY METRIC, log_date;

代码逻辑拆解 #

1. flagged_records CTE：先给每条记录打个标签，区分是否满足你的积压阈值（这里是VALUE>40），后续判断更直观。
2. event_starts CTE：
   • 用PARTITION BY METRIC, DATE(ENTRYDATETIME)确保不同指标、不同日期的记录互不干扰，完美适配“多指标+单日统计”的需求。
   • LAG(is_over_threshold)获取上一条记录的标签值，结合COALESCE处理第一条记录的边界情况（第一条记录没有上一条，默认按0处理）。
   • 只要当前记录超标且上一条没超标，就标记为新事件的起点。
3. 最后分组求和：把所有起点加起来，就是当天该指标的积压事件次数。

为什么这方案比游标好？ #

• 性能碾压：窗口函数是集合运算，数据量大的时候比逐行遍历的游标快几个量级。
• 灵活性拉满：
  • 要换阈值？直接改CASE WHEN VALUE > 40里的条件就行（比如改成VALUE >= 50）。
  • 要统计所有指标？去掉WHERE过滤，分组会自动按指标拆分。
  • 要跨日期统计？把DATE(ENTRYDATETIME)换成周/月维度的分组就行。
• 可读性强：每一步逻辑清晰，后续维护起来比游标容易太多。

适配不同数据库的小细节 #

如果用MySQL，DATE(ENTRYDATETIME)可以换成DATE_FORMAT(ENTRYDATETIME, '%Y-%m-%d')；如果是SQL Server，用CAST(ENTRYDATETIME AS DATE)就行——核心逻辑完全一致。

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