通过基于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.中标麒麟(NeoKylin)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回环检测
回环检测的关键:如何有效的检测出相机经过同一个地方。如果成功的检测到可以为后端的位姿图提供更多有效数据,得到全局一致的估计。 回环检测提供了当前数据和所有历史数据的关联,还可以用回环检测进行重定位。 具体方法: 一&am…...

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

内嵌于球的等边三棱柱
( A, B )---3*30*2---( 1, 0 )( 0, 1 ) 做一个网络让输入只有3个节点,每个训练集里有两张图片,让B的训练集全为0,排列组合A,观察迭代次数平均值的变化。共完成了64组,但只有12组不同的迭代次数。 差值结构 A-B 迭代次…...
论文解读 | [CVPR2020] ContourNet:向精确的任意形状场景文本检测迈出进一步
目录 1 研究背景和目的 1.1 主要贡献: 1.2 两个挑战: 2 ContourNet 3 方法论 3.1 Adaptive-RPN 3.2 LOTM 3.3 点重定位算法 4 实验和结果 论文地址:ContourNet: Taking a Further Step toward Accurate Arbitrary-shaped Scene Tex…...

干货分享|数据可视化报表制作技巧
脑中想得再好,也要看最终的效果呈现。但偏偏有些用户分析思维不差,就是数据分析报表的制作拖了后腿,导致始终无法完美呈现数据可视化分析效果。本文将总结奥威BI软件上的常用的数据可视化报表制作技巧,供大家随时查阅。 BI数据可…...
Longhorn,企业级云原生容器分布式存储 - 备份与恢复
Longhorn,企业级云原生容器分布式存储 - 备份与恢复快照手动快照周期性快照和备份使用 Longhorn UI 设置周期性快照使用 StorageClass 设置 Recurring Jobs分离卷时允许 Recurring Job容灾卷创建容灾(DR)卷备份设置备份目标使用阿里云OSS备份存储准备工作为 S3 兼容…...

