当前位置: 首页 > 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;但跨名字空间时没有这个要求。 名字空间作用域仅针对带有名字…...

练习(含atoi的模拟实现,自定义类型等练习)

一、结构体大小的计算及位段 &#xff08;结构体大小计算及位段 详解请看&#xff1a;自定义类型&#xff1a;结构体进阶-CSDN博客&#xff09; 1.在32位系统环境&#xff0c;编译选项为4字节对齐&#xff0c;那么sizeof(A)和sizeof(B)是多少&#xff1f; #pragma pack(4)st…...

汽车生产虚拟实训中的技能提升与生产优化​

在制造业蓬勃发展的大背景下&#xff0c;虚拟教学实训宛如一颗璀璨的新星&#xff0c;正发挥着不可或缺且日益凸显的关键作用&#xff0c;源源不断地为企业的稳健前行与创新发展注入磅礴强大的动力。就以汽车制造企业这一极具代表性的行业主体为例&#xff0c;汽车生产线上各类…...

【论文笔记】若干矿井粉尘检测算法概述

总的来说&#xff0c;传统机器学习、传统机器学习与深度学习的结合、LSTM等算法所需要的数据集来源于矿井传感器测量的粉尘浓度&#xff0c;通过建立回归模型来预测未来矿井的粉尘浓度。传统机器学习算法性能易受数据中极端值的影响。YOLO等计算机视觉算法所需要的数据集来源于…...

Psychopy音频的使用

Psychopy音频的使用 本文主要解决以下问题&#xff1a; 指定音频引擎与设备&#xff1b;播放音频文件 本文所使用的环境&#xff1a; Python3.10 numpy2.2.6 psychopy2025.1.1 psychtoolbox3.0.19.14 一、音频配置 Psychopy文档链接为Sound - for audio playback — Psy…...

项目部署到Linux上时遇到的错误(Redis,MySQL,无法正确连接,地址占用问题)

Redis无法正确连接 在运行jar包时出现了这样的错误 查询得知问题核心在于Redis连接失败&#xff0c;具体原因是客户端发送了密码认证请求&#xff0c;但Redis服务器未设置密码 1.为Redis设置密码&#xff08;匹配客户端配置&#xff09; 步骤&#xff1a; 1&#xff09;.修…...

听写流程自动化实践,轻量级教育辅助

随着智能教育工具的发展&#xff0c;越来越多的传统学习方式正在被数字化、自动化所优化。听写作为语文、英语等学科中重要的基础训练形式&#xff0c;也迎来了更高效的解决方案。 这是一款轻量但功能强大的听写辅助工具。它是基于本地词库与可选在线语音引擎构建&#xff0c;…...

FFmpeg:Windows系统小白安装及其使用

一、安装 1.访问官网 Download FFmpeg 2.点击版本目录 3.选择版本点击安装 注意这里选择的是【release buids】&#xff0c;注意左上角标题 例如我安装在目录 F:\FFmpeg 4.解压 5.添加环境变量 把你解压后的bin目录&#xff08;即exe所在文件夹&#xff09;加入系统变量…...

提升移动端网页调试效率:WebDebugX 与常见工具组合实践

在日常移动端开发中&#xff0c;网页调试始终是一个高频但又极具挑战的环节。尤其在面对 iOS 与 Android 的混合技术栈、各种设备差异化行为时&#xff0c;开发者迫切需要一套高效、可靠且跨平台的调试方案。过去&#xff0c;我们或多或少使用过 Chrome DevTools、Remote Debug…...

Leetcode33( 搜索旋转排序数组)

题目表述 整数数组 nums 按升序排列&#xff0c;数组中的值 互不相同 。 在传递给函数之前&#xff0c;nums 在预先未知的某个下标 k&#xff08;0 < k < nums.length&#xff09;上进行了 旋转&#xff0c;使数组变为 [nums[k], nums[k1], …, nums[n-1], nums[0], nu…...

LCTF液晶可调谐滤波器在多光谱相机捕捉无人机目标检测中的作用

中达瑞和自2005年成立以来&#xff0c;一直在光谱成像领域深度钻研和发展&#xff0c;始终致力于研发高性能、高可靠性的光谱成像相机&#xff0c;为科研院校提供更优的产品和服务。在《低空背景下无人机目标的光谱特征研究及目标检测应用》这篇论文中提到中达瑞和 LCTF 作为多…...