ClickHouse实现大数据探索性分析

已于 2023-04-17 16:42:26 修改
阅读量2k 收藏 4
点赞数 2
分类专栏： ClickHouse 文章标签： clickhouse 数据分析 探索性分析
于 2023-04-17 16:30:16 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/neweastsun/article/details/130203134
版权

分析数据一般会从探索性分析开始，即尝试理解数据本身的概况。通常包括中位数、平均值或分布情况，Python Numpy/Pandas很容易实现，但如果数据量为Tb级，不能简单依赖RAM工具实现。ClickHouse提供的强大的工具来挖掘数据，不仅局限于min/max/avg分析。

测试数据

下面在clickhouse中创建示例表，并生成测试数据：

CREATE TABLE test (
  `time` DateTime, `a` UInt64, `b` Float64,
  `c` UInt32, `d` UInt32, `e` UInt32
) 
ENGINE = MergeTree
ORDER BY time

新版ClickHouse可以不指定ENGINE，为了兼容之前版本，这里显示指定表引擎，下面生成10亿测试数据：

INSERT INTO test
SELECT now() - number, rand64()/1000000000,
       round(rand32()/1000000)/100,
       rand32()/1000000, rand32()/10000, rand32()/100
FROM numbers(1000000000);

探索性分析

Min, Max, Avg, Median

首先通过基本的聚集函数实现：

SELECT min(a), max(a), avg(a), median(a) FROM test

可以a列的总体情况：

返回结果：


SELECT
    min(a),
    max(a),
    avg(a),
    median(a)
FROM test

Query id: ba893a8e-1c1e-4ae0-93d1-b966a370c841

┌─min(a)─┬──────max(a)─┬────────────avg(a)─┬────median(a)─┐
│     27 │ 18446744043 │ 9223167195.242725 │ 9269412176.5 │
└────────┴─────────────┴───────────────────┴──────────────┘

注意：median函数是近似实现（为了性能考虑），如果需要准确结果实用medianExact()。

对于相同类型列，可以实用ForEach合并器展示所有列的聚集结果：

WITH [a, c, d, e] AS col
SELECT maxForEach(col), minForEach(col),
       avgForEach(col), medianForEach(col)
FROM test

注意，这里跳过b列，因为b为Float类型，不是Int类型。结果返回多个数组值，顺序于with子句保持一致：

查看结果：


WITH [a, c, d, e] AS col
SELECT
    maxForEach(col),
    minForEach(col),
    avgForEach(col),
    medianForEach(col)
FROM test

Query id: e0da1a1d-0dd7-4ac1-8061-13e5b1d32637

┌─maxForEach(col)────────────────────┬─minForEach(col)─┬─avgForEach(col)────────────────────────────────────────────────────────┬─medianForEach(col)────────────────────┐
│ [18446744043,4294,429496,42949672] │ [27,0,0,0]      │ [9223167195.242725,2147.084726271,214757.971990777,21475846.697796952] │ [9094439831,2158,215857.5,21585783.5] │
└────────────────────────────────────┴─────────────────┴────────────────────────────────────────────────────────────────────────┴───────────────────────────────────────┘

加权平均

有时需要计算加权平均，一列为业务度量（假设为a列）、一列为权重（这里假设为c列）：

SELECT avgWeighted(a, c) FROM test

结果与标准平均有差异，因为c列表示每行的权重：


SELECT avgWeighted(a, c)
FROM test

Query id: b5af5a0f-2b15-4410-a4b9-2432673b43e1

┌─avgWeighted(a, c)─┐
│ 4500282.012782601 │
└───────────────────┘

分位数

数据值分布分析用于理解大多数样本值的分布情况，可以使用quantile函数，下面展示a列的值分布情况：

SELECT quantile(0.01)(a), quantile(0.05)(a), quantile(0.1)(a),
       quantile(0.5)(a),  quantile(0.9)(a),  quantile(0.95)(a),
       quantile(0.99)(a)
FROM test

结果如下：


SELECT
    quantile(0.01)(a),
    quantile(0.05)(a),
    quantile(0.1)(a),
    quantile(0.5)(a),
    quantile(0.9)(a),
    quantile(0.95)(a),
    quantile(0.99)(a)
FROM test

Query id: 3085d3ef-387a-4e01-a6b0-42ce33393839

┌─quantile(0.01)(a)─┬─quantile(0.05)(a)─┬─quantile(0.1)(a)─┬─quantile(0.5)(a)─┬───quantile(0.9)(a)─┬──quantile(0.95)(a)─┬─quantile(0.99)(a)─┐
│      177601178.51 │       896692543.5 │     1874314795.8 │       9045538672 │ 16552719369.300003 │ 17504816857.949997 │    18283462553.02 │
└───────────────────┴───────────────────┴──────────────────┴──────────────────┴────────────────────┴────────────────────┴───────────────────┘

