clickhouse 随心所欲的聚合模型-AggregatingMergeTree

clickhouse 强大的 MergeTree 系列引擎令人信服，其 ReplacingMergeTree、SummingMergeTree 在数据唯一性和汇总场景中表现非凡。但你是否还有保留最小(大)、平均等预聚合需求，甚至在一个模型中既有唯一性语意也有汇总、最小、最大、平均值语意该如何处理。在 doris 中 Aggregate 数据模型可以轻松解决，那么同为头部 AP 数据库的 clickhouse 是否可以随心所欲的定义聚合模型呢？

一、AggregatingMergeTree

1.1 基本使用

AggregatingMergeTree 表引擎作为 MergeTree 系列引擎也是遵循其家族的基本逻辑的，它能够在合并分区的时候按照预先定义的方式聚合数据。与 ReplacingMergeTree、SummingMergeTree 不同的是表引擎已经内置好了聚合方式，用户只能指定字段在分区合并时对字段进行去重或累加，AggregatingMergeTree 则进一步开发底层给用户，用户需要指定在分区合并时采用何种聚合函数，以及针对哪些字段进行计算，下面是该引擎的使用方式（复刻 doris 官方文档的案例）

drop table if exists tbl_agg;
create table if not exists tbl_agg
(`user_id` String comment '用户id',`date`    datetime comment '数据灌入日期时间',`city`    String comment '用户所在城市',`age`     Int8 comment '用户年龄',`sex`     Int8 comment '用户性别',`last_visit_date` AggregateFunction(anyLast,DateTime) comment '用户最后一次访问时间',`cost` AggregateFunction(sum, Int256) comment '用户总消费',`max_dwell_time` AggregateFunction(max,Int64) comment '用户最大停留时间',`min_dwell_time` AggregateFunction(min,Int64) comment '用户最小停留时间'
) engine AggregatingMergeTree()order by (user_id, date, city, age, sex);

AggregateFunction 是 clickhouse 提供的特殊数据类型，它能够以二进制的形式存储中间状态结果。其使用方式也十分特殊，在定义的时候需要提供聚合方式以及数据类型。常用的聚合方式整理如下：

  1. count: 计数非空行数
  2. sum: 累加
  3. max: 最大值
  4. min: 最小值
  5. anyLast: 最后一个非空值
  6. uniq: 去重计数

当然 clickhouse 提供的聚合函数很多，详情可以访问: https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference

因为 AggregateFunction 是二进制存储的中间结果，我们在插入数据时也需要将明文数据转换为 AggregateFunction 可以接受的数据类型，clickhouse 为每个聚合函数都提供了转换为 AggregateFunction 类型的 *State 函数

insert into tbl_agg
select 10000,'2017-10-01','北京',20,0,anyLastState(toDateTime('2017-10-01 06:00:00')),sumState(toInt256(20)),maxState(toInt64(10)),minState(toInt64(10));insert into tbl_agg
select 10000,'2017-10-01','北京',20,0,anyLastState(toDateTime('2017-10-01 07:00:00')),sumState(toInt256(15)),maxState(toInt64(2)),minState(toInt64(2));

同理我们在查询是也需要特殊的函数将 AggregateFunction 类型转换为明文（类似序列化与反序列区别），而查询时需要使用 *Merge 函数

SELECTuser_id,anyLastMerge(last_visit_date) AS last_visit_date,sumMerge(cost) AS cost,maxMerge(max_dwell_time) AS max_dwell_time,minMerge(min_dwell_time) AS min_dwell_time
FROM tbl_agg
GROUP BY user_id
ORDER BY user_id ASCQuery id: 30a237df-6018-42fa-a6a9-1d324e21310d┌─user_id─┬─────last_visit_date─┬─cost─┬─max_dwell_time─┬─min_dwell_time─┐
│ 10000   │ 2017-10-01 06:00:00 │   35 │             10 │              2 │
└─────────┴─────────────────────┴──────┴────────────────┴────────────────┘1 row in set. Elapsed: 0.005 sec.

看到这里是否觉得这种方式过于繁琐，连正常的数据插入都需要借助 State 函数，那么在升级改造时将寸步难行。好在上面的方式并不是主流的方式，我们可以借助物化视图来屏蔽 State 过程，让数据插入保持原生。

