当前位置: 首页 > news >正文

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

文章目录 业务场景以固定时间&#xff08;年/月/日/时/分/秒&#xff09;聚合to_char聚合date_trunc聚合 以任意时间聚合date_bin聚合实际应用 业务场景 我们做的是交通信控平台&#xff0c;需要根据实时采集到的交通大数据&#xff0c;计算出一些指标&#xff0c;存储到数据库…...

sql注入(二)盲注,二次注入,宽字节注入

目录 目录 一、布尔盲注 1.判断库名的长度 2.判断数据库名 2.1判断数据库名首字符 2.2 判断数据库名的其余字符 二、时间盲注&#xff1a; 1.判断库名的长度 2.判断库名&#xff1a; 3.判断表名payload&#xff1a; 4.爆出列名 5.爆数据 三、二次注入 1.原理&#…...

Linux 基础操作

Linux学习教程&#xff0c;Linux入门教程&#xff08;超详细&#xff09; chown (change owner) &#xff1a; 修改所属用户与组。chmod (change mode) &#xff1a; 修改用户的权限。 要查看文件或目录的属性&#xff0c;可以使用ls命令&#xff0c;加上-l选项。例如&#xff…...

2.4 等比数列

学习步骤&#xff1a; 如果我要学习等比数列&#xff0c;我会按照以下步骤进行学习&#xff1a; 定义和性质&#xff1a;首先了解等比数列的定义和性质&#xff0c;包括公比、首项、通项公式、求和公式等。 例题练习&#xff1a;通过练习一些简单的例题来理解等比数列的概念和…...

2022年新能源汽车专题讲座

2022年新能源汽车专题讲座 单选题&#xff08;共5题&#xff0c;每题6分&#xff09; 1、《中华人民共和国数据安全法》自&#xff08;&#xff09;起施行。 正确答案&#xff1a;C、2021年9月1日 2、典型的智能汽车结构主要分为&#xff08;&#xff09;个层次。 正确答案…...

Git操作远程仓库

远程仓库 码云 https://gitee.com/ 是国内的一个代码托管平台&#xff0c;由于服务器在国内&#xff0c;所以相比于GitHub&#xff0c;码云速度会更快 码云使用流程 注册账号----登录码云-----点击新建仓库----记得保存地址 GitHub https://github.com/ 是一个面向开源…...

制造策略 ETO、MTO、ATO、MTS

ETO 按交货周期跨度从长到短来讲&#xff0c;首先就是 ETO&#xff0c;Engineer To Order – 面向订单设计、定制生产或特殊生产。 就是客户给的订单&#xff0c;你要生产的话&#xff0c;你之前的原产品改动很大&#xff0c;或者基本上用不上&#xff0c;要完全按照客户的要求…...

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继承与多态 - 多态的特性 面向对象的三大特征&#xff1a;封装性、继承性、多态性&#xff1b; extends继承 或者 implements实现&#xff0c;是多态性的前提&#xff1b; 用学生类创建一个对象 - 小明&#xff0c;他是一个 学生&#xff08;学生形态&#xff09;&…...

将系统从机械硬盘迁移至固态硬盘上

1、使用DiskGenius软件、分区助手或者傲梅分区助手进行系统迁移 参考&#xff1a; 系统迁移 - DiskGenius 分区助手专业版如何迁移系统到固态硬盘-迁移系统到固态硬盘的方法_华军软件园 (onlinedown.net) 无需重装&#xff0c;轻松将系统迁移到固态硬盘! (disktool.cn) 注…...

《Netty》从零开始学netty源码(五十一)之PoolArena的内存释放与扩容

目录 释放空间free()destroyChunk()freeChunk() 扩容reallocate()memoryCopy() 释放空间free() 当chunk使用完毕后需要释放&#xff0c;如果是池化chunk则需要放回池中供下次循环利用&#xff0c;如果是非池化的则直接释放对应的物理空间。 下面继续分析释放具体物理空间的des…...

Android Input系统事件分发分析

“本文基于Android13源码&#xff0c;分析Input系统中&#xff0c;事件分发的实现原理“ 整个事件分发到事件的确认过程很长&#xff0c;如果读者只是想大概了解一下&#xff0c;可以直接看末尾总结部分的流程图。 1. 前言 在文章之前&#xff0c;有必要提一下InputReader。其…...

Linux-基本指令2

文章目录 touch&#xff08;新建一个文件&#xff09;whoami(查看当前用户名)概念&#xff1a;1.你是如何看待指令的&#xff1f;2.我们在执行指令之前&#xff0c;我们应该先做什么? 概念&#xff1a;/tree . (树状显示文件夹和文件)rmdir && rmrmdirrm ctrl c通配符…...

辨析 项目范围核查、WBS核查

项目范围核查、WBS核查辨析 项目范围核查 项目目标是否完善和准确指标是否可靠和有效约束和限制条件是否真实和符合实际重要假设前提是否合理风险是否可以接受成功把握是否很大范围定义是否能够保证上述目标的实现范围能够给出的效益是否高于成本范围定义是否需要进一步进行辅…...

跳跃游戏类题目 总结篇

一.跳跃游戏类题目简单介绍 跳跃游戏是一种典型的算法题目&#xff0c;经常是给定一数组arr&#xff0c;从数组的某一位置i出发&#xff0c;根据一定的跳跃规则&#xff0c;比如从i位置能跳arr[i]步&#xff0c;或者小于arr[i]步&#xff0c;或者固定步数&#xff0c;直到到达某…...

Ubuntu20.04 交叉编译Paddle-OCR

