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 如下：

创建示例数据表：

CREATE TABLE ta (unit_id int, swf_num int, fscl_date date, op_time date DEFAULT CURRENT_TIMESTAMP);

写入测试数据：

INSERT INTO ta VALUES (1950, 1000, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 22:25:45', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1950, 1001, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 22:27:11', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1950, 1002, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 22:38:29', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1950, 1003, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 22:39:22', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1950, 1004, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 22:43:45', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1950, 1006, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 22:58:50', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1950, 1007, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 22:59:55', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1957, 1000, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 09:06:41', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1957, 1001, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 09:09:36', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1957, 1002, to_date('2020-06-27', 'YYYY-MM-DD'), to_date('2020-06-27 12:20:59', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1957, 1003, to_date('2020-06-28', 'YYYY-MM-DD'), to_date('2020-06-28 07:50:09', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1957, 1004, to_date('2020-06-28', 'YYYY-MM-DD'), to_date('2020-06-28 08:14:37', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1957, 1005, to_date('2020-06-28', 'YYYY-MM-DD'), to_date('2020-06-28 08:19:00', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1950, 1006, to_date('2020-06-28', 'YYYY-MM-DD'), to_date('2020-06-28 10:18:12', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1950, 1007, to_date('2020-06-28', 'YYYY-MM-DD'), to_date('2020-06-28 10:24:57', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1950, 1008, to_date('2020-06-29', 'YYYY-MM-DD'), to_date('2020-06-29 07:36:07', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1957, 1006, to_date('2020-06-29', 'YYYY-MM-DD'), to_date('2020-06-29 09:10:39', 'YYYY-MM-DD hh24:mi:ss'));
INSERT INTO ta VALUES (1957, 1007, to_date('2020-06-29', 'YYYY-MM-DD'), to_date('2020-06-29 12:30:19', 'YYYY-MM-DD hh24:mi:ss'));

WITH TEMP_TA AS ( --原始数据
	SELECT TA.UNIT_ID, TA.SWF_NUM, TA.OP_TIME, TA.FSCL_DATE
	  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 TEMP_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 TEMP_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,MI.MIN_SWF_NUM-1 AS SWF_NUM, FSCL_DATE,
		   MI.OP_TIME-11/(24*60) AS OP_TIME --制单时间比第一个真实记录的制单时间早 11 分钟
    FROM MI MI --制造第一个相差分钟数大于 11 分钟（反正间隔大于 10 分钟就行）的记录，使之不符合要求
    --制造更小记录 结束
    UNION ALL
    --正常记录 开始
    SELECT X.UNIT_ID,X.SWF_NUM,X.FSCL_DATE,X.OP_TIME FROM TEMP_TA X
    --正常记录 结束
    UNION ALL
    --制造更大记录 开始 在第后一个记录后制造一个记录号+1 的更大记录 
    SELECT MA.UNIT_ID,MA.MAX_SWF_NUM + 1 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, XR.FSCL_DATE,
      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, FI.FSCL_DATE, 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, FI.FSCL_DATE
HAVING MAX(FI.SWF_ROW-COALESCE(FI2.SWF_ROW,0)) > 1
ORDER BY FI.UNIT_ID,FI.FSCL_DATE