1.2 优化体验

首先我们创建相同结构的普通表作为底表

drop table if exists tbl_agg_basic;
create table if not exists tbl_agg_basic
(`user_id`         String comment '用户id',`date`            datetime comment '数据灌入日期时间',`city`            String comment '用户所在城市',`age`             Int8 comment '用户年龄',`sex`             Int8 comment '用户性别',`last_visit_date` datetime comment '用户最后一次访问时间',`cost`            Int256 comment '用户总消费',`max_dwell_time`  Int64 comment '用户最大停留时间',`min_dwell_time`  Int64 comment '用户最小停留时间'
) engine MergeTreeorder by (user_id, date, city, age, sex);

之后我们将 State 过程写入物化视图中

drop table if exists mv_tbl_agg;
create materialized view if not exists mv_tbl_agg to tbl_agg
as
select user_id,date,city,age,sex,anyLastState(last_visit_date) as last_visit_date,sumState(cost)                as cost,maxState(max_dwell_time)      as max_dwell_time,minState(min_dwell_time)      as min_dwell_time
from tbl_agg_basic
group by user_id, date, city, age, sex;

对用户来说将明细数据优雅的写入底表中，tbl_agg 对外提供查询功能，用户无需关系数据怎么序列化

flow

下面我们只需要假装什么都不知道向明细数据表插入数据

insert into tbl_agg_basic
values (10000, '2017-10-01', '北京', 20, 0, '2017-10-01 06:00:00', 20, 10, 10),(10000, '2017-10-01', '北京', 20, 0, '2017-10-01 07:00:00', 15, 2, 2),(10001, '2017-10-01', '北京', 30, 1, '2017-10-01 17:05:45', 2, 22, 22),(10002, '2017-10-02', '上海', 20, 1, '2017-10-02 12:59:12', 200, 5, 5),(10003, '2017-10-02', '广州', 32, 0, '2017-10-02 11:20:00', 30, 11, 11),(10004, '2017-10-01', '深圳', 35, 0, '2017-10-01 10:00:15', 100, 3, 3),(10004, '2017-10-03', '深圳', 35, 0, '2017-10-03 10:20:22', 11, 6, 6);

数据会自动同步到 tbl_agg 中，在查询时我们只需要面向 tbl_agg 此时会比直接查询 tbl_agg_basic 有更高的性能

SELECTuser_id,date,city,age,sex,anyLastMerge(last_visit_date) AS last_visit_date,sumMerge(cost) AS cost,maxMerge(max_dwell_time) AS max_dwell_time,minMerge(min_dwell_time) AS min_dwell_time
FROM tbl_agg
GROUP BYuser_id,date,city,age,sex
ORDER BY user_id ASCQuery id: 6f7fd017-9378-4f42-8c20-56bd711487d1┌─user_id─┬────────────────date─┬─city─┬─age─┬─sex─┬─────last_visit_date─┬─cost─┬─max_dwell_time─┬─min_dwell_time─┐
│ 10000   │ 2017-10-01 00:00:00 │ 北京 │  20 │   0 │ 2017-10-01 07:00:00 │   35 │             10 │              2 │
│ 10001   │ 2017-10-01 00:00:00 │ 北京 │  30 │   1 │ 2017-10-01 17:05:45 │    2 │             22 │             22 │
│ 10002   │ 2017-10-02 00:00:00 │ 上海 │  20 │   1 │ 2017-10-02 12:59:12 │  200 │              5 │              5 │
│ 10003   │ 2017-10-02 00:00:00 │ 广州 │  32 │   0 │ 2017-10-02 11:20:00 │   30 │             11 │             11 │
│ 10004   │ 2017-10-01 00:00:00 │ 深圳 │  35 │   0 │ 2017-10-01 10:00:15 │  100 │              3 │              3 │
│ 10004   │ 2017-10-03 00:00:00 │ 深圳 │  35 │   0 │ 2017-10-03 10:20:22 │   11 │              6 │              6 │
└─────────┴─────────────────────┴──────┴─────┴─────┴─────────────────────┴──────┴────────────────┴────────────────┘6 rows in set. Elapsed: 0.008 sec.