quantile函数参数表示需要计算的百分位数，范围：0~1之间。

  • 0.01 表 1% 百分位 (表示：a列值中仅1%小于计算结果),
  • 0.99 表示 99% 百分位,
  • 0.5 表示 50% 百分位 (或中位数).

我们可以使用箱线图展示列数据的分布情况。通常用于展示数据分布及异常值。

直方图

histogram函数计算数据直方图，参数为分组数量：

SELECT histogram(10)(a) FROM test

结果如下：

Query id: 12671173-07b6-4626-88cf-1c1e0b06b311

┌─hist_a─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [(27,1778599749.8154733,96220818.875),(1778599749.8154733,3567318758.13501,96866341.75),(3567318758.13501,5383282603.941727,98348320.75),(5383282603.941727,7229037840.827569,100085775.125),(7229037840.827569,9098282038.82383,101508675.125),(9098282038.82383,10979523217.501022,102238136.25),(10979523217.501022,12860395774.905602,102171698.875),(12860395774.905602,14731643787.83552,101508574.5),(14731643787.83552,16590229016.271152,100691124.875),(16590229016.271152,18446744043,100360533.875)] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 21.144 sec. Processed 1.00 billion rows, 8.00 GB (47.29 million rows/s., 378.36 MB/s.)

返回10元组数组，每个tuple包括起始值、截止值和频数。上面结果不够直观，下面使用bar函数进行展示：

WITH    count(*) AS m, histogram(10)(a) AS h
SELECT  m, arrayJoin(h).3 AS v, bar(v, 0, m / 10, 50) hist
FROM    test

这里计算a列直方图，分为10个组，用频次作为高度:获取元组的第三个值：arrayJoin(h).3 。bar中几个参数意义分别为：当前值，最小值，最大值，最大宽度（bar的最大宽度），结果如下：


WITH
    count(*) AS m,
    histogram(10)(a) AS h
SELECT
    m,
    arrayJoin(h).3 AS v,
    bar(v, 0, m / 10, 50) AS hist
FROM test

Query id: 84d73ecf-4ad6-436a-b007-d4028a8703b5

┌──────────m─┬─────────────v─┬─hist───────────────────────────────────────────────┐
│ 1000000000 │      96191900 │ ████████████████████████████████████████████████   │
│ 1000000000 │   96848619.75 │ ████████████████████████████████████████████████▍  │
│ 1000000000 │  98342270.625 │ █████████████████████████████████████████████████▏ │
│ 1000000000 │   100073373.5 │ ██████████████████████████████████████████████████ │
│ 1000000000 │ 101491254.375 │ ██████████████████████████████████████████████████ │
│ 1000000000 │  102252583.25 │ ██████████████████████████████████████████████████ │
│ 1000000000 │ 102199888.125 │ ██████████████████████████████████████████████████ │
│ 1000000000 │  101529766.75 │ ██████████████████████████████████████████████████ │
│ 1000000000 │ 100703527.125 │ ██████████████████████████████████████████████████ │
│ 1000000000 │   100366816.5 │ ██████████████████████████████████████████████████ │
└────────────┴───────────────┴────────────────────────────────────────────────────┘

10 rows in set. Elapsed: 20.589 sec. Processed 1.00 billion rows, 8.00 GB (48.57 million rows/s., 388.56 MB/s.)

偏度(skewness)和峰度(kurtosis) 偏度

SELECT skewPop(a), skewPop(b), skewPop(c), skewPop(d) FROM test

可以看到完美的对称数据，如果偏度范围是 -0.5~0.5，则为对称。


SELECT
    skewPop(a),
    skewPop(b),
    skewPop(c),
    skewPop(d)
FROM test

Query id: 3d8f142e-540a-45cf-b29b-92ebcb680655

┌─────────────skewPop(a)─┬──────────────skewPop(b)─┬──────────────skewPop(c)─┬──────────────skewPop(d)─┐
│ 0.00008286246282390312 │ -0.00009044879302832024 │ -0.00009040144066574894 │ -0.00009043134876494848 │
└────────────────────────┴─────────────────────────┴─────────────────────────┴─────────────────────────┘

下面检查峰度情况，峰度包括正态分布（峰度值=3），厚尾（峰度值>3），瘦尾（峰度值<3）。

