1. 首页
  2. 文章中心
  3. 大数据

Clickhouse 消除由group by产生的间隙

59 阅读 0 评论
我是靠谱客的博主 诚心大树，最近开发中收集的这篇文章主要介绍Clickhouse 消除由group by产生的间隙，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

        这是好几个月前遇到的问题了，一直以为自己已经记录过了，突然发现并没有 /捂脸.jpg/。需求是根据日期、国家、供应商、设备……等条件，统计DUA、留存率等数据信息。这个需求不是本人负责，只是同事遇到由于group by而导致的日期不连续问题，问到我。本文仅提供一种思路而已，最终同事也并没有使用此方案在数据库层面解决此问题，而是在java程序中解决。

        1. 简化过的问题复现，e.g.由于中国区4月5到20日dua和use_times均为0，所以这几天在结果中消失了，产生了时间不连续。

SELECT
    event_date,
    country_name,
    groupBitmapOr(active_user) AS dau,
    sum(use_times) AS use_times
FROM dws_app_global_active_retained
WHERE (country_name IN ('China', 'France', 'Hong Kong')) AND (event_date >= '2022-03-25')
GROUP BY
    event_date,
    country_name
ORDER BY
    country_name ASC,
    event_date ASC
┌─event_date─┬─country_name─┬─dau─┬─use_times─┐
│ 2022-03-27 │ China        │   1 │         1 │
│ 2022-03-28 │ China        │   1 │         2 │
│ 2022-03-29 │ China        │   2 │        64 │
│ 2022-03-30 │ China        │   1 │         4 │
│ 2022-03-31 │ China        │   2 │         4 │
│ 2022-04-01 │ China        │   1 │         5 │
│ 2022-04-02 │ China        │   1 │         1 │
│ 2022-04-03 │ China        │   1 │         1 │
│ 2022-04-04 │ China        │   1 │         2 │
│ 2022-04-21 │ China        │   1 │         5 │
│ 2022-04-28 │ China        │   1 │         6 │
│ 2022-04-29 │ China        │   1 │         4 │
│ 2022-04-30 │ China        │   1 │        12 │
│ 2022-05-01 │ China        │   1 │        10 │
│ 2022-05-02 │ China        │   1 │         9 │
│ 2022-05-03 │ China        │   1 │         3 │
│ 2022-05-04 │ China        │   1 │         2 │
│ 2022-05-05 │ China        │   1 │         6 │
│ 2022-05-06 │ China        │   1 │         3 │
│ 2022-05-07 │ China        │   1 │         5 │
│ 2022-05-08 │ China        │   1 │         4 │
│ 2022-03-25 │ Hong Kong    │   2 │         5 │
│ 2022-03-26 │ Hong Kong    │   1 │         2 │
│ 2022-03-27 │ Hong Kong    │   1 │         2 │
│ 2022-03-28 │ Hong Kong    │   2 │        26 │
│ 2022-03-29 │ Hong Kong    │   2 │        56 │
│ 2022-03-30 │ Hong Kong    │   3 │        17 │
│ 2022-03-31 │ Hong Kong    │   4 │        12 │
│ 2022-04-01 │ Hong Kong    │   1 │         1 │
│ 2022-04-02 │ Hong Kong    │   2 │        23 │
│ 2022-04-06 │ Hong Kong    │   1 │         1 │
│ 2022-04-07 │ Hong Kong    │   2 │         9 │
│ 2022-04-08 │ Hong Kong    │   2 │        29 │
└────────────┴──────────────┴─────┴───────────┘

        2. 先填补时间间隙（WITH FILL STEP n）

SELECT
    event_date,
    country_name,
    groupBitmapOr(active_user) AS dau,
    sum(use_times) AS use_times
FROM dws_app_global_active_retained
WHERE (country_name IN ('China', 'France', 'Hong Kong')) AND (event_date >= '2022-03-25')
GROUP BY
    event_date,
    country_name
ORDER BY
    country_name ASC,
    event_date ASC WITH FILL STEP 1

