hive 根据记录表，获取开始时间和结束时间，时间差

表结构

id                      int                     主键                  
projectid               int                     项目id              

createdate              string                  创建时间  

select
projectid,
createdate,
next_1_time,
(unix_timestamp(next_1_time,'yyyy-MM-dd HH:mm:ss')- unix_timestamp(createdate,'yyyy-MM-dd HH:mm:ss'))/3600
,desk
from
(
select 
  projectid
  ,createdate
  ,ROW_NUMBER() OVER(PARTITION BY projectid ORDER BY createdate) desk 
  ,LEAD(createdate,1,'null') OVER(PARTITION BY projectid ORDER BY createdate) AS next_1_time
from ods.yp_pai_project_change_log 
where projectid=179
) res
where (desk%2)=1

结果数据

179     2019-01-03 16:14:31.0   2019-01-26 12:54:45.0
179     2019-01-26 12:54:47.0   2019-01-27 11:08:21.0
179     2019-01-27 11:08:25.0   null