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

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);

    SELECT page_id, count(DISTINCT device_id) AS slow_user_count
    FROM Test_Table
    WHERE
      and is_slow = 'true'
    group by page_id

 SELECT count(DISTINCT device_id) AS total_user_count
    FROM Test_Table

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;

/ 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><h5 id="36vsp" 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"> 从行业测试的结果上来看：</h5><p>   从这篇文章来看，这几个方法的效果如下：</p><figure class=""><div class="rno-markdown-img-url" style="text-align:center"><div class="rno-markdown-img-url-inner" style="width:100%"><div style="width:100%"><img src="https://cdn.static.attains.cn/app/developer-bbs/upload/1723334205940622894.png" /></div></div></div></figure><figure class=""><div class="rno-markdown-img-url" style="text-align:center"><div class="rno-markdown-img-url-inner" style="width:100%"><div style="width:100%"><img src="https://cdn.static.attains.cn/app/developer-bbs/upload/1723334206313244853.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

/*不带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 '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound;

WITH sum(bytes) as s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s;

/* 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;

WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;

/* 一条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

SELECT uniq(UserID) FROM table
SELECT uniqMerge(state) FROM (SELECT uniqState(UserID) AS state FROM table GROUP BY RegionID)