┌─event_date─┬─country_name─┬─dau─┬─use_times─┐
│ 2022-03-27 │ China        │   1 │         1 │
│ 2022-03-28 │ China        │   1 │         2 │
│ 2022-03-29 │ China        │   2 │        64 │
│ 2022-03-30 │ China        │   1 │         4 │
│ 2022-03-31 │ China        │   2 │         4 │
│ 2022-04-01 │ China        │   1 │         5 │
│ 2022-04-02 │ China        │   1 │         1 │
│ 2022-04-03 │ China        │   1 │         1 │
│ 2022-04-04 │ China        │   1 │         2 │
│ 2022-04-05 │              │   0 │         0 │
│ 2022-04-06 │              │   0 │         0 │
│ 2022-04-07 │              │   0 │         0 │
│ 2022-04-08 │              │   0 │         0 │
│ 2022-04-09 │              │   0 │         0 │
│ 2022-04-10 │              │   0 │         0 │
│ 2022-04-11 │              │   0 │         0 │
│ 2022-04-12 │              │   0 │         0 │
│ 2022-04-13 │              │   0 │         0 │
│ 2022-04-14 │              │   0 │         0 │
│ 2022-04-15 │              │   0 │         0 │
│ 2022-04-16 │              │   0 │         0 │
│ 2022-04-17 │              │   0 │         0 │
│ 2022-04-18 │              │   0 │         0 │
│ 2022-04-19 │              │   0 │         0 │
│ 2022-04-20 │              │   0 │         0 │
│ 2022-04-21 │ China        │   1 │         5 │
│ 2022-04-22 │              │   0 │         0 │
│ 2022-04-23 │              │   0 │         0 │
│ 2022-04-24 │              │   0 │         0 │
│ 2022-04-25 │              │   0 │         0 │
│ 2022-04-26 │              │   0 │         0 │
│ 2022-04-27 │              │   0 │         0 │
│ 2022-04-28 │ China        │   1 │         6 │
│ 2022-04-29 │ China        │   1 │         4 │
│ 2022-04-30 │ China        │   1 │        12 │
│ 2022-05-01 │ China        │   1 │        10 │
│ 2022-05-02 │ China        │   1 │         9 │
│ 2022-05-03 │ China        │   1 │         3 │
│ 2022-05-04 │ China        │   1 │         2 │
│ 2022-05-05 │ China        │   1 │         6 │
│ 2022-05-06 │ China        │   1 │         3 │
│ 2022-05-07 │ China        │   1 │         5 │
│ 2022-05-08 │ China        │   1 │         4 │
│ 2022-03-25 │ Hong Kong    │   2 │         5 │
│ 2022-03-26 │ Hong Kong    │   1 │         2 │
│ 2022-03-27 │ Hong Kong    │   1 │         2 │
│ 2022-03-28 │ Hong Kong    │   2 │        26 │
│ 2022-03-29 │ Hong Kong    │   2 │        56 │
│ 2022-03-30 │ Hong Kong    │   3 │        17 │
│ 2022-03-31 │ Hong Kong    │   4 │        12 │
│ 2022-04-01 │ Hong Kong    │   1 │         1 │
│ 2022-04-02 │ Hong Kong    │   2 │        23 │
│ 2022-04-06 │ Hong Kong    │   1 │         1 │
│ 2022-04-07 │ Hong Kong    │   2 │         9 │
│ 2022-04-08 │ Hong Kong    │   2 │        29 │
└────────────┴──────────────┴─────┴───────────┘

        3. 此时又出现了新的问题，即country_name产生了间隙，所以需要利用之前《Clickhouse 空缺值处理》这篇文章提到的“填充相邻值法”对间隙进行填充（arrayFill）。

with temp as( SELECT
    event_date,
    country_name,
    groupBitmapOr(active_user) AS dau,
    sum(use_times) AS use_times
FROM dws_app_global_active_retained
WHERE (country_name IN ('China', 'France', 'Hong Kong')) AND (event_date >= '2022-03-25')
GROUP BY
    event_date,
    country_name
ORDER BY
    country_name ASC,
    event_date ASC WITH FILL STEP 1 )

