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

2024-02-29 03:12

本文主要是介绍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─┐
│ 100002017-10-01 06:00:0035102 │
└─────────┴─────────────────────┴──────┴────────────────┴────────────────┘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─┐
│ 100002017-10-01 00:00:00 │ 北京 │  2002017-10-01 07:00:0035102 │
│ 100012017-10-01 00:00:00 │ 北京 │  3012017-10-01 17:05:4522222 │
│ 100022017-10-02 00:00:00 │ 上海 │  2012017-10-02 12:59:1220055 │
│ 100032017-10-02 00:00:00 │ 广州 │  3202017-10-02 11:20:00301111 │
│ 100042017-10-01 00:00:00 │ 深圳 │  3502017-10-01 10:00:1510033 │
│ 100042017-10-03 00:00:00 │ 深圳 │  3502017-10-03 10:20:221166 │
└─────────┴─────────────────────┴──────┴─────┴─────┴─────────────────────┴──────┴────────────────┴────────────────┘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

这篇关于clickhouse 随心所欲的聚合模型-AggregatingMergeTree的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/757402

相关文章

大模型研发全揭秘:客服工单数据标注的完整攻略

在人工智能(AI)领域,数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者,掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中,工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注,不仅能够帮助提升客服自动化系统的智能化水平,还能优化客户服务流程,提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行

Andrej Karpathy最新采访:认知核心模型10亿参数就够了,AI会打破教育不公的僵局

夕小瑶科技说 原创  作者 | 海野 AI圈子的红人,AI大神Andrej Karpathy,曾是OpenAI联合创始人之一,特斯拉AI总监。上一次的动态是官宣创办一家名为 Eureka Labs 的人工智能+教育公司 ,宣布将长期致力于AI原生教育。 近日,Andrej Karpathy接受了No Priors(投资博客)的采访,与硅谷知名投资人 Sara Guo 和 Elad G

Retrieval-based-Voice-Conversion-WebUI模型构建指南

一、模型介绍 Retrieval-based-Voice-Conversion-WebUI(简称 RVC)模型是一个基于 VITS(Variational Inference with adversarial learning for end-to-end Text-to-Speech)的简单易用的语音转换框架。 具有以下特点 简单易用:RVC 模型通过简单易用的网页界面,使得用户无需深入了

透彻!驯服大型语言模型(LLMs)的五种方法,及具体方法选择思路

引言 随着时间的发展,大型语言模型不再停留在演示阶段而是逐步面向生产系统的应用,随着人们期望的不断增加,目标也发生了巨大的变化。在短短的几个月的时间里,人们对大模型的认识已经从对其zero-shot能力感到惊讶,转变为考虑改进模型质量、提高模型可用性。 「大语言模型(LLMs)其实就是利用高容量的模型架构(例如Transformer)对海量的、多种多样的数据分布进行建模得到,它包含了大量的先验

图神经网络模型介绍(1)

我们将图神经网络分为基于谱域的模型和基于空域的模型,并按照发展顺序详解每个类别中的重要模型。 1.1基于谱域的图神经网络         谱域上的图卷积在图学习迈向深度学习的发展历程中起到了关键的作用。本节主要介绍三个具有代表性的谱域图神经网络:谱图卷积网络、切比雪夫网络和图卷积网络。 (1)谱图卷积网络 卷积定理:函数卷积的傅里叶变换是函数傅里叶变换的乘积,即F{f*g}

秋招最新大模型算法面试,熬夜都要肝完它

💥大家在面试大模型LLM这个板块的时候,不知道面试完会不会复盘、总结,做笔记的习惯,这份大模型算法岗面试八股笔记也帮助不少人拿到过offer ✨对于面试大模型算法工程师会有一定的帮助,都附有完整答案,熬夜也要看完,祝大家一臂之力 这份《大模型算法工程师面试题》已经上传CSDN,还有完整版的大模型 AI 学习资料,朋友们如果需要可以微信扫描下方CSDN官方认证二维码免费领取【保证100%免费

【生成模型系列(初级)】嵌入(Embedding)方程——自然语言处理的数学灵魂【通俗理解】

【通俗理解】嵌入(Embedding)方程——自然语言处理的数学灵魂 关键词提炼 #嵌入方程 #自然语言处理 #词向量 #机器学习 #神经网络 #向量空间模型 #Siri #Google翻译 #AlexNet 第一节:嵌入方程的类比与核心概念【尽可能通俗】 嵌入方程可以被看作是自然语言处理中的“翻译机”,它将文本中的单词或短语转换成计算机能够理解的数学形式,即向量。 正如翻译机将一种语言

AI Toolkit + H100 GPU,一小时内微调最新热门文生图模型 FLUX

上个月,FLUX 席卷了互联网,这并非没有原因。他们声称优于 DALLE 3、Ideogram 和 Stable Diffusion 3 等模型,而这一点已被证明是有依据的。随着越来越多的流行图像生成工具(如 Stable Diffusion Web UI Forge 和 ComyUI)开始支持这些模型,FLUX 在 Stable Diffusion 领域的扩展将会持续下去。 自 FLU

SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

查看原文>>>全流程SWAP农业模型数据制备、敏感性分析及气候变化影响实践技术应用 SWAP模型是由荷兰瓦赫宁根大学开发的先进农作物模型,它综合考虑了土壤-水分-大气以及植被间的相互作用;是一种描述作物生长过程的一种机理性作物生长模型。它不但运用Richard方程,使其能够精确的模拟土壤中水分的运动,而且耦合了WOFOST作物模型使作物的生长描述更为科学。 本文让更多的科研人员和农业工作者

线性因子模型 - 独立分量分析(ICA)篇

序言 线性因子模型是数据分析与机器学习中的一类重要模型,它们通过引入潜变量( latent variables \text{latent variables} latent variables)来更好地表征数据。其中,独立分量分析( ICA \text{ICA} ICA)作为线性因子模型的一种,以其独特的视角和广泛的应用领域而备受关注。 ICA \text{ICA} ICA旨在将观察到的复杂信号