车联网案例,轨迹清洗 - 阿里云RDS PostgreSQL最佳实践 - 窗口查询

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , 窗口函数 , 车联网 , 轨迹 , 轨迹清洗 , lag , lead


背景

车联网中一个非常典型的场景是采集车辆的行驶轨迹,通常来说车辆的轨迹并不会实时上报,可能会堆积若干条轨迹记录,或者间隔多少时间上报一次。

一个典型的数据结构如下

(car_id, pos geometry, crt_time timestamp)  

车辆在行驶,行驶过程中会遇到堵车,红绿灯,那么上报的轨迹记录可能是这样的

1, 位置1, '2017-01-01 12:00:00'  
1, 位置1, '2017-01-01 12:00:05'  
1, 位置1, '2017-01-01 12:00:10'  
1, 位置1, '2017-01-01 12:00:15'  
1, 位置1, '2017-01-01 12:00:20'  
1, 位置2, '2017-01-01 12:00:30'  

也就是说,在同一个位置,因为堵车、等红灯,可能会导致上传多条记录。

那么就涉及到在数据库中清洗不必要的等待记录的需求,在一个点,我们最多保留2条记录,表示到达这个位置和离开这个位置。

这个操作可以使用窗口函数实现。

当然从最佳效率角度来分析,轨迹清洗这个事情,在终端做是更合理的,一个位置的起始点,只留两条。

例子

1、设计表结构

create table car_trace (cid int, pos point, crt_time timestamp);  

2、生成1000万测试数据,假设有1000量车,(为了让数据更容易出现重复,为了测试看效果,位置使用25个点)

insert into car_trace select random()*999, point((random()*5)::int, (random()*5)::int), clock_timestamp() from generate_series(1,10000000);  

3、创建索引

create index idx_car on car_trace (cid, crt_time);  

4、查询数据layout

select * from car_trace where cid=1 order by crt_time limit 1000;  
  
   1 | (3,1) | 2017-07-22 21:30:09.84984  
   1 | (1,4) | 2017-07-22 21:30:09.850297  
   1 | (1,4) | 2017-07-22 21:30:09.852586  
   1 | (1,4) | 2017-07-22 21:30:09.854155  
   1 | (1,4) | 2017-07-22 21:30:09.854425  
   1 | (3,1) | 2017-07-22 21:30:09.854493  
  
观察到了几个重复。  

5、使用窗口过滤单一位置记录,最多仅保留到达这个位置和离开这个位置的两条记录。

这里用到两个窗口函数:

lag,表示当前记录的前面一条记录。

lead,表示当前记录的下一条记录。

判断到达点、离去点的方法如下:

  • 当前pos 不等于 前一条pos,说明这条记录是当前位置的到达点。

  • 当前pos 不等于 下一条pos,说明这条记录是当前位置的离去点。

  • 前一条pos 为空,说明这条记录是第一条记录。

  • 下一条pos 为空,说明这条记录是最后一条记录。

select * from   
(  
select   
  *,   
  lag(pos) over (partition by cid order by crt_time) as lag,   
  lead(pos) over (partition by cid order by crt_time) as lead   
from car_trace   
  where cid=1   
  and crt_time between '2017-07-22 21:30:09.83994' and '2017-07-22 21:30:09.859735'  
) t  
  where pos <> lag  
  or pos <> lead  
  or lag is null  
  or lead is null;  
  
 cid |  pos  |          crt_time          |  lag  | lead    
