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(
oracle 时间转换
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。