ClickHouse应用随笔2——活跃用户留存统计(前方高能~~)

已于 2022-05-11 18:12:11 修改
阅读量4.6k 收藏 16
点赞数 4
分类专栏： Metabase 文章标签： sql 数据库
于 2021-04-04 22:45:38 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44181744/article/details/115430787
版权
本文详细介绍了如何利用ClickHouse查询语句计算用户活跃留存率，包括次留、三留、七留等，涉及多表连接、日期差计算和留存率计算。并展示了如何通过时间筛选交互框实现指定日期范围的查询，提高业务效率同时避免对数据库造成过大压力。
摘要由CSDN通过智能技术生成

前言

众所周知，产品数据监控体系中，用户留存是一个非常重要的指标，细化到业务场景中，留存其实可以拆解出来多个类别：包括新增用户留存、活跃用户留存、付费用户留存、回流用户留存、新增付费用户留存、回流付费用户留存等等，以下主要针对活跃用户留存设计ClickHouse查询语句。

表结构及查询需求

表结构

timeuser_id
January 1, 2021, 08:33 AM112233abc
January 1, 2021, 09:00 AM221133bac
January 1, 2021, 09:20 AM113322acb
January 2, 2021, 10:12 AM113322acb
January 2, 2021, 11:01 AM221133bac
January 3, 2021, 11:45 AM112233abc
…………

查询需求：在表 User_login 中，按指定时间区间给出该区间内每日的活跃次留、三留、七留、十四留、三十留。

ClickHouse语句如何实现

PS：涉及多重嵌套查询

  1. 查询每个 user_id 的登录时间间隔(按天)，按日期分组，得到每个 user_id 两次登录的时间差；
select uid,
	base_date,
	retention_date
from -- 表a，首日登录的用户
	(select distinct 
		date(time) base_date, 
		user_id uid
	from User_login
	where -- 需要计算留存的日期区间
		base_date between '2021-03-01' and '2021-03-31'
	group by uid,
		base_date) a
left join
	-- 表b，表连接匹配后续登录的情况，计算留存
	(select distinct 
		date(time) retention_date, 
		user_id uid
	from User_login
	where -- 此处不应限制最大日期，否则可能导致后续留存率为0
		retention_date >= '2021-03-01'
	group by uid,
		retention_date) b
on a.uid = b.uid
where -- 保证每条记录不会连接到在当前记录时间之前的数据，如昨天的登录
	base_date <= retention_date
order by uid,
	base_date

结果如下（demo数据）：
在这里插入图片描述

  1. 利用 retention_date 减去 base_date 得到两日登录的日期差值；
select uid,
	base_date,
	retention_date,
	retention_date - base_date datediff
from /* 上一步的查询
	(select uid,
		base_date,
		retention_date
	from -- 表a，首日登录的用户
		(select distinct 
			date(time) base_date, 
			user_id uid
		from User_login
		where -- 需要计算留存的日期区间
			base_date between '2021-03-01' and '2021-03-31'
		group by uid,
			base_date) a
	left join -- 表b，表连接匹配后续登录的情况，计算留存
		(select distinct 
			date(time) retention_date, 
			user_id uid
		from User_login
		where -- 此处不应限制最大日期，否则可能导致后续留存率为0
			retention_date >= '2021-03-01'
		group by uid,
			retention_date) b
	on a.uid = b.uid
	where -- 保证每条记录不会连接到在当前记录时间之前的数据，如昨天的登录
		base_date <= retention_date
	order by uid,
		base_date) c
	*/
order by uid,
	base_date

结果如下（demo数据）：
在这里插入图片描述

  1. 对应提取相应的两次登录的日期差，得到当日、次日、三日、七日等多个登录用户数的值；
select base_date,
	sum(case when datediff = 0 then 1 else 0 end) day_0,
	sum(case when datediff = 1 then 1 else 0 end) day_1,
	sum(case when datediff = 2 then 1 else 0 end) day_2,
	sum(case when datediff = 6 then 1 else 0 end) day_6,
	sum(case when datediff = 13 then 1 else 0 end) day_13,
	sum(case when datediff = 29 then 1 else 0 end) day_29
