Clickhouse学习系列——一条SQL完成gourp by分组与不分组数值计算
笔者在近一两年接触了Clickhouse数据库,在项目中也进行了一些实践,但一直都没有一些技术文章的沉淀,近期打算做个系列,通过一些具体的场景将Clickhouse的用法进行沉淀和分享,供大家参考。
首先我们假设一个Clickhouse数据表:
CREATE TABLE Test_Table (page_id String, /* 页面ID */user_id String, /* 用户ID */is_slow String, /* 请求是否慢 */url String, /* 请求URL */
) ENGINE = MergeTree()
ORDER BY (page_id, device_id);
这个表格的字段含义如注释,该表主要存储的数据是:
每个用户(user_id)在某个页面(page_id)发起的请求(url)是否耗时较长(is_slow),耗时较长我们简称“慢请求”
其中,一个用户可能在一个或多个页面发起一个或多个请求,每个请求可能是慢请求,也可能不是
is_slow的内容是枚举值,即:true 和 false
数据表格有了,我们还要设定一个业务需求
我们期望以页面的纬度,查看每个页面中有多少用户遇到了慢请求,并算出在这个页面慢请求用户占在所有页面发起请求的总用户数(无论是否发起了慢请求)的百分比
从业务角度上比较好理解,这个结果其实就是想知道这个页面的慢影响了多少人,占比是多少
从研发者的角度,一个页面一般是一个固定的团队成员开发,甚至就是一个团队成员开发,所以,这个角度主要面向问题的解决和责任的划分,同时也让项目的管理者,技术的管理者知道目前用户体验的痛点在哪里
场景有了,业务需求也有了,那么如何实现呢?
首先,我们要获取的内容有两个,一个是“每个页面中有多少用户遇到了慢请求”
这个比较简单:
SELECT page_id, count(DISTINCT device_id) AS slow_user_countFROM Test_TableWHEREand is_slow = 'true'group by page_id
另一个要获取的是“在所有页面发起请求的总用户数”
SELECT count(DISTINCT device_id) AS total_user_countFROM Test_Table
最后,我们需要获得“在这个页面慢请求用户占在所有页面发起请求的总用户数(无论是否发起了慢请求)的百分比”
这里一般来说,比较常见的方法是使用With来进行拼接:
WITH slow_users AS (SELECT page_id, count(DISTINCT device_id) AS slow_user_countFROM Test_TableWHERE is_slow = 'true'group by page_id
),
total_users AS (SELECT count(DISTINCT device_id) AS total_user_countFROM Test_Table
)
SELECTslow_users.page_id,slow_users.slow_user_count,total_users.total_user_count,slow_user_count * 100.0 / total_users.total_user_count AS slow_user_percentage
FROMslow_users,total_users
ORDER BYslow_user_percentage DESC;
嗯,这个需求做完了,是不是很简单,貌似不熟悉ClickHouse的同学也能写
但既然笔者来写这篇文章,肯定不是想用,这么简单的方案,更何况,这个SQL本身还是有问题
第一个问题是:去重函数的性能问题
首先在Clickhouse里面有多个去重计数的函数,主要包含两类:
1.非精确去重函数:uniq、uniqHLL12、uniqCombined
2.精确去重函数:uniqExact、groupBitmap
从官网资料上来看:
在非精确去重函数中:
uniq函数使用自适应采样算法,
uniqHLL12函数使用的是HyperLogLog 算法
uniqCombined函数使用三种算法的组合:数组、哈希表和包含错误修正表的HyperLogLog算法
官方推荐:uniq和uniqCombined函数,不推荐uniqCombined函数
同时对于uniq和uniqCombined的区别上,官方给出的建议是:

