Using hive to calculate the time difference between two adjacent data

1. Advanced usage of the hive row_number () function row_num displays the number of data entries by a field Partition

Select IMEI, ts, fuel_instant, gps_long1_, gps_latitude,Row_number () over (partition by IMEI order by ts ASC) as row_numFrom sample_data_2

2. row_num is continuous, join itself, and then subtract time to calculate the difference
Create Table obd_20140101

Select. IMEI,. row_num,. TS, coalesce (unix_timestamp (. TS, 'yyyy-mm-dd hh: mm: Ss. s '), 0)-unix_timestamp (B. TS, 'yyyy-mm-dd hh: mm: Ss. s ') as intervel,. fuel_instant,. gps_speed as obd_speed,. gps_status,. gps_longpolling,. gps_latitude,. direct_angle,. obdspeed from obddata_20140101 a join obddata_20140101 B on. IMEI = B. IMEI and. row_num = B. row_num + 1

Using hive to calculate the time difference between two adjacent data