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

通过基于pgsql的timescaleDB的time_bucket函数实现自定义聚合粒度

1、自己写的不完全满足要求的实现方式

with tb_tmp as (select *, //计算该时间距离第一天有多少天((extract(epoch from create_time) /3600/24)::integer) as ct_ifrom test.test_salary
)select min(a.create_time) as create_time,sum(a.salary)
from (select *,//移动数据使得3条数据为一组(ct_i - (select min(ct_i) % 3  from tb_tmp))/3   as circlefrom  tb_tmp
) a
group by a.circle
order by create_time;with tb_tmp as (select *, extract (week from age(create_time, '1970-01-01')) as ct_ifrom test.test_salary
)select min(a.create_time) as create_time,sum(a.salary)
from (select *,(ct_i - (select min(ct_i::integer) % 3  from tb_tmp))/3   as circlefrom  tb_tmp
) a
group by a.circle
order by create_time;select extract (year from timestamp age('2070-02-01', '1970-01-01'));select age('2070-02-01'::date)//年select *, extract (year from age(create_time, '1970-01-01')) as ct_ifrom test.test_salary;
//月select *, (extract (year from age(create_time, '1970-01-01')) - 1) * 12 + extract(month from create_time) -1 as ct_ifrom test_salary ;//周select *, ((extract(epoch from create_time) /3600/24/7)::integer) as ct_ifrom test_salary;//季度select *, extract (year from age(create_time, '1970-01-01')) * 4 + extract(quarter from create_time) as ct_ifrom test_salary;//日       select *, ((extract(epoch from create_time) /3600/24)::integer) as ct_ifrom test_salary;

2、第二种不完全实现方法,主要通过generate_series方法生成序列加上关联业务表实现自定义分组聚合

with ranges as (select tt.ss, tt.ee from (select the_time as ss, lead(t.the_time, 1) over (order by t.the_time) as ee from (select * from generate_series( '2020-01-01 00:00:00'::timestamp, '2024-01-01 00:00:00'::timestamp, '600 second'::interval) as the_time) t) tt
)
select r.ss,r.ee,department , sum(salary)
from ranges r
left join test.test_salary
t on t.create_time >= r.ss and t.create_time < r.ee
group by r.ss,r.ee,t.department 
order by r.ss,r.ee,t.department ;

3、通过time_bucket函数实现随意自定义的分组聚合,如n年,n季度,n月,n周,n天,n小时,n分钟,n秒,以及更复杂的每天的几点到几点,每周的周几到周几,每月的几号到本月或下月的几号

1)、按3天聚合,并且从指定的时间开始,默认是按照自然年,自然月,自然周的起始点开始的,如果不需要指定开始时间去掉第三个参数即可

select time_bucket('3 day', create_time, '2020-01-05'::timestamp) as tb,sum(salary) as salary from test.test_salary 
where create_time >= '2020-01-05' and create_time  <='2020-03-01'
group by tb
order by tb asc

2)、实现每天9点到18点分组聚合

select department , create_time , time_bucket('1 day', create_time) + '9 hour'  as tb,time_bucket('1 day', create_time) + '18 hour'  as tb,  salary from test.test_salary 
where create_time  < '2020-01-10' and extract(epoch from create_time::timestamp::time) >= 32400
and extract(epoch from create_time::timestamp::time) < 64800
order by create_time;

3)、实现每周2到周五分组聚合

select  time_bucket('1 week', create_time) + '1 day' as tb,time_bucket('1 week', create_time) + '4 day' as tb2 , sum(salary) 
from test.test_salary 
where create_time >= '2020-01-01' and create_time  <='2020-05-01' and extract(dow from create_time::timestamp) >= 2 and extract(dow from create_time::timestamp) <=5
group by tb,tb2
order by tb,tb2  asc

4)、实现本月2号到18号分组聚合

select tb1,sum(salary) from (select time_bucket('1 month', create_time)+ '1 day'  tb1, create_time, salary from test.test_salarywhere create_time  < '2021-01-01' and date_part('day',create_time) >= 2  and date_part('day',create_time) <=18  order by create_time) t
group by tb1;

5)、实现本月18号到下月10分组聚合

select tb1,sum(salary) from ((select time_bucket('1 month', create_time) + '17 day'  tb1, create_time, salary from test.test_salarywhere create_time  < '2021-01-01' and date_part('day',create_time) >= 18 order by create_time)union all(select time_bucket('1 month', create_time) + '-1 month 17 day'   tb1, create_time, salary from test.test_salarywhere create_time  < '2021-01-01' and  date_part('day', create_time) <= 10)order by tb1, create_time asc
) t
group by tb1;