还可以插入几条数据来观察 tbl_agg 的结果是否符合我们定义的聚合语意

二、SimpleAggregateFunction

对于上面的案例其实在查询时依然不方便需要调用 Merge 函数，本质因为 AggregateFunction 使用二进制存储。如果数据以明文存储是不是就不需要这么麻烦，clickhouse 针对这类场景提供了 SimpleAggregateFunction

drop table if exists tbl_agg_s;
create table if not exists tbl_agg_s
(`user_id` String comment '用户id',`date`    datetime comment '数据灌入日期时间',`city`    String comment '用户所在城市',`age`     Int8 comment '用户年龄',`sex`     Int8 comment '用户性别',`last_visit_date` SimpleAggregateFunction(anyLast,datetime) comment '用户最后一次访问时间',`cost` SimpleAggregateFunction(sum, Int256) comment '用户总消费',`max_dwell_time` SimpleAggregateFunction(max,Int64) comment '用户最大停留时间',`min_dwell_time` SimpleAggregateFunction(min,Int64) comment '用户最小停留时间'
) engine AggregatingMergeTree()order by (user_id, date, city, age, sex);

此时该模型就可以视为完美复刻了 doris 的聚合模型，因为插入和查询将变得原生化

insert into tbl_agg_s
values (10000, '2017-10-01', '北京', 20, 0, '2017-10-01 06:00:00', 20, 10, 10),(10000, '2017-10-01', '北京', 20, 0, '2017-10-01 07:00:00', 15, 2, 2),(10001, '2017-10-01', '北京', 30, 1, '2017-10-01 17:05:45', 2, 22, 22),(10002, '2017-10-02', '上海', 20, 1, '2017-10-02 12:59:12', 200, 5, 5),(10003, '2017-10-02', '广州', 32, 0, '2017-10-02 11:20:00', 30, 11, 11),(10004, '2017-10-01', '深圳', 35, 0, '2017-10-01 10:00:15', 100, 3, 3),(10004, '2017-10-03', '深圳', 35, 0, '2017-10-03 10:20:22', 11, 6, 6);select * from tbl_agg_s;

从名字也可以看出，相对 AggregateFunction 就不是那么通用即支持的聚合类型相对较少：

  • any
  • anyLast
  • min
  • max
  • sum
  • sumWithOverflow
  • groupBitAnd
  • groupBitOr
  • groupBitXor
  • groupArrayArray
  • groupUniqArrayArray
  • sumMap
  • minMap
  • maxMap

但这些其实已经够用了，同时在上面的聚合场景下 SimpleAggregateFunction 会有更高的性能。

提问: 为什么没有 count

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/265728.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

DBAPI如何使用数组类型参数

DBAPI如何使用数组类型参数

DBAPI如何使用数组类型参数 需求 根据多个id去查询学生信息 API创建 在基本信息标签&#xff0c;创建参数ids &#xff0c;参数类型选择 Array<bigint> 在执行器标签&#xff0c;填写sql&#xff0c;使用in查询 select * from student where id in <foreach ope…
阅读更多...
Unity（第十一部）场景

Unity（第十一部）场景

游戏有多个场景组成&#xff08;新手村&#xff0c;某某副本&#xff0c;主城&#xff09; 场景是有多个物体组成&#xff08;怪物&#xff0c;地形&#xff0c;玩家等&#xff09; 物体是有多个组件组成&#xff08;刚体组件&#xff0c;自定义脚本&#xff09; 创建场景 编辑…
阅读更多...
Folx Pro Mac中文p破解版如何使用？为您带来Folx Pro 详细使用教程！

Folx Pro Mac中文p破解版如何使用？为您带来Folx Pro 详细使用教程！

​ Folx pro 5 中文版是mac上一款功能强大的老牌加速下载软件&#xff0c;新版本的Folx pro整体界面非常的简洁和漂亮&#xff0c;具有非常好用的分类管理功能&#xff0c;支持高速下载、定时下载、速度控制、iTunes集成等功能。Folx pro兼容主流的浏览器&#xff0c;不但可以下…
阅读更多...
【卷积神经网络中用1*1 卷积有什么作用或者好处呢？】