from /* 上一步的查询
	(select uid,
		base_date,
		retention_date,
		retention_date - base_date datediff
	from (select uid,
			base_date,
			retention_date
		from -- 表a，首日登录的用户
			(select distinct 
				date(time) base_date, 
				user_id uid
			from User_login
			where -- 需要计算留存的日期区间
				base_date between '2021-03-01' and '2021-03-31'
			group by uid,
				base_date) a
		left join -- 表b，表连接匹配后续登录的情况，计算留存
			(select distinct 
				date(time) retention_date, 
				user_id uid
			from User_login
			where -- 此处不应限制最大日期，否则可能导致后续留存率为0
				retention_date >= '2021-03-01'
			group by uid,
				retention_date) b
		on a.uid = b.uid
		where -- 保证每条记录不会连接到在当前记录时间之前的数据，如昨天的登录
			base_date <= retention_date
		order by uid,
			base_date) c
	order by uid,
		base_date) d
	*/
group by base_date
order by base_date

结果如下（demo数据，一共查询出31天每天的各个留存情况，其中day_0是当日的登录）：
在这里插入图片描述

  1. 利用次日、三、七日等登录数除以当日登录数，得到对应留存率；
select base_date,
    -- 保留4位小数，用于百分比展示
    round(day_1/day_0, 4) retention_2,
    round(day_2/day_0, 4) retention_3,
    round(day_6/day_0, 4) retention_7,
    round(day_13/day_0, 4) retention_14,
    round(day_29/day_0, 4) retention_30
from /* 上一步的查询
	(select base_date,
		sum(case when datediff = 0 then 1 else 0 end) day_0,
		sum(case when datediff = 1 then 1 else 0 end) day_1,
		sum(case when datediff = 2 then 1 else 0 end) day_2,
		sum(case when datediff = 6 then 1 else 0 end) day_6,
		sum(case when datediff = 13 then 1 else 0 end) day_13,
		sum(case when datediff = 29 then 1 else 0 end) day_29
	from (select uid,
			base_date,
			retention_date,
			retention_date - base_date datediff
		from (select uid,
				base_date,
				retention_date
			from -- 表a，首日登录的用户
				(select distinct 
					date(time) base_date, 
					user_id uid
				from User_login
				where -- 需要计算留存的日期区间
					base_date between '2021-03-01' and '2021-03-31'
				group by uid,
					base_date) a
			left join -- 表b，表连接匹配后续登录的情况，计算留存
				(select distinct 
					date(time) retention_date, 
					user_id uid
				from User_login
				where -- 此处不应限制最大日期，否则可能导致后续留存率为0
					retention_date >= '2021-03-01'
				group by uid,
					retention_date) b
			on a.uid = b.uid
			where -- 保证每条记录不会连接到在当前记录时间之前的数据，如昨天的登录
				base_date <= retention_date
			order by uid,
				base_date) c
		order by uid,
			base_date) d
	group by base_date
	order by base_date
	*/

结果如下（demo数据）：
在这里插入图片描述
到这里所有活跃留存就查询完毕了，百分比的显示只需在metabase设置中调整一下即可，效果如下：
在这里插入图片描述
emmm…总感觉还是差点啥，是的没错！还有一环！！

  1. 增加日期选择交互框，按指定日期进行查询；

这也是最后一步了，如果在编写查询语句时就把查询时间写死了，当业务方想在平台上查看留存时，还得专门找数据小伙伴来调整时间，这不仅给数据侧带来更多工作量，还降低了业务方工作效率；

如果不限制这个时间，则每次查询都要把整个数据表都过一遍，这就不仅影响到查询效率了，当数据量日渐增多，这查询语句也会给数据库带来越来越大的压力；

因此可以使用时间筛选交互框的方式，让业务方自己选择想要查看的日期区间对应的留存情况，操作如下：（在日期筛选时把条件设为 {{date}} 形式，再在设置里将交互框调整为日期类型即可）

select base_date,
    -- 保留4位小数，用于百分比展示
    round(day_1/day_0, 4) retention_2,
    round(day_2/day_0, 4) retention_3,
    round(day_6/day_0, 4) retention_7,
    round(day_13/day_0, 4) retention_14,
    round(day_29/day_0, 4) retention_30
from (select base_date,
		sum(case when datediff = 0 then 1 else 0 end) day_0,
		sum(case when datediff = 1 then 1 else 0 end) day_1,
		sum(case when datediff = 2 then 1 else 0 end) day_2,
		sum(case when datediff = 6 then 1 else 0 end) day_6,
		sum(case when datediff = 13 then 1 else 0 end) day_13,
		sum(case when datediff = 29 then 1 else 0 end) day_29
	from (select uid,
			base_date,
			retention_date,
			retention_date - base_date datediff
		from (select uid,
				base_date,
				retention_date
			from -- 表a，首日登录的用户
				(select distinct 
					date(time) base_date, 
					user_id uid
				from User_login
				where -- 需要计算留存的日期区间
					-- 利用时间选择交互，手动选择时间区间后再运行
					base_date between {{start_date}} and {{end_date}}
				group by uid,
					base_date) a
			left join -- 表b，表连接匹配后续登录的情况，计算留存
				(select distinct 
					date(time) retention_date, 
					user_id uid
				from User_login
				where -- 此处不应限制最大日期，否则可能导致后续留存率为0
					retention_date >= {{start_date}}
				group by uid,
					retention_date) b
			on a.uid = b.uid
			where -- 保证每条记录不会连接到在当前记录时间之前的数据，如昨天的登录
				base_date <= retention_date
			order by uid,
				base_date) c
		order by uid,
			base_date) d
	group by base_date
	order by base_date)