select tuple.1 as event_date, tuple.2 as country_name,tuple.3 as dau, tuple.4 as use_times from
(select  arrayJoin(
		arrayZip(
			groupArray(event_date),
			arrayFill(x ->x !='',groupArray(country_name)) ,
			groupArray(dau),
			groupArray(use_times) 
		)
	) as tuple
from temp)
┌─event_date─┬─country_name─┬─dau─┬─use_times─┐
│ 2022-03-27 │ China        │   1 │         1 │
│ 2022-03-28 │ China        │   1 │         2 │
│ 2022-03-29 │ China        │   2 │        64 │
│ 2022-03-30 │ China        │   1 │         4 │
│ 2022-03-31 │ China        │   2 │         4 │
│ 2022-04-01 │ China        │   1 │         5 │
│ 2022-04-02 │ China        │   1 │         1 │
│ 2022-04-03 │ China        │   1 │         1 │
│ 2022-04-04 │ China        │   1 │         2 │
│ 2022-04-05 │ China        │   0 │         0 │
│ 2022-04-06 │ China        │   0 │         0 │
│ 2022-04-07 │ China        │   0 │         0 │
│ 2022-04-08 │ China        │   0 │         0 │
│ 2022-04-09 │ China        │   0 │         0 │
│ 2022-04-10 │ China        │   0 │         0 │
│ 2022-04-11 │ China        │   0 │         0 │
│ 2022-04-12 │ China        │   0 │         0 │
│ 2022-04-13 │ China        │   0 │         0 │
│ 2022-04-14 │ China        │   0 │         0 │
│ 2022-04-15 │ China        │   0 │         0 │
│ 2022-04-16 │ China        │   0 │         0 │
│ 2022-04-17 │ China        │   0 │         0 │
│ 2022-04-18 │ China        │   0 │         0 │
│ 2022-04-19 │ China        │   0 │         0 │
│ 2022-04-20 │ China        │   0 │         0 │
│ 2022-04-21 │ China        │   1 │         5 │
│ 2022-04-22 │ China        │   0 │         0 │
│ 2022-04-23 │ China        │   0 │         0 │
│ 2022-04-24 │ China        │   0 │         0 │
│ 2022-04-25 │ China        │   0 │         0 │
│ 2022-04-26 │ China        │   0 │         0 │
│ 2022-04-27 │ China        │   0 │         0 │
│ 2022-04-28 │ China        │   1 │         6 │
│ 2022-04-29 │ China        │   1 │         4 │
│ 2022-04-30 │ China        │   1 │        12 │
│ 2022-05-01 │ China        │   1 │        10 │
│ 2022-05-02 │ China        │   1 │         9 │
│ 2022-05-03 │ China        │   1 │         3 │
│ 2022-05-04 │ China        │   1 │         2 │
│ 2022-05-05 │ China        │   1 │         6 │
│ 2022-05-06 │ China        │   1 │         3 │
│ 2022-05-07 │ China        │   1 │         5 │
│ 2022-05-08 │ China        │   1 │         4 │
│ 2022-03-25 │ Hong Kong    │   2 │         5 │
│ 2022-03-26 │ Hong Kong    │   1 │         2 │
│ 2022-03-27 │ Hong Kong    │   1 │         2 │
│ 2022-03-28 │ Hong Kong    │   2 │        26 │
│ 2022-03-29 │ Hong Kong    │   2 │        56 │
│ 2022-03-30 │ Hong Kong    │   3 │        17 │
│ 2022-03-31 │ Hong Kong    │   4 │        12 │
│ 2022-04-01 │ Hong Kong    │   1 │         1 │
│ 2022-04-02 │ Hong Kong    │   2 │        23 │
│ 2022-04-06 │ Hong Kong    │   1 │         1 │
│ 2022-04-07 │ Hong Kong    │   2 │         9 │
│ 2022-04-08 │ Hong Kong    │   2 │        29 │
└────────────┴──────────────┴─────┴───────────┘

最后

以上就是诚心大树为你收集整理的Clickhouse 消除由group by产生的间隙的全部内容，希望文章能够帮你解决Clickhouse 消除由group by产生的间隙所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(39)

相关文章

ClickHouse 知识点整理前文：一、概括二、集群三、优化四、表引擎五、Bitmap六、Flink—ClickHouse 代码
ClickHouse 知识点整理前文：一、概括二、集群三、优化四、表引擎五、Bitmap六、Flink—ClickHouse 代码
【ClickHouse】row_number() over (partition by)的几种实现方法1.row_number排序2.row_number排序后取出rank=1的结果3.特殊场景
【ClickHouse】row_number() over (partition by)的几种实现方法1.row_number排序2.row_number排序后取出rank=1的结果3.特殊场景
使用clickhouse映射加快查询速度
使用clickhouse映射加快查询速度
ClickHouse学习-建表和索引的优化点（一）
ClickHouse学习-建表和索引的优化点（一）
Clickhouse 消除由group by产生的间隙
Clickhouse 消除由group by产生的间隙
ClickHouse学习笔记Click House入门数据类型表引擎SQL操作副本执行计划Click House高级Explain查看执行计划建表优化Click House语法优化规则查询优化数据一致性（重点！！！）物化视图
ClickHouse学习笔记Click House入门数据类型表引擎SQL操作副本执行计划Click House高级Explain查看执行计划建表优化Click House语法优化规则查询优化数据一致性（重点！！！）物化视图
存储设计——如何优化 ClickHouse 索引（一）
存储设计——如何优化 ClickHouse 索引（一）
clickhouse 分区表使用前言
clickhouse 分区表使用前言

评论列表共有 0 条评论

立即
投稿
返回
顶部