基于ClickHouse的用户行为分析实践

2024-09-06 20:32
文章标签 分析 实践 行为 clickhouse 用户

本文主要是介绍基于ClickHouse的用户行为分析实践，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

点击上方蓝色字体，选择“设为星标”

回复”资源“获取更多资源

大数据技术与架构

点击右侧关注，大数据开发领域最强公众号！

暴走大数据

点击右侧关注，暴走大数据！

前言

ClickHouse为用户提供了丰富的多参聚合函数（parametric aggregate function）和基于数组+Lambda表达式的高阶函数（higher-order function），将它们灵活使用可以达到魔法般的效果。在我们的体系中，ClickHouse定位点击流数仓，所以下面举几个用它来做用户行为（路径）分析的实战例子，包括：

  • 路径匹配

  • 智能路径检测

  • 有序漏斗转化

  • 用户留存

  • Session统计


路径匹配

CK默认提供了sequenceMatch函数检查是否有事件链满足输入的模式，sequenceCount函数则统计满足输入模式的事件链的数量。示例：

SELECT site_id,sequenceMatch('(?1)(?t<=15)(?2).*(?3)')(ts_date_time,event_type = 'shtLogon',event_type = 'shtKkclick' AND column_type = 'homePage',event_type = 'shtAddCart') AS is_match
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;┌─site_id─┬─is_match─┐
│   10030 │        1 │
│   10339 │        1 │
│   10266 │        1 │
│   10022 │        1 │
└─────────┴──────────┘
SELECT site_id,sequenceCount('(?1)(?t<=15)(?2).*(?3)')(ts_date_time,event_type = 'shtLogon',event_type = 'shtKkclick' AND column_type = 'homePage',event_type = 'shtAddCart') AS seq_count
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;┌─site_id─┬─seq_count─┐
│   10030 │     33611 │
│   10339 │     14045 │
│   10266 │     74542 │
│   10022 │     31534 │
└─────────┴───────────┘

这两个函数都需要指定模式串、时间列和期望的事件序列（最多可指定32个事件）。模式串的语法有以下三种：

  • (?N)：表示时间序列中的第N个事件，从1开始。例如上述SQL中，(?2)即表示event_type = 'shtKkclick' AND column_type = 'homePage'。

  • (?t op secs)：插入两个事件之间，表示它们发生时需要满足的时间条件（单位为秒）。例如上述SQL中，(?1)(?t<=15)(?2)即表示事件1和2发生的时间间隔在15秒以内。

  • .*：表示任意的非指定事件。

智能路径检测

CK内置的sequenceMatch和sequenceCount函数只能满足部分需求，现有一个更复杂的需求：

给定期望的路径终点、途经点和最大事件时间间隔，查询出符合条件的路径详情及符合路径的用户数（按用户数降序排列）。

目前并没有现成的函数可以直接出结果，但是我们可以曲线救国，用数组和高阶函数的组合间接实现。完整SQL语句如下，略长：

SELECTresult_chain,uniqCombined(user_id) AS user_count
FROM (WITHtoUInt32(maxIf(ts_date_time, event_type = 'shtOrderDone')) AS end_event_maxt,arrayCompact(arraySort(x -> x.1,arrayFilter(x -> x.1 <= end_event_maxt,groupArray((toUInt32(ts_date_time), (event_type, column_type)))))) AS sorted_events,arrayEnumerate(sorted_events) AS event_idxs,arrayFilter((x, y, z) -> z.1 <= end_event_maxt AND (z.2.1 = 'shtOrderDone' OR y > 600),event_idxs,arrayDifference(sorted_events.1),sorted_events) AS gap_idxs,arrayMap(x -> x + 1, gap_idxs) AS gap_idxs_,arrayMap(x -> if(has(gap_idxs_, x), 1, 0), event_idxs) AS gap_masks,arraySplit((x, y) -> y, sorted_events, gap_masks) AS split_eventsSELECTuser_id,arrayJoin(split_events) AS event_chain_,arrayCompact(event_chain_.2) AS event_chain,hasAll(event_chain, [('shtKkClick', 'homePage')]) AS has_midway_hit,arrayStringConcat(arrayMap(x -> concat(x.1, '#', x.2),event_chain), ' -> ') AS result_chainFROM (SELECT ts_date,ts_date_time,event_type,column_type,user_idFROM ods.analytics_access_log_allWHERE ts_date >= '2020-06-30' AND ts_date <= '2020-07-02'AND site_id IN (10266,10022,10339,10030))GROUP BY user_idHAVING length(event_chain) > 1
)
WHERE event_chain[length(event_chain)].1 = 'shtOrderDone'
AND has_midway_hit = 1
GROUP BY result_chain
ORDER BY user_count DESC LIMIT 20;