效果如下：
在这里插入图片描述
在这里插入图片描述
大
功
告
成

制了个了个杖
关注
专栏目录
clickhouse数据模型之留存分析
马斯特杨的博客
10-12 2089
clickhouse数据模型之留存分析 什么是留存，比如在20200701这天操作了“点击banner”的用户有100个，这部分用户在20200702这天操作了“点击app签到”的有20个，那么对于分析时间是20200701，且“点击banner”的用户在次日“点击app签到”的留存率是20%。 背景 关于用户留存模型是各大商业数据分析平台必不可少的功能，企业一般用该模型衡量用户的活跃情况，也是能直接反应产品功能价值的直接指标；如，boss想要了解商城改版后，对用户加购以及后续下单情况的影响等。如
clickhouse 留存分析
cxs812760493的博客
07-08 389
retention函数 retention(cond1, cond2, ..., cond32); ## cond 为判断条件 ## 支持最长32个参数的输入，也就是说 至少支持一个完整自然月的留存分析查询
clickHouse留存计算
xkxdqj的博客
12-09 408
click House计算留存
Clickhouse 在大数据分析平台 - 留存分析
魂影魔宅
06-16 683
背景 你可能听说过Growingio、神策等数据分析平台，本文主要介绍实现留存分析工具相关的内容。留存分析是一种用来分析用户参与情况/活跃程度的分析模型，可考查进行初始行为后的用户中，有多少人会进行后续行为，这是衡量产品对用户价值高低的重要指标。如，为评估产品更新效果或渠道推广效果，我们常常需要对同期进入产品或同期使用了产品某个功能的用户的后续行为表现进行评估 [1]。大部分数据分析平台主要包括如图的几个功能(以神策为例) 本文主要介绍留存分析工具的优化方案（只涉及数据存储和查询的方案设计，不涉及平台）。
clickhouse join_ClickHouse留存分析工具十亿数据秒级查询方案
weixin_39951419的博客
11-22 520
作者：陈璐，腾讯 CSIG 高级数据分析师本文实践了对于千万级别的用户，操作总数达万级别，每日几十亿操作流水的留存分析工具秒级别查询的数据构建方案。同时，除了留存分析，对于用户群分析，事件分析等也可以尝试用此方案来解决。背景你可能听说过Growingio、神策等数据分析平台，本文主要介绍实现留存分析工具相关的内容。留存分析是一种用来分析用户参与情况/活跃程度的分析模型，可考查进行初始行为后的用户中...
随笔记录——FCOS及其应用
AI Flash
02-12 2223
Paper地址：https://arxiv.org/abs/1904.01355 FCOS简介 FCOS是全卷积、单阶段、无锚框类型的目标检测器（Fully Convolutional One-stage Anchor-free Objector Detector），自提出以来被广泛应用于图像/视频内容理解领域，包括商品检索、Logo检测等应用。FCOS总体结构、及特点如下： 全卷积网络： Backbone、Neck与Head均为全卷积构造，以便于模块化建模、可扩展应用与推理优化支持（如Ten
学习随笔——张明明.doc
12-23
【学习随笔——张明明.doc】这篇文档记录了作者张明明在进行网络远程研修学习过程中的感悟和收获。从内容来看，这篇文章突出了几个重要的教育理念和学习方向： 1. **转变观念**：首先，张明明认识到教育观念的转变...
随笔记录——不同模态信号、表征与应用
AI Flash
01-23 3786
深度学习模型（主要是感知类模型）接受的输入信号，主要包括图像、文本、语音等信号。不同模态的输入信号，经过模型的逐层抽象、转换之后，转变为不同程度的抽象表示，并应用于不同的任务场景。 如上图所示，深度学习模型包含前处理、浅层、深层、任务相关层与后处理多个阶段，不同阶段的输入/输出具备不同的含义，简述如下（图像信号处理以CNN模型为例、语音/文本信号处理以BERT/Transformer模型为例）： 输入信号：模型接收的输入信号，是原始的、或仅初步加工的数据（raw data），包括自然信号（图像、语音
红楼随笔——所谓狗尾续貂-用于合并.doc
10-08
《红楼梦》是中国古典文学的一颗璀璨明珠，作者曹雪芹以其独特的人物塑造和深刻的社会洞察，使得这部作品成为中国四大名著之一。然而，《红楼梦》的后四十回由高鹗所续，这部分内容常被读者批评为“狗尾续貂”，在...
教学随笔——怎样提高学生的习作水平.doc
11-16
教学随笔——怎样提高学生的习作水平.doc
Clickhouse 留存方法对比retention 和bitmapAndCardinality
工作中学习，学习中进步
06-01 1346
最近在做留存统计，看了一篇使用位图函数写留存的，效率很高，所以跟同事说了下，同事测试了一下发现效率很高； 表结构 CREATE TABLE tb_book_chapter_request_record ( `id` UInt64 COMMENT '自增ID', `uid` UInt64 COMMENT '用户ID', `bid` UInt64 COMMENT '书籍ID', `chapterno` UInt64 COMMENT '章节排序值', `client` UI
Clickhouse 实现留存分析与数据可视化
qq_40694671的博客
04-18 1783
Clickhouse 实现留存分析于数据可视化 1、前言 2、环境准备 3、留存分析实现代码 4、留存图的pyecharts实现
Clickhouse 专栏---数据模型之留存分析
东城庞太师
09-23 581
什么是留存，比如在20200701这天操作了“点击banner”的用户有100个，这部分用户在20200702这天操作了“点击app签到”的有20个，那么对于分析时间是20200701，且“点击banner”的用户在次日“点击app签到”的留存率是20%。 背景 关于用户留存模型是各大商业数据分析平台必不可少的功能，企业一般用该模型衡量用户的活跃情况，也是能直接反应产品功能价值的直接指标；如，boss想要了解商城改版后，对用户加购以及后续下单情况的影响等。如下图，这就是一个典型的留存分析功能：
一段sql完成一个用户留存率计算报表，这就是clickhouse的魅力
大力水手z博客
03-04 566
一段sql，就将整个报表的数据全部计算出来了，相比之前用定时脚本去统计mysql，效率也高了不少。个人感觉还是蛮实用的，希望能对大家有帮助。
Clickhouse在大数据分析平台-留存分析上的应用
张普的专栏
01-15 443
导语|本文实践了对于千万级别的用户，操作总数达万级别，每日几十亿操作流水的留存分析工具秒级别查询的数据构建方案。同时，除了留存分析，对于用户群分析，事件分析等也可以尝试用此方案来解决。...
clIckhouse 游戏数据分析实战，计算留存LTV
最新发布
qq_30884175的博客
06-14 184
clIckhouse 游戏数据分析实战，计算留存LTV。
clickhouse（二、高阶函数应用retention计算留存数）
yyoc97的专栏
06-22 6825
文章目录前言准备查询结论 前言 clickhouse提供retention(cond1, cond2, …)函数方便计算用户留存率，当然也可以应用在其他需求上。 准备 建表 CREATE TABLE login_log -- 用户登录日志 ( uid Int32, -- 用户唯一id login_time DateTime -- 用户登录时间 ) ENGINE = MergeTree P...
腾讯 Clickhouse 在大数据分析平台-留存分析上的应用
SmartSi
04-05 264
本文实践了对于千万级别的用户，操作总数达万级别，每日几十亿操作流水的留存分析工具秒级别查询的数据构建方案。同时，除了留存分析，对于用户群分析，事件分析等也可以尝试用此方案来解决。
Clickhouse中bitmap介绍以及计算留存Demo
老骆驼的博客
03-09 768
参考了腾迅的大数据分析-计算留存，能够根据用户自定义属性，以及玩家行为进行留存的计算。最初计算留存的方法使用的是clickhosue自带的rentention函数，使用这个函数不用关注太多细节，只需要把留存条件放入函数即可。但是这个如果需要关联用户属性，就比较麻烦了。因此参考了bitmap实现自定义留存。这里先介绍几个bitmap相关的函数。
51单片机C语言入门教程——使用KEILuVISION2
教程作者分享了学习笔记和随笔，内容包括C语言相对于汇编语言的优势，以及如何使用KEIL uVision2集成开发环境进行C程序的编写和调试。" 在51单片机的学习中，C语言是一种常见的编程语言，因为它具有较高的可读性...
评论 10
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

制了个了个杖

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值