分桶排序算法在SQL中应用

2021-09-17 21:27:44

业务需求分析中对数据按时序划分为不同的片段，针对相应片段进行分析的场景也有不少：停车时长、运行时长、断电时长等等。现结合实际需求的简化版来分析下如何运用分桶算法

案例：运输车辆上安装的有一设备可以监控到车辆启停状态，某天的监控状态数据如下表：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
    from values('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 order by 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 order by device_time) flag
    from tb2
    where ac_state = 0 --过滤全为0的数据方便进行分桶
)
--结果展示如下
device_id   device_time ac_state    datetime    lag_ac_state    flag
E1  1628317421  0   2021-08-07 14:23:41 1   1
E1  1628317422  0   2021-08-07 14:23:42 0   1
E1  1628317423  0   2021-08-07 14:23:43 0   1
E1  1628317424  0   2021-08-07 14:23:44 0   1
E1  1628317425  0   2021-08-07 14:23:45 0   1
E1  1628317429  0   2021-08-07 14:23:49 1   2
E1  1628317430  0   2021-08-07 14:23:50 0   2
E1  1628317431  0   2021-08-07 14:23:51 0   2
E2  1628317510  0   2021-08-07 14:25:10 1   1
E2  1628317511  0   2021-08-07 14:25:11 0   1
E2  1628317515  0   2021-08-07 14:25:15 1   2
E2  1628317516  0   2021-08-07 14:25:16 0   2
E2  1628317517  0   2021-08-07 14:25:17 0   2
E2  1628317518  0   2021-08-07 14:25:18 0   2
E2  1628317521  0   2021-08-07 14:25:21 1   3
E2  1628317522  0   2021-08-07 14:25:22 0   3
E2  1628317523  0   2021-08-07 14:25:23 0   3
E2  1628317524  0   2021-08-07 14:25:24 0   3
E2  1628317525  0   2021-08-07 14:25:25 0   3
E2  1628317526  0   2021-08-07 14:25:26 0   3

计算停车次数

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) as date,
    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
group by device_id,flag;
--结果如下
date    device_id   power_off_ct    sn  power_off_duration  start_time  end_time
2021-08-07  E1  2   1   4   2021-08-07 14:23:41 2021-08-07 14:23:45
2021-08-07  E1  2   2   2   2021-08-07 14:23:49 2021-08-07 14:23:51
2021-08-07  E2  3   1   1   2021-08-07 14:25:10 2021-08-07 14:25:11
2021-08-07  E2  3   2   3   2021-08-07 14:25:15 2021-08-07 14:25:18
2021-08-07  E2  3   3   5   2021-08-07 14:25:21 2021-08-07 14:25:26

以上就是分析过程，在业务分析过程中该方法能很好的解决类似需求，举一反三，希望能帮助到大家。

拜了个拜

码农公寓

相关文章