简述思路：

  1. 将用户的行为用groupArray函数整理成<时间, <事件名, 页面名>>的元组，并用arraySort函数按时间升序排序；

  2. 利用arrayEnumerate函数获取原始行为链的下标数组；

  3. 利用arrayFilter和arrayDifference函数，过滤出原始行为链中的分界点下标。分界点的条件是路径终点或者时间差大于最大间隔；

  4. 利用arrayMap和has函数获取下标数组的掩码（由0和1组成的序列），用于最终切分，1表示分界点；

  5. 调用arraySplit函数将原始行为链按分界点切分成单次访问的行为链。注意该函数会将分界点作为新链的起始点，所以前面要将分界点的下标加1；

  6. 调用arrayJoin和arrayCompact函数将事件链的数组打平成多行单列，并去除相邻重复项。

  7. 调用hasAll函数确定是否全部存在指定的途经点。如果要求有任意一个途经点存在即可，就换用hasAny函数。当然，也可以修改WHERE谓词来排除指定的途经点。

  8. 将最终结果整理成可读的字符串，按行为链统计用户基数，完成。


有序漏斗转化

CK提供了windowFunnel函数实现漏斗，以指定时长（单位为秒）滑动窗口按序匹配事件链，并返回在窗口内转化到的步数。如有多种匹配，以步数最大（转换最深）的为准。

通过对该步数进行统计，即可得到漏斗中每步的转化率。SQL语句如下，查询结果是敏感数据，不再贴出来了。

SELECT level,user_count,conv_rate_percent
FROM (SELECT level,uniqCombined(user_id) AS user_count,neighbor(user_count, -1) AS prev_user_count,if (prev_user_count = 0, -1, round(user_count / prev_user_count * 100, 3)) AS conv_rate_percentFROM (SELECTuser_id,windowFunnel(900)(ts_date_time,event_type = 'shtLogon',event_type = 'shtKkClick' AND column_type = 'homePage',event_type = 'shtOpenGoodsDetail',event_type = 'shtAddCart',event_type = 'shtOrderDone') AS levelFROM (SELECT ts_date,ts_date_time,event_type,column_type,user_idFROM ods.analytics_access_log_allWHERE ts_date >= '2020-06-30' AND ts_date <= '2020-07-02'AND site_id IN (10266,10022,10339,10030))GROUP BY user_id)WHERE level > 0GROUP BY levelORDER BY level ASC
);

如果想要更准确一些，实现漏斗步骤之间的字段关联（如商品详情→加入购物车→下单三步中的商品ID关联）怎么办呢？可以利用https://github.com/housepower/olap2018项目中提出的xFunnel函数。它是windowFunnel函数的鼻祖，不过需要修改ClickHouse源码并重新编译之，今后有时间的话会简单写一下过程。

用户留存

retention函数可以方便地计算留存情况。该函数接受多个条件，以第一个条件的结果为基准，观察后面的各个条件是否也满足，若满足则置1，不满足则置0，最终返回0和1的数组。通过统计1的数量，即可计算出留存率。

下面的SQL语句计算次日重复下单率与七日重复下单率（语义与留存相同）。

SELECTsum(ret[1]) AS original,sum(ret[2]) AS next_day_ret,round(next_day_ret / original * 100, 3) AS next_day_ratio,sum(ret[3]) AS seven_day_ret,round(seven_day_ret / original * 100, 3) AS seven_day_ratio
FROM (WITH toDate('2020-06-24') AS first_dateSELECTuser_id,retention(ts_date = first_date,ts_date = first_date + INTERVAL 1 DAY,ts_date = first_date + INTERVAL 7 DAY) AS retFROM ods.ms_order_done_allWHERE ts_date >= first_date AND ts_date <= first_date + INTERVAL 7 DAYGROUP BY user_id
);

Session统计

Session，即"会话"，是指在指定的时间段内在网站/H5/小程序/APP上发生的一系列用户行为的集合。例如，一次会话可以包含多个页面浏览、交互事件等。Session是具备时间属性的，根据不同的切割规则，可以生成不同长度的Session。

可见，Session统计与上述智能路径检测的场景有相似之处，都需要寻找用户行为链的边界并进行切割。以下SQL语句以30分钟为超时时间，按天统计所有用户的Session总数（跨天的Session也会被切割）。

SELECT ts_date,sum(length(session_gaps)) AS session_cnt
FROM (WITHarraySort(groupArray(toUInt32(ts_date_time))) AS times,arrayDifference(times) AS times_diffSELECTts_date,arrayFilter(x -> x > 1800, times_diff) AS session_gapsFROM ods.analytics_access_log_allWHERE ts_date >= '2020-06-30'GROUP BY ts_date,user_id
)
GROUP BY ts_date;

版权声明：

本文为大数据技术与架构整理，原作者独家授权。未经原作者允许转载追究侵权责任。

编辑｜胡晓

微信公众号｜import_bigdata