-----+-------+----------------------------+-------+-------  
   1 | (2,1) | 2017-07-22 21:30:09.83994  |       | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.839953 | (2,1) | (5,2)  
   1 | (5,2) | 2017-07-22 21:30:09.840704 | (3,1) | (4,4)  
   1 | (4,4) | 2017-07-22 21:30:09.84179  | (5,2) | (5,2)  
   1 | (5,2) | 2017-07-22 21:30:09.843787 | (4,4) | (1,5)  
   1 | (1,5) | 2017-07-22 21:30:09.844165 | (5,2) | (0,5)  
   1 | (0,5) | 2017-07-22 21:30:09.84536  | (1,5) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.845896 | (0,5) | (3,3)  
   1 | (3,3) | 2017-07-22 21:30:09.846958 | (4,1) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.84984  | (3,3) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.850297 | (3,1) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.854425 | (1,4) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.854493 | (1,4) | (3,2)  
   1 | (3,2) | 2017-07-22 21:30:09.854541 | (3,1) | (2,0)  
   1 | (2,0) | 2017-07-22 21:30:09.855297 | (3,2) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.857592 | (2,0) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.857595 | (4,1) | (0,4)  
   1 | (0,4) | 2017-07-22 21:30:09.857597 | (4,1) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.858996 | (0,4) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.859735 | (3,1) |   
(20 rows)  

未加清洗轨迹,得到的结果如下:

select   
  *,   
  lag(pos) over (partition by cid order by crt_time) as lag,   
  lead(pos) over (partition by cid order by crt_time) as lead   
from car_trace   
  where cid=1   
  and crt_time between '2017-07-22 21:30:09.83994' and '2017-07-22 21:30:09.859735';  
  
 cid |  pos  |          crt_time          |  lag  | lead    
-----+-------+----------------------------+-------+-------  
   1 | (2,1) | 2017-07-22 21:30:09.83994  |       | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.839953 | (2,1) | (5,2)  
   1 | (5,2) | 2017-07-22 21:30:09.840704 | (3,1) | (4,4)  
   1 | (4,4) | 2017-07-22 21:30:09.84179  | (5,2) | (5,2)  
   1 | (5,2) | 2017-07-22 21:30:09.843787 | (4,4) | (1,5)  
   1 | (1,5) | 2017-07-22 21:30:09.844165 | (5,2) | (0,5)  
   1 | (0,5) | 2017-07-22 21:30:09.84536  | (1,5) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.845896 | (0,5) | (3,3)  
   1 | (3,3) | 2017-07-22 21:30:09.846958 | (4,1) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.84984  | (3,3) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.850297 | (3,1) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.852586 | (1,4) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.854155 | (1,4) | (1,4)  
   1 | (1,4) | 2017-07-22 21:30:09.854425 | (1,4) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.854493 | (1,4) | (3,2)  
   1 | (3,2) | 2017-07-22 21:30:09.854541 | (3,1) | (2,0)  
   1 | (2,0) | 2017-07-22 21:30:09.855297 | (3,2) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.857592 | (2,0) | (4,1)  
   1 | (4,1) | 2017-07-22 21:30:09.857595 | (4,1) | (0,4)  
   1 | (0,4) | 2017-07-22 21:30:09.857597 | (4,1) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.858996 | (0,4) | (3,1)  
   1 | (3,1) | 2017-07-22 21:30:09.859735 | (3,1) |   
(22 rows)  

使用lag, lead清洗掉了停留过程中的记录。

被跟踪对象散落导致的扫描IO放大的优化

因为业务中涉及的车辆ID可能较多,不同车辆汇聚的数据会往数据库中写入,如果不做任何优化,那么不同车辆的数据进入数据库后,可能是交错存放的,也就是说一个数据块中,可能有不同车辆的数据。

那么在查询单一车辆的轨迹时,会扫描很多数据块(扫描IO放大)。

优化思路有两种。

1、业务端汇聚分组排序后写入数据库。例如程序在接收到车辆终端提交的数据后,按车辆ID分组,按时间排序,写入数据库(insert into tbl values (),(),...();)。这样的话,同样车辆的数据,可能会尽可能的落在同一个数据块内。

2、数据库端使用分区,重组数据。例如,按车辆ID,每辆车、或者车辆HASH分区存放。