第一步&#xff1a;交叉编译Paddle-Lite 参考链接&#xff1a;https://blog.csdn.net/sz76211822/article/details/130466597?spm1001.2014.3001.5501 第二步&#xff1a;交叉编译opencv4.x 参考链接&#xff1a;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 枚举的应用 一、权限修饰符 权限修饰符 用于约束成员变量、构造器、方法等的访问范围。 权限修饰符&#xff1a; 有四种作用范围由小到大 (p…...

Linux命令集(Linux文件管理命令--touch指令篇)

Linux命令集&#xff08;Linux文件管理命令--touch指令篇&#xff09; Linux文件管理命令集&#xff08;touch指令篇&#xff09;6. touch(touch)1. 创建名为 file1 的空文件2. 创建名为 file1 和名为 file2 的多个文件3. 创建名为 file1 的文件并将访问时间设置为特定日期4. 创…...

软件工程学习教程大纲

软件工程学习教程大纲 第一章&#xff1a;软件工程概述 1.1 软件工程的定义和作用 软件工程的发展历程和趋势 软件工程的应用领域和特点 1.2 软件开发生命周期 软件开发生命周期的定义和阶段 软件开发生命周期的模型和方法 1.3 软件工程方法和工具 软件工程方法和工具…...

使用ChatGPT生成了十种排序算法

前言 当前ChatGPT非常火爆&#xff0c;对于程序员来说&#xff0c;ChatGPT可以帮助编写很多有用的代码。比如&#xff1a;在算法的实现上&#xff0c;就可以替我们省很多事。所以&#xff0c;小试牛刀一下&#xff0c;看看ChatGPT生成了排序算法怎么样&#xff1f; 简介 排序…...

DeepSeek 赋能智慧能源:微电网优化调度的智能革新路径

目录 一、智慧能源微电网优化调度概述1.1 智慧能源微电网概念1.2 优化调度的重要性1.3 目前面临的挑战 二、DeepSeek 技术探秘2.1 DeepSeek 技术原理2.2 DeepSeek 独特优势2.3 DeepSeek 在 AI 领域地位 三、DeepSeek 在微电网优化调度中的应用剖析3.1 数据处理与分析3.2 预测与…...

React hook之useRef

React useRef 详解 useRef 是 React 提供的一个 Hook&#xff0c;用于在函数组件中创建可变的引用对象。它在 React 开发中有多种重要用途&#xff0c;下面我将全面详细地介绍它的特性和用法。 基本概念 1. 创建 ref const refContainer useRef(initialValue);initialValu…...

Module Federation 和 Native Federation 的比较

前言 Module Federation 是 Webpack 5 引入的微前端架构方案&#xff0c;允许不同独立构建的应用在运行时动态共享模块。 Native Federation 是 Angular 官方基于 Module Federation 理念实现的专为 Angular 优化的微前端方案。 概念解析 Module Federation (模块联邦) Modul…...

Matlab | matlab常用命令总结

常用命令 一、 基础操作与环境二、 矩阵与数组操作(核心)三、 绘图与可视化四、 编程与控制流五、 符号计算 (Symbolic Math Toolbox)六、 文件与数据 I/O七、 常用函数类别重要提示这是一份 MATLAB 常用命令和功能的总结,涵盖了基础操作、矩阵运算、绘图、编程和文件处理等…...

Python如何给视频添加音频和字幕

在Python中&#xff0c;给视频添加音频和字幕可以使用电影文件处理库MoviePy和字幕处理库Subtitles。下面将详细介绍如何使用这些库来实现视频的音频和字幕添加&#xff0c;包括必要的代码示例和详细解释。 环境准备 在开始之前&#xff0c;需要安装以下Python库&#xff1a;…...

鸿蒙DevEco Studio HarmonyOS 5跑酷小游戏实现指南

1. 项目概述 本跑酷小游戏基于鸿蒙HarmonyOS 5开发&#xff0c;使用DevEco Studio作为开发工具&#xff0c;采用Java语言实现&#xff0c;包含角色控制、障碍物生成和分数计算系统。 2. 项目结构 /src/main/java/com/example/runner/├── MainAbilitySlice.java // 主界…...

Linux C语言网络编程详细入门教程:如何一步步实现TCP服务端与客户端通信

文章目录 Linux C语言网络编程详细入门教程&#xff1a;如何一步步实现TCP服务端与客户端通信前言一、网络通信基础概念二、服务端与客户端的完整流程图解三、每一步的详细讲解和代码示例1. 创建Socket&#xff08;服务端和客户端都要&#xff09;2. 绑定本地地址和端口&#x…...

Redis:现代应用开发的高效内存数据存储利器

一、Redis的起源与发展 Redis最初由意大利程序员Salvatore Sanfilippo在2009年开发&#xff0c;其初衷是为了满足他自己的一个项目需求&#xff0c;即需要一个高性能的键值存储系统来解决传统数据库在高并发场景下的性能瓶颈。随着项目的开源&#xff0c;Redis凭借其简单易用、…...

【无标题】湖北理元理律师事务所:债务优化中的生活保障与法律平衡之道

文/法律实务观察组 在债务重组领域&#xff0c;专业机构的核心价值不仅在于减轻债务数字&#xff0c;更在于帮助债务人在履行义务的同时维持基本生活尊严。湖北理元理律师事务所的服务实践表明&#xff0c;合法债务优化需同步实现三重平衡&#xff1a; 法律刚性&#xff08;债…...

macOS 终端智能代理检测

&#x1f9e0; 终端智能代理检测&#xff1a;自动判断是否需要设置代理访问 GitHub 在开发中&#xff0c;使用 GitHub 是非常常见的需求。但有时候我们会发现某些命令失败、插件无法更新&#xff0c;例如&#xff1a; fatal: unable to access https://github.com/ohmyzsh/oh…...