当数据相隔误差小于某个值时候,认为他们是连续的,将其分割成N个片段进行计算
1.计算出相邻数据时间差秒数
2. sum(if(abs(date_diff) > 10, 1, 0)) over ( PARTITION BY vin ORDER BY message_time rows BETWEEN unbounded preceding AND current ROW ) group_id
借用sum,将小于某个阈值的归属一组,sum(0),否则就是sum(1)新组
select vin,
message_time_first,
message_time_last,
sum(BMS_BattCurr)
from (select vin,
message_time,
BMS_BattCurr,
BMS_BattSocAct,
group_id,
first_value(BMS_BattSocAct) over (PARTITION BY concat( vin, group_id) ORDER BY message_time) as soc_first,
first_value(BMS_BattSocAct) over (PARTITION BY concat( vin, group_id) ORDER BY message_time desc) as soc_last,
first_value(message_time) over (PARTITION BY concat( vin, group_id) ORDER BY message_time) as message_time_first,
first_value(message_time) over (PARTITION BY concat( vin, group_id) ORDER BY message_time desc) as message_time_last
from (select vin,
message_time,
next_time,
BMS_BattCurr,
BMS_BattSocAct,
date_diff,
sum(if(abs(date_diff) > 10, 1, 0)) over ( PARTITION BY vin ORDER BY message_time rows BETWEEN unbounded preceding AND current ROW ) group_id
from (select vin,
message_time,
BMS_BattCurr,
BMS_BattSocAct,
next_time,
time_to_sec(timediff(message_time, next_time)) date_diff
from (SELECT vin,
message_time,
BMS_BattCurr,
BMS_BattSocAct,
lead(message_time, 1, '9999-02-02 00:00:00')
OVER ( PARTITION BY vin ORDER BY message_time ) AS next_time
FROM a) b) c
order by vin, message_time) d) e
where soc_last - soc_first > 30
group by vin,
message_time_first,
message_time_last,
soc_last - soc_first
order by vin, message_time_first