Clickhouse学习系列——一条SQL完成gourp by分组与不分组数值计算

发布于

​

笔者在近一两年接触了Clickhouse数据库，在项目中也进行了一些实践，但一直都没有一些技术文章的沉淀，近期打算做个系列，通过一些具体的场景将Clickhouse的用法进行沉淀和分享，供大家参考。

首先我们假设一个Clickhouse数据表：

代码语言：javascript
复制
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

数据表格有了，我们还要设定一个业务需求

我们期望以页面的纬度，查看每个页面中有多少用户遇到了慢请求，并算出在这个页面慢请求用户占在所有页面发起请求的总用户数（无论是否发起了慢请求）的百分比

从业务角度上比较好理解，这个结果其实就是想知道这个页面的慢影响了多少人，占比是多少

从研发者的角度，一个页面一般是一个固定的团队成员开发，甚至就是一个团队成员开发，所以，这个角度主要面向问题的解决和责任的划分，同时也让项目的管理者，技术的管理者知道目前用户体验的痛点在哪里

场景有了，业务需求也有了，那么如何实现呢？

首先，我们要获取的内容有两个，一个是“每个页面中有多少用户遇到了慢请求”

这个比较简单：

代码语言：javascript
复制
    SELECT page_id, count(DISTINCT device_id) AS slow_user_count
    FROM Test_Table
    WHERE
      and is_slow = 'true'
    group by page_id

另一个要获取的是“在所有页面发起请求的总用户数”

代码语言：javascript
复制
 SELECT count(DISTINCT device_id) AS total_user_count
    FROM Test_Table

最后，我们需要获得“在这个页面慢请求用户占在所有页面发起请求的总用户数（无论是否发起了慢请求）的百分比”

这里一般来说，比较常见的方法是使用With来进行拼接：

代码语言：javascript
复制
WITH slow_users AS (
    SELECT page_id, count(DISTINCT device_id) AS slow_user_count
    FROM Test_Table
    WHERE 
      is_slow = 'true'
    group by page_id
),
total_users AS (
    SELECT count(DISTINCT device_id) AS total_user_count
    FROM Test_Table
)
SELECT
  slow_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
FROM
  slow_users,
  total_users
ORDER BY
  slow_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函数比较特殊，参数得是一个无符号整数列，算法主要用的是“位图或聚合计算”

从这篇文章中查看了两个函数的源码：

代码语言：javascript
复制
/ 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());
    }
}</code></pre></div></div><p><strong>uniqExact函数使用了</strong>HashSetTable数据结构来解决，这里是比较<strong>费内存</strong>的，所以耗时也比较长</p><p><strong>groupBitmap函数</strong>使用了RoaringBitmap，一个<strong>低内存</strong>去重方案，具体的算法参考</p><p>大数据分析常用去重算法分析『Bitmap篇』</p><h4 id="48hkd" name="%E4%BB%8E%E8%A1%8C%E4%B8%9A%E6%B5%8B%E8%AF%95%E7%9A%84%E7%BB%93%E6%9E%9C%E4%B8%8A%E6%9D%A5%E7%9C%8B%EF%BC%9A"> 从行业测试的结果上来看：</h4><p>   从这篇文章来看，这几个方法的效果如下：</p><figure class=""><div class="rno-markdown-img-url" style="text-align:center"><div class="rno-markdown-img-url-inner" style="width:89.16%"><div style="width:100%"><img src="https://cdn.static.attains.cn/app/developer-bbs/upload/1723334203228819560.png" /></div></div></div></figure><p> 可以看到<strong>精确去重函数</strong>的耗时是比较长的，<strong>非精确去重函数</strong>的误差在0.5%以内，而在实际的也场景中，很多数据分析平台更多的是需要一个数量级的概念，而不需要一个精确的数据，比如一个产品的UV为2600万，这个2600万就是一个概略数字，且随着变动越大，故可以用非精确去重函数</p><p>在上面的SQL中<strong>DISTINCT</strong>方法实际上是在用<strong>uniqExact</strong>，也就是最耗时的精确去重函数，在这个场景下，<strong>我们假设用户数据量比较多，请求数据量也比较多</strong>，我们更关注哪个页面问题多，问题大，而不是有精确的多少个慢请求数，符合非精确去重函数的场景，</p><p>那这里进行修改：    </p><div class="rno-markdown-code"><div class="rno-markdown-code-toolbar"><div class="rno-markdown-code-toolbar-info"><div class="rno-markdown-code-toolbar-item is-type"><span class="is-m-hidden">代码语言：</span>javascript</div></div><div class="rno-markdown-code-toolbar-opt"><div class="rno-markdown-code-toolbar-copy"><i class="icon-copy"></i><span class="is-m-hidden">复制</span></div></div></div><div class="developer-code-block"><pre class="prism-token token line-numbers language-javascript"><code class="language-javascript" style="margin-left:0">/* 精确但耗时的方法 */



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 SELECT statement that can be used in a subsequent SELECT statement. 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可以转换成以下嵌套子查询

代码语言：javascript
复制
/*不带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.使用常量作为"变量"

代码语言：javascript
复制
WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound;

2.封装表达式

代码语言：javascript
复制
WITH sum(bytes) as s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s;

3.使用标量子查询的结果（这个和前面的with有点像，但不能用.xxx的形式获取值，且Select只能一个值）

代码语言：javascript
复制
/* this example would return TOP 10 of most huge tables */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE 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.在子查询中重用表达式

代码语言：javascript
复制
WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;

其中第一个、第二个相当于直接替换，没有啥影响，而第三种、第四种和CTE的作用差不多，都会逐个去执行SQL，也就意味着二次查询（读两次盘）

所以看起来使用With无法避免二次读盘的问题

那这里，有没有一次读盘就可以解决这里的问题呢？看起来group by分组前后的数据做数值计算也是一个经典场景

那这里就得用到Clickhouse经典的窗口函数和物化视图了

窗口函数这篇文章有比较详细的介绍

物化试图这篇文章有比较详细的介绍

先看结果SQL

代码语言：javascript
复制
/* 一条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是等价的：

代码语言：javascript
复制
SELECT uniq(UserID) FROM table

SELECT 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