业务需求分析中对数据按时序划分为不同的片段，针对相应片段进行分析的场景也有不少：停车时长、运行时长、断电时长等等。现结合实际需求的简化版来分析下如何运用分桶算法
案例：运输车辆上安装的有一设备可以监控到车辆启停状态，某天的监控状态数据如下表：device_id为设备id,device_time为设备上传数据的时间一秒一上传，ac_state为车辆启动停止的状态(1启动 0熄火)，以下是模拟数据
device_id |
device_time |
ac_state |
... |
... |
... |
E1 |
1628317418 |
1 |
E1 |
1628317419 |
1 |
E1 |
1628317420 |
1 |
E1 |
1628317421 |
0 |
E1 |
1628317422 |
0 |
E1 |
1628317423 |
0 |
E1 |
1628317424 |
0 |
E1 |
1628317425 |
0 |
E1 |
1628317426 |
1 |
E1 |
1628317427 |
1 |
E1 |
1628317428 |
1 |
E1 |
1628317429 |
0 |
E1 |
1628317430 |
0 |
E1 |
1628317431 |
0 |
E1 |
1628317432 |
1 |
E1 |
1628317433 |
1 |
E1 |
1628317434 |
1 |
E2 |
1628317510 |
0 |
E2 |
1628317511 |
0 |
E2 |
1628317512 |
1 |
E2 |
1628317513 |
1 |
E2 |
1628317514 |
1 |
E2 |
1628317515 |
0 |
E2 |
1628317516 |
0 |
E2 |
1628317517 |
0 |
E2 |
1628317518 |
0 |
E2 |
1628317519 |
1 |
E2 |
1628317520 |
1 |
E2 |
1628317521 |
0 |
E2 |
1628317522 |
0 |
E2 |
1628317523 |
0 |
E2 |
1628317524 |
0 |
E2 |
1628317525 |
0 |
E2 |
1628317526 |
0 |
... |
... |
... |
需要分析某天车辆停车次数、停车时长及停车开始和结束时间，如下表所示
date |
device_id |
power_off_ct |
sn |
power_off_duration |
start_time |
end_time |
2021-08-07 |
E1 |
2 |
1 |
5 |
2021-08-07 14:23:41 | 2021-08-07 14:23:45 |
2021-08-07 |
E1 |
2 |
2 |
3 |
2021-08-07 14:23:49 | 2021-08-07 14:23:51 |
... |
... |
... |
... |
... |
... |
... |
分析：观察数据就会发现ac_state字段已经分好组了，这在之前的分析就是一个标记列了(满足条件标记1不满足标记0)，虽已经分好组但是不能直接根据这个组进行计算，我们需要将这个组重新分组并标注递增的组好，如何重新分组呢；我们先看下将ac_state整体往下移动一条数据的距离，会发现不同分组数据有交叉，有了这个交叉之后，可以对数据重新标记
新标记的一列数据进行累加，0值相加还未0，遇到1就累积增1，这就行成了分组效果，也即是将数据划分为不同的桶，可以利用sum(if)组合进行实现，这在之前的文章分析中已经直接用了但未做具体解释
- 首先生成示例数据
with tb1 as(select device_id, device_time, ac_state fromvalues('E1',1628317418,1),('E1',1628317419,1),('E1',1628317420,1),('E1',1628317421,0),('E1',1628317422,0),('E1',1628317423,0),('E1',1628317424,0),('E1',1628317425,0),('E1',1628317426,1),('E1',1628317427,1),('E1',1628317428,1),('E1',1628317429,0),('E1',1628317430,0),('E1',1628317431,0),('E1',1628317432,1),('E1',1628317433,1),('E1',1628317434,1),('E2',1628317510,0),('E2',1628317511,0),('E2',1628317512,1),('E2',1628317513,1),('E2',1628317514,1),('E2',1628317515,0),('E2',1628317516,0),('E2',1628317517,0),('E2',1628317518,0),('E2',1628317519,1),('E2',1628317520,1),('E2',1628317521,0),('E2',1628317522,0),('E2',1628317523,0),('E2',1628317524,0),('E2',1628317525,0),('E2',1628317526,0) t(device_id,device_time,ac_state))
- 数据移动采用lag函数进行
tb2 as(select device_id, device_time, ac_state, from_unixtime(device_time)datetime, lag(ac_state,1,1) over(partition by device_id orderby device_time) lag_ac_state from tb1 )
- 使用sum(if)进行分桶
tb3 as(select device_id, device_time, ac_state,datetime, lag_ac_state, sum(if(ac_state!=lag_ac_state,1,0)) over(partition by device_id orderby device_time) flag from tb2 where ac_state =0--过滤全为0的数据方便进行分桶)--结果展示如下device_id device_time ac_state datetime lag_ac_state flag E1 162831742102021-08-0714:23:4111E1 162831742202021-08-0714:23:4201E1 162831742302021-08-0714:23:4301E1 162831742402021-08-0714:23:4401E1 162831742502021-08-0714:23:4501E1 162831742902021-08-0714:23:4912E1 162831743002021-08-0714:23:5002E1 162831743102021-08-0714:23:5102E2 162831751002021-08-0714:25:1011E2 162831751102021-08-0714:25:1101E2 162831751502021-08-0714:25:1512E2 162831751602021-08-0714:25:1602E2 162831751702021-08-0714:25:1702E2 162831751802021-08-0714:25:1802E2 162831752102021-08-0714:25:2113E2 162831752202021-08-0714:25:2203E2 162831752302021-08-0714:25:2303E2 162831752402021-08-0714:25:2403E2 162831752502021-08-0714:25:2503E2 162831752602021-08-0714:25:2603
- 计算停车次数
tb4 as(select device_id, device_time, ac_state,datetime, flag, max(flag) over(partition by device_id) ct from tb3 )
- 按设备和分桶号进行分组统计结果
select substr(min(datetime),1,10)asdate, device_id, min(ct)as power_off_ct, flag as sn, max(device_time)-min(device_time)as power_off_duration, min(datetime)as start_time, max(datetime)as end_time from tb4 groupby device_id,flag;--结果如下date device_id power_off_ct sn power_off_duration start_time end_time 2021-08-07 E1 2142021-08-0714:23:412021-08-0714:23:452021-08-07 E1 2222021-08-0714:23:492021-08-0714:23:512021-08-07 E2 3112021-08-0714:25:102021-08-0714:25:112021-08-07 E2 3232021-08-0714:25:152021-08-0714:25:182021-08-07 E2 3352021-08-0714:25:212021-08-0714:25:26
以上就是分析过程，在业务分析过程中该方法能很好的解决类似需求，举一反三，希望能帮助到大家。
拜了个拜
