分桶排序算法在SQL中应用

简介: 分桶一词,大家应该不陌生,使用过Hive的同学都知道,hive里有个分通表,即针对某一列进行哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。写sql时将数据划分到对应组中进行分析也正是运用了分桶

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

案例:运输车辆上安装的有一设备可以监控到车辆启停状态,某天的监控状态数据如下表: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整体往下移动一条数据的距离,会发现不同分组数据有交叉,有了这个交叉之后,可以对数据重新标记

image.png

新标记的一列数据进行累加,0值相加还未0,遇到1就累积增1,这就行成了分组效果,也即是将数据划分为不同的桶,可以利用sum(if)组合进行实现,这在之前的文章分析中已经直接用了但未做具体解释

image.png

  1. 首先生成示例数据
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))
  1. 数据移动采用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
)
  1. 使用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
  1. 计算停车次数
tb4 as(select        device_id,        device_time,        ac_state,datetime,        flag,        max(flag) over(partition by device_id) ct
from tb3
)
  1. 按设备和分桶号进行分组统计结果
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

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

拜了个拜

目录
打赏
0
0
0
1
106
分享
相关文章
企业上网监控系统中红黑树数据结构的 Python 算法实现与应用研究
企业上网监控系统需高效处理海量数据,传统数据结构存在性能瓶颈。红黑树通过自平衡机制,确保查找、插入、删除操作的时间复杂度稳定在 O(log n),适用于网络记录存储、设备信息维护及安全事件排序等场景。本文分析红黑树的理论基础、应用场景及 Python 实现,并探讨其在企业监控系统中的实践价值,提升系统性能与稳定性。
18 1
基于 C++ 语言的迪杰斯特拉算法在局域网计算机管理中的应用剖析
在局域网计算机管理中,迪杰斯特拉算法用于优化网络路径、分配资源和定位故障节点,确保高效稳定的网络环境。该算法通过计算最短路径,提升数据传输速率与稳定性,实现负载均衡并快速排除故障。C++代码示例展示了其在网络模拟中的应用,为企业信息化建设提供有力支持。
125 15
监控局域网其他电脑:Go 语言迪杰斯特拉算法的高效应用
在信息化时代,监控局域网成为网络管理与安全防护的关键需求。本文探讨了迪杰斯特拉(Dijkstra)算法在监控局域网中的应用,通过计算最短路径优化数据传输和故障检测。文中提供了使用Go语言实现的代码例程,展示了如何高效地进行网络监控,确保局域网的稳定运行和数据安全。迪杰斯特拉算法能减少传输延迟和带宽消耗,及时发现并处理网络故障,适用于复杂网络环境下的管理和维护。
公司局域网管理视域下 Node.js 图算法的深度应用研究:拓扑结构建模与流量优化策略探析
本文探讨了图论算法在公司局域网管理中的应用,针对设备互联复杂、流量调度低效及安全监控困难等问题,提出基于图论的解决方案。通过节点与边建模局域网拓扑结构,利用DFS/BFS实现设备快速发现,Dijkstra算法优化流量路径,社区检测算法识别安全风险。结合WorkWin软件实例,展示了算法在设备管理、流量调度与安全监控中的价值,为智能化局域网管理提供了理论与实践指导。
93 3
基于 C# 时间轮算法的控制局域网上网时间与实践应用
在数字化办公与教育环境中,局域网作为内部网络通信的核心基础设施,其精细化管理水平直接影响网络资源的合理配置与使用效能。对局域网用户上网时间的有效管控,已成为企业、教育机构等组织的重要管理需求。这一需求不仅旨在提升员工工作效率、规范学生网络使用行为,更是优化网络带宽资源分配的关键举措。时间轮算法作为一种经典的定时任务管理机制,在局域网用户上网时间管控场景中展现出显著的技术优势。本文将系统阐述时间轮算法的核心原理,并基于 C# 编程语言提供具体实现方案,以期深入剖析该算法在局域网管理中的应用逻辑与实践价值。
69 5
论上网限制软件中 Python 动态衰减权重算法于行为管控领域的创新性应用
在网络安全与行为管理的学术语境中,上网限制软件面临着精准识别并管控用户不合规网络请求的复杂任务。传统的基于静态规则库或固定阈值的策略,在实践中暴露出较高的误判率与较差的动态适应性。本研究引入一种基于 “动态衰减权重算法” 的优化策略,融合时间序列分析与权重衰减机制,旨在显著提升上网限制软件的实时决策效能。
78 2
公司员工电脑监控软件剖析:PHP 布隆过滤器算法的应用与效能探究
在数字化办公的浪潮下,公司员工电脑监控软件成为企业管理的重要工具,它能够帮助企业了解员工的工作状态、保障数据安全以及提升工作效率。然而,随着监控数据量的不断增长,如何高效地处理和查询这些数据成为了关键问题。布隆过滤器(Bloom Filter)作为一种高效的概率型数据结构,在公司员工电脑监控软件中展现出独特的优势,本文将深入探讨 PHP 语言实现的布隆过滤器算法在该软件中的应用。
78 1
解决大小写、保留字与特殊字符问题!Oracle双引号在SQL中的特殊应用
在Oracle数据库开发中,双引号的使用是一个重要但易被忽视的细节。本文全面解析了双引号在SQL中的特殊应用场景,包括解决标识符与保留字冲突、强制保留大小写、支持特殊字符和数字开头标识符等。同时提供了最佳实践建议,帮助开发者规避常见错误,提高代码可维护性和效率。
157 6
基于 PHP 语言的滑动窗口频率统计算法在公司局域网监控电脑日志分析中的应用研究
在当代企业网络架构中,公司局域网监控电脑系统需实时处理海量终端设备产生的连接日志。每台设备平均每分钟生成 3 至 5 条网络请求记录,这对监控系统的数据处理能力提出了极高要求。传统关系型数据库在应对这种高频写入场景时,性能往往难以令人满意。故而,引入特定的内存数据结构与优化算法成为必然选择。
101 3

热门文章

最新文章

AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问