Oracle两行数据相减

突然接到一个需求，所有的数据都在一张大表里，想要找出创建时间间隔大于15分钟的数据。
整体思路为——将创建日期转为秒来计算，but如何进行一张表内两行时间列的差值计算呢。
这里就用了Oracle提供的Lag函数。

上图为数据样例，就是上一行减去下一行，以此类推知道最后。
现在使用Lag函数将CreateAt列，平移添加另一列Last_CreateAt,这里不是简单的平移而是把CreateAt的第一行作为了Last_CreateAt的第二行,这样的话，我们将CreateAt-Last_CreateAt就得到了两行之间的差值了

with temp as(
  SELECT 
       CreateAt, 
       LAG(CreateAt) OVER(ORDER BY CreateAt) Last_CreateAt
  FROM table1
  WHERE VIN = 'xxxx' 
  ORDER BY CreateAt
)
select * from temp

下图为使用Lag函数后的样子
接着将日期类型的数值转为秒级使用UNIX_TIMESTAMP函数

with temp as(
  SELECT 
        VIN ,CreateAt, 
        LAG(CreateAt) OVER(ORDER BY CreateAt) Last_CreateAt,
  FROM table1
  WHERE VIN = 'xxxx' 
  ORDER BY CreateAt
),
temp2 as (
  SELECT  VIN ,CreateAt, Last_CreateAt,
  UNIX_TIMESTAMP(CreateAt) as seconds,
  NVL (UNIX_TIMESTAMP(Last_CreateAt),1) as last_seconds
  FROM temp
)
select * from temp2

转换后的结果图
接着就计算差值呗，将大于15分钟的过滤出来

with temp as(
  SELECT 
        VIN ,CreateAt, 
        LAG(CreateAt) OVER(ORDER BY CreateAt) Last_CreateAt
  FROM tabel1
  WHERE VIN = 'xxxx' 
  ORDER BY CreateAt
),
temp2 as (
  SELECT  VIN ,CreateAt, Last_CreateAt,
  UNIX_TIMESTAMP(CreateAt) as seconds,
  NVL (UNIX_TIMESTAMP(Last_CreateAt),1) as last_seconds
  FROM temp
)
SELECT  
        ROW_NUMBER() OVER ( ORDER BY CreateAt ) AS rid,
        temp2.CreateAt,temp2.Last_CreateAt,
        temp2.vin, 
        (temp2.seconds - temp2.last_seconds) as vals
  FROM temp2 
  WHERE temp2.last_seconds is not NULL
  AND (temp2.seconds - temp2.last_seconds) >900

结果图为
那么假设15分钟内的中间有距离，如果想要计算时间大于15分的数据中间的速度，该怎么计算呢。这时需要运用另一个函数Lead——是将数据往上平移。在本次代码中也就是将Last_CreateAt网上平移，与原始的CreateAt中间形成了一个大于15分钟的区间。这样就可以计算距离了。
下面就将整个代码贡献上来了。

with temp as(
SELECT 
      VIN ,CreateAt, LAG(CreateAt) OVER(ORDER BY CreateAt) Last_CreateAt,
      VehTotDistance,
      LAG(VehTotDistance) OVER(ORDER BY CreateAt) last_VehTotDistance
FROM table1
WHERE VIN = 'xxxx' 
ORDER BY CreateAt
),
temp2 as (
SELECT  VIN ,CreateAt, Last_CreateAt,VehTotDistance,last_VehTotDistance,
UNIX_TIMESTAMP(CreateAt) as seconds,
NVL (UNIX_TIMESTAMP(Last_CreateAt),1) as last_seconds
FROM temp
),
temp3 as(
 SELECT  
      ROW_NUMBER() OVER ( ORDER BY CreateAt ) AS rid,
      temp2.CreateAt,temp2.Last_CreateAt,
      lead(Last_CreateAt) Over(ORDER BY CreateAt) new_Last,
      VehTotDistance,last_VehTotDistance,
      lead(last_VehTotDistance) Over(ORDER BY CreateAt) new_last_VehTotDistance,
      temp2.vin, 
      (temp2.seconds - temp2.last_seconds) as vals
FROM temp2 
WHERE temp2.last_seconds is not NULL
AND (temp2.seconds - temp2.last_seconds) >900
)
SELECT  
     temp3.CreateAt beginTime,new_Last endTime,
     NVL(new_last_VehTotDistance - VehTotDistance,0) Distance,
     NVL((new_last_VehTotDistance - VehTotDistance)/3600,0) V
FROM   temp3

最终的结果为

The End Thanks