在精确去重函数中:
uniqExact函数是uniq系列方法中的一个,比 uniq 使用更多的内存,因为状态的大小随着不同值的数量的增加而无界增长。参数可以是Tuple, Array, Date, DateTime, String,或数字类型。
groupBitmap函数比较特殊,参数得是一个无符号整数列,算法主要用的是“位图或聚合计算”
从这篇文章中查看了两个函数的源码:
/ count(distinct)
// HashSetTable
void merge(const Self & rhs){if (!this->hasZero() && rhs.hasZero()){this->setHasZero();++this->m_size;}for (size_t i = 0; i < rhs.grower.bufSize(); ++i)if (!rhs.buf[i].isZero(*this))this->insert(rhs.buf[i].getValue());}
// groupBitmap
// RoaringBitmapWithSmallSet
void merge(const RoaringBitmapWithSmallSet & r1){if (r1.isLarge()){if (isSmall())toLarge();*rb |= *r1.rb;}else{for (const auto & x : r1.small)add(x.getValue());}}
uniqExact函数使用了HashSetTable数据结构来解决,这里是比较费内存的,所以耗时也比较长
groupBitmap函数使用了RoaringBitmap,一个低内存去重方案,具体的算法参考
大数据分析常用去重算法分析『Bitmap篇』
从行业测试的结果上来看:
从这篇文章来看,这几个方法的效果如下:


可以看到精确去重函数的耗时是比较长的,非精确去重函数的误差在0.5%以内,而在实际的也场景中,很多数据分析平台更多的是需要一个数量级的概念,而不需要一个精确的数据,比如一个产品的UV为2600万,这个2600万就是一个概略数字,且随着变动越大,故可以用非精确去重函数
在上面的SQL中DISTINCT方法实际上是在用uniqExact,也就是最耗时的精确去重函数,在这个场景下,我们假设用户数据量比较多,请求数据量也比较多,我们更关注哪个页面问题多,问题大,而不是有精确的多少个慢请求数,符合非精确去重函数的场景,
那这里进行修改:
/* 精确但耗时的方法 */
COUNT(DISTINCT device_id) FILTER (WHERE is_slow = 'true') AS slow_user_count/* 不精确但快速的方法*/
uniqIf(device_id, is_slow = 'true') AS slow_user_count
知识点:上图中,除了将DISTINCT修改uniq外,还增加了如果增加了IF判断应该怎么写的语法
第二个问题是:With用法的性能问题
在Clickhouse,说起来,With有两种用法,
一种是通用SQL常见的用法 :with alias as (…),这个叫CTE,common table expression,是SQL定义中的一部份,按照这篇文章来看:
The common table expression (CTE) is a powerful construct in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and eliminated after query execution. CTEs often act as a bridge to transform the data in source tables to the format expected by the query.
A common table expression, or CTE, is a temporary named result set created from a simple
SELECTstatement that can be used in a subsequentSELECTstatement. Each SQL CTE is like a named query, whose result is stored in a virtual table (a CTE) to be referenced later in the main query.
就是建立一个虚拟表,来存储中间数据,然后进行使用,值得一提的是,子查询和CTE嵌套的性能理论上是一样的,但后者的可读性更好,不过在某些关系型数据库的引擎上略有区别,但本质上区别不大
比如上一章节根据业务输出的带With的SQL可以转换成以下嵌套子查询
/*不带with版本*/
SELECT page_id,COUNT(DISTINCT device_id) FILTER (WHERE is_slow = 'true') AS slow_user_count,(SELECT COUNT(DISTINCT device_id)FROM Test_Table) AS total_user_count,slow_user_count * 100.0 / total_user_count AS slow_user_percentage
FROM Test_Table
WHERE is_slow = 'true'
group by page_id
ORDER BY slow_user_percentage DESC;
但意义不大,因为性能没啥变化,两种方式都是二次查询(读两次盘)
另一种是with (…) as alias,这个是ClickHouse的宏展开一样的能力,是Clickhouse独有的语法
根据官方的文档:主要有四种用法
1.使用常量作为"变量"
WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHEREEventDate = toDate(ts_upper_bound) ANDEventTime <= ts_upper_bound;
2.封装表达式
WITH sum(bytes) as s
SELECTformatReadableSize(s),table
FROM system.parts
GROUP BY table
ORDER BY s;
3.使用标量子查询的结果(这个和前面的with有点像,但不能用.xxx的形式获取值,且Select只能一个值)
/* this example would return TOP 10 of most huge tables */
WITH(SELECT sum(bytes)FROM system.partsWHERE active) AS total_disk_usage
SELECT(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;
4.在子查询中重用表达式
WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;
其中第一个、第二个相当于直接替换,没有啥影响,而第三种、第四种和CTE的作用差不多,都会逐个去执行SQL,也就意味着二次查询(读两次盘)
所以看起来使用With无法避免二次读盘的问题
那这里,有没有一次读盘就可以解决这里的问题呢?看起来group by分组前后的数据做数值计算也是一个经典场景
那这里就得用到Clickhouse经典的窗口函数和物化视图了
窗口函数这篇文章有比较详细的介绍
物化试图这篇文章有比较详细的介绍
先看结果SQL
/* 一条sql的版本*/
SELECT page_id,uniqIf(device_id, is_slow = 'true') AS slow_user_count,uniq(device_id) AS page_user_count,uniqMerge(uniqState(device_id)) OVER () as total_user_count
FROM Test_Table
group by page_id
前两个一个是某个页面慢请求的用户数,一个该页面所欲请求的用户数
第三个需要拆开来看, uniqState是一个物化视图的方法,可以理解成一个AggregateFunction类型的数据的中间状态,这里可以理解基于每个页面都生成了一个数组,存储对应的用户名单
而uniqMerge可以将多个AggregateFunction类型的中间状态组合计算为最终的聚合结果,比如以下两个SQL是等价的:
SELECT uniq(UserID) FROM tableSELECT uniqMerge(state) FROM (SELECT uniqState(UserID) AS state FROM table GROUP BY RegionID)
当然在这里用到的是uniqState 和 uniqMerge
这里可以换成任何以-State和-Merge为后缀的方法
回到这个SQL,这里uniqMerge(uniqState(device_id)) OVER () 相当于合并了“基于每个页面都生成的数组,每个数组存储对应的用户名单”,即访问所有页面的所有用户数
这样就比较优雅的实现了不用with的问题,且这里的性能也是比较快的
相关文章:
Clickhouse学习系列——一条SQL完成gourp by分组与不分组数值计算
笔者在近一两年接触了Clickhouse数据库,在项目中也进行了一些实践,但一直都没有一些技术文章的沉淀,近期打算做个系列,通过一些具体的场景将Clickhouse的用法进行沉淀和分享,供大家参考。 首先我们假设一个Clickhouse数…...
做好“关键基础设施提供商”角色,亚马逊云科技加快生成式AI落地
一场关于生产力的革命已在酝酿之中。全球管理咨询公司麦肯锡在最近的报告《生成式人工智能的经济潜力:下一波生产力浪潮》中指出,生成式AI每年可能为全球经济增加2.6万亿到4.4万亿美元的价值。在几天前的亚马逊云科技纽约峰会中,「生成式AI」…...
如何使用 ChatGPT 规划家居装修
你正在计划家庭装修项目,但不确定从哪里开始?ChatGPT 随时为你提供帮助。从集思广益的设计理念到估算成本,ChatGPT 可以简化你的家居装修规划流程。在本文中,我们将讨论如何使用 ChatGPT 有效地规划家居装修,以便你的项…...
题解 | #1002.Random Nim Game# 2023杭电暑期多校7
1002.Random Nim Game 诈骗博弈题 题目大意 Nim是一种双人数学策略游戏,玩家轮流从不同的堆中移除棋子。在每一轮游戏中,玩家必须至少取出一个棋子,并且可以取出任意数量的棋子,条件是这些棋子都来自同一个棋子堆。走最后一步棋…...
篇九:组合模式:树形结构的力量
篇九:“组合模式:树形结构的力量” 开始本篇文章之前先推荐一个好用的学习工具,AIRIght,借助于AI助手工具,学习事半功倍。欢迎访问:http://airight.fun/。 另外有2本不错的关于设计模式的资料,…...
【注册表】windows系统注册表常用修改方案
文章目录 ◆ 修改IE浏览器打印页面参数设置◆气泡屏幕保护◆彩带屏幕保护程序◆过滤IP(适用于WIN2000)◆禁止显示IE的地址栏◆禁止更改IE默认的检查(winnt适用)◆允许DHCP(winnt适用)◆局域网自动断开的时间(winnt适用)◆禁止使用“重置WEB设置”◆禁止更…...
ant-design-vue 4.x升级问题-样式丢失问题
[vue] ant-design-vue 4.x升级问题-样式丢失问题 项目环境问题场景解决方案 该文档是在升级ant-design-vue到4.x版本的时候遇到的问题 项目环境 "vue": "^3.3.4", "ant-design-vue": "^4.0.0", "vite": "^4.4.4&quo…...
【果树农药喷洒机器人】Part3:变量喷药系统工作原理介绍
本专栏介绍:免费专栏,持续更新机器人实战项目,欢迎各位订阅关注。 关注我,带你了解更多关于机器人、嵌入式、人工智能等方面的优质文章! 文章目录 一、变量喷药系统工作原理二、液压通路设计与控制系统封装2.1液压通路…...
GoogLeNet创新点总结
GoogLeNet是一种深度卷积神经网络架构,于2014年由Google团队提出,是ILSVRC(ImageNet Large Scale Visual Recognition Challenge)比赛的冠军模型,其创新点主要集中在以下几个方面: Inception模块&#…...
不同路径1、2、3合集(980. 不同路径 III)
不同路径一 矩形格,左上角 到 右下角。 class Solution {int [] directX new int[]{-1,1,0,0};int [] directY new int[]{0,0,-1,1};int rows;int cols;public int uniquePathsIII(int[][] grid) {if (grid null || grid.length 0 || grid[0].length 0) {ret…...
【云原生】Yaml文件详解
目录 一、YAML 语法格式1.1查看 api 资源版本标签1.2 写一个yaml文件demo1.3 详解k8s中的port 一、YAML 语法格式 Kubernetes 支持 YAML 和 JSON 格式管理资源对象JSON 格式:主要用于 api 接口之间消息的传递YAML格式:用于配置和管理,YAML 是…...
ffmpeg下载安装教程
ffmpeg官网下载地址https://ffmpeg.org/download.html 这里以windows为例,鼠标悬浮到windows图标上,再点击 Windows builds from gyan.dev 或者直接打开 https://www.gyan.dev/ffmpeg/builds/ 下载根据个人需要下载对应版本 解压下载的文件,并复制bin所在目录 新打开一个命令…...
uniapp之当你问起“tab方法触发时eventchange也跟着触发了咋办”时
我相信没有大佬会在这个问题上卡两个小时吧,记下来大家就当看个乐子了。 当时问题就是,点击tab头切换的时候,作为tab滑动事件的eventchange同时触发了,使得接口请求了两次 大概是没睡好,我当时脑子老想着怎么阻止它冒…...
TS 踩坑之路(四)之 Vue3
一、在使用定义默认值withDefaults和defineProps 组合时,默认值设置报错 代码案例 报错信息 不能将类型“{ isBackBtn: false; }”分配给类型“(props: PropsType) > RouteMsgType”。 对象字面量只能指定已知属性,并且“isBackBtn”不在类型“(pro…...
【音视频】edge与chrome在性能上的比较
目录 结论先说 实验 结论 实验机器的cpu配置 用EDGE拉九路编辑 google拉五路就拉不出来了 资源使用情况 edge报错编辑 如果服务器端 性能也满 了,就会不回复;验证方式 手动敲 8081,不回应。 结论先说 实验 用chrome先拉九路&#…...
Docker Compose编排部署LNMP服务
目录 安装docker-ce 阿里云镜像加速器 文件 启动 安装docker-ce [rootlocalhost ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo --2023-08-03 18:34:32-- http://mirrors.aliyun.com/repo/Centos-7.repo 正在解析主机 m…...
git使用(常见用法)
一.下载git git官方下载跳转 安装简单,有手就行 二. git的简单使用 1. 连接远程仓库 #初始化 git init #配置账户 git config --global user.name “输入你的用户名” git config --global user.email “输入你的邮箱” git config --list #--q退出 #配置验证邮箱 ssh-key…...
用例拆分情况考虑方案
文章目录 1、方案一方案概述方案分析(1) 把对应图商地图的逻辑给分离开(2) 要使用命令行的方式执行方法 2、方案二3、最终决定 1、方案一 方案概述 每个图商(GD、BD、自建)拆分成单独的类 把参数化的几个图商类别拆分成对应的图商类,在每个类…...
一文搞懂IS-IS报文通用格式
报文格式 IS-IS报文是直接封装在数据链路层的帧结构中的。PDU可以分为两个部分,报文头和变长字段部分。其中头部又可分为通用头部和专用头部。对于所有PDU来说,通用报头都是相同的,但专用报头根据PDU类型不同而有所差别。 IS-IS的PDU有4种类…...
位置参数 关键字参数
在Python中,函数参数可以按照位置或关键字来传递。这导致了两种主要的参数类型:位置参数和关键字参数。 位置参数: 这是最常见的参数类型,当我们调用函数时,传递给函数的参数值是按照它们的位置来确定的。例如,def fun…...
Vue记事本应用实现教程
文章目录 1. 项目介绍2. 开发环境准备3. 设计应用界面4. 创建Vue实例和数据模型5. 实现记事本功能5.1 添加新记事项5.2 删除记事项5.3 清空所有记事 6. 添加样式7. 功能扩展:显示创建时间8. 功能扩展:记事项搜索9. 完整代码10. Vue知识点解析10.1 数据绑…...
MySQL 隔离级别:脏读、幻读及不可重复读的原理与示例
一、MySQL 隔离级别 MySQL 提供了四种隔离级别,用于控制事务之间的并发访问以及数据的可见性,不同隔离级别对脏读、幻读、不可重复读这几种并发数据问题有着不同的处理方式,具体如下: 隔离级别脏读不可重复读幻读性能特点及锁机制读未提交(READ UNCOMMITTED)允许出现允许…...
无法与IP建立连接,未能下载VSCode服务器
如题,在远程连接服务器的时候突然遇到了这个提示。 查阅了一圈,发现是VSCode版本自动更新惹的祸!!! 在VSCode的帮助->关于这里发现前几天VSCode自动更新了,我的版本号变成了1.100.3 才导致了远程连接出…...
使用分级同态加密防御梯度泄漏
抽象 联邦学习 (FL) 支持跨分布式客户端进行协作模型训练,而无需共享原始数据,这使其成为在互联和自动驾驶汽车 (CAV) 等领域保护隐私的机器学习的一种很有前途的方法。然而,最近的研究表明&…...
ESP32读取DHT11温湿度数据
芯片:ESP32 环境:Arduino 一、安装DHT11传感器库 红框的库,别安装错了 二、代码 注意,DATA口要连接在D15上 #include "DHT.h" // 包含DHT库#define DHTPIN 15 // 定义DHT11数据引脚连接到ESP32的GPIO15 #define D…...
Qt Http Server模块功能及架构
Qt Http Server 是 Qt 6.0 中引入的一个新模块,它提供了一个轻量级的 HTTP 服务器实现,主要用于构建基于 HTTP 的应用程序和服务。 功能介绍: 主要功能 HTTP服务器功能: 支持 HTTP/1.1 协议 简单的请求/响应处理模型 支持 GET…...
WordPress插件:AI多语言写作与智能配图、免费AI模型、SEO文章生成
厌倦手动写WordPress文章?AI自动生成,效率提升10倍! 支持多语言、自动配图、定时发布,让内容创作更轻松! AI内容生成 → 不想每天写文章?AI一键生成高质量内容!多语言支持 → 跨境电商必备&am…...
RNN避坑指南:从数学推导到LSTM/GRU工业级部署实战流程
本文较长,建议点赞收藏,以免遗失。更多AI大模型应用开发学习视频及资料,尽在聚客AI学院。 本文全面剖析RNN核心原理,深入讲解梯度消失/爆炸问题,并通过LSTM/GRU结构实现解决方案,提供时间序列预测和文本生成…...
AI,如何重构理解、匹配与决策?
AI 时代,我们如何理解消费? 作者|王彬 封面|Unplash 人们通过信息理解世界。 曾几何时,PC 与移动互联网重塑了人们的购物路径:信息变得唾手可得,商品决策变得高度依赖内容。 但 AI 时代的来…...
2025季度云服务器排行榜
在全球云服务器市场,各厂商的排名和地位并非一成不变,而是由其独特的优势、战略布局和市场适应性共同决定的。以下是根据2025年市场趋势,对主要云服务器厂商在排行榜中占据重要位置的原因和优势进行深度分析: 一、全球“三巨头”…...