【卷积神经网络中用1*1 卷积有什么作用或者好处呢？】

&#x1f680; 作者 &#xff1a;“码上有前” &#x1f680; 文章简介 &#xff1a;深度学习 &#x1f680; 欢迎小伙伴们 点赞&#x1f44d;、收藏⭐、留言&#x1f4ac; 1*1 卷积有什么作用或者好处呢 作用降维和增加非线性特征组合和交互网络的宽度和深度调整全连接替代增强…
阅读更多...
09-认证-自研微服务框架

09-认证-自研微服务框架

认证 1. 开启https支持 func (e *Engine) RunTLS(addr, certFile, keyFile string) {err : http.ListenAndServeTLS(addr, certFile, keyFile, e.Handler())if err ! nil {log.Fatal(err)} }1.1 测试 证书生成&#xff1a; 安装openssl 网站下载&#xff1a;http://slproweb…
阅读更多...
华为HCIP Datacom H12-831 卷24

华为HCIP Datacom H12-831 卷24

多选题 1、如图所示&#xff0c;某园区部署OSPF实现网络互通&#xff0c;其中Area1部署为NSSA区域。某工程师为了实现R1访问R4的环回口地址&#xff0c;在R4的OSPF进程中引入直连路由。以下关于该场景的描述,错误的有哪些项? A、在R4引入直连路由后&#xff0c;R1通过转换后的…
阅读更多...
Socket网络编程(三)——TCP快速入门

Socket网络编程(三)——TCP快速入门

目录 概述TCP连接可靠性1. 三次握手过程2. 四次挥手过程3. 为什么挥手需要四次&#xff1f; 传输可靠性TCP核心APITCP传输初始化配置&建立连接客户端创建Socket建立连接服务端创建ServerSocket监听连接ServerSocket 和 Socket的关系 Socket基本数据类型传输客户端数据传输服…
阅读更多...
postman测试接口

postman测试接口

1、postman测试接口 &#xff08;1&#xff09;首先安装postman 下载地址&#xff1a;Download Postman | Get Started for Free 选择对应版本下载&#xff0c;然后安装即可 &#xff08;2&#xff09;使用postman发送请求 比如以下这个请求例子&#xff1a; 使用postman发…
阅读更多...
UE4 材质多张图片拼接成一张图片（此处用2×2拼接）

UE4 材质多张图片拼接成一张图片（此处用2×2拼接）

UE4 材质多张图片拼接成一张图片&#xff08;此处用22拼接&#xff09; //TexCoord,TextureA,TextureB,TextureC,TextureDfloat3 ReturnTexture TextureA; if(TexCoord.x < 0.5 && TexCoord.y < 0.5) {ReturnTexture TextureA; } else if(TexCoord.x > 0.5…
阅读更多...
php docx,pptx,excel表格上传阿里云,腾讯云存储后截取第一页生成缩略图

php docx,pptx,excel表格上传阿里云,腾讯云存储后截取第一页生成缩略图

