hive 下一行時間減去上一行，多行時間減法

一個人有多行時間，需要每一行 時間與下一行時間間隔

也就是：利用下一行的時間 減去上一行的時間

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