MySQL 連續範圍查詢技巧：利用行號差值分組高效找出連續區間

MySQL 連續範圍查詢技巧

業務中常需從離散的數字序列 (如設備運行號、流水號) 中找出「連續區間」。本文介紹經典的行號差值分組法：利用連續數字與其行號的差值恆定特性，單條 SQL 高效解決。

核心邏輯

對於連續數列 1,2,3,4,5，其行號 (1,2,3,4,5) 與數值差值皆為 0；若序列斷裂 1,2, 5,6，前兩差值為 0，後兩差值為 3。**差值相同即屬同一連續區間**。

逐步構建 SQL (MySQL 8.0+ CTE 寫法)

步驟 1：轉型排序並產生行號

WITH sorted AS (
  SELECT 
    CAST(running_number AS UNSIGNED) AS rn_val,
    ROW_NUMBER() OVER (ORDER BY CAST(running_number AS UNSIGNED)) AS seq
  FROM device_data 
  WHERE device_id = 13
)

步驟 2：計算差值

, diff_calc AS (
  SELECT rn_val, seq, (rn_val - seq) AS diff_val
  FROM sorted
)

步驟 3：按差值分組取極值

SELECT 
  MIN(rn_val) AS range_start,
  MAX(rn_val) AS range_end,
  COUNT(*) AS continuity_count
FROM diff_calc
GROUP BY diff_val
ORDER BY range_start;

完整單條 SQL

WITH sorted AS (
  SELECT CAST(running_number AS UNSIGNED) AS rn_val,
         ROW_NUMBER() OVER (ORDER BY CAST(running_number AS UNSIGNED)) AS seq
  FROM device_data WHERE device_id = 13
),
diff_calc AS (
  SELECT rn_val, (rn_val - seq) AS diff_val FROM sorted
)
SELECT MIN(rn_val) AS min_val, MAX(rn_val) AS max_val
FROM diff_calc
GROUP BY diff_val
ORDER BY min_val;

MySQL 5.7 兼容寫法 (用戶變數模擬行號)

SELECT MIN(rn_val) AS min_val, MAX(rn_val) AS max_val
FROM (
  SELECT 
    CAST(running_number AS UNSIGNED) AS rn_val,
    @row := @row + 1 AS seq,
    (CAST(running_number AS UNSIGNED) - @row) AS diff_val
  FROM device_data, (SELECT @row := 0) r
  WHERE device_id = 13
  ORDER BY rn_val
) t
GROUP BY diff_val
ORDER BY min_val;

應用場景擴展

• 缺口檢測： 反向思維，找出差值變化點即為斷點。
• 日期連續性： 將日期轉為天數 (TO_DAYS) 同理適用。
• 分組連續： 在 PARTITION BY group_id 中應用窗口函數。

參考：《MySQL技術內幕：SQL編程》2.6.2 連續範圍問題。