SELECT kurtPop(a), kurtPop(b), kurtPop(c), kurtPop(d) FROM test

结果如下：


SELECT
    kurtPop(a),
    kurtPop(b),
    kurtPop(c),
    kurtPop(d)
FROM test

Query id: bfa86913-a7ec-46d2-b568-4337cd36c8f1

┌─────────kurtPop(a)─┬─────────kurtPop(b)─┬────────kurtPop(c)─┬─────────kurtPop(d)─┐
│ 1.7999958442027149 │ 1.8000103475159206 │ 1.800009989793573 │ 1.8000101052993867 │
└────────────────────┴────────────────────┴───────────────────┴────────────────────┘

唯一值

ClickHouse使用高效内存近似计算唯一值算法，通常用在探索性分析阶段：

SELECT uniq(a), uniq(b), uniq(c), uniq(d), uniq(e) FROM test

如果需要准确计算，采用count(distinct s) 。uniq采用HLL（HyperLogLog，默认精度为17），可以通过uniqCombined()指定精度，精度越高准确度越高，占用内存越大。


SELECT
    uniq(a),
    uniq(b),
    uniq(c),
    uniq(d),
    uniq(e)
FROM test

Query id: dff853d6-b2b6-4d60-8a7f-8769feb91a68

┌───uniq(a)─┬─uniq(b)─┬─uniq(c)─┬─uniq(d)─┬──uniq(e)─┐
│ 976951893 │    4296 │    4295 │  431282 │ 42794211 │
└───────────┴─────────┴─────────┴─────────┴──────────┘

最受欢迎值

计算数据集中最受欢迎值

SELECT a, count(*) AS total FROM test
GROUP BY a ORDER BY total DESC LIMIT 5

该语句等价于：

SELECT topK(5)(a) FROM test

结果如下：


SELECT topK(5)(a)
FROM test

Query id: a97a98fa-1ce5-42cf-a201-2094f6cd999f

┌─topK(5)(a)─────────────────────────────────────────────────┐
│ [5887434584,5400923505,14324499147,1971311740,12559436788] │
└────────────────────────────────────────────────────────────┘

topK比传统写法效率更高、更简洁，建议使用topK.

标准差

标准差可以理解数据的分散程度，varPop函数可以计算标准差：

SELECT varPop(a), varPop(c), varPop(d) FROM test

结果如下：

SELECT
    varPop(a),
    varPop(c),
    varPop(d)
FROM test

Query id: 47696a42-dd17-47fa-a937-00e49bc6d11b

┌────────────varPop(a)─┬─────────varPop(c)─┬─────────varPop(d)─┐
│ 28357107720360067000 │ 1537149.986743688 │ 15371500762.27394 │
└──────────────────────┴───────────────────┴───────────────────┘

计算公式：
在这里插入图片描述

相关性

计算两列的相关性：

SELECT covarPop(c,d) FROM test

返回结果：


SELECT covarPop(c, d)
FROM test

Query id: 0ac4a304-03e8-42ce-b521-dc23187c6aea

┌─────covarPop(c, d)─┐
│ 153714998.91794837 │
└────────────────────┘

计算公式：

在这里插入图片描述

实际应用中相关性系数更常用，它可以消除量纲，计算相关系数：

SELECT corr(c,d) FROM test

返回结果：


SELECT corr(c, d)
FROM test

Query id: 450eb864-f1f5-4945-9bf4-c88bcf9a75d5

┌─────────corr(c, d)─┐
│ 0.9999999725139772 │
└────────────────────┘

因为完美插入测试数据采用不同比例，两者当然为正相关，结果接近1.

总结

ClickHouse提供数学函数、聚集函数用于对大数据量进行探索性分析。一些函数采用近似算法进行优化，如：unique,quantile，效率更高，通常用于数据分析的初始阶段。下面列举探索性阶段常用的函数：

  • min(), max(), avg() and median() 最常用的几个聚集函数
  • avgWeighted(col, weights) 计算加权平均
  • quantile(level)(col) 计算分位数
  • histogram(bins)(col) and bar() 计算并画直方图
  • skewPop(col) 计算偏度，判断数据是否对称
  • uniq(col) 近似唯一值计算
  • topK(N)(col) 计算最受欢迎的N个值
  • varPop(col) 计算标准差