php把word转图片的方法：首先给服务器安装libreoffice；然后使用exec函数来调用命令行操作；最后通过“exec(“soffice --headless --invisible…””方法把word转图片即可。 服务器环境：centos7 *集成环境：宝塔 我们开始给服务器安装libreoffice 直接执行下面的代码就可以…
阅读更多...
代码随想录Leetcode213. 打家劫舍 II

代码随想录Leetcode213. 打家劫舍 II

题目&#xff1a; 代码(首刷看解析 2024年2月29日&#xff09;&#xff1a; class Solution { public:int robRange(vector<int>& nums, int start, int end) {if (start end) return nums[start];vector<int> dp(nums.size(), 0); // 遍历dp[start] nums[s…
阅读更多...
【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器

【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器

前言 嘿&#xff0c;数据库大魔法师们&#xff01;准备好迎接新的技术大招了吗&#xff1f;今天我们要探索的是Oracle数据库中的神奇利器——RMAN恢复管理器&#xff01;&#x1f6e1;️&#x1f4be; 在这篇博文【Oracle】玩转Oracle数据库&#xff08;七&#xff09;&#xf…
阅读更多...
mov和mp4格式哪个好？专业讲师告诉你答案【详】

mov和mp4格式哪个好？专业讲师告诉你答案【详】

在数字化时代&#xff0c;我们经常面临着选择视频格式的困境&#xff0c;尤其是需要在不同设备和平台上播放或分享视频时。在这些选择中&#xff0c;MOV和MP4格式是两种最常见的选项之一。 然而&#xff0c;mov和mp4格式哪个好呢&#xff1f;这个问题并不容易回答&#xff0c;…
阅读更多...
C++重点---STL简介

C++重点---STL简介

顾得泉&#xff1a;个人主页 个人专栏&#xff1a;《Linux操作系统》 《C从入门到精通》 《LeedCode刷题》 键盘敲烂&#xff0c;年薪百万&#xff01; 一、STL简介 STL&#xff08;Standard Template Library&#xff09;是C标准库中的一个重要组成部分&#xff0c;它提供了…
阅读更多...
LabVIEW水下温盐深数据一体化采集与分析

LabVIEW水下温盐深数据一体化采集与分析

LabVIEW水下温盐深数据一体化采集与分析 开发一个基于LabVIEW的水下温盐深数据一体化采集与分析系统&#xff0c;实现海洋环境监测的自动化和精确化。通过集成温度、盐度和深度传感器&#xff0c;结合USB数据采集卡&#xff0c;利用LabVIEW软件开发的图形化界面&#xff0c;实…
阅读更多...
理解计算着色器中glsl语言的内置变量

理解计算着色器中glsl语言的内置变量

概要 本文通过示例的方式&#xff0c;着重解释以下几个内置变量&#xff1a; gl_WorkGroupSizegl_NumWorkGroupsgl_LocalInvocationIDgl_WorkGroupIDgl_GlobalInvocationID 基本概念 局部工作组与工作项 一个3x2x1的局部工作组示例如下&#xff0c;每个小篮格子表示一个工作项…
阅读更多...
403页面绕过

403页面绕过

403页面绕过 文章目录 403页面绕过姿势一: 端口利用姿势二&#xff1a;修改HOST姿势三&#xff1a;覆盖请求URL姿势四&#xff1a;Referer标头绕过姿势五&#xff1a;代理IP姿势六&#xff1a;扩展名绕过 姿势一: 端口利用 拿到客户给的地址后&#xff0c;首先进行信息收集。端…
阅读更多...
[数据集][目标检测]游泳者溺水数据集VOC+YOLO格式2类别895张

[数据集][目标检测]游泳者溺水数据集VOC+YOLO格式2类别895张

数据集制作单位&#xff1a;未来自主研究中心(FIRC) 数据集格式&#xff1a;Pascal VOC格式YOLO格式(不包含分割路径的txt文件&#xff0c;仅仅包含jpg图片以及对应的VOC格式xml文件和yolo格式txt文件) 图片数量(jpg文件个数)&#xff1a;895 标注数量(xml文件个数)&#xff1a…
阅读更多...
js 面试 什么是WebSockets？HTTP和HTTPS有什么不同？web worker是什么？

js 面试 什么是WebSockets？HTTP和HTTPS有什么不同？web worker是什么？

概念&#xff1a; webSocket 是一种在客户端和服务端之间建立持久连接的协议&#xff0c;它提供全双工通信通道&#xff0c;是服务器可以主动向客户端推送数据&#xff0c;同时也可以接受客户端发送的数据。 1 webSocket与https区别&#xff1f; 在网络通信中&#xff0c;We…
阅读更多...
Android进阶之路 - RecyclerView停止滑动后Item自动居中（SnapHelper辅助类）

Android进阶之路 - RecyclerView停止滑动后Item自动居中（SnapHelper辅助类）

之前一直没注意 SnapHelper 辅助类的功能&#xff0c;去年的时候看到项目中仅通过俩行代码设置 RecyclerView 后就提升了用户体验&#xff0c;觉得还是很有必要了解一下&#xff0c;尝试过后才发现其 PagerSnapHelper、LinearSnapHelper 子类可以作用于不同场景&#xff0c;且听…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023