以上两种方法,都是要将数据按查询需求重组,从而达到降低扫描IO的目的。

这个方法与《PostgreSQL 证券行业数据库需求分析与应用》的方法类似,有兴趣的朋友可以参考。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
20天前
|
SQL 监控 关系型数据库
|
28天前
|
弹性计算 运维 Java
最佳实践:阿里云倚天ECS在千寻位置时空智能服务的规模化应用
阿里云、平头哥及安谋科技联合举办的飞天技术沙龙探讨了倚天Arm架构在业务创新中的应用。活动中,千寻位置运维专家分享了将核心业务迁移到倚天处理器ECS实例的成功案例,强调了倚天处理器的高能效比和降本增效优势。迁移过程涉及操作系统、CICD系统和监控系统的适配,以及业务系统的性能测试。目前,千寻已迁移了上千台ECS实例到倚天处理器,实现了成本和效率的显著提升。未来计划继续扩展倚天处理器在核心业务和K8S中的应用。
|
15天前
|
SQL 关系型数据库 数据库
Python执行PostgreSQL数据库查询语句,并打印查询结果
本文介绍了如何使用Python连接和查询PostgreSQL数据库。首先,确保安装了`psycopg2`库,然后创建数据库连接函数。接着,展示如何编写SQL查询并执行,例如从`employees`表中选取所有记录。此外,还讨论了处理查询结果、格式化输出和异常处理的方法。最后,提到了参数化查询和事务处理以增强安全性及确保数据一致性。
Python执行PostgreSQL数据库查询语句,并打印查询结果
|
5天前
|
人工智能 自然语言处理 算法
阿里云PAI大模型评测最佳实践
在大模型时代,模型评测是衡量性能、精选和优化模型的关键环节,对加快AI创新和实践至关重要。PAI大模型评测平台支持多样化的评测场景,如不同基础模型、微调版本和量化版本的对比分析。本文为您介绍针对于不同用户群体及对应数据集类型,如何实现更全面准确且具有针对性的模型评测,从而在AI领域可以更好地取得成就。
|
15天前
|
SQL 关系型数据库 数据库
Python查询PostgreSQL数据库
木头左教你如何用Python连接PostgreSQL数据库:安装`psycopg2`库,使用提供的代码模板建立连接。执行SQL脚本,如创建表、插入和查询数据,同时掌握错误处理和事务管理,确保数据一致性。关注性能优化和高级特性,如索引、批量操作以及PostgreSQL的存储过程和触发器。别忘了点赞哦!
|
13天前
|
关系型数据库 数据管理 数据库
数据管理DMS产品使用合集之如何极速恢复RDS(关系型数据库服务)中的数据表
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
|
15天前
|
SQL 关系型数据库 数据库
Python查询PostgreSQL数据库
木头左教你如何用Python连接PostgreSQL数据库:安装`psycopg2`库,建立连接,执行SQL脚本如创建表、插入数据,同时掌握错误处理和事务管理。别忘了性能优化,利用索引、批量操作提升效率。下期更精彩!💡 csvfile
Python查询PostgreSQL数据库
|
19天前
|
SQL 关系型数据库 数据库
nacos 2.2.3版本 查看配置文件的历史版本的接口 是针对MySQL数据库的sql 改成postgresql后 sql语句报错 该怎么解决
在Nacos 2.2.3中切换到PostgreSQL后,执行配置文件历史版本分页查询出错,因`LIMIT 0, 10`语法不被PostgreSQL支持,需改为`LIMIT 10 OFFSET 0`。仅当存在历史版本时报错。解决方案是调整查询SQL以兼容PostgreSQL语法。
|
21天前
|
监控 关系型数据库 运维
|
25天前
|
监控 关系型数据库 数据库
关系型数据库识别常查询字段
【5月更文挑战第20天】
32 4

相关产品

  • 云数据库 RDS MySQL 版
  • 云原生数据库 PolarDB