将一天数据分割成N个连续片段

dian张

于 2022-09-19 09:21:51 发布

阅读量237

点赞数

分类专栏： flink 文章标签：数据库

本文链接：https://blog.csdn.net/u013086392/article/details/126926973

版权

flink 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

当数据相隔误差小于某个值时候，认为他们是连续的，将其分割成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