PostgreSQL数据库以任意时间间隔聚合查询group by
文章目录
- 业务场景
- 以固定时间(年/月/日/时/分/秒)聚合
- to_char聚合
- date_trunc聚合
- 以任意时间聚合
- date_bin聚合
- 实际应用
业务场景
- 我们做的是交通信控平台,需要根据实时采集到的交通大数据,计算出一些指标,存储到数据库,供后续聚合查询
- 前端设备上报原始数据,统一使用socket上报,服务端使用netty接收。经过kafka批量发送到采集服务处理,批量写入clickhouse数据库
- 根据原始数据,按照信控周期(相位或周期)或者固定时间(5分钟),计算出数据指标存储PostgreSQL数据库
- 服务端展示时,一部分查询已有数据列表,一部分要按照不同时间粒度聚合展示
- 我们目前使用的PostgreSQL数据库版本为14.2
以固定时间(年/月/日/时/分/秒)聚合
- 对于日期,我们可以存储时间戳、毫秒数、字符串等,处理时再根据相应类型转换下即可
to_char聚合
- 日期时间一般是 年、月、日、时、分、秒,所以精确到这些粒度都比较简单
- 下面给出一个使用
to_char函数的实现,其实主要就是做下字符串截取 - 注意,函数里需要标注参数类型,使用
::DATE标注
to_char(date::DATE, 'YYYY') as year
to_char(date::DATE, 'YYYY-MM') as month
to_char(date::DATE, 'YYYY-MM-DD') as day
to_char(date::DATE, 'YYYY-MM-DD HH24') as hour
to_char(date::DATE, 'YYYY-MM-DD HH24:MI ') as minute
to_char(date::DATE, 'YYYY-MM-DD HH24:MI:SS ') as second
date_trunc聚合
- 使用
date_trunc也可以做到上面的效果,还可以加上不同时区
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-1620:38:40+00');
Result: 2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-1620:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00
以任意时间聚合
- 对于以任意时间聚合,就比较麻烦了,还需要数学计算,SQL写起来会特别长
- 这个问题在PostgreSQL14之后得到解决,因为这个版本增加了一个新函数支持
date_bin - 官方文档描述为:
The function date_bin “bins” the input timestamp into the specified interval (the stride) aligned with a specified origin. - 渣翻一下为
函数date_bin将输入时间戳“存储”到与指定原点对齐的指定间隔(步长)中。 - 有了这个函数后,我们可以很方便的根据任意时间间隔聚合
- 对齐时间可以根据你的需要写,如果都是今年的新数据,你写
2023-01-01都没问题
date_bin聚合
- 下面给出几个示例
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17',TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17',TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30
实际应用
- 根据我们的业务场景,按照15分钟聚合
- 其实时间间隔
15 minutes,是作为参数传递进去的,其他时间间隔也都可以实现 - 给一个官方文档地址:PostgreSQL14
SELECTintersection_id,approach,date_bin ( '15 minutes', time_stamp, TIMESTAMP '2023-01-01' ) AS time_stamp2,SUM ( traffic_flow ) AS traffic_flow,round( AVG ( congestion_index ) :: NUMERIC, 2 ) AS congestion_index,round( AVG ( saturation ) :: NUMERIC, 2 ) AS saturation,round( AVG ( queue_length ) :: NUMERIC, 2 ) AS queue_length,round( AVG ( delay ) :: NUMERIC, 2 ) AS delay
FROMsituation_analysis_intersection
WHEREintersection_id = 1687005 and approach = 'WB'AND time_stamp >= '2023-04-20 00:00:00' AND time_stamp < '2023-04-29 00:00:00'
GROUP BYtime_stamp2,intersection_id,approach
ORDER BYtime_stamp2 LIMIT 20 OFFSET 0
相关文章:
PostgreSQL数据库以任意时间间隔聚合查询group by
文章目录 业务场景以固定时间(年/月/日/时/分/秒)聚合to_char聚合date_trunc聚合 以任意时间聚合date_bin聚合实际应用 业务场景 我们做的是交通信控平台,需要根据实时采集到的交通大数据,计算出一些指标,存储到数据库…...
sql注入(二)盲注,二次注入,宽字节注入
目录 目录 一、布尔盲注 1.判断库名的长度 2.判断数据库名 2.1判断数据库名首字符 2.2 判断数据库名的其余字符 二、时间盲注: 1.判断库名的长度 2.判断库名: 3.判断表名payload: 4.爆出列名 5.爆数据 三、二次注入 1.原理&#…...
Linux 基础操作
Linux学习教程,Linux入门教程(超详细) chown (change owner) : 修改所属用户与组。chmod (change mode) : 修改用户的权限。 要查看文件或目录的属性,可以使用ls命令,加上-l选项。例如ÿ…...
2.4 等比数列
学习步骤: 如果我要学习等比数列,我会按照以下步骤进行学习: 定义和性质:首先了解等比数列的定义和性质,包括公比、首项、通项公式、求和公式等。 例题练习:通过练习一些简单的例题来理解等比数列的概念和…...
2022年新能源汽车专题讲座
2022年新能源汽车专题讲座 单选题(共5题,每题6分) 1、《中华人民共和国数据安全法》自()起施行。 正确答案:C、2021年9月1日 2、典型的智能汽车结构主要分为()个层次。 正确答案…...
Git操作远程仓库
远程仓库 码云 https://gitee.com/ 是国内的一个代码托管平台,由于服务器在国内,所以相比于GitHub,码云速度会更快 码云使用流程 注册账号----登录码云-----点击新建仓库----记得保存地址 GitHub https://github.com/ 是一个面向开源…...
制造策略 ETO、MTO、ATO、MTS
ETO 按交货周期跨度从长到短来讲,首先就是 ETO,Engineer To Order – 面向订单设计、定制生产或特殊生产。 就是客户给的订单,你要生产的话,你之前的原产品改动很大,或者基本上用不上,要完全按照客户的要求…...
Git(六):基本命令(3):储藏、标签、拉取、子模块
目录 17、stash 储藏 17.2 描述 17.3 基本用法 18、tag 标签 18.1 描述 18.2 基本用法 19、fetch 获取 19.1 描述 19.2 基本用法 20、pull 整合 20.1 描述 20.2 基本用法 20.3 pull 与 fetch 的区别 21、push 更新推送 21.1 描述 21.2 基本用法 22、remote 管…...
7.0、Java继承与多态 - 多态的特性
7.0、Java继承与多态 - 多态的特性 面向对象的三大特征:封装性、继承性、多态性; extends继承 或者 implements实现,是多态性的前提; 用学生类创建一个对象 - 小明,他是一个 学生(学生形态)&…...
将系统从机械硬盘迁移至固态硬盘上
1、使用DiskGenius软件、分区助手或者傲梅分区助手进行系统迁移 参考: 系统迁移 - DiskGenius 分区助手专业版如何迁移系统到固态硬盘-迁移系统到固态硬盘的方法_华军软件园 (onlinedown.net) 无需重装,轻松将系统迁移到固态硬盘! (disktool.cn) 注…...
《Netty》从零开始学netty源码(五十一)之PoolArena的内存释放与扩容
目录 释放空间free()destroyChunk()freeChunk() 扩容reallocate()memoryCopy() 释放空间free() 当chunk使用完毕后需要释放,如果是池化chunk则需要放回池中供下次循环利用,如果是非池化的则直接释放对应的物理空间。 下面继续分析释放具体物理空间的des…...
Android Input系统事件分发分析
“本文基于Android13源码,分析Input系统中,事件分发的实现原理“ 整个事件分发到事件的确认过程很长,如果读者只是想大概了解一下,可以直接看末尾总结部分的流程图。 1. 前言 在文章之前,有必要提一下InputReader。其…...
Linux-基本指令2
文章目录 touch(新建一个文件)whoami(查看当前用户名)概念:1.你是如何看待指令的?2.我们在执行指令之前,我们应该先做什么? 概念:/tree . (树状显示文件夹和文件)rmdir && rmrmdirrm ctrl c通配符…...
辨析 项目范围核查、WBS核查
项目范围核查、WBS核查辨析 项目范围核查 项目目标是否完善和准确指标是否可靠和有效约束和限制条件是否真实和符合实际重要假设前提是否合理风险是否可以接受成功把握是否很大范围定义是否能够保证上述目标的实现范围能够给出的效益是否高于成本范围定义是否需要进一步进行辅…...
跳跃游戏类题目 总结篇
一.跳跃游戏类题目简单介绍 跳跃游戏是一种典型的算法题目,经常是给定一数组arr,从数组的某一位置i出发,根据一定的跳跃规则,比如从i位置能跳arr[i]步,或者小于arr[i]步,或者固定步数,直到到达某…...
Ubuntu20.04 交叉编译Paddle-OCR
第一步:交叉编译Paddle-Lite 参考链接:https://blog.csdn.net/sz76211822/article/details/130466597?spm1001.2014.3001.5501 第二步:交叉编译opencv4.x 参考链接:https://blog.csdn.net/sz76211822/article/details/13046168…...
Java 基础进阶篇(四)—— 权限修饰符、final 关键字与枚举
文章目录 一、权限修饰符二、final 关键字2.1 final 作用2.2 final 修饰变量举例2.3 常量 三、枚举3.1 枚举的格式3.2 枚举的特征3.3 枚举的应用 一、权限修饰符 权限修饰符 用于约束成员变量、构造器、方法等的访问范围。 权限修饰符: 有四种作用范围由小到大 (p…...
Linux命令集(Linux文件管理命令--touch指令篇)
Linux命令集(Linux文件管理命令--touch指令篇) Linux文件管理命令集(touch指令篇)6. touch(touch)1. 创建名为 file1 的空文件2. 创建名为 file1 和名为 file2 的多个文件3. 创建名为 file1 的文件并将访问时间设置为特定日期4. 创…...
软件工程学习教程大纲
软件工程学习教程大纲 第一章:软件工程概述 1.1 软件工程的定义和作用 软件工程的发展历程和趋势 软件工程的应用领域和特点 1.2 软件开发生命周期 软件开发生命周期的定义和阶段 软件开发生命周期的模型和方法 1.3 软件工程方法和工具 软件工程方法和工具…...
使用ChatGPT生成了十种排序算法
前言 当前ChatGPT非常火爆,对于程序员来说,ChatGPT可以帮助编写很多有用的代码。比如:在算法的实现上,就可以替我们省很多事。所以,小试牛刀一下,看看ChatGPT生成了排序算法怎么样? 简介 排序…...
手游刚开服就被攻击怎么办?如何防御DDoS?
开服初期是手游最脆弱的阶段,极易成为DDoS攻击的目标。一旦遭遇攻击,可能导致服务器瘫痪、玩家流失,甚至造成巨大经济损失。本文为开发者提供一套简洁有效的应急与防御方案,帮助快速应对并构建长期防护体系。 一、遭遇攻击的紧急应…...
linux之kylin系统nginx的安装
一、nginx的作用 1.可做高性能的web服务器 直接处理静态资源(HTML/CSS/图片等),响应速度远超传统服务器类似apache支持高并发连接 2.反向代理服务器 隐藏后端服务器IP地址,提高安全性 3.负载均衡服务器 支持多种策略分发流量…...
基于距离变化能量开销动态调整的WSN低功耗拓扑控制开销算法matlab仿真
目录 1.程序功能描述 2.测试软件版本以及运行结果展示 3.核心程序 4.算法仿真参数 5.算法理论概述 6.参考文献 7.完整程序 1.程序功能描述 通过动态调整节点通信的能量开销,平衡网络负载,延长WSN生命周期。具体通过建立基于距离的能量消耗模型&am…...
在HarmonyOS ArkTS ArkUI-X 5.0及以上版本中,手势开发全攻略:
在 HarmonyOS 应用开发中,手势交互是连接用户与设备的核心纽带。ArkTS 框架提供了丰富的手势处理能力,既支持点击、长按、拖拽等基础单一手势的精细控制,也能通过多种绑定策略解决父子组件的手势竞争问题。本文将结合官方开发文档,…...
UE5 学习系列(三)创建和移动物体
这篇博客是该系列的第三篇,是在之前两篇博客的基础上展开,主要介绍如何在操作界面中创建和拖动物体,这篇博客跟随的视频链接如下: B 站视频:s03-创建和移动物体 如果你不打算开之前的博客并且对UE5 比较熟的话按照以…...
pam_env.so模块配置解析
在PAM(Pluggable Authentication Modules)配置中, /etc/pam.d/su 文件相关配置含义如下: 配置解析 auth required pam_env.so1. 字段分解 字段值说明模块类型auth认证类模块,负责验证用户身份&am…...
重启Eureka集群中的节点,对已经注册的服务有什么影响
先看答案,如果正确地操作,重启Eureka集群中的节点,对已经注册的服务影响非常小,甚至可以做到无感知。 但如果操作不当,可能会引发短暂的服务发现问题。 下面我们从Eureka的核心工作原理来详细分析这个问题。 Eureka的…...
Yolov8 目标检测蒸馏学习记录
yolov8系列模型蒸馏基本流程,代码下载:这里本人提交了一个demo:djdll/Yolov8_Distillation: Yolov8轻量化_蒸馏代码实现 在轻量化模型设计中,**知识蒸馏(Knowledge Distillation)**被广泛应用,作为提升模型…...
4. TypeScript 类型推断与类型组合
一、类型推断 (一) 什么是类型推断 TypeScript 的类型推断会根据变量、函数返回值、对象和数组的赋值和使用方式,自动确定它们的类型。 这一特性减少了显式类型注解的需要,在保持类型安全的同时简化了代码。通过分析上下文和初始值,TypeSc…...
淘宝扭蛋机小程序系统开发:打造互动性强的购物平台
淘宝扭蛋机小程序系统的开发,旨在打造一个互动性强的购物平台,让用户在购物的同时,能够享受到更多的乐趣和惊喜。 淘宝扭蛋机小程序系统拥有丰富的互动功能。用户可以通过虚拟摇杆操作扭蛋机,实现旋转、抽拉等动作,增…...
