sql表记录邻近计算

引入：窗口函数（row_number、LEAD）

原始数据表

表名aaa

需求

按照uid，cid对ts邻近记录进行减法，获取记录的停留时间

sql如下

SELECT uid,cid,chapter,ts,action_type,
row_number() over(partition by uid,cid order by ts) as row_rank,
LEAD(ts, 1) OVER (partition by uid,cid ORDER BY ts) AS next_ts
FROM aaa;

执行结果

计算时间跨度

sql如下

SELECT uid,cid,chapter,ts,action_type,row_rank,next_ts,next_ts-ts as time_elapse
from
(
SELECT uid,cid,chapter,ts,action_type,
row_number() over(partition by uid,cid order by ts) as row_rank,
LEAD(ts, 1) OVER (partition by uid,cid ORDER BY ts) AS next_ts
FROM aaa
)t1

执行结果如下