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

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

  1. 相邻两条记录的时间戳间隔不超过10分钟;
  2. 满足第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 进行分组统计取最大值。

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分钟的记录,使之不符合要求
--制造更小记录 结束
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 --制造第一个相差分钟数大于5分钟的记录,防止结尾出现符合条件的连续记录
--制造更大记录 结束
) 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) <=10 THEN 1 WHEN ROUND((XR.OP_TIME-X.OP_TIME) * 24 * 60) >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

发表评论

评论(2)

  1. 姜辰

    专业的文章啊!

    1. 秩秩斯干

      @姜辰 哈哈,之前没这么做过,想到这个方法就记下来,一方面防止忘记,另一个面可以给需要的朋友提供一点思路。