亿级高并发电商项目-- 实战篇 --万达商城项目 十(安装与配置Elasticsearch和kibana、编写搜索功能、向ES同步数据库商品数据)
亿级高并发电商项目-- 实战篇 --万达商城项目搭建 一 (商家端与用户端功能介绍、项目技术架构、数据库表结构等设计) 亿级高并发电商项目-- 实战篇 --万达商城项目搭建 一 (商家端与用户端功能介绍、项目技术架构、数据库表结构等设计&#x…...

windwos安装spring-cloud-alibaba-nacos
windwos安装spring-cloud-alibaba-nacos前言一、预备环境二、下载源码或者安装包1.启动2.关闭总结前言 这个快速开始手册是帮忙您快速在您的电脑上,下载、安装并使用 Nacos。 一、预备环境 Nacos 依赖 Java 环境来运行。如果您是从代码开始构建并运行Nacos&#x…...
Spring Boot 项目如何统一结果,统一异常,统一日志
1 统一结果返回目前的前后端开发大部分数据的传输格式都是json,因此定义一个统一规范的数据格式有利于前后端的交互与UI的展示。1.1 统一结果的一般形式是否响应成功;响应状态码;状态码描述;响应数据;其他标识符&#…...
Ubuntu下用Lean源码编译openwrt及一行命令u盘启动openwrt安装x86硬盘上
Ubuntu下用Lean源码编译openwrt 源码地址:https://github.com/coolsnowwolf/lede 1:首先微软云服务器装好 Ubuntu 64bit,推荐 Ubuntu 20.04 LTS x64,免费一年。ip设置在地球某处。总结就是每一步需要下载的都得下载完,…...
JavaScript Number 对象
JavaScript 是一门非常强大的编程语言,它提供了许多内置对象来帮助开发者在编写 JavaScript 应用时更轻松地处理数据。其中一个非常有用的对象是 JavaScript Number 对象,它可以帮助我们处理数值类型的数据,例如整数和浮点数。在本文中&#…...

【原创】java+swing+mysql银行ATM管理系统
本文主要介绍使用javaswingmysql去设计一个银行ATM管理系统,模仿实现存款、取款、转账、余额查询等功能。 功能分析: 隐含ATM管理系统一般分为管理员和用户角色,管理员可以进行用户管理、账单管理,用户可以进行转取存款等功能如…...
博弈论--总结
博弈分类 按照是否对外产出或消耗 零和博弈:博弈过程作为整体对外无产出也无消耗。非零和博弈:博弈过程作为整体对外有产出或有消耗。 按照博弈参与人数 1人博弈2人博弈3人博弈n人博弈 按照博弈是否重复 注:同一规则的同一博弈过程反复…...

AMBA低功耗接口规范(Low Power Interface Spec)
1.简介 AMBA提供的低功耗接口,用于实现power控制功能。目前AMBA里面包含2种低功耗接口: Q-Channel:实现简单的power控制,如上电,下电。 P-Channel:实现复杂的power控制,如全上电,半上…...

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

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

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

练习(含atoi的模拟实现,自定义类型等练习)
一、结构体大小的计算及位段 (结构体大小计算及位段 详解请看:自定义类型:结构体进阶-CSDN博客) 1.在32位系统环境,编译选项为4字节对齐,那么sizeof(A)和sizeof(B)是多少? #pragma pack(4)st…...

汽车生产虚拟实训中的技能提升与生产优化
在制造业蓬勃发展的大背景下,虚拟教学实训宛如一颗璀璨的新星,正发挥着不可或缺且日益凸显的关键作用,源源不断地为企业的稳健前行与创新发展注入磅礴强大的动力。就以汽车制造企业这一极具代表性的行业主体为例,汽车生产线上各类…...
【论文笔记】若干矿井粉尘检测算法概述
总的来说,传统机器学习、传统机器学习与深度学习的结合、LSTM等算法所需要的数据集来源于矿井传感器测量的粉尘浓度,通过建立回归模型来预测未来矿井的粉尘浓度。传统机器学习算法性能易受数据中极端值的影响。YOLO等计算机视觉算法所需要的数据集来源于…...

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

项目部署到Linux上时遇到的错误(Redis,MySQL,无法正确连接,地址占用问题)
Redis无法正确连接 在运行jar包时出现了这样的错误 查询得知问题核心在于Redis连接失败,具体原因是客户端发送了密码认证请求,但Redis服务器未设置密码 1.为Redis设置密码(匹配客户端配置) 步骤: 1).修…...

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

FFmpeg:Windows系统小白安装及其使用
一、安装 1.访问官网 Download FFmpeg 2.点击版本目录 3.选择版本点击安装 注意这里选择的是【release buids】,注意左上角标题 例如我安装在目录 F:\FFmpeg 4.解压 5.添加环境变量 把你解压后的bin目录(即exe所在文件夹)加入系统变量…...
提升移动端网页调试效率:WebDebugX 与常见工具组合实践
在日常移动端开发中,网页调试始终是一个高频但又极具挑战的环节。尤其在面对 iOS 与 Android 的混合技术栈、各种设备差异化行为时,开发者迫切需要一套高效、可靠且跨平台的调试方案。过去,我们或多或少使用过 Chrome DevTools、Remote Debug…...
Leetcode33( 搜索旋转排序数组)
题目表述 整数数组 nums 按升序排列,数组中的值 互不相同 。 在传递给函数之前,nums 在预先未知的某个下标 k(0 < k < nums.length)上进行了 旋转,使数组变为 [nums[k], nums[k1], …, nums[n-1], nums[0], nu…...
LCTF液晶可调谐滤波器在多光谱相机捕捉无人机目标检测中的作用
中达瑞和自2005年成立以来,一直在光谱成像领域深度钻研和发展,始终致力于研发高性能、高可靠性的光谱成像相机,为科研院校提供更优的产品和服务。在《低空背景下无人机目标的光谱特征研究及目标检测应用》这篇论文中提到中达瑞和 LCTF 作为多…...