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生成了排序算法怎么样? 简介 排序…...
Linux链表操作全解析
Linux C语言链表深度解析与实战技巧 一、链表基础概念与内核链表优势1.1 为什么使用链表?1.2 Linux 内核链表与用户态链表的区别 二、内核链表结构与宏解析常用宏/函数 三、内核链表的优点四、用户态链表示例五、双向循环链表在内核中的实现优势5.1 插入效率5.2 安全…...
基于FPGA的PID算法学习———实现PID比例控制算法
基于FPGA的PID算法学习 前言一、PID算法分析二、PID仿真分析1. PID代码2.PI代码3.P代码4.顶层5.测试文件6.仿真波形 总结 前言 学习内容:参考网站: PID算法控制 PID即:Proportional(比例)、Integral(积分&…...
C++:std::is_convertible
C++标志库中提供is_convertible,可以测试一种类型是否可以转换为另一只类型: template <class From, class To> struct is_convertible; 使用举例: #include <iostream> #include <string>using namespace std;struct A { }; struct B : A { };int main…...
MFC内存泄露
1、泄露代码示例 void X::SetApplicationBtn() {CMFCRibbonApplicationButton* pBtn GetApplicationButton();// 获取 Ribbon Bar 指针// 创建自定义按钮CCustomRibbonAppButton* pCustomButton new CCustomRibbonAppButton();pCustomButton->SetImage(IDB_BITMAP_Jdp26)…...
无法与IP建立连接,未能下载VSCode服务器
如题,在远程连接服务器的时候突然遇到了这个提示。 查阅了一圈,发现是VSCode版本自动更新惹的祸!!! 在VSCode的帮助->关于这里发现前几天VSCode自动更新了,我的版本号变成了1.100.3 才导致了远程连接出…...
从零开始打造 OpenSTLinux 6.6 Yocto 系统(基于STM32CubeMX)(九)
设备树移植 和uboot设备树修改的内容同步到kernel将设备树stm32mp157d-stm32mp157daa1-mx.dts复制到内核源码目录下 源码修改及编译 修改arch/arm/boot/dts/st/Makefile,新增设备树编译 stm32mp157f-ev1-m4-examples.dtb \stm32mp157d-stm32mp157daa1-mx.dtb修改…...
大模型多显卡多服务器并行计算方法与实践指南
一、分布式训练概述 大规模语言模型的训练通常需要分布式计算技术,以解决单机资源不足的问题。分布式训练主要分为两种模式: 数据并行:将数据分片到不同设备,每个设备拥有完整的模型副本 模型并行:将模型分割到不同设备,每个设备处理部分模型计算 现代大模型训练通常结合…...
【C语言练习】080. 使用C语言实现简单的数据库操作
080. 使用C语言实现简单的数据库操作 080. 使用C语言实现简单的数据库操作使用原生APIODBC接口第三方库ORM框架文件模拟1. 安装SQLite2. 示例代码:使用SQLite创建数据库、表和插入数据3. 编译和运行4. 示例运行输出:5. 注意事项6. 总结080. 使用C语言实现简单的数据库操作 在…...
华为云Flexus+DeepSeek征文|DeepSeek-V3/R1 商用服务开通全流程与本地部署搭建
华为云FlexusDeepSeek征文|DeepSeek-V3/R1 商用服务开通全流程与本地部署搭建 前言 如今大模型其性能出色,华为云 ModelArts Studio_MaaS大模型即服务平台华为云内置了大模型,能助力我们轻松驾驭 DeepSeek-V3/R1,本文中将分享如何…...
mysql已经安装,但是通过rpm -q 没有找mysql相关的已安装包
文章目录 现象:mysql已经安装,但是通过rpm -q 没有找mysql相关的已安装包遇到 rpm 命令找不到已经安装的 MySQL 包时,可能是因为以下几个原因:1.MySQL 不是通过 RPM 包安装的2.RPM 数据库损坏3.使用了不同的包名或路径4.使用其他包…...
