Oracle 统计连续时间间隔的最大记录数

最近需要统计一项数据，原数据包含记录行生成的时间戳字段 OP_TIME 和一个自增序列中间可能不连续的单据编号 SWF_NUM，要求统计相邻两条记录的时间戳间隔不超过 10 分钟的最大连续记录数。概括起来有两个要求：

1. 相邻两条记录的时间戳间隔不超过 10 分钟；
2. 满足第 1 条的记录且连续不间断的最大记录数。

实现方法

整体思路是：

1. 筛选出结果集；
2. 处理结果集：
• 对结果集中符合条件和不符合条件的记录打上标签；
• 同时，通过 ROW_NUMBER() 对结果集排序编号。
3. 第二次处理结果集：
• 对处理好的结果集通过 ROW_NUMBER()=ROW_NUMBER()-1 自关联，过滤掉符合条件的记录；
• 使用 ROW_NUMBER() 对查询结果第二次排序编号。
4. 第三次处理结果集：
• 利用排序编号 ROW_NUMBER()=ROW_NUMBER()-1 对第二次处理得到的结果集自关联；
• 在查询结果中将第一次的排序编号相减，差值-1 就是连续记录数；
• 对上述结果分组取最大值，得到最大连续记录数。

现在用 ROW_NUMBER() 对筛选好的源数据排序，自关联比较相邻记录的时间差，对符合要求的记录通过 ROW_NUMBER() 再排序，得到的结果通过第二次的 ROW_NUMBER() 值自关联，在查询结果中用第一次的 ROW_NUMBER() 值求差，差值就是符合条件的联续记录数，分组后取最大值，得到结果：

1. 处理原数据，给结果加上按 OP_TIME 降序的 ROW_NUMBER()，取别名 SWF_ROW；
2. 对上一步的结果，通过 SWF_ROW = SWF_ROW-1 自关联，比较相邻记录的时间差：在查询结果里对自关联的源数据记录比较时间戳，小于等于 10 分钟的赋值 1（下一步会丢弃这些记录），大于 10 分钟的赋值 0，取别名 TT；
   
3. 对上一步的结果进行执行查询处理，条件里筛选出 TT=0 的记录，给执行结果加上按 SWF_ROW 降序的 ROW_NUMBER()，取别名 RNN，待下一步用；
   
4. 最后，对第三步的结果通过 RNN=RNN-1 自关联，查询结果对 SWF_ROW-SWF_ROW 进行分组统计取最大值。
   

完整 sql 如下：

WITH TA AS ( --原始数据
SELECT TA.UNIT_ID,TA.SWF_NUM,TA.OP_TIME FROM TA
),
MA AS ( --最大时间记录
SELECT X.UNIT_ID,MAX(X.SWF_NUM) AS MAX_SWF_NUM,X.FSCL_DATE,MAX(X.OP_TIME) AS OP_TIME FROM TA X
GROUP BY X.UNIT_ID,X.FSCL_DATE
),
MI AS ( --最小时间记录
SELECT X.UNIT_ID,MIN(X.SWF_NUM) AS MIN_SWF_NUM,X.FSCL_DATE,MIN(X.OP_TIME) AS OP_TIME FROM TA X
GROUP BY X.UNIT_ID,X.FSCL_DATE
),
XR AS (--处理源数据
  SELECT UN.*,ROW_NUMBER() OVER( PARTITION BY UN.UNIT_ID ORDER BY UN.UNIT_ID,UN.OP_TIME DESC) SWF_ROW
  FROM (
    --制造更小记录 开始 在第一个记录前制造一个记录号-1 的更小记录，防止开头出现符合条件的连续记录
    SELECT MI.UNIT_ID,TO_CHAR(MI.MIN_SWF_NUM-1,'FM0000000000') AS SWF_NUM,FSCL_DATE,
      (MI.OP_TIME-11/(24*60)) AS OP_TIME --制单时间比第一个真实记录的制单时间早 11 分钟
    FROM MI MI --制造第一个相差分钟数大于 11 分钟（反正间隔大于 10 分钟就行）的记录，使之不符合要求
    --制造更小记录 结束
    UNION
    --正常记录 开始
    SELECT X.UNIT_ID,X.SWF_NUM,X.FSCL_DATE,X.OP_TIME FROM TA X
    --正常记录 结束
    UNION
    --制造更大记录 开始 在第后一个记录后制造一个记录号+1 的更大记录 
    SELECT MA.UNIT_ID,TO_CHAR(MA.MAX_SWF_NUM+1,'FM0000000000') AS SWF_NUM,FSCL_DATE,
      (MA.OP_TIME+11/(24*60)) AS OP_TIME --制单时间比实际最后一个记录制单时间晚 6 分钟
    FROM MA MA  --制造第一个相差分钟数大于 11 分钟的记录，防止结尾出现符合条件的连续记录
    --制造更大记录 结束
  ) UN
),
FI AS(
  SELECT A.*,ROW_NUMBER() OVER( PARTITION BY A.UNIT_ID  ORDER BY A.UNIT_ID,A.SWF_ROW DESC ) RNN FROM (
    SELECT XR.UNIT_ID,XR.SWF_ROW,
      CASE 
        WHEN ROUND((XR.OP_TIME-X.OP_TIME) * 24 * 60,2) <=10 THEN 1
        WHEN ROUND((XR.OP_TIME-X.OP_TIME) * 24 * 60,2) >10 THEN 0 
      END AS TT --是否符合间隔 10 分钟的条件，符合置 1 不符合置 0
    FROM XR XR
    LEFT JOIN XR X ON XR.UNIT_ID=X.UNIT_ID AND XR.FSCL_DATE=X.FSCL_DATE AND X.SWF_ROW-1=XR.SWF_ROW
    WHERE X.SWF_NUM IS NOT NULL
    ORDER BY XR.SWF_NUM DESC
  ) A
  WHERE A.TT='0'
)
SELECT FI.UNIT_ID,MAX(FI.SWF_ROW-NVL(FI2.SWF_ROW,0)) MAX_CNT FROM FI
LEFT JOIN FI FI2 ON FI.UNIT_ID=FI2.UNIT_ID AND FI.RNN=FI2.RNN-1
GROUP BY FI.UNIT_ID