4、在我们使用time_bucket方法进行分组聚合获取数据时,我们会发现个问题,可能由于咱们的业务表中的数据并不是每个周期都有数据,比如咱们按月分组,但表中的数据没有2023-02的数据,那查询结果就直接没有2023-02这条数据,但有时我们的业务又需要补齐这条没有的数据,就2023-02的数据虽然是0但必须得有。这个时候另外一个方法就派上用场了。

time_bucket_gapfill:该方法可以补齐没有的数据,但它也有它的局限性,

第一:如果使用该方法,那么时间字段比如有where条件,也就是必须明确数据范围,也很好理解,如果明确,它也不知道该补齐哪些数据;

第二:该方法如果指定第三个参数,也就是指定从哪个时间开始分组的话不生效,比如咱们指定从02-02开始2天一个分组,那么02-02和02-03应该是一个组,02-04和02-05一个组,但这个方法还是会让02-01和02-02一个组不满足业务需求;

第三:该方法不支持增加一个时间间隔(interval),也就是上面sql中用到的time_bucket('1 month', create_time) + '-1 month 17 day'中的 + '-1 month 17 day'。

相关文章:

通过基于pgsql的timescaleDB的time_bucket函数实现自定义聚合粒度

1、自己写的不完全满足要求的实现方式 with tb_tmp as (select *, //计算该时间距离第一天有多少天((extract(epoch from create_time) /3600/24)::integer) as ct_ifrom test.test_salary )select min(a.create_time) as create_time,sum(a.salary) from (select *,//移动数据…...

一台电脑安装26个操作系统(windows,macos,linux)

首先看看安装了哪些操作系统1-4: windows系统 四个5.Ubuntu6.deepin7.UOS家庭版8.fydeOS9.macOS10.银河麒麟11.红旗OS12.openSUSE Leap13.openAnolis14.openEuler(未安装桌面UI)15.中标麒麟&#xff08;NeoKylin&#xff09;16.centos17.debian Edu18.fedora19.oraclelinux20.R…...

dockerfile文件

dockerfile文件内容 Form ip端口/centos:regular ENV JAVA_HOME /E:/Program Files/Java/jdk1.8.0_351 ENV PATH $JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH ENV LANG en_US.UTF-8 ENV LANGUAGE en_US:en ENV LC_ALL en_US.UTF-8 WORKDIR /opt COPY target/fast.jar /op…...

视觉SLAM ch11回环检测

回环检测的关键&#xff1a;如何有效的检测出相机经过同一个地方。如果成功的检测到可以为后端的位姿图提供更多有效数据&#xff0c;得到全局一致的估计。 回环检测提供了当前数据和所有历史数据的关联&#xff0c;还可以用回环检测进行重定位。 具体方法&#xff1a; 一&am…...

关于Ubuntu20.04文件系统思考

文章目录问题产生Ubuntu文件系统中普通用户可读写地址Ubuntu文件系统Ubuntu文件系统详解一级目录二级目录查找Ubuntu中软件安装位置Ubuntu修改文件权限问题产生 使用electron框架开发桌面端跨平台软件时&#xff0c;当开发完成的程序部署到Ubuntu上&#xff0c;系统无法产生日…...

内嵌于球的等边三棱柱

( A, B )---3*30*2---( 1, 0 )( 0, 1 ) 做一个网络让输入只有3个节点&#xff0c;每个训练集里有两张图片&#xff0c;让B的训练集全为0&#xff0c;排列组合A&#xff0c;观察迭代次数平均值的变化。共完成了64组&#xff0c;但只有12组不同的迭代次数。 差值结构 A-B 迭代次…...

论文解读 | [CVPR2020] ContourNet:向精确的任意形状场景文本检测迈出进一步

目录 1 研究背景和目的 1.1 主要贡献&#xff1a; 1.2 两个挑战&#xff1a; 2 ContourNet 3 方法论 3.1 Adaptive-RPN 3.2 LOTM 3.3 点重定位算法 4 实验和结果 论文地址&#xff1a;ContourNet: Taking a Further Step toward Accurate Arbitrary-shaped Scene Tex…...

干货分享|数据可视化报表制作技巧

脑中想得再好&#xff0c;也要看最终的效果呈现。但偏偏有些用户分析思维不差&#xff0c;就是数据分析报表的制作拖了后腿&#xff0c;导致始终无法完美呈现数据可视化分析效果。本文将总结奥威BI软件上的常用的数据可视化报表制作技巧&#xff0c;供大家随时查阅。 BI数据可…...

Longhorn,企业级云原生容器分布式存储 - 备份与恢复

Longhorn&#xff0c;企业级云原生容器分布式存储 - 备份与恢复快照手动快照周期性快照和备份使用 Longhorn UI 设置周期性快照使用 StorageClass 设置 Recurring Jobs分离卷时允许 Recurring Job容灾卷创建容灾(DR)卷备份设置备份目标使用阿里云OSS备份存储准备工作为 S3 兼容…...

亿级高并发电商项目-- 实战篇 --万达商城项目 十(安装与配置Elasticsearch和kibana、编写搜索功能、向ES同步数据库商品数据)

亿级高并发电商项目-- 实战篇 --万达商城项目搭建 一 &#xff08;商家端与用户端功能介绍、项目技术架构、数据库表结构等设计&#xff09; 亿级高并发电商项目-- 实战篇 --万达商城项目搭建 一 &#xff08;商家端与用户端功能介绍、项目技术架构、数据库表结构等设计&#x…...

windwos安装spring-cloud-alibaba-nacos

windwos安装spring-cloud-alibaba-nacos前言一、预备环境二、下载源码或者安装包1.启动2.关闭总结前言 这个快速开始手册是帮忙您快速在您的电脑上&#xff0c;下载、安装并使用 Nacos。 一、预备环境 Nacos 依赖 Java 环境来运行。如果您是从代码开始构建并运行Nacos&#x…...

Spring Boot 项目如何统一结果,统一异常,统一日志

1 统一结果返回目前的前后端开发大部分数据的传输格式都是json&#xff0c;因此定义一个统一规范的数据格式有利于前后端的交互与UI的展示。1.1 统一结果的一般形式是否响应成功&#xff1b;响应状态码&#xff1b;状态码描述&#xff1b;响应数据&#xff1b;其他标识符&#…...

Ubuntu下用Lean源码编译openwrt及一行命令u盘启动openwrt安装x86硬盘上

Ubuntu下用Lean源码编译openwrt 源码地址&#xff1a;https://github.com/coolsnowwolf/lede 1&#xff1a;首先微软云服务器装好 Ubuntu 64bit&#xff0c;推荐 Ubuntu 20.04 LTS x64&#xff0c;免费一年。ip设置在地球某处。总结就是每一步需要下载的都得下载完&#xff0c;…...

JavaScript Number 对象

JavaScript 是一门非常强大的编程语言&#xff0c;它提供了许多内置对象来帮助开发者在编写 JavaScript 应用时更轻松地处理数据。其中一个非常有用的对象是 JavaScript Number 对象&#xff0c;它可以帮助我们处理数值类型的数据&#xff0c;例如整数和浮点数。在本文中&#…...

【原创】java+swing+mysql银行ATM管理系统

本文主要介绍使用javaswingmysql去设计一个银行ATM管理系统&#xff0c;模仿实现存款、取款、转账、余额查询等功能。 功能分析&#xff1a; 隐含ATM管理系统一般分为管理员和用户角色&#xff0c;管理员可以进行用户管理、账单管理&#xff0c;用户可以进行转取存款等功能如…...

博弈论--总结

博弈分类 按照是否对外产出或消耗 零和博弈&#xff1a;博弈过程作为整体对外无产出也无消耗。非零和博弈&#xff1a;博弈过程作为整体对外有产出或有消耗。 按照博弈参与人数 1人博弈2人博弈3人博弈n人博弈 按照博弈是否重复 注&#xff1a;同一规则的同一博弈过程反复…...

AMBA低功耗接口规范(Low Power Interface Spec)

1.简介 AMBA提供的低功耗接口&#xff0c;用于实现power控制功能。目前AMBA里面包含2种低功耗接口&#xff1a; Q-Channel&#xff1a;实现简单的power控制&#xff0c;如上电&#xff0c;下电。 P-Channel&#xff1a;实现复杂的power控制&#xff0c;如全上电&#xff0c;半上…...

matlab-汽车四分之一半主动悬架模糊控制

1、内容简介汽车四分之一半主动悬架模糊控制651-可以交流、咨询、答疑2、内容说明半主动悬架汽车 1/4 动力学模型建立 本章主要对悬架类型进行简要介绍&#xff0c;并对其进行对比分析&#xff0c;提出半主动悬架的优越性&#xff0c;论述半主动悬架的工作原理&#xff0c;并对…...

【安全加密】通信加密算法介绍

加密常用于通信中&#xff0c;如战争中电台通讯有明码和密码&#xff0c;密码需要不断更换密码本&#xff1b;另外&#xff0c;商用软件也需要用到加密技术&#xff0c;如根据电脑的mac地址设置权限&#xff0c;防止软件被恶意传播。 文章目录一、介绍1. 单向散列/哈希算法2. 对…...

kubernetes教程 --组件详细介绍

组件详细介绍 NameSpace 在 Kubernetes 中&#xff0c;名字空间&#xff08;Namespace&#xff09; 提供一种机制&#xff0c;将同一集群中的资源划分为相互隔离的组。 同一名字空间内的资源名称要唯一&#xff0c;但跨名字空间时没有这个要求。 名字空间作用域仅针对带有名字…...

【JavaEE】-- HTTP

1. HTTP是什么&#xff1f; HTTP&#xff08;全称为"超文本传输协议"&#xff09;是一种应用非常广泛的应用层协议&#xff0c;HTTP是基于TCP协议的一种应用层协议。 应用层协议&#xff1a;是计算机网络协议栈中最高层的协议&#xff0c;它定义了运行在不同主机上…...

【解密LSTM、GRU如何解决传统RNN梯度消失问题】

解密LSTM与GRU&#xff1a;如何让RNN变得更聪明&#xff1f; 在深度学习的世界里&#xff0c;循环神经网络&#xff08;RNN&#xff09;以其卓越的序列数据处理能力广泛应用于自然语言处理、时间序列预测等领域。然而&#xff0c;传统RNN存在的一个严重问题——梯度消失&#…...

电脑插入多块移动硬盘后经常出现卡顿和蓝屏

当电脑在插入多块移动硬盘后频繁出现卡顿和蓝屏问题时&#xff0c;可能涉及硬件资源冲突、驱动兼容性、供电不足或系统设置等多方面原因。以下是逐步排查和解决方案&#xff1a; 1. 检查电源供电问题 问题原因&#xff1a;多块移动硬盘同时运行可能导致USB接口供电不足&#x…...

页面渲染流程与性能优化

页面渲染流程与性能优化详解&#xff08;完整版&#xff09; 一、现代浏览器渲染流程&#xff08;详细说明&#xff09; 1. 构建DOM树 浏览器接收到HTML文档后&#xff0c;会逐步解析并构建DOM&#xff08;Document Object Model&#xff09;树。具体过程如下&#xff1a; (…...

让AI看见世界:MCP协议与服务器的工作原理

让AI看见世界&#xff1a;MCP协议与服务器的工作原理 MCP&#xff08;Model Context Protocol&#xff09;是一种创新的通信协议&#xff0c;旨在让大型语言模型能够安全、高效地与外部资源进行交互。在AI技术快速发展的今天&#xff0c;MCP正成为连接AI与现实世界的重要桥梁。…...

用docker来安装部署freeswitch记录

今天刚才测试一个callcenter的项目&#xff0c;所以尝试安装freeswitch 1、使用轩辕镜像 - 中国开发者首选的专业 Docker 镜像加速服务平台 编辑下面/etc/docker/daemon.json文件为 {"registry-mirrors": ["https://docker.xuanyuan.me"] }同时可以进入轩…...

ios苹果系统,js 滑动屏幕、锚定无效

现象&#xff1a;window.addEventListener监听touch无效&#xff0c;划不动屏幕&#xff0c;但是代码逻辑都有执行到。 scrollIntoView也无效。 原因&#xff1a;这是因为 iOS 的触摸事件处理机制和 touch-action: none 的设置有关。ios有太多得交互动作&#xff0c;从而会影响…...

华硕a豆14 Air香氛版,美学与科技的馨香融合

在快节奏的现代生活中&#xff0c;我们渴望一个能激发创想、愉悦感官的工作与生活伙伴&#xff0c;它不仅是冰冷的科技工具&#xff0c;更能触动我们内心深处的细腻情感。正是在这样的期许下&#xff0c;华硕a豆14 Air香氛版翩然而至&#xff0c;它以一种前所未有的方式&#x…...

Redis的发布订阅模式与专业的 MQ(如 Kafka, RabbitMQ)相比,优缺点是什么?适用于哪些场景?

Redis 的发布订阅&#xff08;Pub/Sub&#xff09;模式与专业的 MQ&#xff08;Message Queue&#xff09;如 Kafka、RabbitMQ 进行比较&#xff0c;核心的权衡点在于&#xff1a;简单与速度 vs. 可靠与功能。 下面我们详细展开对比。 Redis Pub/Sub 的核心特点 它是一个发后…...

JVM 内存结构 详解

内存结构 运行时数据区&#xff1a; Java虚拟机在运行Java程序过程中管理的内存区域。 程序计数器&#xff1a; ​ 线程私有&#xff0c;程序控制流的指示器&#xff0c;分支、循环、跳转、异常处理、线程恢复等基础功能都依赖这个计数器完成。 ​ 每个线程都有一个程序计数…...