梦想画家
关注
  • 2
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
ClickHouse大数据分析与实战
04-09
<img src="https://img-bss.csdnimg.cn/202104090925218020.png" alt="" />
交互式分析领域，为何ClickHouse能够杀出重围？
QcloudCommunity的博客
10-20 620
导语 | 在百花齐放的交互式分析领域，ClickHouse 绝对是后起之秀，它虽然年轻，却有非常大的发展空间。本文将分享 PB 级分析型数据库 ClickHouse 的应用场景、整体架构...
ClickHouse常用函数统计
热门推荐
思过留痕
10-16 5万+
1、统计类函数 1.1 分位数 90%分位数： quantile(0.9)() 在求（平均）响应时间时用到，quantile(0.9)(url_rt) 1.2 中位数 median() 还未使用到，median(url_rt) 1.3 相关系数 计算x与y的相关系数，计算公式：∑(x−x‾)(y−y‾)∑(x−x‾)2∑(y−y‾)2\dfrac{\sum(x-\overline{x})(y-...
Clickhouse 聚合函数
vkingnew 的技术博客
08-31 5460
clickhouse的聚合函数包含了标准SQL的聚合函数，此外还有一些自有的聚合函数： 函数功能 函数名 标准OR自有 函数别名 计数 count 求和 sum 求平均 avg 最大值 max 最小值 min 中位数 media any anyHeavy ...
分位数计算方法 percentile() quantile()
DK_ZLP的博客
08-23 921
Y=idx(x)+(idx(x+1)-idx(x))*0.z Y=idx整数部分序号对应的值+（（idx整数部分序号+1）对应值 - idx整数部分序号对应的值）*idx小数部分。分位，对应的是第 （N-1）* P/100 +1个数，序号 idx =（N-1）* P/100。数组重新升序排列，数组个数 n=11，分位数 q=0.11，分位数P对应的数字的序号为。分位，对应的是第 （N-1）* P +1个数，序号 idx =（N-1）* P。如果idx为整数，则Y=idx对应的数值。
clickhouse普通函数，聚合函数使用总结
dair6的博客
02-11 7906
## 一.创建clickhouse表 ### 1.建表 CREATE TABLE ck_test ( `id` String COMMENT 'id', `int_1` UInt32 COMMENT '整型列1', `int_2` UInt32 COMMENT '整型列2', `str_1` String COMMENT '字符串列1', `str_2` String COMMENT '字符串列2' ) ENGINE = MergeTree ORDER BY id...
ClickHouse初入门
xiaoxin1024的博客
07-04 859
目录 什么是ClickHouse： OLAP场景得关键属性： ClickHouse的独特功能： 1.真正面向列的DBMS， 2.数据压缩 3，数据磁盘存储 4，多个核心的并行处理 5，多个服务器上多的分布式处理 6，SQL支持 7，矢量引擎 8，实时数据更新 9，索引 10，适合在线查询 11，支持近似计算 12，数据复制和数据完整性支持 ClickHouse的...
ClickHouse函数介绍
努力努力再努力！的博客
07-27 8816
ClickHouse函数介绍 ClickHouse中至少存在两种类型的函数 ：常规函数和聚合函数。 常规函数的工作就像分别为每一行执行一次函数计算一样（对于每一行，函数的结果不依赖于其他行）。 聚合函数则从各行累积一组值（即函数的结果依赖整个结果集）。 1. 常规函数 1.1 算数函数 对于所有算术函数，如果存在这样的类型，则结果类型将计算为结果适合的最小数字类型。 根据位数、是否有符号、是否浮点数，同时取最小值。 如果没有足够的位，则采用最高位类型，如： SELECT toTypeName(0), toT
[ClickHouse] 使用 Pandas 读写 ClickHouse
weixin_42902669的博客
11-22 9214
由于 Pandas 本身不支持 ClickHouse 相关操作, 所以自己封装了一些读写操作 一. Pandas 读取 ClickHouse GitHub 上面有几个 Python 操作 ClickHouse 的开源项目, 不过收藏数量都不多(几百个 star), 经过试用, 发现 clickhouse_driver 这个项目相对靠谱一点, 先使用 pip 安装, 读取数据的代码如下: from clickhouse_driver import Client import pandas as pd impo
clickhouse 常用函数
ailian_f的博客
09-09 601
https://blog.csdn.net/u012111465/article/details/85250030?biz_id=102&utm_term=clickhouse%20%E6%8C%89%E5%91%A8%E5%88%86%E7%BB%84&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-0-85250030&spm=1018.2118.3001.4187
ClickHouse大数据实战
12-06
ClickHouse大数据实战课程，本课程基于ClickHouse最新稳定版本进行讲解，着重讲解ClickHouse大数据技术理论与实战。课程全面包含ClickHouse核心概念、ClickHouse架构设计、ClickHouse数据实时查询、MergeTree表引擎底层原理、ClickHouse集群部署、ClickHouse副本与分片读写原理、ClickHouse与Kafka、MySQL、Spark、Flink等集成开发以及ClickHouse全流程大数据项目实战等内容，让大家从基础到实战系统快速掌握ClickHouse大数据分析技术。
ClickHouse大数据实战课程
05-17
分享一套ClickHouse大数据实战课程，希望对大家有帮助。
基于Kettle+Clickhouse+Superset构建亿级大数据实时数据分析平台视频教程
12-03
基于Kettle+Clickhouse+Superset构建亿级大数据实时数据分析平台视频教程，该系统以热门的互联网电商实际业务应用场景为案例讲解，对电商数据的常见实战指标处理使用kettle等工具进行了详尽讲解，具体指标包括：流量分析、新增用户分析、活跃用户分析订单分析、团购分析。能承载海量数据的实时分析，数据分析涵盖全端（PC、移动、小程序）应用。 项目代码也是具有很高的商业价值的，大家可以根据自己的业务进行修改,便可以使用
ClickHouse 高可用之副本
JIE的博客
04-23 879
官方给出的副本表创建示例：参数说明，其中表示分片标识信息，大多数情况下，只需要写入一个占位符。参数二：ZooKeeper 中该表的副本名称，该值必须与其它机器不同！在创建副本表时，它们可以存储在不同的库中，并不会影响副本的创建，只需要保证它们使用的是同一个 Zookeeper 路径即可。
CentOS-7安装clickhouse并允许其他主机登录
weixin_42924611的博客
04-23 465
修改把 :: 的注释打开，这样的话才能让ClickHouse被除本机以外的服务器访问。访问 https://packages.clickhouse.com/rpm/stable/ 下载文件。
ClickHouse 数据类型、表引擎与TTL
JIE的博客
04-22 1001
你可以为表中的某个列设置 TTL，指定数据的存储时间，一旦数据的时间戳超过了 TTL 设置的时间，数据将被自动删除。不同的引擎具有不同的特性和适用场景，可以根据数据的特点和应用需求选择合适的引擎来存储和处理数据。ReplacingMergeTree 引擎是 MergeTree 引擎的变种，支持在插入新数据时自动删除旧数据，适用于周期性更新的数据存储场景。查询出来，可能会出现并没有去重成功的情况，这是因为使用的 ClickHouse 是老版本的，在插入数据时不会立即去重。的表，用于存储网站访问日志数据。
技术流 | clickhouse如何加速merge
边逛边看边学习
04-23 549
我们在使用clickhouse的过程中，往往会发现一个奇怪的现象：明明机器性能很强，如36core， 1TB内存，但是merge的时候资源却并不能充分利用。 原因何在呢？
clickhouse安装部署
最新发布
cyhysr的博客
04-26 464
虚拟机：virtualbox7.0操作系统：ubuntu server 22.04.3虚拟机硬件：cpu 1，内存 2G， 硬盘 100G采用默认安装参照。
大数据Clickhouse
12-14
ClickHouse是一款面向列式存储的分布式数据库管理系统，主要用于OLAP场景下的数据查询和分析。它的特点是高性能、高并发、高可用、高扩展性和低成本。以下是ClickHouse的一些特点和用途： 1. 列式存储：ClickHouse采用列式存储，可以大幅度提高查询性能，特别是在需要查询大量数据的情况下。 2. 高性能：ClickHouse的查询性能非常高，可以支持百亿级别的数据查询，而且查询速度非常快。 3. 分布式架构：ClickHouse采用分布式架构，可以支持PB级别的数据存储和查询，而且可以实现数据的高可用和负载均衡。 4. SQL支持：ClickHouse支持类SQL语言，可以方便地进行数据查询和分析。 5. OLAP场景：ClickHouse主要用于OLAP场景下的数据查询和分析，可以支持实时查询和离线查询。 6. 数据仓库：ClickHouse可以作为数据仓库来使用，可以方便地进行数据的存储、查询和分析。 7. 日志分析：ClickHouse可以用于日志分析，可以方便地进行日志的存储、查询和分析。 8. 时序数据：ClickHouse可以用于时序数据的存储和查询，可以支持高并发的时序数据查询。 以下是一个使用ClickHouse进行数据查询和分析的例子： ```sql -- 创建表 CREATE TABLE test ( id UInt32, name String, age UInt8 ) ENGINE = MergeTree() ORDER BY id; -- 插入数据 INSERT INTO test VALUES (1, 'Tom', 20), (2, 'Jerry', 22), (3, 'Lucy', 18); -- 查询数据 SELECT * FROM test WHERE age > 20; ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值