通过基于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) 提供一种机制,将同一集群中的资源划分为相互隔离的组。 同一名字空间内的资源名称要唯一,但跨名字空间时没有这个要求。 名字空间作用域仅针对带有名字…...
VB.net复制Ntag213卡写入UID
本示例使用的发卡器:https://item.taobao.com/item.htm?ftt&id615391857885 一、读取旧Ntag卡的UID和数据 Private Sub Button15_Click(sender As Object, e As EventArgs) Handles Button15.Click轻松读卡技术支持:网站:Dim i, j As IntegerDim cardidhex, …...
服务器硬防的应用场景都有哪些?
服务器硬防是指一种通过硬件设备层面的安全措施来防御服务器系统受到网络攻击的方式,避免服务器受到各种恶意攻击和网络威胁,那么,服务器硬防通常都会应用在哪些场景当中呢? 硬防服务器中一般会配备入侵检测系统和预防系统&#x…...
华为OD机试-食堂供餐-二分法
import java.util.Arrays; import java.util.Scanner;public class DemoTest3 {public static void main(String[] args) {Scanner in new Scanner(System.in);// 注意 hasNext 和 hasNextLine 的区别while (in.hasNextLine()) { // 注意 while 处理多个 caseint a in.nextIn…...
视频字幕质量评估的大规模细粒度基准
大家读完觉得有帮助记得关注和点赞!!! 摘要 视频字幕在文本到视频生成任务中起着至关重要的作用,因为它们的质量直接影响所生成视频的语义连贯性和视觉保真度。尽管大型视觉-语言模型(VLMs)在字幕生成方面…...
MySQL 8.0 OCP 英文题库解析(十三)
Oracle 为庆祝 MySQL 30 周年,截止到 2025.07.31 之前。所有人均可以免费考取原价245美元的MySQL OCP 认证。 从今天开始,将英文题库免费公布出来,并进行解析,帮助大家在一个月之内轻松通过OCP认证。 本期公布试题111~120 试题1…...
OpenPrompt 和直接对提示词的嵌入向量进行训练有什么区别
OpenPrompt 和直接对提示词的嵌入向量进行训练有什么区别 直接训练提示词嵌入向量的核心区别 您提到的代码: prompt_embedding = initial_embedding.clone().requires_grad_(True) optimizer = torch.optim.Adam([prompt_embedding...
【HTTP三个基础问题】
面试官您好!HTTP是超文本传输协议,是互联网上客户端和服务器之间传输超文本数据(比如文字、图片、音频、视频等)的核心协议,当前互联网应用最广泛的版本是HTTP1.1,它基于经典的C/S模型,也就是客…...
Kafka入门-生产者
生产者 生产者发送流程: 延迟时间为0ms时,也就意味着每当有数据就会直接发送 异步发送API 异步发送和同步发送的不同在于:异步发送不需要等待结果,同步发送必须等待结果才能进行下一步发送。 普通异步发送 首先导入所需的k…...
现有的 Redis 分布式锁库(如 Redisson)提供了哪些便利?
现有的 Redis 分布式锁库(如 Redisson)相比于开发者自己基于 Redis 命令(如 SETNX, EXPIRE, DEL)手动实现分布式锁,提供了巨大的便利性和健壮性。主要体现在以下几个方面: 原子性保证 (Atomicity)ÿ…...
Windows安装Miniconda
一、下载 https://www.anaconda.com/download/success 二、安装 三、配置镜像源 Anaconda/Miniconda pip 配置清华镜像源_anaconda配置清华源-CSDN博客 四、常用操作命令 Anaconda/Miniconda 基本操作命令_miniconda创建环境命令-CSDN博客...
