hive 下一行时间减去上一行，多行时间减法

最新推荐文章于 2023-07-21 11:28:11 发布

置顶不爱学习的

最新推荐文章于 2023-07-21 11:28:11 发布

阅读量6.4k

点赞数 1

分类专栏： hive sql

本文链接：https://blog.csdn.net/weixin_44788745/article/details/102695855

版权

hive sql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一个人有多行时间，需要每一行时间与下一行时间间隔

也就是：利用下一行的时间减去上一行的时间

1，原始数据长这样：根据需要保留分秒

usercode time1
8501 2019-07-24
8501 2019-07-25
8501 2019-07-30
8501 2019-08-01
8501 2019-08-02

2，最后需要的数据成这样：在这里插入图片描述

3,怎么实现

select usercode,
time1,
ROW_NUMBER() OVER(PARTITION BY usercode ORDER BY time1) AS rank1,
LAG(time1,1) OVER(PARTITION BY usercode ORDER BY time1) AS time2
from 表1
;

4， gap 是下一步时间差做减法

select usercode,time1,rank1,time2 ,datediff(time1,time2)gap from(
select
usercode,
time1,
ROW_NUMBER() OVER(PARTITION BY usercode ORDER BY time1) AS rank1,
LAG(time1,1) OVER(PARTITION BY usercode ORDER BY time1) AS time2
from 表1 )a

– 可以改序号，或者里面填充
– LAG(daily,1,‘1970-01-01’) OVER(PARTITION BY usercode ORDER BY daily) AS last_1_time
– LAG(activatedtime,2) OVER(PARTITION BY usercode ORDER BY activatedtime) AS last_2_time