欢迎点赞+收藏+转发朋友圈素质三连

文章不错？点个【在看】吧！ ????

这篇关于基于ClickHouse的用户行为分析实践的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1143042。

相关文章

基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
系统架构师考试学习笔记第三篇——架构设计高级知识（20）通信系统架构设计理论与实践

系统架构师考试学习笔记第三篇——架构设计高级知识（20）通信系统架构设计理论与实践

本章知识考点：         第20课时主要学习通信系统架构设计的理论和工作中的实践。根据新版考试大纲,本课时知识点会涉及案例分析题(25分),而在历年考试中,案例题对该部分内容的考查并不多,虽在综合知识选择题目中经常考查,但分值也不高。本课时内容侧重于对知识点的记忆和理解,按照以往的出题规律,通信系统架构设计基础知识点多来源于教材内的基础网络设备、网络架构和教材外最新时事热点技术。本课时知识
阅读更多...
SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

查看原文>>>全流程SWAP农业模型数据制备、敏感性分析及气候变化影响实践技术应用 SWAP模型是由荷兰瓦赫宁根大学开发的先进农作物模型，它综合考虑了土壤-水分-大气以及植被间的相互作用；是一种描述作物生长过程的一种机理性作物生长模型。它不但运用Richard方程，使其能够精确的模拟土壤中水分的运动，而且耦合了WOFOST作物模型使作物的生长描述更为科学。 本文让更多的科研人员和农业工作者
阅读更多...
MOLE 2.5 分析分子通道和孔隙

MOLE 2.5 分析分子通道和孔隙

软件介绍 生物大分子通道和孔隙在生物学中发挥着重要作用，例如在分子识别和酶底物特异性方面。 我们介绍了一种名为 MOLE 2.5 的高级软件工具，该工具旨在分析分子通道和孔隙。 与其他可用软件工具的基准测试表明，MOLE 2.5 相比更快、更强大、功能更丰富。作为一项新功能，MOLE 2.5 可以估算已识别通道的物理化学性质。 软件下载 https://pan.quark.cn/s/57
阅读更多...
衡石分析平台使用手册-单机安装及启动

衡石分析平台使用手册-单机安装及启动

单机安装及启动​ 本文讲述如何在单机环境下进行 HENGSHI SENSE 安装的操作过程。 在安装前请确认网络环境，如果是隔离环境，无法连接互联网时，请先按照 离线环境安装依赖的指导进行依赖包的安装，然后按照本文的指导继续操作。如果网络环境可以连接互联网，请直接按照本文的指导进行安装。 准备工作​ 请参考安装环境文档准备安装环境。 配置用户与安装目录。 在操作前请检查您是否有 sud
阅读更多...
线性因子模型 - 独立分量分析（ICA）篇

线性因子模型 - 独立分量分析（ICA）篇

序言 线性因子模型是数据分析与机器学习中的一类重要模型，它们通过引入潜变量（ latent variables \text{latent variables} latent variables）来更好地表征数据。其中，独立分量分析（ ICA \text{ICA} ICA）作为线性因子模型的一种，以其独特的视角和广泛的应用领域而备受关注。 ICA \text{ICA} ICA旨在将观察到的复杂信号
阅读更多...
【软考】希尔排序算法分析

【软考】希尔排序算法分析

目录 1. c代码2. 运行截图3. 运行解析 1. c代码 #include <stdio.h>#include <stdlib.h> void shellSort(int data[], int n){// 划分的数组，例如8个数则为[4, 2, 1]int *delta;int k;// i控制delta的轮次int i;// 临时变量，换值int temp;in
阅读更多...
三相直流无刷电机(BLDC)控制算法实现：BLDC有感启动算法思路分析

三相直流无刷电机(BLDC)控制算法实现：BLDC有感启动算法思路分析

一枚从事路径规划算法、运动控制算法、BLDC/FOC电机控制算法、工控、物联网工程师，爱吃土豆。如有需要技术交流或者需要方案帮助、需求：以下为联系方式—V 方案1：通过霍尔传感器IO中断触发换相 1.1 整体执行思路 霍尔传感器U、V、W三相通过IO+EXIT中断的方式进行霍尔传感器数据的读取。将IO口配置为上升沿+下降沿中断触发的方式。当霍尔传感器信号发生发生信号的变化就会触发中断在中断
阅读更多...
【Kubernetes】K8s 的安全框架和用户认证

【Kubernetes】K8s 的安全框架和用户认证

K8s 的安全框架和用户认证 1.Kubernetes 的安全框架1.1 认证：Authentication1.2 鉴权：Authorization1.3 准入控制：Admission Control 2.Kubernetes 的用户认证2.1 Kubernetes 的用户认证方式2.2 配置 Kubernetes 集群使用密码认证 Kubernetes 作为一个分布式的虚拟
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2